Database Migrations — From Basics to Production-Safe

Grounded in a real moment: on the my work backend backend I pulled code and found 130 migration files on disk but only 73 run in the database — 57 pending, some of them destructive (drop_...). This doc is everything I learned making sense of that.

1. What a migration actually is

A migration is a small file that describes a change to your database structure (schema), written in code instead of clicked by hand.

“Add a status column to card_requests.” → that’s one migration. “Create the org_licenses table.” → another migration.

Each file has two directions:

• up — apply the change (add the column).
• down — undo the change (drop the column).

In knex (what my work backend uses) it looks like this:

exports.up = async function (knex) {
  await knex.schema.alterTable('card_requests', (t) => {
    t.string('testing_status').defaultTo('pending');
  });
};

exports.down = async function (knex) {
  await knex.schema.alterTable('card_requests', (t) => {
    t.dropColumn('testing_status');
  });
};

2. The problem migrations solve

Without migrations, how does a schema change reach the live database? Someone opens a SQL client and types ALTER TABLE ... by hand. That breaks down fast:

Mental model: migrations are version control (git) for your database schema. Git tracks changes to your code; migrations track changes to your tables.

3. Code world vs database world — THE key insight

This is the thing that confuses everyone at first:

git pull      → changes your CODE  (downloads the .js migration files)
knex migrate  → changes your DATABASE (actually runs those files)

These are two separate worlds. git pull brought 57 new migration files onto my disk — but my MySQL database did not change one bit. Git has no idea my database exists.

The database only changes when you run the migrations.

My real moment: 130 files pulled, but the DB still showed 73 run. The code was ~3 weeks ahead of the database until I chose to migrate.

4. How the tool knows what’s already run

Knex keeps a hidden bookkeeping table inside your database called knex_migrations. Every time a migration runs, its filename gets recorded there.

When you run knex migrate:latest, knex:

1. Lists all .js files in migrations/.
2. Reads knex_migrations to see what already ran.
3. Runs only the missing ones, in filename order, then records them.

SELECT COUNT(*) FROM knex_migrations;   -- how many the DB has run
SELECT name FROM knex_migrations ORDER BY id DESC LIMIT 5;  -- the latest ones

That’s why every migration filename starts with a timestamp like 20260603100001_.... The timestamp = the run order. Never rename or reorder old migrations — knex matches them by exact filename.

5. The core commands (knex)

my work backend wraps these as npm scripts:

npm run knex:migrate:status   # READ-ONLY: lists each migration Completed / Pending
npm run knex:migrate          # runs all pending migrations (knex migrate:latest)
npm run knex:migrate:rollback # undoes the LAST BATCH of migrations (runs their `down`)
npm run knex:migrate:make name_here   # creates a new empty migration file

Always run status before migrate. It tells you exactly what’s about to happen — no surprises.

6. Manual vs migrations — which is “good”?

Short answer: use migrations. Almost never hand-edit the schema directly.

When manual is acceptable:

• A throwaway local experiment you’ll reset anyway.
• An emergency prod hotfix — but then you immediately write a matching migration so the record stays truthful (this is called “backfilling” the migration).
• Inspecting data (SELECT ...) — reading is always fine.

The rule: the schema’s source of truth is the migration files, not whatever someone typed once. If you hand-edit prod and don’t record it, the next person’s migration run can collide with reality and break.

7. How to avoid destruction — the safety rules

Destructive migrations are the ones that lose data: dropColumn, dropTable, drop_..., narrowing a column type, deleting rows. My 57 pending included several (drop_system_test_cards, drop_dead_key_columns, drop_v2_outcome_validation_tables).

Rule 1 — Back up before you migrate (non-negotiable on real data)

"C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqldump.exe" -u dbuser -p myappdb > backup_2026-06-08.sql

This is your undo button if anything goes wrong. Restore with:

"C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe" -u dbuser -p myappdb < backup_2026-06-08.sql

