Paul SerbanSoftware Engineer

Database Migrations in SQL: Fundamentals, Practices, and the Domain Language Every Team NeedsHow to evolve a production schema without breaking the system that depends on it

Introduction

Every application with a relational database eventually faces the same problem: the schema you designed on day one is not the schema you need on day five hundred. Requirements change, new features demand new tables, indexes need tuning, and old assumptions about data shape stop holding. The mechanism for making these changes safely, repeatably, and in a way that a whole team can reason about is the database migration. It sounds like a narrow, mechanical topic, but in practice it sits at the intersection of software engineering discipline, operational risk management, and team communication.

A migration is, at its simplest, a versioned, scripted change to a database schema. But that simple definition hides a lot of nuance. Migrations must be ordered, they must be reproducible across environments, they must sometimes run against tables with millions of live rows without taking the application down, and they must be understood by engineers who did not write them. This article walks through the fundamentals of SQL migrations, the practical patterns experienced teams use, the vocabulary that lets engineers talk precisely about schema change, and the trade-offs that come with different approaches. The goal is not to sell a particular tool, but to build a mental model that transfers across Flyway, Liquibase, Rails migrations, Django migrations, Prisma Migrate, or a hand-rolled SQL migration runner.

Context: Why Schema Change Is Harder Than It Looks

In application code, changing behavior is usually low-risk. You edit a function, run your tests, and deploy. If something is wrong, you roll back to the previous version and the world resets. Databases do not work this way, because a database carries state. A schema change is not just a change to structure; it is a change applied to data that already exists, often data that is being read and written concurrently by a live application. Rolling back a migration does not undo the writes that happened while the new schema was live, and in many cases a "rollback" migration is itself a forward-only change that tries to approximate reversing the previous one.

This state-carrying property is what makes migrations fundamentally different from ordinary deployments. A CREATE TABLE statement is trivial. Adding a NOT NULL column to a table with ten million existing rows, table without a default value, is not, because every existing row violates the new constraint the moment it is created. Renaming a column that three microservices read directly is not a schema problem so much as a coordination problem across teams and deploy schedules. The practical difficulty of migrations, in other words, scales with the amount of data and number of consumers already depending on the shape of that data, not with the apparent complexity of the SQL statement itself.

There is also a temporal dimension that application code changes rarely need to consider: a migration and the application code that depends on the new schema are not deployed atomically. During a rolling deployment, old application code and new application code may run simultaneously against the same database for seconds or minutes. If a migration drops a column that the old code still reads, that window of overlap becomes an outage window. Teams that treat migrations as "just another commit" tend to discover this the hard way, usually during a release that looked safe in staging but broke in production because staging did not have concurrent traffic or a rolling deploy strategy.

The Domain Language of Migrations

Every mature engineering practice develops a shared vocabulary, and migrations are no exception. Getting these terms straight matters because imprecise language leads to imprecise incident postmortems and imprecise code review comments.

A migration is a single, ordered unit of schema change, usually represented as a script or a pair of scripts (up and down). The migration history or schema version table is the database's own record of which migrations have already been applied; tools like Flyway (flyway_schema_history) and Rails (schema_migrations) maintain this as an actual table inside the database being migrated, which is what allows a migration runner to be idempotent and safe to re-run. A migration is described as idempotent if running it more than once produces the same end state without error, which matters because deploy pipelines sometimes retry failed steps. Forward-only migrations are changes that are never rolled back with a corresponding "down" script; instead, mistakes are fixed by writing a new forward migration, a practice increasingly favored because down-migrations are rarely tested and often silently broken by the time anyone needs them.

The expand-contract pattern (also called parallel change) is arguably the single most important idea in this domain. It describes a three-phase approach to backward-incompatible changes: first expand the schema by adding the new structure alongside the old one, deploy application code that can read and write both, then contract by removing the old structure once every consumer has migrated. This is how experienced teams rename columns, change types, or split tables without downtime, and it directly addresses the overlap problem described above. Related to this is the idea of a backward-compatible migration, one that old application code can tolerate even though it doesn't use the new structure, versus a breaking migration, which old code cannot survive.

Finally, teams distinguish between schema migrations (structural changes: tables, columns, constraints, indexes) and data migrations (changes to the values stored in existing rows, such as backfilling a new column or reshaping JSON blobs). The two are often mechanically similar, since both may be expressed as SQL run through the same migration runner, but they carry very different risk profiles: a data migration touching every row of a large table can lock resources or generate enormous replication lag, in a way that adding an empty nullable column typically does not.

Deep Technical Explanation: How Migration Tooling Actually Works

