> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify.com/drizzle-team/drizzle-orm/llms.txt
> Use this file to discover all available pages before exploring further.

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

```bash theme={null}
npm install -D drizzle-kit
```

### Configuration File

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

<CodeGroup>
  ```typescript PostgreSQL theme={null}
  import { defineConfig } from 'drizzle-kit';

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

  ```typescript MySQL theme={null}
  import { defineConfig } from 'drizzle-kit';

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

  ```typescript SQLite theme={null}
  import { defineConfig } from 'drizzle-kit';

  export default defineConfig({
    dialect: 'sqlite',
    schema: './src/schema.ts',
    out: './drizzle',
    dbCredentials: {
      url: './sqlite.db',
    },
  });
  ```
</CodeGroup>

## Generating Migrations

### Generate Migration

After modifying your schema, generate a migration:

```bash theme={null}
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:

```sql drizzle/0001_add_users_table.sql theme={null}
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:

```typescript migrate.ts theme={null}
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:

```bash theme={null}
npx tsx migrate.ts
```

### Migration Using Drizzle Kit

Alternatively, use Drizzle Kit directly:

```bash theme={null}
npx drizzle-kit migrate
```

## Migration Configuration

### Custom Migration Table

Configure the migrations tracking table:

```typescript theme={null}
import { migrate } from 'drizzle-orm/node-postgres/migrator';

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

### Multiple Schema Directories

```typescript drizzle.config.ts theme={null}
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

<Steps>
  <Step title="Modify schema">
    Update your schema files with new tables, columns, or constraints:

    ```typescript schema.ts theme={null}
    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'),
    });
    ```
  </Step>

  <Step title="Generate migration">
    ```bash theme={null}
    npx drizzle-kit generate
    ```

    Review the generated SQL to ensure it matches your intent.
  </Step>

  <Step title="Run migration">
    ```bash theme={null}
    npx tsx migrate.ts
    ```

    Or use Drizzle Kit:

    ```bash theme={null}
    npx drizzle-kit migrate
    ```
  </Step>

  <Step title="Commit changes">
    Commit both schema changes and migration files:

    ```bash theme={null}
    git add src/schema.ts drizzle/
    git commit -m "Add role column to users table"
    ```
  </Step>
</Steps>

### Production Deployment

<Steps>
  <Step title="Test migrations">
    Run migrations in a staging environment first:

    ```bash theme={null}
    DATABASE_URL=staging_url npx tsx migrate.ts
    ```
  </Step>

  <Step title="Backup database">
    Always backup your production database before running migrations:

    ```bash theme={null}
    pg_dump -U postgres mydb > backup.sql
    ```
  </Step>

  <Step title="Run migrations">
    Execute migrations during deployment:

    ```bash theme={null}
    DATABASE_URL=production_url npx tsx migrate.ts
    ```
  </Step>

  <Step title="Verify">
    Verify the migration succeeded:

    ```bash theme={null}
    npx drizzle-kit check
    ```
  </Step>
</Steps>

## Introspection

### Pull from Database

Generate schema from an existing database:

```bash theme={null}
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):

```bash theme={null}
npx drizzle-kit push
```

<Warning>
  `drizzle-kit push` is destructive and should only be used in development. Always use migrations in production.
</Warning>

## Custom Migrations

### Manual SQL

Create custom migration files for complex changes:

```sql drizzle/0003_custom.sql theme={null}
-- 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:

```sql theme={null}
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:

```json drizzle/meta/_journal.json theme={null}
{
  "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:

```json drizzle/meta/0001_snapshot.json theme={null}
{
  "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

```typescript theme={null}
// 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:

```sql theme={null}
ALTER TABLE "users" ADD COLUMN "email" text;
```

### Renaming Columns

Drizzle Kit detects renames by comparing snapshots:

```typescript theme={null}
// The migration will contain:
ALTER TABLE "users" RENAME COLUMN "old_name" TO "new_name";
```

### Adding Indexes

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

### Changing Column Types

```typescript theme={null}
// Before: text
email: text('email')

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

Migration:

```sql theme={null}
ALTER TABLE "users" ALTER COLUMN "email" TYPE varchar(255);
```

## Best Practices

<Note>
  * **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`
</Note>

<Warning>
  * 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
</Warning>

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

```sql drizzle/0001_rollback.sql theme={null}
-- Rollback for 0001_add_users_table.sql
DROP TABLE IF EXISTS "users";
```

## Next Steps

<CardGroup cols={2}>
  <Card title="Transactions" icon="rotate" href="/core/transactions">
    Learn about atomic database operations
  </Card>

  <Card title="Schema Declaration" icon="table" href="/core/schema-declaration">
    Review schema definition patterns
  </Card>
</CardGroup>