Skip to main content

​
Overview

Umami uses database migrations to evolve the schema over time. Migrations are automatically applied during the build process or when starting the application in Docker.
Migrations are managed by Prisma for PostgreSQL and custom SQL scripts for ClickHouse.

​
How Migrations Work

Migrations are versioned changes to the database schema that:
  • Create new tables and columns
  • Add or modify indexes
  • Transform existing data
  • Ensure schema compatibility across versions
  • Managed by Prisma Migrate
  • Located in /prisma/migrations/
  • Automatically tracked in _prisma_migrations table
  • Applied sequentially in order

​
Automatic Migration

Migrations run automatically in several scenarios:

​
During Build

When building from source: 
pnpm build
The build script includes:
package.json
{
  "scripts": {
    "build": "npm-run-all check-env build-db check-db build-tracker build-geo build-app",
    "build-db": "npm-run-all build-db-client build-prisma-client",
    "build-db-client": "prisma generate",
    "update-db": "prisma migrate deploy"
  }
}

​
Docker Startup

When starting the Docker container: 
docker compose up -d
The startup script runs:
package.json
{
  "scripts": {
    "start-docker": "npm-run-all check-db update-tracker start-server",
    "check-db": "node scripts/check-db.js"
  }
}
The check-db.js script:
  1. Validates DATABASE_URL is set
  2. Tests database connection
  3. Checks PostgreSQL version (minimum 9.4.0)
  4. Runs prisma migrate deploy
Set SKIP_DB_MIGRATION=1 to prevent automatic migrations on startup.

​
Manual Migration

​
PostgreSQL

Run migrations manually: 
# Deploy pending migrations
pnpm prisma migrate deploy

# Check migration status
pnpm prisma migrate status

# Generate Prisma client
pnpm prisma generate

​
ClickHouse

ClickHouse migrations are applied automatically by the application. For manual application: 
# Apply schema
clickhouse-client --multiquery < db/clickhouse/schema.sql

# Apply specific migration
clickhouse-client --multiquery < db/clickhouse/migrations/08_update_hostname_view.sql

​
Migration History

​
PostgreSQL Migrations

Located in /prisma/migrations/, each migration has its own directory:
Creates the foundation:
  • User authentication tables
  • Website configuration
  • Session tracking
  • Event data storage
  • Basic indexes
This is the largest migration, establishing all core functionality.
Adds reporting features:
  • Report configuration tables
  • Session data attributes
  • Report parameters storage
Enables saved reports and custom session properties.
Performance optimization:
  • Adds composite indexes for common queries
  • Optimizes website_event lookups
  • Improves session query performance
Significantly speeds up dashboard queries.
Team collaboration features:
  • Team management tables
  • Team membership relationships
  • Access control
Enables multi-user team functionality.
Visit tracking:
  • Adds visit_id column to events
  • Creates indexes for visit-based queries
Enables visit-level analytics (distinct from sessions).
Enhanced session tracking:
  • Session data table improvements
  • Additional indexes for session lookups
  • Data type optimizations
Event tagging:
  • Adds tag column to website_event
  • Creates tag indexes
Allows categorizing events with custom tags.
Marketing attribution:
  • UTM parameters (utm_source, utm_medium, utm_campaign, utm_content, utm_term)
  • Click IDs (gclid, fbclid, msclkid, ttclid, li_fat_id, twclid)
  • Indexes for attribution queries
Comprehensive marketing campaign tracking.
Geographic and hostname tracking:
  • Hostname field for multi-domain tracking
  • Region/subdivision tracking
  • Geographic indexes
User identification:
  • Adds distinct_id for user tracking across sessions
  • Indexes for user-level analytics
Enables cross-session user analytics.

​
ClickHouse Migrations

Located in /db/clickhouse/migrations/:
MigrationDescription
01_edit_keys.sqlSchema key adjustments
02_add_visit_id.sqlVisit tracking support
03_session_data.sqlSession data table
04_add_tag.sqlEvent tag support
05_add_utm_clid.sqlUTM and click ID columns
06_update_subdivision.sqlGeographic subdivision tracking
07_add_distinct_id.sqlUser identification
08_update_hostname_view.sqlMaterialized view updates

​
Migration Safety

​
Backup Before Migration

Always backup your database before running migrations, especially in production!
# Create backup
pg_dump -U umami -h localhost umami > backup-$(date +%Y%m%d).sql

# Compressed backup
pg_dump -U umami -h localhost -Fc umami > backup-$(date +%Y%m%d).dump

​
Test Migrations

Test migrations in a development environment first: 
# 1. Clone production database to test environment
pg_dump -U umami production-host -Fc umami | pg_restore -U umami -d umami_test

# 2. Run migration on test database
DATABASE_URL=postgresql://user:pass@localhost/umami_test pnpm prisma migrate deploy

# 3. Verify application works
DATABASE_URL=postgresql://user:pass@localhost/umami_test pnpm start

# 4. If successful, apply to production

​
Viewing Migration Status

