Migrations

Manage database schema changes safely with Drizzle ORM’s migration system

Overview

Drizzle provides a robust migration system that generates SQL migration files from your schema changes. Migrations ensure your database schema stays in sync with your code across environments.

Setup

Install Drizzle Kit

Drizzle Kit is the CLI companion for managing migrations:

npm install -D drizzle-kit

Configuration File

Create a drizzle.config.ts file in your project root:

PostgreSQL

import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  dialect: 'postgresql',
  schema: './src/schema.ts',
  out: './drizzle',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});

Generating Migrations

Generate Migration

After modifying your schema, generate a migration:

npx drizzle-kit generate

This creates SQL migration files in your configured output directory:

drizzle/
├── 0000_init.sql
├── 0001_add_users_table.sql
├── 0002_add_posts_table.sql
└── meta/
    ├── _journal.json
    └── 0000_snapshot.json

Migration Files

Generated SQL files contain the DDL statements:

drizzle/0001_add_users_table.sql

CREATE TABLE IF NOT EXISTS "users" (
	"id" serial PRIMARY KEY NOT NULL,
	"name" varchar(256) NOT NULL,
	"email" text NOT NULL,
	"created_at" timestamp DEFAULT now() NOT NULL,
	CONSTRAINT "users_email_unique" UNIQUE("email")
);

Running Migrations

Programmatic Migration

Run migrations from your application code:

migrate.ts

import { drizzle } from 'drizzle-orm/node-postgres';
import { migrate } from 'drizzle-orm/node-postgres/migrator';
import { Pool } from 'pg';

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});

const db = drizzle({ client: pool });

async function main() {
  console.log('Running migrations...');
  
  await migrate(db, { migrationsFolder: './drizzle' });
  
  console.log('Migrations complete!');
  
  await pool.end();
}

main();

Run the migration script:

npx tsx migrate.ts

Migration Using Drizzle Kit

Alternatively, use Drizzle Kit directly:

npx drizzle-kit migrate

Migration Configuration

Custom Migration Table

Configure the migrations tracking table:

import { migrate } from 'drizzle-orm/node-postgres/migrator';

await migrate(db, {
  migrationsFolder: './drizzle',
  migrationsTable: 'custom_migrations',
  migrationsSchema: 'public',
});

Multiple Schema Directories

drizzle.config.ts

import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  dialect: 'postgresql',
  schema: ['./src/schema/*.ts', './src/auth/schema.ts'],
  out: './drizzle',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});

Migration Workflows

Development Workflow

1. Modify schema

   Update your schema files with new tables, columns, or constraints:

   schema.ts

   export const users = pgTable('users', {
     id: serial('id').primaryKey(),
     name: text('name').notNull(),
     email: text('email').notNull(),
     // Add new column
     role: text('role').default('user'),
   });
   
2. Generate migration

   npx drizzle-kit generate
   

   Review the generated SQL to ensure it matches your intent.

3. Run migration

   npx tsx migrate.ts
   

   Or use Drizzle Kit:

   npx drizzle-kit migrate
   
4. Commit changes

   Commit both schema changes and migration files:

   git add src/schema.ts drizzle/
   git commit -m "Add role column to users table"
   

Production Deployment

1. Test migrations

   Run migrations in a staging environment first:

   DATABASE_URL=staging_url npx tsx migrate.ts
   
2. Backup database

   Always backup your production database before running migrations:

   pg_dump -U postgres mydb > backup.sql
   
3. Run migrations

   Execute migrations during deployment:

   DATABASE_URL=production_url npx tsx migrate.ts
   
4. Verify

   Verify the migration succeeded:

   npx drizzle-kit check
   

Introspection

Pull from Database

Generate schema from an existing database:

npx drizzle-kit introspect

This creates TypeScript schema files from your database structure.

Push to Database

Quickly push schema changes without generating migration files (development only):

npx drizzle-kit push

drizzle-kit push is destructive and should only be used in development. Always use migrations in production.

Custom Migrations

Manual SQL

Create custom migration files for complex changes:

drizzle/0003_custom.sql

-- Custom data migration
UPDATE users 
SET role = 'admin' 
WHERE email LIKE '[e-mail]';

-- Custom index
CREATE INDEX CONCURRENTLY idx_users_email_lower 
ON users(LOWER(email));

Breaking Point Notation

For queries that must run separately:

CREATE TABLE users (
  id serial PRIMARY KEY
);

--> statement-breakpoint

CREATE INDEX idx_users_id ON users(id);

Migration Metadata

Journal File

The _journal.json tracks migration history:

drizzle/meta/_journal.json

{
  "version": "6",
  "dialect": "postgresql",
  "entries": [
    {
      "idx": 0,
      "version": "6",
      "when": 1704067200000,
      "tag": "0000_init",
      "breakpoints": true
    },
    {
      "idx": 1,
      "version": "6",
      "when": 1704153600000,
      "tag": "0001_add_users_table",
      "breakpoints": true
    }
  ]
}

Snapshot Files

Snapshot files store the schema state at each migration:

drizzle/meta/0001_snapshot.json

{
  "version": "6",
  "dialect": "postgresql",
  "tables": {
    "users": {
      "name": "users",
      "columns": {
        "id": {
          "name": "id",
          "type": "serial",
          "primaryKey": true,
          "notNull": true
        },
        "name": {
          "name": "name",
          "type": "text",
          "primaryKey": false,
          "notNull": true
        }
      }
    }
  }
}

Common Patterns

Adding Columns

// Before
export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
});

// After
export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  email: text('email'), // New column
});

Generated migration:

ALTER TABLE "users" ADD COLUMN "email" text;

Renaming Columns

Drizzle Kit detects renames by comparing snapshots:

// The migration will contain:
ALTER TABLE "users" RENAME COLUMN "old_name" TO "new_name";

Adding Indexes

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: text('email').notNull(),
}, (table) => [{
  emailIdx: index('email_idx').on(table.email),
}]);

Changing Column Types

// Before: text
email: text('email')

// After: varchar
email: varchar('email', { length: 255 })

Migration:

ALTER TABLE "users" ALTER COLUMN "email" TYPE varchar(255);

Best Practices

• Version control: Always commit migration files with schema changes
• Review migrations: Check generated SQL before running in production
• Incremental changes: Make small, focused schema changes
• Backup first: Always backup production before migrations
• Test thoroughly: Run migrations in staging before production
• Avoid push: Use migrations in production, not drizzle-kit push

• Never edit generated migration files unless absolutely necessary
• Don’t delete migration files that have run in production
• Don’t modify the _journal.json or snapshot files manually

Troubleshooting

Migration Conflicts

If team members generate migrations simultaneously:

1. Pull latest migrations from version control
2. Regenerate your migration: npx drizzle-kit generate
3. Resolve any conflicts in the schema files
4. Commit both schema and new migrations

Failed Migrations

If a migration fails midway:

1. Check the error message
2. Fix the issue (schema or database state)
3. Consider using transactions to ensure atomicity
4. For PostgreSQL/SQLite, migrations run in transactions by default

Rollback

Drizzle doesn’t auto-generate rollback migrations. Create manual down migrations:

drizzle/0001_rollback.sql

-- Rollback for 0001_add_users_table.sql
DROP TABLE IF EXISTS "users";

Next Steps

Transactions

Learn about atomic database operations

Schema Declaration

Review schema definition patterns