Underneath the tool-specific syntax, nearly every SQL migration framework follows the same architecture. There is a directory of migration files, each named or numbered so that ordering is unambiguous (a timestamp prefix like 20240315120000_add_users_email_index.sql is common precisely because timestamps sort correctly and rarely collide across a team). There is a schema history table living inside the target database itself, which records which migration identifiers have already been applied and, often, a checksum of the migration's contents so the tool can detect if a previously applied file was edited after the fact. On each run, the tool diffs the list of available migrations against the history table, applies anything missing in order, and writes a new row to the history table for each one, ideally inside the same transaction as the schema change so a mid-migration crash cannot leave the history table out of sync with reality.

This architecture is what makes migrations safe to run from a CI/CD pipeline without a human present. It also explains a common failure mode: two developers add migrations with the same timestamp or an out-of-order dependency, and the tool applies them in an order neither developer tested locally. Most tools mitigate this by failing loudly on such conflicts rather than guessing, and teams mitigate it further with code review conventions that flag when a migration branch is stale relative to main.

A second layer of complexity is transactional DDL. PostgreSQL famously supports running most DDL statements inside a transaction, meaning a multi-statement migration can be rolled back entirely if any statement fails partway through. MySQL, by contrast, historically performs an implicit commit on most DDL statements, so a failed migration midway through can leave a database in a partially migrated state that must be manually reconciled. This single difference between database engines has outsized influence on how conservative a migration strategy needs to be: teams on MySQL tend to write smaller, single-purpose migrations specifically to minimize the blast radius of a partial failure, while PostgreSQL users can afford to batch related changes into one transactional migration with more confidence.

A third layer worth understanding is locking behavior. Many DDL operations acquire an ACCESS EXCLUSIVE lock (in PostgreSQL terms) or an equivalent metadata lock, which blocks all reads and writes to the table for the duration of the operation. Historically, adding a column with a default value required rewriting every row of the table under this kind of lock; PostgreSQL 11 changed this for simple, non-volatile defaults so that the operation became effectively instantaneous, but adding an index, adding a NOT NULL constraint without a prior CHECK, or changing a column's type can still require a full table rewrite or scan under lock, depending on the version and engine. This is why experienced teams use CREATE INDEX CONCURRENTLY in PostgreSQL, or online DDL tools like pt-online-schema-change and gh-ost in MySQL environments, specifically to avoid holding a blocking lock on a hot table.

Implementation: Writing Migrations in Practice

The clearest way to see these ideas in action is to walk through a realistic scenario: renaming a column on a live users table from email to email_address, following the expand-contract pattern rather than a naive rename.

A naive approach would be a single migration:

ALTER TABLE users RENAME COLUMN email TO email_address;

This is a one-line change, but it is also a breaking migration: the instant it runs, every currently-running instance of the old application code that references email will start failing. In a rolling deployment, that is guaranteed to happen. The expand-contract version instead splits the work into three migrations deployed across three separate releases.

Migration 1 (expand) adds the new column and starts keeping it in sync:

-- 20240610090000_add_email_address_column.sql
ALTER TABLE users ADD COLUMN email_address TEXT;

-- Backfill existing rows in batches to avoid a long-running lock
-- (application-level backfill script shown below, not run inline)

Because backfilling millions of rows inside a single migration transaction can hold locks and bloat write-ahead logs, the backfill is usually done as a separate, batched job rather than one giant UPDATE:

import time
from sqlalchemy import text

BATCH_SIZE = 5_000

def backfill_email_address(engine):
    with engine.connect() as conn:
        while True:
            result = conn.execute(
                text("""
                    UPDATE users
                    SET email_address = email
                    WHERE id IN (
                        SELECT id FROM users
                        WHERE email_address IS NULL
                        LIMIT :batch_size
                    )
                """),
                {"batch_size": BATCH_SIZE},
            )
            conn.commit()
            if result.rowcount == 0:
                break
            time.sleep(0.1)  # brief pause to avoid saturating replication

Between migration 1 and the next step, the application is deployed to write to both email and email_address on every insert or update, so no new rows fall out of sync while the backfill runs. Only once the backfill is complete and monitoring confirms the two columns match does the team ship the code that reads from email_address exclusively. Only after that code has been running successfully for a safe observation period does migration 2 (contract) drop the old column:

-- 20240701090000_drop_email_column.sql
ALTER TABLE users DROP COLUMN email;

The same pattern generalizes to type changes, table splits, and constraint additions. A NOT NULL constraint, for instance, is safer added as a CHECK constraint marked NOT VALID first, validated separately, and only converted to a hard NOT NULL once validation confirms no existing row violates it:

ALTER TABLE orders ADD CONSTRAINT orders_total_not_null
    CHECK (total IS NOT NULL) NOT VALID;

-- Run separately, does not hold a long lock:
ALTER TABLE orders VALIDATE CONSTRAINT orders_total_not_null;

-- Once validated and confirmed safe:
ALTER TABLE orders ALTER COLUMN total SET NOT NULL;

Trade-offs and Pitfalls

