database-migration

Create a migration file exporting up and down functions that receive queryInterface and Sequelize. Use queryInterface.createTable, addColumn, or removeColumn inside up, and reverse each operation in down. Run with npx sequelize-cli db:migrate and roll back with db:migrate:undo.

Use a multi-phase approach: add the new column, deploy code that writes to both columns, backfill existing rows, deploy code reading from the new column, then drop the old column. Each phase is a separate small migration that stays backward compatible.

Sequelize and TypeORM use explicit code-based migration files with up/down methods, giving full control over rollback logic. Prisma generates migrations from schema.prisma diffs via prisma migrate dev, which is simpler but offers less control over custom data transformations.

Wrap migration steps in a transaction so failures roll back automatically, or create a backup table before changes and restore from it on error. Every up migration should have a tested down counterpart, and migrations should be verified on staging first.

Yes, detect the dialect with queryInterface.sequelize.getDialect() and branch the logic, for example using JSONB columns on PostgreSQL and JSON on MySQL. This keeps a single migration file portable across database engines.

Common pitfalls include skipping rollback testing, making breaking changes without a downtime strategy, ignoring NULL handling and foreign key constraints, and migrating too much data in one step. Break changes into small, idempotent, transactional migrations.