Migrations - Drizzle ORM

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:

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

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'),
});

Generate migration

npx drizzle-kit generate

Review the generated SQL to ensure it matches your intent.

Run migration

npx tsx migrate.ts

Or use Drizzle Kit:

npx drizzle-kit migrate

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

Test migrations

Run migrations in a staging environment first:

DATABASE_URL=staging_url npx tsx migrate.ts

Backup database

Always backup your production database before running migrations:

pg_dump -U postgres mydb > backup.sql

Run migrations

Execute migrations during deployment:

DATABASE_URL=production_url npx tsx migrate.ts

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 '%@company.com';

-- 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:

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

Failed Migrations

If a migration fails midway:

Check the error message
Fix the issue (schema or database state)
Consider using transactions to ensure atomicity
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";

Get Started

Core Concepts

Database Drivers

Query Builders

Schema Definition

Advanced Features

Ecosystem

Guides

​Overview

​Setup

​Install Drizzle Kit

​Configuration File

​Generating Migrations

​Generate Migration

​Migration Files

​Running Migrations

​Programmatic Migration

​Migration Using Drizzle Kit

​Migration Configuration

​Custom Migration Table

​Multiple Schema Directories

​Migration Workflows

​Development Workflow

​Production Deployment

​Introspection

​Pull from Database

​Push to Database

​Custom Migrations

​Manual SQL

​Breaking Point Notation

​Migration Metadata

​Journal File

​Snapshot Files

​Common Patterns

​Adding Columns

​Renaming Columns

​Adding Indexes

​Changing Column Types

​Best Practices

​Troubleshooting

​Migration Conflicts

​Failed Migrations

​Rollback

​Next Steps

Transactions

Schema Declaration

Build docs developers (and LLMs) love

Overview

Setup

Install Drizzle Kit

Configuration File

Generating Migrations

Generate Migration

Migration Files

Running Migrations

Programmatic Migration

Migration Using Drizzle Kit

Migration Configuration

Custom Migration Table

Multiple Schema Directories

Migration Workflows

Development Workflow

Production Deployment

Introspection

Pull from Database

Push to Database

Custom Migrations

Manual SQL

Breaking Point Notation

Migration Metadata

Journal File

Snapshot Files

Common Patterns

Adding Columns

Renaming Columns

Adding Indexes

Changing Column Types

Best Practices

Troubleshooting

Migration Conflicts

Failed Migrations

Rollback

Next Steps