No migration strategy is free of cost, and the expand-contract pattern in particular trades speed for safety. It requires three coordinated deployments instead of one, more code review cycles, and application code that temporarily writes to two places, which is itself a source of bugs if the dual-write logic is inconsistent or forgotten in some code path. Teams working on small, low-traffic databases sometimes reasonably decide this ceremony is overkill, since a five-second lock on a table with a thousand rows is invisible to users, while a team running a table with billions of rows and strict latency SLAs cannot afford to skip a single step.

There is a related tension between forward-only migrations and reversible migrations. Down-migrations sound appealing in theory: if something goes wrong, just run the down script. In practice, down-migrations are rarely exercised in the normal course of development, which means they rot. A down-migration written eight months ago may reference a column that no longer exists, or may silently destroy data that was added by a later migration, since a schema rollback cannot restore data that never existed in the first version of a table. Many teams, including large ones at companies running PostgreSQL and MySQL at scale, have moved toward forward-only migrations paired with feature flags: instead of rolling back the schema, you roll forward with a fix, and you keep the previous application code path behind a flag so you can disable a feature without touching the database at all.

Another common pitfall is treating a data migration exactly like a schema migration in terms of blast radius. A schema migration that adds a nullable column is nearly free. A data migration that rewrites a JSON column across every row of a hundred-million-row table is not, and running it as a single unbatched UPDATE inside the same transaction as a deploy can generate enough write-ahead log volume to threaten replica lag or even fill disk on the primary. The fix, as shown in the Python example above, is almost always batching, throttling, and running the migration out of band from the deploy pipeline, with its own monitoring and the ability to pause.

Finally, migrations that are only tested against an empty local database are a recurring source of production incidents. A migration that adds a NOT NULL column without a default will succeed instantly on an empty test database and fail immediately in production against real data, because PostgreSQL and MySQL both need a value to backfill into for every existing row before the constraint can hold. Testing migrations against a realistic copy of production data volume, even a sampled subset, catches an entire category of these problems before they reach a release.

Best Practices

A handful of habits separate teams that treat migrations as routine from teams for whom every schema change is an adrenaline event. First, keep every migration small and single-purpose. A migration that adds a column, backfills it, and adds an index in one file is harder to review, harder to reason about under lock contention, and harder to safely retry if one part fails. Splitting these into separate, ordered migrations makes each step's risk legible on its own.

Second, make every migration idempotent and safe to re-run, using IF NOT EXISTS guards and checking for existing constraints before adding them where the database engine supports it. Deploy pipelines sometimes retry failed steps automatically, and a migration that errors ungracefully on a second run turns a transient network blip into a stuck deployment.

Third, separate schema changes from data changes in both code and deployment timing. Schema migrations should run automatically as part of the deploy pipeline because they are typically fast and low-risk when written carefully. Data migrations, especially ones touching large tables, deserve their own operational runbook, their own monitoring dashboard, and often their own on-call awareness, run independently of the code deploy that depends on them.

Fourth, default to backward-compatible, expand-first changes for anything touching a table under active read/write load, and reserve simple, single-step migrations for tables that are small, low-traffic, or not yet in production. This is a judgment call, not a rule that applies uniformly, but the decision should be made deliberately rather than by accident.

Fifth, review migrations with the same rigor as application code, and specifically ask what lock each statement takes and how long it is likely to hold that lock given current table size. Tools like pganalyze, pg_stat_activity queries, or simply running EXPLAIN and checking documentation for lock levels can answer this before a migration ever reaches production, and many teams add this check as an explicit line item in their pull request template for anything touching the schema.

Key Takeaways

  • Treat migrations as forward-only by default, and manage mistakes with new migrations or feature flags rather than relying on untested down-scripts.
  • Use the expand-contract pattern for any backward-incompatible change to a table under live traffic, splitting the work into expand, migrate, and contract deployments.
  • Separate schema migrations (fast, structural) from data migrations (potentially slow, row-touching), and give data migrations their own batching, throttling, and monitoring.
  • Understand your database engine's locking and transactional DDL behavior before writing a migration; PostgreSQL and MySQL differ meaningfully here.
  • Test migrations against data volumes that resemble production, not an empty local database, since many failure modes only appear once real rows exist.

Conclusion

Database migrations are deceptively simple at the syntax level and genuinely difficult at the systems level. The SQL statements themselves are usually a handful of lines; the discipline required to run them safely against a live, concurrently accessed, growing dataset is what actually separates reliable engineering teams from ones that dread every schema change. Understanding the domain language, expand and contract, forward-only, backward-compatible, schema versus data migration gives a team the vocabulary to reason precisely about risk during code review, rather than discovering the risk during an incident.

None of this requires exotic tooling. Flyway, Liquibase, Rails' Active Record migrations, Django's migration framework, and Prisma Migrate all implement roughly the same underlying model described here, and the practices in this article apply regardless of which one a team chooses. What matters more than the tool is the mental model: migrations carry state, state cannot be rolled back the way code can, and the safest changes are the ones that give old and new application code a window to coexist peacefully while the schema catches up.

References