Rule 2 — Read what you’re about to run

Run status, then actually open the new migration files. Look for drop, delete, alter ... type. Know what data, if any, disappears.

Rule 3 — Always write a down

If your migration can’t be reversed (e.g. you dropped a column and the data is gone), say so explicitly and lean harder on the backup. A migration with no down is a one-way door.

Rule 4 — The expand → migrate → contract pattern (zero-downtime)

Never drop-and-recreate in one shot on a live system. Split a risky change into safe steps across releases:

1. Expand — add the new column/table. Nothing breaks; old code ignores it.
2. Backfill — copy/transform data into the new shape (often its own migration — my work backend has files literally named backfill_...).
3. Migrate the app — switch code to use the new column.
4. Contract — only after everything’s proven, drop the old column.

This is why you see pairs in the repo: a create_..._slot_assignment migration followed by a backfill_..._slot_assignment migration. That’s the pattern in action.

Rule 5 — Test on a copy first

Run migrations against a local/staging copy of the data before prod. If it breaks, it breaks somewhere safe.

Rule 6 — One logical change per migration

Small migrations are easy to review, easy to roll back, and pinpoint failures. Don’t cram “add 3 tables + drop 2 columns + seed data” into one file.

Rule 7 — Migrations are forward-only once shipped

Once a migration has run on prod, don’t edit that file. Others (and prod) already ran the old version. To change course, write a new migration. Editing shipped migrations is how teams get “it works on my machine” schema drift.

8. Safe handling workflow (the routine to internalize)

1. git pull                         # get the latest migration files
2. npm run knex:migrate:status      # see what's pending
3. open & read the pending files    # spot anything destructive
4. mysqldump  → backup              # safety net (real data)
5. npm run knex:migrate             # apply
6. verify the app + spot-check tables
7. if broken → restore backup OR rollback, investigate, fix forward

Do this every time you pull code that touched migrations/. It becomes muscle memory.

9. Rollbacks — how undo works

npm run knex:migrate:rollback   # undoes the most recent BATCH

A “batch” = all the migrations applied together in one migrate:latest run. So if you just ran 57 at once, a single rollback tries to reverse all 57 (running each one’s down). That’s powerful and a little scary — another reason to migrate in smaller, deliberate batches when you can.

Important: rollback only works if the down functions are correct. And rollback cannot bring back data that a dropColumn already deleted — it only rebuilds structure. The backup is the real data safety net; rollback is the structure one.

10. Common gotchas

• “It ran on my machine but not prod.” You forgot prod is a separate database world. Migrations must be run on each environment.
• Editing a migration after it ran. Knex already recorded the filename; your edit silently never executes again. Write a new migration instead.
• Renaming an old migration file. Knex matches by filename — renaming makes it look “new” and it tries to run again. Don’t.
• Seed data inside schema migrations. Mixing data inserts into structural migrations is messy; knex has a separate seed system (npm run knex:seed). (Some teams still do small seeds in migrations deliberately — my work backend has seed_test_catalog — but know the distinction.)
• No backup before a drop. The one time you skip it is the time you need it.

11. My real worked example (my work backend, 2026-06-08)

Files on disk after pull ........ 130
Recorded in knex_migrations ......  73
Pending .......................... 57   ← downloaded but NOT yet applied
Last migration the DB ran ........ 20260511100001 (May 11)
Newest file on disk .............. 20260603100003 (Jun 3)

So my code was 3 weeks ahead of my database. Nothing was broken — the DB was simply waiting. The correct, non-destructive move:

npm run knex:migrate:status     # confirm the 57 pending
# read the drop_* files
mysqldump ... > backup.sql      # safety net
npm run knex:migrate            # apply all 57 in order

git pull was safe and reversible. knex migrate is the step that actually commits to the change — which is exactly why it gets the backup + read-first treatment.

12. Cheat sheet

12b. Backup vs SQL dump — are they the same?

Not exactly. One is the idea, the other is one way to do it.

