Chapter 6 — Writing Migrations

The why

Imagine you have a working app with 50,000 orders. Your manager asks you to add a refund_reason column. You can't just delete the table and recreate it — that loses all the data. A migration is how you evolve a live database safely.

A migration is a versioned, tracked script that evolves your database structure safely and reproducibly.

Think of migrations as Git commits for your database. Each one is:

• Numbered — applied in order, every time, on every environment
• Tracked — the database records which migrations have run
• Irreversible (in production) — once merged, you write a NEW migration to undo, never edit the old one

The iron rule

How migrations work in this project

The tool is sequelize-cli. It reads from src/database/migrations/ and tracks which files have run in a table called schema_migrations.

# Run all pending migrations
npm run db:migrate
 
# Undo the last migration
npm run db:migrate:undo
 
# Undo all migrations (wipe schema)
npm run db:migrate:undo:all

How to write a migration

Step 1 — Create the file

Name it with a timestamp prefix so migrations run in order:

src/database/migrations/20260501120000_create_orders.js

Format: YYYYMMDDHHMMSS_description.js

Step 2 — Write the up function

// src/database/migrations/20260501120000_create_orders.js
'use strict';
 
module.exports = {
  async up(q, Sequelize) {
    // Wrap everything in a transaction so partial failures roll back
    await q.sequelize.transaction(async (t)

Rules you must follow — no exceptions

Adding a column safely (without downtime)

Simple case — nullable column

// Instant in PostgreSQL — no lock, no downtime
await q.addColumn('orders', 'refund_reason', {
  type: Sequelize.TEXT,
  allowNull: true,
});

Hard case — NOT NULL column on an existing table

-- ❌ This locks the whole table for minutes on large tables
ALTER TABLE orders ADD COLUMN source VARCHAR(20) NOT NULL DEFAULT 'api';

Do it in 3 steps across 3 separate migrations:

Migration 1 — Add nullable:

await q.addColumn('orders', 'source', {
  type: Sequelize.STRING(20),
  allowNull: true,
});

Between migrations — Deploy code that always sets source on new records.

Background job — Backfill existing rows in batches (not in a migration):

await Order.update({ source: 'api' }, {
  where: { source: null },
  limit: 10000,
});
// Repeat until no more nulls

Migration 2 — Add the NOT NULL constraint after all rows are filled:

await q.changeColumn('orders', 'source', {
  type: Sequelize.STRING(20),
  allowNull: false,
});

Dropping a column safely

Adding an index on a large table

-- ❌ This locks the table for writes during index creation
CREATE INDEX idx_orders_user ON orders (user_id);
 
-- ✅ This builds the index without blocking writes (PostgreSQL only)
CREATE INDEX CONCURRENTLY idx_orders_user ON orders (user_id);

In a migration:

await q.sequelize.query(
  'CREATE INDEX CONCURRENTLY idx_orders_user ON orders (user_id)',
  { transaction: null }  // CONCURRENTLY cannot run inside a transaction!
);

Seeds — reference data

Seeds are separate from migrations. They insert reference data — things like role names, currency codes — that the app needs to function.

Seeds must be idempotent — running twice should not create duplicates:

module.exports = {
  async up(q) {
    await q.bulkInsert('roles', [
      { id: 'r1', name: 'admin',   created_at: new Date() },

Common migration mistakes

One thing to remember