Migrating with Live Data
28 min · Intermediate
Need help? Ask a tutor on WhatsApp (opens in a new tab)A new, empty table is easy. A live table has rows and users. If one migration makes a change that the old rows cannot satisfy, it fails or locks the table. The safe way is a sequence of small steps.
A required column meets old rows
Server default is a default value stored in the table definition, so the database itself fills the column for existing rows and for inserts that do not give a value.
You add a NOT NULL column, but a thousand rows already exist and have no value for it. The database refuses, because those rows would break the rule. Either give the column a default or split the work in steps.
# fails: existing rows would have NULLop.add_column("customer", sa.Column("status", sa.String(10), nullable=False))
# works: the database fills old rows with the defaultop.add_column("customer", sa.Column( "status", sa.String(10), nullable=False, server_default="active"))Example 1 · Why a bare NOT NULL fails, and the default fix
import sqlalchemy as safrom alembic.migration import MigrationContextfrom alembic.operations import Operations
engine = sa.create_engine("sqlite://")conn = engine.connect()op = Operations(MigrationContext.configure(conn))
def cols(table): return [c["name"] for c in sa.inspect(conn).get_columns(table)]
op.create_table( "customer", sa.Column("id", sa.Integer, primary_key=True), sa.Column("full_name", sa.String(60), nullable=False),)conn.execute(sa.text("insert into customer (full_name) values ('Asha Rao'), ('Ravi Kumar'), ('Meena Das')"))
def rows(): return conn.execute(sa.text("select * from customer order by id")).all()
try: op.add_column("customer", sa.Column("status", sa.String(10), nullable=False))except sa.exc.OperationalError as err: print("refused:", err.orig)op.add_column("customer", sa.Column("status", sa.String(10), nullable=False, server_default="active"))print(rows())refused: Cannot add a NOT NULL column with default value NULL [(1, 'Asha Rao', 'active'), (2, 'Ravi Kumar', 'active'), (3, 'Meena Das', 'active')]
What this code does
- SQLite refuses the first call because the three old rows would have no value.
- With
server_default="active", every old row got the default.
A server_default is the shortest fix when every old row should get the same value. When the value depends on other data, use the three steps in the next section.
Add, backfill, then tighten
Backfill means filling in values for rows that already exist after a new column is added, usually with an UPDATE based on other data.
Do not do everything in one step. First add the column as nullable, so old rows are accepted. Second, backfill: fill the old rows with real values. Third, change the column to NOT NULL, which is now safe.
op.add_column("orders", sa.Column("size", sa.String(10), nullable=True))op.execute("update orders set size = case when total >= 1000 then 'big' else 'small' end")with op.batch_alter_table("orders") as batch: batch.alter_column("size", existing_type=sa.String(10), nullable=False)Example 2 · Add, backfill, then tighten
import sqlalchemy as safrom alembic.migration import MigrationContextfrom alembic.operations import Operations
engine = sa.create_engine("sqlite://")conn = engine.connect()op = Operations(MigrationContext.configure(conn))
def cols(table): return [c["name"] for c in sa.inspect(conn).get_columns(table)]
op.create_table("orders", sa.Column("id", sa.Integer, primary_key=True), sa.Column("total", sa.Integer, nullable=False))conn.execute(sa.text("insert into orders (total) values (200), (1500), (999), (4000)"))
op.add_column("orders", sa.Column("size", sa.String(10), nullable=True))print("after add:", conn.execute(sa.text("select count(*) from orders where size is null")).scalar_one(), "rows empty")op.execute("update orders set size = case when total >= 1000 then 'big' else 'small' end")print("after backfill:", conn.execute(sa.text("select count(*) from orders where size is null")).scalar_one(), "rows empty")with op.batch_alter_table("orders") as batch: batch.alter_column("size", existing_type=sa.String(10), nullable=False)print([(c["name"], c["nullable"]) for c in sa.inspect(conn).get_columns("orders")])print(conn.execute(sa.text("select total, size from orders order by id")).all())after add: 4 rows empty
after backfill: 0 rows empty
[('id', False), ('total', False), ('size', False)]
[(200, 'small'), (1500, 'big'), (999, 'small'), (4000, 'big')]What this code does
- The nullable column is accepted. The backfill gives each row a value based on its total.
- Only then is the NOT NULL rule added, and it can no longer fail.
Example 3 · Backfill in batches
import sqlalchemy as safrom alembic.migration import MigrationContextfrom alembic.operations import Operations
engine = sa.create_engine("sqlite://")conn = engine.connect()op = Operations(MigrationContext.configure(conn))
def cols(table): return [c["name"] for c in sa.inspect(conn).get_columns(table)]
op.create_table("ticket", sa.Column("id", sa.Integer, primary_key=True), sa.Column("priority", sa.String(10), nullable=True))conn.execute(sa.text("insert into ticket (id) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)"))
batch_size = 4last_id = 0batches = 0while True: result = conn.execute(sa.text("update ticket set priority = 'normal' where id in (select id from ticket where id > :last and priority is null order by id limit :n)"), {"last": last_id, "n": batch_size}) if result.rowcount == 0: break last_id += batch_size batches += 1print(batches, "batches")print(conn.execute(sa.text("select count(*) from ticket where priority is null")).scalar_one(), "rows empty")3 batches 0 rows empty
What this code does
- Each loop updates at most 4 rows, and a real migration would commit after each batch.
- Ten rows needed three batches: 4, 4 and 2. The next loop found nothing and stopped.
If the table is big, run the backfill in small batches and commit between them, so no single statement holds a lock for long.
Expand and contract for renames
Expand and contract is a way to change a schema in two phases: first add the new structure beside the old one, and drop the old structure only after nothing uses it.
Renaming a column in one step breaks the running app, which still uses the old name. Instead, expand first: add the new column and write to both. Then migrate the data. Contract last: drop the old column when nothing uses it.
# 1 expand: add column "name"; the app writes both columns# 2 migrate: update customer set name = full_name where name is null# 3 switch: the app reads "name" only# 4 contract: drop column "full_name"Example 4 · Rename with expand and contract
import sqlalchemy as safrom alembic.migration import MigrationContextfrom alembic.operations import Operations
engine = sa.create_engine("sqlite://")conn = engine.connect()op = Operations(MigrationContext.configure(conn))
def cols(table): return [c["name"] for c in sa.inspect(conn).get_columns(table)]
op.create_table( "customer", sa.Column("id", sa.Integer, primary_key=True), sa.Column("full_name", sa.String(60), nullable=False),)conn.execute(sa.text("insert into customer (full_name) values ('Asha Rao'), ('Ravi Kumar'), ('Meena Das')"))
def rows(): return conn.execute(sa.text("select * from customer order by id")).all()
op.add_column("customer", sa.Column("name", sa.String(60), nullable=True))conn.execute(sa.text("insert into customer (full_name, name) values ('Sara Ali', 'Sara Ali')"))print("expand:", rows())op.execute("update customer set name = full_name where name is null")print("migrated:", [r.name for r in rows()])with op.batch_alter_table("customer") as batch: batch.alter_column("name", existing_type=sa.String(60), nullable=False) batch.drop_column("full_name")print("contract:", [c["name"] for c in sa.inspect(conn).get_columns("customer")])print(rows())expand: [(1, 'Asha Rao', None), (2, 'Ravi Kumar', None), (3, 'Meena Das', None), (4, 'Sara Ali', 'Sara Ali')] migrated: ['Asha Rao', 'Ravi Kumar', 'Meena Das', 'Sara Ali'] contract: ['id', 'name'] [(1, 'Asha Rao'), (2, 'Ravi Kumar'), (3, 'Meena Das'), (4, 'Sara Ali')]
What this code does
- In the expand step, the new row writes both columns, like the updated app does. The old rows have no
nameyet. - After the migrate step all rows have
name. Contract dropsfull_namewhen nothing needs it.
Each step is a separate release, and each one is safe to roll back. The old and new code can both run while the change is in progress.
Next step
Check your understanding
11 quick questions