Skip to content
Tutorials Center
My learning

Topics

Lesson 30 of 37 ยท Alembic and Migrations

Production Migrations: Data Changes, Batch Mode, Offline SQL, Branches

Migrate real databases safely: backfill data, change columns with batch_alter_table on SQLite, generate offline SQL, resolve branched heads and deploy with confidence.

Adding a nullable column is easy. Real releases are harder: a column must become NOT NULL on a table with a million rows, two teammates created migrations at the same time, a DBA wants to read the SQL before it runs, or the target is SQLite, which cannot alter most things in place. This lesson covers those cases.

The code uses Alembic's Operations object directly against an in-memory database, which is exactly what op does inside a migration file.

The safe recipe for a new NOT NULL column

You cannot add a NOT NULL column without a default to a table that already has rows. The portable pattern is three steps, often inside one revision:

  1. Add the column as nullable.
  2. Backfill the value for existing rows.
  3. Alter the column to NOT NULL.
Python Add, backfill and tighten a column

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

For very large tables, step 2 as a single UPDATE can lock the table for a long time. Run it in chunks (WHERE id BETWEEN ... in a loop) or as a separate background job, and make the NOT NULL step a later revision.

Data migrations: use lightweight tables, not your models

A migration is a snapshot in time. If it imports from myapp.models import User, it breaks the day you rename User, because the old revision runs against new code. Define a small table() construct inside the migration containing only the columns that revision needs:

Python bulk_insert and a data-only update

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Batch mode for SQLite (and other limited databases)

SQLite supports ADD COLUMN and RENAME, but it cannot drop a column in older versions, change a column's type or nullability, or add constraints to an existing table. Alembic's batch mode works around it with the "move and copy" recipe: create a new table with the desired shape, copy the rows over, drop the old table and rename the new one. You wrap the changes in op.batch_alter_table(...) and write them as usual.

Python
with op.batch_alter_table("customer") as batch:
    batch.alter_column("country", nullable=False)
    batch.drop_column("legacy_flag")
    batch.create_unique_constraint("uq_customer_name", ["name"])

On PostgreSQL the same block simply emits ordinary ALTER TABLE statements, so one migration file works on both. If you develop on SQLite and deploy on PostgreSQL, set render_as_batch=True in env.py's context.configure(...) so autogenerate writes batch blocks for you.

Offline mode: generate SQL instead of running it

Some teams must hand the SQL to a DBA, or deploy through a tool that only accepts SQL scripts. alembic upgrade head --sql runs the migration in offline mode and prints the statements instead of executing them. You can see the idea with a PostgreSQL dialect and no database at all:

Python Offline SQL for PostgreSQL

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The statements are written to the output buffer (standard output by default). With the command line you redirect them: alembic upgrade head --sql > release.sql. Offline mode cannot run data steps that read from the database (a SELECT result is unavailable), so keep such logic in op.execute SQL statements.

Branches and merging

Two developers each create a revision with the same parent. Now there are two heads, and alembic upgrade head refuses to choose (it reports "Multiple head revisions"). The fix is a merge revision that has both heads as parents.

flowchart LR
    A["0002"] --> B["0003a<br/>add phone<br/>(Dana)"]
    A --> C["0003b<br/>add index<br/>(Eli)"]
    B --> M["0004<br/>merge"]
    C --> M

Two branches share a parent. A merge revision with down_revision = ("0003a", "0003b") joins them back into one line.

TEXT
alembic heads                       # shows both heads
alembic merge -m "merge heads" 0003a 0003b
alembic upgrade head

A merge revision is usually empty. It only records the join. A good team habit is to make CI run alembic heads and fail when it prints more than one line.

Deployment checklist

  • Back up first, and test the migration on a copy of production data.
  • Backward-compatible steps (expand, then contract). Deploy the schema change that old code tolerates (add a nullable column), then the code that uses it, then a later migration removes what is unused. This keeps rolling deploys safe.
  • Never edit a migration that already ran elsewhere. Create a new revision instead.
  • One purpose per revision, with a real downgrade().
  • Mind locks. Adding an index or a default can lock big tables; PostgreSQL offers CREATE INDEX CONCURRENTLY (run it with autocommit_block() in Alembic).
  • Run migrations once, from a release job, not at the startup of every app instance.
  • Pin the revision in the version table after restoring backups: alembic stamp <rev> records the correct state.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. How do you safely add a NOT NULL column to a table that already has rows?

    The three-step pattern (nullable, backfill, tighten) works on every database.
  2. 2. Why should data migrations avoid importing your ORM models?

    A migration must keep working forever, so it should not depend on the current shape of application code.
  3. 3. What is batch_alter_table for?

    Batch mode performs the move-and-copy recipe on SQLite and emits plain ALTER statements elsewhere.
  4. 4. alembic upgrade head reports multiple heads. What is the fix?

    alembic merge creates a revision whose down_revision is a tuple of the heads, rejoining the history.

Summary

  • For new NOT NULL columns: add nullable, backfill, then tighten.
  • Describe tables with sa.table() inside data migrations rather than importing models.
  • batch_alter_table lets SQLite (and similar databases) alter columns and constraints.
  • --sql offline mode prints SQL for review; it cannot read data back.
  • Parallel branches create multiple heads; alembic merge joins them.
  • Use expand-then-contract steps, back up, keep revisions small and never edit applied ones.

Next: Finding and Fixing N+1 Queries, and Bulk Operations