Migrations - Drizzle ORM

Drizzle Kit generates SQL migration files with metadata for tracking and applying database schema changes.

Migration Files

File Structure

Migrations are stored in the output directory (default: drizzle/) with the following structure:

drizzle/
├── meta/
│   ├── _journal.json
│   └── 0000_snapshot.json
├── 0000_initial.sql
└── 0001_add_users.sql

SQL Migration File

Generated SQL files contain DDL statements with optional breakpoints:

CREATE TABLE "users" (
  "id" serial PRIMARY KEY NOT NULL,
  "name" text NOT NULL,
  "email" text NOT NULL,
  "created_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE UNIQUE INDEX "email_idx" ON "users" ("email");
--> statement-breakpoint
CREATE INDEX "created_at_idx" ON "users" ("created_at");

Breakpoints (--> statement-breakpoint):

Enable sequential execution of statements
Required for MySQL, SQLite, and SingleStore (no multi-statement transactions)
Optional for PostgreSQL (supports multi-statement transactions)

Migration Metadata

Journal File (`meta/_journal.json`)

Tracks all migrations in order:

{
  "version": "7",
  "dialect": "postgresql",
  "entries": [
    {
      "idx": 0,
      "version": "7",
      "when": 1709553600000,
      "tag": "0000_initial",
      "breakpoints": true
    },
    {
      "idx": 1,
      "version": "7",
      "when": 1709640000000,
      "tag": "0001_add_users",
      "breakpoints": true
    }
  ]
}

string

Drizzle Kit metadata format version

string

Database dialect: postgresql, mysql, sqlite, etc.

array

List of migration entries in chronological order

number

Sequential migration index

string

Migration filename (without .sql)

number

Unix timestamp (milliseconds) when migration was created

boolean

Whether migration uses statement breakpoints

Snapshot File (`meta/XXXX_snapshot.json`)

Complete schema snapshot at migration time:

{
  "version": "7",
  "dialect": "postgresql",
  "id": "a1b2c3d4-e5f6-4a5b-8c9d-0e1f2a3b4c5d",
  "prevId": "00000000-0000-0000-0000-000000000000",
  "tables": {
    "users": {
      "name": "users",
      "schema": "",
      "columns": {
        "id": {
          "name": "id",
          "type": "serial",
          "primaryKey": true,
          "notNull": true
        },
        "name": {
          "name": "name",
          "type": "text",
          "primaryKey": false,
          "notNull": true
        },
        "email": {
          "name": "email",
          "type": "text",
          "primaryKey": false,
          "notNull": true
        }
      },
      "indexes": {
        "email_idx": {
          "name": "email_idx",
          "columns": ["email"],
          "isUnique": true
        }
      },
      "foreignKeys": {},
      "compositePrimaryKeys": {},
      "uniqueConstraints": {}
    }
  },
  "enums": {},
  "schemas": {},
  "sequences": {},
  "_meta": {
    "schemas": {},
    "tables": {},
    "columns": {}
  }
}

Snapshots enable:

Detecting schema changes between migrations
Generating accurate diff SQL
Validating migration consistency

Migration Workflow

1. Generate Migration

Detect schema changes and create migration:

drizzle-kit generate

Process:

Reads current Drizzle schema from schema files
Compares with latest snapshot in meta/
Generates SQL diff statements
Creates new migration file with timestamp/index
Updates _journal.json and creates new snapshot

2. Review Migration

Inspect generated SQL before applying:

cat drizzle/0001_*.sql

Check for:

Correct DDL statements
Data migration needs
Potential data loss (DROP statements)
Index creation order

3. Apply Migration

Execute migrations against database:

drizzle-kit migrate

Process:

Connects to database using dbCredentials
Creates migrations table if not exists
Queries applied migrations from database
Applies pending migrations in order
Records each migration in migrations table

4. Verify Changes

Open Drizzle Studio to verify:

drizzle-kit studio

Programmatic API

Use migration functions directly in your application.

PostgreSQL

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(pool);

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

await pool.end();

MySQL

import { drizzle } from 'drizzle-orm/mysql2';
import { migrate } from 'drizzle-orm/mysql2/migrator';
import mysql from 'mysql2/promise';

const connection = await mysql.createConnection({
  host: 'localhost',
  user: 'root',
  database: 'mydb',
});

const db = drizzle(connection);

await migrate(db, { 
  migrationsFolder: './drizzle',
});

await connection.end();

SQLite

import { drizzle } from 'drizzle-orm/better-sqlite3';
import { migrate } from 'drizzle-orm/better-sqlite3/migrator';
import Database from 'better-sqlite3';

const sqlite = new Database('local.db');
const db = drizzle(sqlite);

migrate(db, { 
  migrationsFolder: './drizzle',
});

sqlite.close();

Turso (LibSQL)

import { drizzle } from 'drizzle-orm/libsql';
import { migrate } from 'drizzle-orm/libsql/migrator';
import { createClient } from '@libsql/client';

const client = createClient({
  url: process.env.TURSO_DATABASE_URL!,
  authToken: process.env.TURSO_AUTH_TOKEN,
});

const db = drizzle(client);

await migrate(db, { 
  migrationsFolder: './drizzle',
});

Migration Options

string

required

Path to migrations directory

{ migrationsFolder: './drizzle' }

string

default:"__drizzle_migrations"

Custom migrations tracking table name

{ migrationsTable: 'custom_migrations' }

string

default:"drizzle"

PostgreSQL: Custom schema for migrations table

{ migrationsSchema: 'public' }

Custom Migrations

Generate Empty Migration

Create migration file for manual SQL:

drizzle-kit generate --custom --name=seed_data

Generated file:

-- Custom SQL migration
-- Add your SQL here

Add your SQL statements:

-- Seed initial data
INSERT INTO roles (name) VALUES 
  ('admin'),
  ('user'),
  ('guest');
--> statement-breakpoint
INSERT INTO settings (key, value) VALUES 
  ('site_name', 'My App'),
  ('maintenance_mode', 'false');

Data Migrations

Combine DDL and data changes:

-- Add new column
ALTER TABLE "users" ADD COLUMN "status" text DEFAULT 'active' NOT NULL;
--> statement-breakpoint
-- Migrate existing data
UPDATE "users" SET "status" = 'inactive' WHERE "last_login" < NOW() - INTERVAL '1 year';
--> statement-breakpoint
-- Add constraint
ALTER TABLE "users" ADD CONSTRAINT "status_check" CHECK ("status" IN ('active', 'inactive', 'suspended'));

Migration Prefixes

Control migration file naming:

Index (default)

0000_initial.sql
0001_add_users.sql
0002_add_posts.sql

Config:

migrations: {
  prefix: 'index',
}

Timestamp

20240304120000_initial.sql
20240304130000_add_users.sql

Config:

migrations: {
  prefix: 'timestamp',
}

Unix Timestamp

1709553600_initial.sql
1709640000_add_users.sql

Config:

migrations: {
  prefix: 'unix',
}

Supabase Format

20240304120000_initial.sql

Compatible with Supabase migration format. Config:

migrations: {
  prefix: 'supabase',
}

No Prefix

initial.sql
add_users.sql

Config:

migrations: {
  prefix: 'none',
}

Using none requires unique migration names and careful ordering.

Migration Management

Drop Migration

Remove the last migration:

drizzle-kit drop

Interactive prompt:

? Select migration to remove
❯ 0002_add_posts.sql
  0001_add_users.sql
  Cancel

This:

Removes migration SQL file
Removes snapshot file
Updates _journal.json
Does NOT revert applied migrations in database

To revert database changes, manually create a new migration or restore from backup.

Check Migrations

Validate migration consistency:

drizzle-kit check

Verifies:

Migration files exist for all journal entries
Snapshot files are valid JSON
No duplicate migration indices
SQL syntax is parseable

Upgrade Migrations

Update migration format after Drizzle Kit upgrades:

drizzle-kit up

This updates:

Metadata version in snapshots
Journal format
Snapshot schema structure

Preserves:

SQL migration files (unchanged)
Migration order
Applied migrations in database

Best Practices

1. Version Control

Commit all migration files:

git add drizzle/
git commit -m "Add users table migration"

2. Review Before Apply

Always review generated SQL:

# Generate
drizzle-kit generate

# Review
cat drizzle/0001_*.sql

# Apply only after review
drizzle-kit migrate

3. One-Way Migrations

Migrations are forward-only. To undo:

# Create reverse migration
drizzle-kit generate --name=revert_users

Manually write rollback SQL:

DROP TABLE "users";

4. Test Migrations

Test on development database first:

# Dev environment
export DATABASE_URL=postgresql://localhost/myapp_dev
drizzle-kit migrate

# Verify
drizzle-kit studio

# Then production
export DATABASE_URL=postgresql://prod/myapp
drizzle-kit migrate

5. Backup Before Migration

Always backup production before migrating:

# PostgreSQL
pg_dump myapp > backup.sql

# Then migrate
drizzle-kit migrate

6. Handle Data Migration

For complex data transformations:

-- Add new column
ALTER TABLE "users" ADD COLUMN "full_name" text;
--> statement-breakpoint
-- Migrate data
UPDATE "users" SET "full_name" = "first_name" || ' ' || "last_name";
--> statement-breakpoint
-- Make NOT NULL after data migration
ALTER TABLE "users" ALTER COLUMN "full_name" SET NOT NULL;
--> statement-breakpoint
-- Drop old columns
ALTER TABLE "users" DROP COLUMN "first_name";
--> statement-breakpoint
ALTER TABLE "users" DROP COLUMN "last_name";

7. Use Named Migrations

Descriptive names for clarity:

drizzle-kit generate --name=add_user_roles
drizzle-kit generate --name=create_posts_table
drizzle-kit generate --name=add_email_verification

Troubleshooting

Migration Already Applied

Error: Migration has already been applied Solution: Check migrations table:

SELECT * FROM drizzle.__drizzle_migrations;

Manually remove if needed (use with caution).

Conflicting Migrations

Error: Migration conflict detected Solution:

Pull latest migrations from version control
Regenerate migration with latest schema
Resolve conflicts manually

Breakpoint Errors (MySQL/SQLite)

Error: Multi-statement execution not supported Solution: Enable breakpoints:

export default defineConfig({
  breakpoints: true,
});

Snapshot Mismatch

Error: Schema snapshot doesn’t match database Solution:

Ensure all migrations are applied: drizzle-kit migrate
Regenerate snapshot: drizzle-kit generate
Or pull fresh schema: drizzle-kit pull

Production Deployment

Run migrations on application startup:

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(pool);

async function runMigrations() {
  console.log('Running migrations...');
  
  try {
    await migrate(db, { migrationsFolder: './drizzle' });
    console.log('Migrations completed successfully');
  } catch (error) {
    console.error('Migration failed:', error);
    process.exit(1);
  }
}

// Run before starting server
runMigrations().then(() => {
  startServer();
});

Or use dedicated migration script:

// scripts/migrate.ts
import { migrate } from './migrate-function';

migrate()
  .then(() => {
    console.log('Migrations complete');
    process.exit(0);
  })
  .catch((err) => {
    console.error('Migration error:', err);
    process.exit(1);
  });

Package.json:

{
  "scripts": {
    "migrate": "tsx scripts/migrate.ts",
    "deploy": "npm run migrate && npm start"
  }
}

​Migration Files

​File Structure

​SQL Migration File

​Migration Metadata

​Journal File (meta/_journal.json)

​Snapshot File (meta/XXXX_snapshot.json)

​Migration Workflow

​1. Generate Migration

​2. Review Migration

​3. Apply Migration

​4. Verify Changes

​Programmatic API

​PostgreSQL

​MySQL

​SQLite

​Turso (LibSQL)

​Migration Options

​Custom Migrations

​Generate Empty Migration

​Data Migrations

​Migration Prefixes

​Index (default)

​Timestamp

​Unix Timestamp

​Supabase Format

​No Prefix

​Migration Management

​Drop Migration

​Check Migrations

​Upgrade Migrations

​Best Practices

​1. Version Control

​2. Review Before Apply

​3. One-Way Migrations

​4. Test Migrations

​5. Backup Before Migration

​6. Handle Data Migration

​7. Use Named Migrations

​Troubleshooting

​Migration Already Applied

​Conflicting Migrations

​Breakpoint Errors (MySQL/SQLite)

​Snapshot Mismatch

​Production Deployment

Migration Files

File Structure

SQL Migration File

Migration Metadata

Journal File (`meta/_journal.json`)

Snapshot File (`meta/XXXX_snapshot.json`)

Migration Workflow

1. Generate Migration

2. Review Migration

3. Apply Migration

4. Verify Changes

Programmatic API

PostgreSQL

MySQL

SQLite

Turso (LibSQL)

Migration Options

Custom Migrations

Generate Empty Migration

Data Migrations

Migration Prefixes

Index (default)

Timestamp

Unix Timestamp

Supabase Format

No Prefix

Migration Management

Drop Migration

Check Migrations

Upgrade Migrations

Best Practices

1. Version Control

2. Review Before Apply

3. One-Way Migrations

4. Test Migrations

5. Backup Before Migration

6. Handle Data Migration

7. Use Named Migrations

Troubleshooting

Migration Already Applied

Conflicting Migrations

Breakpoint Errors (MySQL/SQLite)

Snapshot Mismatch

Production Deployment