Database Migrations: From "Just Altering a Table" to "A Versioned Database"

In one sentence: when you change the schema of a database that's already running in production, you're not facing a single SQL statement — you're facing a whole engineering problem of how to change it, how to record it, and how to revert it. That's the subject of this article.

If you've been following this series, you already know the internals:

By the end, you'll be able to:

1. Explain why "just altering the production database" is a disaster, and exactly what problem migrations solve.
2. Understand the core machinery — the migrations table, migration files, up/down, and the diff engine.
3. Build a complete, revertable migration workflow with drizzle-kit, from scratch.
4. Spot the production pitfalls that bite (locking, zero-downtime, data backfills).

• It helps to have just finished the "Build a Database from Zero" series, so you appreciate that schema and row data are stored separately.
• You don't need to know every ORM detail; I'll use Drizzle as the example tool, but the principles are universal.
• You'll want a local PostgreSQL to follow along (or spin one up with Docker).

The database we hand-wrote in the last series ended up with a schema like this:

The database goes live, and real data starts flowing in. Two weeks later, the product person walks over and says:

"We need to add a stock field to products, and price should support decimals."

Sounds simple, right? Just tweak the table:

But the moment you actually type those two lines into the production database, the trouble begins:

• How does anyone else know what you changed? Ten people on the team — who changed the table? How many times?
• What if it goes wrong? You forgot DEFAULT 0, so you write ADD COLUMN stock integer NOT NULL — there's no value to fill existing rows with, the NOT NULL check fails on the spot (column contains null values), and the step aborts;
• What if environments drift? Local works, staging doesn't, production ran something else — three databases, three schemas.
• Where's the history? What changes did the database go through from "yesterday" to "today"? Nobody remembers.

That's when you realize: a schema is not simply code. It is code, but at the same time it is state — it lives inside a database that already has data and is being hammered by millions of users. You can't just amend a React component and redeploy.

This "I changed the schema, but I don't know where, whether it was applied, or how to revert it" — that's the exact pain migrations exist to solve.

Before diving into tooling, let's see what's really going on. Let's break "changing a table" apart and look at what a bare ALTER TABLE is actually missing.

Many dev tools offer schema push (Drizzle and Prisma both have something like it). It's convenient — you edit a model and it syncs straight to the database. But push's philosophy is "make the database look like the current models", which means:

• The database is defined as following the code. That means it's not a process you can audit or rewind.
• It has no history. Today it looks like this; tomorrow you edit the model and it changes directly, and nobody knows what it looked like before.
• It doesn't generate migration files, so you can't review, version, or collaborate on them.

Push is great for development/prototyping, but not for production. What you ship is an evolution traced as a versioned sequence.

This is the single most important analogy in this article. Think about how you use Git as a frontend developer:

• You never edit the live dist directly. Instead you edit source → commit → review → merge → deploy.
• Each commit has a hash, a timestamp, an author, a message.
• If something breaks, you git revert.

Evolving a database schema is exactly the same. When you thought you were just changing API code, you were actually running a version-controlled repository for a database. Migration files are commits; the migrations table is the git log.

That's how we turn "altering a table" from a one-off action into a timeline:

Now, any fresh environment (local, CI, staging, production) can grab this chain of migrations, run from v1 to the latest, and get exactly the same schema. That's "reproducible".

Let's break down the three parts of migrations.

A migration file is the smallest unit of change. It usually has both directions of SQL:

The requirement from earlier maps to a migration file like this:

Only when both directions are spelled out is it a "complete" migration. up drives forward, down backs up — together they make "rollback" possible.

Files alone aren't enough — you need to record which migrations have already been applied. So the database gains a special table, usually named migrations (or Drizzle's __drizzle_migrations):

Every time a migration runs, the tool inserts a row here. As a result:

• The database itself knows which step it's on.
• When the tool starts, it SELECTs this table, computes the diff between "already applied" and "pending", and runs only the missing ones.

Same idea as Git computing the diff from HEAD — except the database remembers for you. That's why, even if a different developer or a different machine runs migrations, nothing gets executed twice.

So where does the SQL in a migration file come from? Two ways:

• Hand-written: you write ALTER TABLE ... directly in the migration file. That's fine, but it's error-prone and easy to miss things, and you end up eyeballing "what's the difference between these two schemas yourself".
• Auto-generated (diff): the modern ORM killer feature. You take care of a single "target schema" (the ORM models), and the tool diffs the database's real current state against it, generating the migration SQL for you.

The key insight is: hand-written DDL can't be reliable, because "I don't know what the database looks like right now." Only by letting the tool inspect the current database and compare it with the models you want can it produce accurate migrations that capture just that one step.

That's what Drizzle's drizzle-kit generate does:

It compares your schema.ts against the current database structure and outputs a migration file.

Theory's done — let's build. We'll walk the whole thing through with drizzle-kit. Assume you already have a PostgreSQL database.

