Generating Custom SQL Migrations in Drizzle Kit

Drizzle Kit generates tidy SQL migrations from your TypeScript schema, and for most additive changes that is all you need. Then comes a change the generator cannot express safely: an index that must be built CONCURRENTLY on a large PostgreSQL table, a foreign key that must be added NOT VALID and validated later, a batched backfill between two schema steps, a trigger for dual-writes. Editing a generated migration works for some of these, but the cleanest tool is drizzle-kit generate --custom, which creates an empty, correctly registered migration file for SQL you write yourself. This guide shows how to use custom migrations for the zero-downtime cases the generator misses, how they fit Drizzle’s journal and snapshots, and where the non-transactional statements need special handling. It extends Drizzle ORM Type Sync.

Symptom / Error Signatures

You need a custom migration when one of these appears:

• A generated migration contains CREATE INDEX "orders_customer_ref_idx" ON "orders" USING btree ("customer_ref"); for a large table, and running it blocks writes.
• You edited a generated file to add CONCURRENTLY, and the migrator failed with CREATE INDEX CONCURRENTLY cannot run inside a transaction block.
• A change needs a data backfill between two generated steps, and there is no place for it.
• Someone added a SQL file to the migrations folder by hand, and the migrator ignored it because it was not in meta/_journal.json — or drizzle-kit generate later produced a migration that repeated its changes because the snapshot never learned about them.

Root Cause Analysis

Drizzle Kit’s migration folder has three parts: numbered SQL files, meta/_journal.json listing them in order, and a snapshot JSON per migration describing the schema model after it. drizzle-kit generate diffs the TypeScript schema against the latest snapshot and writes a new SQL file, journal entry and snapshot. The runtime migrator (migrate() from drizzle-orm/<driver>/migrator) reads the journal, runs files not yet recorded in its migrations table (__drizzle_migrations, in the drizzle schema on PostgreSQL), and records each one.

generate --custom creates the same three parts with an empty SQL file, so hand-written SQL becomes a first-class migration: it has a journal entry, it is recorded when applied, and its snapshot is a copy of the previous one. That last property matters — a custom migration must not change what the TypeScript schema describes, or the next generate will try to make the same change again. Use custom migrations for how a change is applied (online DDL, backfills, triggers), and let generated migrations describe what the schema is.

The remaining constraint is transactions. On PostgreSQL the Drizzle migrator applies pending migrations inside a transaction, so CREATE INDEX CONCURRENTLY cannot run through it, custom file or not. Statements that need to run outside a transaction belong in a separate pipeline step, as described in running concurrent index builds outside migration transactions.

Immediate Mitigation

1. Create an empty custom migration.

# Shell · project root · drizzle.config.ts points at the schema and migrations folder
npx drizzle-kit generate --custom --name=orders_customer_fk_not_valid
# writes drizzle/0009_orders_customer_fk_not_valid.sql, a journal entry and a copied snapshot

2. Write the online form of the change. For a foreign key, add it without validation in one custom migration and validate in the next. Declare the relationship in the TypeScript schema too, so the model matches — but generate before declaring it, or remove the generated statement, so the constraint is not created twice.

-- PostgreSQL · drizzle/0009_orders_customer_fk_not_valid.sql · applied by the Drizzle migrator
-- WARNING: new rows are checked immediately; existing rows are checked by the next migration.
SET LOCAL lock_timeout = '3s';
ALTER TABLE "orders" ADD CONSTRAINT "orders_customer_fk"
  FOREIGN KEY ("customer_id") REFERENCES "customers" ("id") NOT VALID;
-- ROLLBACK PATH: ALTER TABLE "orders" DROP CONSTRAINT IF EXISTS "orders_customer_fk";

-- PostgreSQL · drizzle/0010_orders_customer_fk_validate.sql · SHARE UPDATE EXCLUSIVE, writes continue
ALTER TABLE "orders" VALIDATE CONSTRAINT "orders_customer_fk";

3. Run concurrent index builds as a separate step. Keep them out of the Drizzle folder and run them idempotently before or after the migrator in the deploy pipeline:

# Shell · deploy pipeline step · runs outside any transaction (psql autocommit)
psql "$MIGRATION_URL" -v ON_ERROR_STOP=1 -c "SET lock_timeout = '3s'" \
  -c 'CREATE INDEX CONCURRENTLY IF NOT EXISTS "orders_customer_ref_idx" ON "orders" ("customer_ref")'

Declare the index in the TypeScript schema as well so the model knows about it, and make sure the generated migration that would create it is either removed or guarded with IF NOT EXISTS so it becomes a no-op after the concurrent step has run.

4. Check the journal after every manual change. Every SQL file in the folder must appear in meta/_journal.json; files added by hand without --custom are silently ignored by the migrator.

Permanent Fix / Long-Term Pattern

Adopt a clear division: generated migrations for schema model changes, custom migrations for online techniques and data, and a separate pipeline step for anything that must run outside a transaction. Review custom SQL with the same rigour as any hand-written migration — engine comment, lock timeout, rollback path — and keep each custom file to one purpose. Pair it with a CI check that regenerates migrations from the schema and fails if anything new appears (drizzle-kit generate producing a file means the schema and snapshots disagree), and a check that drizzle-kit check reports the snapshot history as consistent.

For data work, prefer background jobs over migrations for large tables, as described in Backfill Optimization, and remember that drizzle-kit push bypasses all of this — it applies the schema diff directly without migration files — which is why it belongs only in development, as discussed in Drizzle push vs migrate in production.

Verification Checklist

• Every SQL file in the migrations folder has an entry in meta/_journal.json.
• Custom migrations do not change the schema model; running drizzle-kit generate afterwards produces no new file.
• Constraints on large tables are added NOT VALID and validated in a separate custom migration.
• Concurrent index builds run in a separate, idempotent pipeline step, not through the Drizzle migrator.
• __drizzle_migrations records every journal entry after the deploy.

Frequently Asked Questions

What does drizzle-kit generate --custom create? An empty SQL migration file with the next sequence number, a matching entry in meta/_journal.json, and a snapshot identical to the previous one. You fill the SQL file with statements the generator cannot produce.

Can I just add a SQL file to the folder by hand? Not reliably. The migrator follows the journal, so a file without a journal entry is ignored. Always create files with generate or generate --custom.

Why can’t a custom migration contain CREATE INDEX CONCURRENTLY? The Drizzle migrator applies migrations inside a transaction on PostgreSQL, and concurrent index builds cannot run inside one. Run them as a separate pipeline step with psql or a small script that uses autocommit.

Will a custom migration confuse the next generate? Only if it changes something the TypeScript schema does not describe, or describes something differently. Keep the TypeScript schema as the description of the final state and use custom SQL only for the path to it.