Database migrations are application changes, not just SQL files. This guide compares Prisma Migrate, Drizzle Kit, Knex, and hand-written SQL, then gives you a reusable checklist for reviewing schema changes, validating them in CI, deploying without avoidable downtime, and responding when a migration fails.
Overview
A migration workflow should make four things clear: what changes, how the change is applied, how application code remains compatible during deployment, and what happens if the release must be stopped. The safest choice is therefore less about selecting a universally “best” tool and more about matching the workflow to your database, team, deployment process, and tolerance for manual SQL.
| Workflow | Best fit | Strengths | Trade-offs to review |
|---|---|---|---|
| Prisma Migrate | Teams already using Prisma as their data-access layer | Schema-driven development, generated migration history, and a clear relationship between the Prisma schema and client | Generated SQL still needs review; production deployment and rollback planning remain your responsibility |
| Drizzle Kit | TypeScript projects that prefer schema definitions close to application code | Type-safe schema workflow and generated migration files that can be inspected before deployment | Confirm the selected migration mode, generated-file conventions, and database-specific SQL before relying on automation |
| Knex | Teams wanting a programmable JavaScript or TypeScript migration layer | Explicit migration code, broad control over operations, and a familiar runner-based model | More decisions are left to the team, including reversibility, safety checks, and SQL quality |
| Hand-written SQL | Complex, performance-sensitive, or database-specific changes | Maximum control over indexes, constraints, backfills, locks, and vendor-specific features | Requires disciplined naming, ordering, testing, observability, and a reliable migration runner |
Whichever workflow you choose, commit migration files to version control and treat them as an append-only record. Avoid editing a migration that has already run in a shared environment. Create a new corrective migration instead. Before using any command in a build pipeline, verify it against the installed tool version and your database engine; command names, generated files, and supported features can change.
Checklist by scenario
Adding a nullable column
- Define the new column as nullable or give it a database-safe default.
- Deploy the schema migration before deploying code that writes or depends on the field.
- Release application code that can read both the old and new row shape.
- Backfill existing rows in controlled batches if the field must eventually be populated.
- Enforce
NOT NULLonly after the backfill is complete and verified.
An illustrative SQL migration might be:
ALTER TABLE accounts ADD COLUMN display_name TEXT NULL;The application can then write display_name for new and updated records while safely handling older rows.
Renaming a column or changing its meaning
Do not assume a rename is instantly safe. Older application instances may still query the original name during a rolling deployment. Prefer an expand-and-contract sequence:
- Expand: add the replacement column, table, or index without removing the old structure.
- Dual read or dual write: update application code so versions can coexist. If both columns are written, define which value wins and how conflicts are monitored.
- Backfill: copy existing values in batches, recording progress and failures.
- Cut over: make reads depend on the new structure after validation.
- Contract: remove the old column or constraint in a later migration, after older code is no longer deployed.
-- Expand
ALTER TABLE profiles ADD COLUMN preferred_name TEXT NULL;
-- Backfill in application-controlled batches, or with carefully tested SQL
UPDATE profiles
SET preferred_name = legal_name
WHERE preferred_name IS NULL;
-- Contract later, after the application no longer uses legal_name
-- ALTER TABLE profiles DROP COLUMN legal_name;The exact backfill strategy depends on table size, indexes, write volume, and database behavior. For large tables, prefer bounded batches over one unbounded transaction unless you have tested the lock and transaction consequences.
Adding an index or constraint
- Inspect the query pattern first; an index that is unused or redundant adds write and storage cost.
- Check whether your database supports a low-lock or concurrent index-building option.
- Understand whether the migration runner wraps the operation in a transaction. Some database-specific index operations cannot run inside a transaction.
- Validate existing data before adding a uniqueness or check constraint. A constraint can fail because historical rows do not satisfy the new rule.
Using each migration workflow
Prisma: generate a migration from the schema change, inspect the generated SQL, apply it in a disposable or staging database, and use the project’s documented production deployment command in CI. Keep the Prisma schema, migration directory, and generated client behavior aligned.
Drizzle: generate or author the migration using the repository’s configured Drizzle Kit workflow, inspect the SQL output, and confirm which schema files are included. Do not assume a generated diff understands the operational risk of a large backfill or a blocking index.
Knex: create a timestamped migration with an explicit up path and a carefully considered down path. A down migration that drops data is not a safe rollback merely because it runs successfully.
export async function up(knex) {
await knex.schema.alterTable('accounts', table => {
table.text('display_name').nullable();
});
}
export async function down(knex) {
// Only use this if removing the column is an accepted recovery action.
await knex.schema.alterTable('accounts', table => {
table.dropColumn('display_name');
});
}Hand-written SQL: give every migration an ordered identifier, record how it is applied, and run it through a trusted migration runner rather than executing ad hoc statements from a laptop. Include comments for lock expectations, backfill requirements, and whether the operation is reversible.
What to double-check
CI validation
- Start an isolated database at the same engine and compatible version family used by the target environment.
- Apply all migrations from an empty database.
- Apply the same migrations to a realistic schema snapshot or seeded dataset.
- Run application tests, type checks, and API contract tests against the migrated database.
- Test the deployment command in CI without allowing application startup to silently create schema changes.
- Check that migration status is visible and that two deployment jobs cannot apply migrations concurrently.
For a stronger check, test the transition between the previous application version and the new version. This catches code that assumes the migration has completed everywhere or that breaks while old and new instances run together.
Transactions, locks, and data volume
Ask whether the operation is transactional on your database, how long it may hold locks, and what happens if the connection is interrupted. A transaction can provide atomicity, but a long transaction can also increase contention, replication lag, or recovery time. Conversely, splitting a change into steps may improve availability while making partial completion possible. Document the trade-off rather than treating “transactional” as automatically safer.
Rollback versus roll-forward
Schema rollback is often limited. Once a migration deletes data, changes a value’s meaning, or exposes a new constraint, reversing the DDL may not restore the original state. Prepare a recovery plan that may include restoring application compatibility, disabling a feature flag, replaying from a backup, or writing a corrective migration. Test backups and restoration separately; a rollback file is not a substitute for a recovery procedure.
Common mistakes
- Combining destructive schema changes with the first code release. Separate removal from introduction so old instances can finish safely.
- Running a large backfill during the migration transaction. Measure the data volume and consider an observable, resumable job.
- Trusting generated SQL without reading it. Generated output describes a structural diff, not your complete production risk.
- Using a down migration as an undo button. Dropping a column or constraint may be irreversible from a data perspective.
- Testing only an empty database. Existing duplicates, nulls, unexpected encodings, and large tables are where production failures usually appear.
- Allowing every application instance to run migrations on startup. Use one controlled migration step with ownership, logs, timeout behavior, and a clear failure signal.
- Editing applied migration files. This causes different environments to report the same migration identifier with different behavior.
When to revisit
Review this workflow before a major release, a seasonal traffic increase, a database upgrade, an ORM or migration-tool upgrade, or a change to deployment orchestration. Revisit it whenever you add or rename a high-volume table, introduce a uniqueness rule, change a column’s meaning, or move from a single instance to rolling or multi-region deployment.
Before each production migration, use this final checklist:
- Write down the expand, backfill, cutover, and contract steps.
- Confirm the migration was tested on representative data and the target database version.
- Estimate locks, runtime, transaction behavior, and monitoring signals.
- Verify old and new application versions can coexist.
- Define a stop condition and a recovery or roll-forward action.
- Back up or otherwise verify the available recovery path before destructive work.
- Assign an owner to watch logs, database metrics, error rates, and migration status.
- Record the result, including any manual follow-up migration or cleanup.
Migration tooling should reduce repetition, not replace judgment. Treat generated files as reviewable code, favor expand-and-contract for compatibility-sensitive changes, and make the operational plan part of the pull request. For related backend workflow checks, see the API debugging tools workflow and the safe JavaScript dependency update workflow.