​
PostgreSQL

Check which migrations have been applied: 
# Using Prisma CLI
pnpm prisma migrate status
Output: 
Database schema is up to date!

The following migrations have been applied:

migrations/
  └─ 01_init/
      └─ migration.sql
  └─ 02_report_schema_session_data/
      └─ migration.sql
  ...
Query the migrations table directly: 
SELECT
  migration_name,
  finished_at,
  applied_steps_count
FROM _prisma_migrations
ORDER BY finished_at DESC;

​
ClickHouse

ClickHouse doesn’t have a built-in migration tracking system. Check schema version: 
-- Check if table exists
EXISTS TABLE umami.website_event;

-- Check table structure
DESCRIBE TABLE umami.website_event;

-- Verify specific columns exist
SELECT name FROM system.columns
WHERE database = 'umami' AND table = 'website_event'
AND name IN ('visit_id', 'tag', 'distinct_id');

​
Rollback Strategies

Prisma doesn’t support automatic rollback. You must manually revert changes.

​
Restore from Backup

The safest rollback method: 
# PostgreSQL
pg_restore -U umami -d umami -c backup.dump

# Or from SQL file
psql -U umami umami < backup.sql

​
Manual Rollback

For simple migrations, manually reverse the changes: 
-- If migration added a column
ALTER TABLE website_event DROP COLUMN new_column;

-- If migration added a table
DROP TABLE new_table;

-- If migration added an index
DROP INDEX index_name;
Then mark the migration as not applied: 
DELETE FROM _prisma_migrations
WHERE migration_name = 'migration_to_rollback';

​
Data Migrations

Some migrations include data transformations. These are in /db/postgresql/data-migrations/:

​
populate-revenue-table.sql

Populates the revenue table from event data: 
-- Extracts revenue data from custom events
INSERT INTO revenue (revenue_id, website_id, session_id, event_id, ...)
SELECT ... FROM event_data WHERE data_key = 'revenue';

​
convert-utm-clid-columns.sql

Migrates UTM and click ID data from event_data to dedicated columns: 
-- Moves utm_source from event_data to website_event column
UPDATE website_event we
SET utm_source = ed.string_value
FROM event_data ed
WHERE ed.event_id = we.event_id AND ed.data_key = 'utm_source';
Data migrations may take considerable time on large databases. Run during low-traffic periods.

​
Troubleshooting Migrations

Usually caused by existing data violating new constraints.
  1. Check the error message for the specific constraint
  2. Identify problematic data:
-- Example: Find null values where NOT NULL is required
SELECT * FROM website_event WHERE column_name IS NULL;
  1. Fix or remove problematic data
  2. Retry migration
Large migrations may timeout:
  1. Increase timeout in Prisma:
DATABASE_URL="postgresql://...?connect_timeout=300"
  1. Or run migration directly with psql:
psql -U umami umami < migration.sql
If migration tracking is out of sync:
# Mark migration as resolved
pnpm prisma migrate resolve --applied migration_name

# Or mark as rolled back
pnpm prisma migrate resolve --rolled-back migration_name
Manual changes to the database caused drift:
# View drift
pnpm prisma migrate status

# Reset to schema (DESTRUCTIVE)
pnpm prisma migrate reset

# Or create migration from current state
pnpm prisma migrate dev

​
Best Practices

Always Backup

Create backups before running migrations, especially in production.

Test First

Test migrations on a copy of production data before applying.

Monitor Progress

Watch logs during migration for errors or warnings.

Plan Downtime

Large migrations may require application downtime.

​
Migration Checklist

  • Backup database
  • Test migration in staging environment
  • Check disk space (migrations may temporarily double space)
  • Review migration SQL for expected changes
  • Plan maintenance window if needed
  • Notify users of potential downtime
  • Have rollback plan ready
  • Monitor migration logs
  • Watch database performance metrics
  • Check for lock conflicts
  • Verify migration completes successfully
  • Verify application functionality
  • Check migration status
  • Review database logs for errors
  • Test critical user workflows
  • Monitor performance for issues
  • Keep backup until stability confirmed

​
Skipping Migrations

In some scenarios, you may want to skip automatic migrations: 
# Skip database check and migration
SKIP_DB_CHECK=1 pnpm start

# Skip only migration (still checks connection)
SKIP_DB_MIGRATION=1 pnpm start
Only skip migrations if you’re managing schema manually. Running Umami with outdated schema will cause errors.

​
Creating Custom Migrations

If you’re modifying Umami’s source: 
# Create a new migration
pnpm prisma migrate dev --name description_of_changes

# This will:
# 1. Generate migration SQL
# 2. Apply it to your database
# 3. Regenerate Prisma Client
Custom migrations are for development only. Production deployments should use official migrations.

​
Next Steps

PostgreSQL

Configure PostgreSQL database

ClickHouse

Set up ClickHouse for scale

Upgrading

Update Umami to new versions

Troubleshooting

Solve migration issues

Build docs developers (and LLMs) love

Get started for freeTalk to us