• Backup = the general concept: any saved copy of your data you can restore from.
• SQL dump (mysqldump) = one specific kind of backup — a text file full of SQL statements (CREATE TABLE..., INSERT...) that rebuild the database when fed back in.

A SQL dump is a backup, but not every backup is a SQL dump.

For a local dev MySQL, mysqldump is the right backup — one file, easy restore.

Is the mysqldump command the same as exporting from MySQL Workbench?

Yes — same result, different door. The command line and Workbench’s Data Export both produce the same .sql dump file. Workbench’s export feature is essentially a GUI front-end that runs mysqldump for you; the checkboxes just become flags:

So this command:

& "C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqldump.exe" -u dbuser -p myappdb > "backup_2026-06-08.sql"

= exporting myappdb to a single self-contained .sql file in Workbench. Either file restores with mysql ... < backup.sql. The CLI is just faster to script and repeat. (Caveat: Workbench occasionally uses its own export engine if versions mismatch, but the output is still a restorable .sql dump.)

12c. How to restore from a backup

Restoring = feeding the .sql dump back into MySQL so it re-runs all its CREATE TABLE + INSERT statements and rebuilds the database.

Command line (PowerShell)

cd "C:\Users\addar\Desktop\My Workspace\my work backend\backend"

& "C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe" -u dbuser -p myappdb < "backup_2026-06-08.sql"

The direction flips between backup and restore:

Backup uses mysqldump; restore uses the plain mysql client. Easy to mix up — the dump program only dumps; the regular client reads the file back in.

Restore overwrites, it doesn’t merge

A dump contains DROP TABLE IF EXISTS + CREATE TABLE for each table, so restoring replaces the current tables with the backup’s versions. The target database (myappdb) should already exist — the dump fills it. If you need a fresh empty DB first:

& "C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe" -u dbuser -p -e "CREATE DATABASE IF NOT EXISTS myappdb;"

MySQL Workbench (GUI way)

Server → Data Import → Import from Self-Contained File → pick the .sql file → choose target schema myappdb → Start Import.

Verify the restore

& "C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe" -u dbuser -p myappdb -e "SELECT COUNT(*) FROM knex_migrations;"

If you restored a backup taken before migrating, this count drops back to its old value — proof the restore rewound the DB to its pre-migration state.

13. Copy-paste runbook (Windows / PowerShell)

The exact sequence I use on my work backend when a pull brings new migrations. Run it in order, from the backend folder.

# 0. Go to the backend
cd "C:\Users\addar\Desktop\My Workspace\my work backend\backend"

# 1. See what's pending (READ-ONLY, changes nothing)
npm run knex:migrate:status

# 2. Back up the database FIRST (your undo button)
& "C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqldump.exe" -u dbuser -p myappdb > "backup_2026-06-08.sql"

# 3. Apply all pending migrations
npm run knex:migrate

# 4. Verify — knex_migrations count should jump to match the files on disk
& "C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe" -u dbuser -p myappdb -e "SELECT COUNT(*) FROM knex_migrations;"

If anything breaks, restore the backup:

& "C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe" -u dbuser -p myappdb < "backup_2026-06-08.sql"

Roll back instead (undo the last batch, structure only):

npm run knex:migrate:rollback

PowerShell gotchas

• Use the call operator & before a quoted .exe path that contains spaces ("C:\Program Files\..."), otherwise PowerShell won’t execute it.
• > writes the backup file, < feeds it back in — both work natively in PowerShell.
• -pYOURPASSWORD has no space after -p (it’s -p glued to the password). Leaving just -p with a space makes mysql prompt for the password interactively — which is the safer habit (keeps the password out of your shell history).

One-line summary

A migration is versioned, reversible, code-described schema change. git pull brings the files; knex migrate changes the database. Before applying: read for drop, back up, then run. Never hand-edit a shipped migration, always write a down, and split risky changes into expand → backfill → contract.