drizzle.config.ts tells the tool where your schema is and which database to connect to:

Define your ORM models in src/db/schema.ts:

It creates a first migration file (here, a CREATE TABLE) in ./drizzle, then you apply it:

After that, the products table appears, and so does the __drizzle_migrations table, recording this migration's fingerprint.

Now do the requirement from the top of the article — add stock, change price's type:

Run it again:

This time it diffs the change from step two and generates the corresponding ALTER TABLE migration file. You can review it first:

Once satisfied, apply:

Now drizzle-kit migrate reads the __drizzle_migrations table, sees this new migration hasn't run, and runs only that one. The database moves smoothly from v1 to v2.

To see where you are:

It tells you which migration the database is on and which are still pending.

Rolling back (down) in drizzle-kit is "heavier" — it's not as one-line-friendly as some other tools. The reason is what we'll come back to: automatically rolling back schema in production is often dangerous. That said, you can reverse the operations in a migration file by hand, or write the down logic as SQL.

This is the most realistic trap. Say you ran ALTER TABLE products ADD COLUMN color text; directly on production (bypassing migrations), and the tool doesn't know. The next time you migrate, the tool finds "what the database says" and "the migration history" disagree — that's schema drift.

Drizzle reports something like [✓] No changes, database is already in sync (if the structure happens to match) or directly warns about unsynced differences. The strategy:

• Make migration history the single source of truth: no hand-run DDL in the database; every change goes through generate.
• If drift happens, prefer turning the drift into a migration rather than diffing out a mysterious new one.
• Use drizzle-kit --dialect=postgresql --name=xxx generate to create a reconciliation migration that "adopts" the drift.

A toy database can be poked all you like, but in production, schema changes are among the most common causes of incidents. These points are worth remembering.

What really locks a table for a long time is an operation that forces a full table rewrite. During the rewrite, PostgreSQL holds an ACCESS EXCLUSIVE lock, which conflicts with the ACCESS SHARE a SELECT needs — so both reads and writes get blocked and requests pile up. Typical triggers:

• ADD COLUMN ... DEFAULT now() (a volatile default) → every row must run the function → full rewrite;
• ALTER COLUMN ... TYPE (changing a column's type) → every row gets converted → full rewrite;
• ADD COLUMN ... NOT NULL on old PostgreSQL (< 11) → also rewrites.

A common misconception: ADD COLUMN ... NOT NULL (with no DEFAULT) on a non-empty table does not rewrite and does not hold a long lock — it fails fast (column contains null values), because there's no value to fill existing rows with and it violates NOT NULL.

Mitigation: on PostgreSQL 11+, ADD COLUMN with a constant DEFAULT uses the "missing value" optimization — it only touches metadata and doesn't rewrite (a very short lock). Avoid volatile defaults; for a big type change, consider a three-phase migration or backfill into a new column in batches.

Principle: change the code first, then the database — or the reverse — but always keep both the old and new versions of the code working. The common approach is an "expand/contract" two-phase:

1. Expand (widen): add the stock column (old INSERTs don't write it; it uses the default). Now both old and new code can run.
2. Ship the app: the new code starts reading/writing stock.
3. Contract (narrow): backfill the historical data, confirm it's correct, then drop/adjust constraints (e.g. DROP COLUMN or SET NOT NULL).

That way, at any moment, the running code never crashes because it didn't know about a column.

Changes like price integer → numeric(10,2) that add precision are safe (no data loss). But narrowing (like numeric → integer) or a semantic type change (string → jsonb) may silently lose data. For any such change, write a backfill script first and process it in batches — don't UPDATE a million rows inside a single migration file.

A rough safety ordering:

• ✅ Add a nullable column / add a column with a default → safest;
• ✅ Add an index (now even CONCURRENTLY on big tables) → fairly safe;
• ⚠️ Change a type / add a NOT NULL constraint → needs rewrite or backfill, be careful;
• ❌ Drop a column/table → always last, after confirming nothing references it.

• A schema is both code and state — it lives in a database with real data, so changing it demands the rigor of changing production code.
• Migrations are the Git of the database world — migration files are commits, the migrations table is the git log, up/down is forward and reverse.
• The diff engine is the trustworthy source — let the tool compare "current database state" against "target models" and generate accurate migration SQL. Hand-written DDL isn't reliable.
• Production changes must be revertable, non-locking, incremental — expand/contract, non-blocking DDL, and data backfills, all of which matter.

Keywords: migrations, schema drift, diff engine, up/down, migrations table, locking, zero-downtime migration, backfill, drizzle-kit.

This article moves you from "knowing how to alter tables" to "knowing how to alter them with versioning." But there's a thornier problem for production databases: when you're already running a database that's always online, how do you change the schema without affecting reads and writes at all? (Migrations plus backfills plus phased rollout.) And: how do you roll back a migration that has already produced bad data?

If this helped, look forward to our next series — moving from "a versioned schema" to "a database that can safely evolve in production."