Database Migrations

​

Migration Files Location

drizzle/
  migrations/
    0000_init.sql
    0001_seed-categories.sql
    0002_seed-feature-flags-allow-signup.sql
    0003_use-single-display-name.sql
    ...
    meta/
      _journal.json
      0000_snapshot.json
      0001_snapshot.json
      ...
  migrate/              # Standalone migration runner
    migrate.ts
    package.json

​

Configuration

import { defineConfig } from 'drizzle-kit'

export default defineConfig({
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DB_URL!,
    ssl: false,
  },
  out: './drizzle/migrations',        // Migration output directory
  schema: './src/server/db/schema.ts', // Schema source
  casing: 'snake_case',                // Database naming convention
  strict: true,
  migrations: {
    schema: 'public',
    table: '__drizzle_migrations',     // Migration tracking table
  },
})

​

Database Schema Organization

• src/server/db/schema.ts - Central export that combines all schemas
• src/server/db/schemas/ - Individual table definitions:
  • user.ts, session.ts, account.ts - Authentication
  • bank-account.ts - Bank accounts
  • transaction.ts - Financial transactions
  • category.ts - Categories
  • budget.ts - Budgets
  • currency-rate.ts - Exchange rates
  • group.ts, group-user.ts - Groups
  • user-settings.ts - User settings
  • feature-flags.ts - Feature flags
  • enums.ts - Shared enums

​

Common Migration Commands

​

Apply Migrations

pnpm run db:migrate

1. Connects to the database specified in DB_URL
2. Checks the __drizzle_migrations table
3. Applies any pending migrations in order

​

Generate New Migration

pnpm run db:generate

1. Compare your schema to the current database state
2. Generate SQL migration files
3. Update the metadata in drizzle/migrations/meta/

​

Push Schema (Development Only)

pnpm run db:push

​

Open Drizzle Studio

pnpm run db:studio

​

Development Environment Workflow

​

Using Development Environment

# Generate migration using .env.development
pnpm run db:generate:dev

# Apply migrations to development database
pnpm run db:migrate:dev

# Push schema to development database
pnpm run db:push:dev

# Open Studio for development database
pnpm run db:studio:dev

​

Creating Migrations

​

Step-by-Step Workflow

1. Modify the schema Edit files in src/server/db/schemas/, for example transaction.ts:
   export const transactions = pgTable('transaction', {
     id: serial('id').primaryKey(),
     description: text('description').notNull(),
     amount: decimal('amount', { precision: 10, scale: 2 }).notNull(),
     // Add new column
     notes: text('notes'),
   })
   
2. Generate the migration
   pnpm run db:generate
   
   Drizzle Kit will prompt you for a migration name or auto-generate one.
3. Review the migration Check the generated SQL in drizzle/migrations/[number]_[name].sql:
   ALTER TABLE "transaction" ADD COLUMN "notes" text;
   
4. Apply the migration
   pnpm run db:migrate
   
5. Commit both the schema and migration files
   git add src/server/db/schemas/transaction.ts
   git add drizzle/migrations/
   git commit -m "Add notes column to transactions"
   

​

Patching Migrations

pnpm run db:patch-migrations

# Or for development environment
pnpm run db:patch-migrations:dev

​

Production Migrations

​

Docker Entrypoint

# From entrypoint.sh
./drizzle/migrate/node_modules/.bin/tsx \
  ./drizzle/migrate/migrate.ts \
  --db-url="$DB_URL" \
  --migrations-folder=./drizzle/migrations

​

Manual Production Migrations

DB_URL="postgres://user:pass@host:5432/db" pnpm run db:migrate

cd drizzle/migrate
node migrate.js \
  --db-url="postgres://user:pass@host:5432/db" \
  --migrations-folder=../migrations

​

Migration Tracking

SELECT * FROM __drizzle_migrations ORDER BY created_at DESC;

​

Best Practices

​

Do:

• Always generate migrations for schema changes in production
• Review generated SQL before applying migrations
• Test migrations on a copy of production data
• Commit migrations with schema changes in the same PR
• Use descriptive migration names when prompted
• Back up production database before major migrations

​

Don’t:

• Don’t use db:push in production - it bypasses migration history
• Don’t edit applied migrations - create a new migration instead
• Don’t skip migrations - apply them in order
• Don’t commit schema changes without migrations

​

Troubleshooting

​

Migration Already Applied

1. Check __drizzle_migrations table
2. Verify the migration hash matches
3. If developing locally, consider resetting your database

​

Migration Failed

1. Check PostgreSQL logs for errors
2. Verify your database user has sufficient permissions
3. Manually fix the database state if needed
4. Remove the failed entry from __drizzle_migrations
5. Re-run the migration

​

Schema Out of Sync

1. Run pnpm run db:generate to see the diff
2. Apply with pnpm run db:migrate
3. Or use pnpm run db:push if in development

​

Related Resources

• Drizzle ORM Documentation
• Drizzle Kit Commands
• Project Structure - Understanding the database layer