beginnerintermediateadvanced

Django · Section 33

Safe Database Migrations

Level: advanced
Read: 22 min
Concepts: 2

The documented, specific pattern for the most common dangerous migration — adding a non-nullable field to a table with existing rows — splits into three migrations (add nullable, backfill, tighten), since the database has no value to fill existing rows with otherwise. atomic=False plus processing rows in small, independently-committed batches avoids holding one long lock across a large backfill. The general expand-and-contract technique is the answer to zero-downtime schema changes during a rolling deployment, where old and new code genuinely coexist for a real window: add the new schema alongside the old, deploy dual-compatible code, backfill, switch over, then remove the old schema only once nothing depends on it anymore. SeparateDatabaseAndState decouples the real SQL a migration runs from what Django's own state tracking believes happened, needed for changes (like a table rename) that don't map cleanly onto Django's normal migration operations.

Django overview

Python Django Full Course for Beginners | Complete All-in-One Tutorial | 3 HoursDave Gray

What is true here

Adding a non-nullable field to a table with existing rows needs three migrations — add nullable, backfill via a data migration, then tighten to non-nullable — never one direct step.
atomic = False plus small, independently-committed batches avoids holding one long lock across a large data migration, at the cost of a real (documented) race window for rows created mid-migration.
Expand-and-contract is the general technique for a rolling deployment, where old and new code genuinely coexist for a real window — never remove something the currently-running old code still depends on.
A direct RenameField/RenameModel breaks old code still running against the old name during a rolling deployment — expand-and-contract (add new, dual-write, backfill, switch, remove old) is the safer path.
SeparateDatabaseAndState lets the real SQL a migration runs diverge from what Django's state tracking believes happened — needed for schema changes (like a table rename) that would otherwise generate an expensive, data-risking drop-and-recreate.

What you will be able to do

Write a safe, three-step migration for adding a non-nullable field to an existing table
Use atomic=False and batching correctly for a large data migration
Apply expand-and-contract to a schema change that must survive a rolling deployment
Use SeparateDatabaseAndState when a real schema change doesn't map cleanly onto Django's normal operations

Adding non-nullable fields, and large backfills

The documented three-migration pattern, and atomic=False plus batching for large tables.

Adding non-nullable fields, and large data migrations

coreadvanced

Adding a non-nullable field to a table with EXISTING rows fails outright — the database has no value to put in those rows. Django's own documented fix is three migrations: add the field nullable with a default, backfill every existing row via a data migration, THEN alter the field to drop null=True. For genuinely large tables, wrapping a big backfill in Django's normal single transaction holds a lock for the whole run — atomic = False on the migration class, combined with processing rows in small batches, avoids that.

Think of it as

A migration that adds a required (non-nullable) field is really asking the database to answer a question it can't answer on its own: "what value should THIS EXISTING row have for a field it never had?" Django can't invent that answer, so the honest fix is to split the single conceptual change into three separate, safe steps — first make the column exist but optional (nullable, or with a default), then go fill in a real value for every existing row, THEN (only once every row genuinely has a value) tighten the constraint to non-nullable. Skipping straight to the tightened constraint is what fails. The batching concern is a separate, second problem — even a WORKING backfill, if written as one giant UPDATE (or one huge Django migration transaction) against a table with millions of rows, can hold a lock for the entire duration, blocking other queries the whole time; atomic=False plus small batches trades one long lock for many short ones, letting other traffic interleave between batches.

python

class Migration(migrations.Migration):
    atomic = False   # required before batching a large backfill safely
    operations = [migrations.RunPython(backfill_in_batches)]

What we're doing: Backfill a UUID field on a large table in small batches, each its own transaction, avoiding one long-held lock across the entire table.

myapp/migrations/0006_backfill_uuid.pypython

def backfill_uuid(apps, schema_editor):
    MyModel = apps.get_model("myapp", "MyModel")
    while MyModel.objects.filter(uuid__isnull=True).exists():
        with transaction.atomic():
            for row in MyModel.objects.filter(uuid__isnull=True)[:1000]:
                row.uuid = uuid.uuid4()
                row.save(update_fields=["uuid"])

class Migration(migrations.Migration):
    atomic = False   # the migration itself is not one big transaction
    operations = [migrations.RunPython(backfill_uuid)]

1: The while loop processes 1000 rows at a time, repeating until none remain — never holding a lock across the ENTIRE table at once.
5: Each batch of 1000 gets its own transaction.atomic() block — a lock held only for that batch's duration, released before the next batch starts, letting other queries run in between.

Why this works: A naive migration backfilling all rows in one RunPython call, inside Django's default single migration transaction, would hold a lock on every touched row for the ENTIRE backfill's duration — on a table with millions of rows, that could be minutes of blocked writes; batching trades one long lock for thousands of short ones, letting normal application traffic interleave between batches.

Adding a required field directly, without the nullable-then-backfill-then-tighten sequence

Wrong

python

# one migration, straight to non-nullable, on a table with existing rows
migrations.AddField("MyModel", "uuid", models.UUIDField())   # no null=True, no default
# fails immediately: existing rows have no value for this new required column

Better

python

# split into 3 migrations: add nullable → backfill → tighten (see the keyFacts table)

What you see: django.db.utils.IntegrityError (or a similar constraint-violation error) raised the moment migrate runs, on any table that already has rows — the migration cannot proceed at all.

Why: A non-nullable field with no default has no value the database can use for rows that already existed before the column did — the database is not being unreasonable here, there genuinely is no answer to "what should this existing row's new required field be" until something (a backfill) supplies one, which is exactly why the safe pattern splits this into three separate steps instead of one.

The three-migration pattern for a non-nullable field

1. Add, nullable

AddField(..., null=True) — the column exists, nothing required yet

2. Backfill

a data migration sets a real value on every existing row

3. Tighten

AlterField(..., null=False) — safe now that every row has a value

The three-migration pattern for a non-nullable field

The three-migration pattern for a non-nullable field
Step	Migration does
1. Add, nullable	AddField(..., null=True) — the column exists, but nothing is required yet
2. Backfill	a data migration (RunPython) setting a real value on every existing row
3. Tighten	AlterField(..., null=False) — now safe, since every row has a value

Together

python

# migration 1: AddField("MyModel", "uuid", models.UUIDField(null=True))
# migration 2 (data migration):
def gen_uuid(apps, schema_editor):
    MyModel = apps.get_model("myapp", "MyModel")
    for row in MyModel.objects.filter(uuid__isnull=True):
        row.uuid = uuid.uuid4()
        row.save(update_fields=["uuid"])
# migration 3: AlterField("MyModel", "uuid", models.UUIDField(unique=True))

Remember: Adding a non-nullable field to a table with existing rows needs three migrations: add nullable, backfill via a data migration, then tighten to non-nullable — never all in one step. atomic = False plus small per-batch transactions avoids holding one long lock across a large backfill. Rows created in the gap between "added" and "backfilled" can still need explicit handling.

Expand-and-contract, and SeparateDatabaseAndState

The general zero-downtime technique, and decoupling real SQL from Django's tracked model state.

Expand-and-contract, and SeparateDatabaseAndState

coreadvanced

Expand-and-contract is the general answer to "how do I change a schema without downtime, when old and new code both run during a rolling deploy": add the new schema ALONGSIDE the old (expand), deploy code that can handle both, backfill data, switch reads/writes to the new schema, THEN remove the old schema (contract) — only once nothing depends on it anymore. SeparateDatabaseAndState lets a migration change the REAL database differently from what Django believes the models look like — needed for a schema change (like renaming a table) that Django's normal operations can't express without appearing to delete and recreate everything.

Think of it as

A rolling deployment means, for some window of time, OLD code and NEW code are both running against the SAME database simultaneously — expand-and-contract exists because a schema change that only the new code understands would break the old code still running, and a schema change the old code depends on breaks the new code the moment it starts up. "Expand" means the schema grows to support BOTH versions at once (add a column, don't yet remove the old one) — old code keeps working because nothing it relies on was touched, new code can start using the new column immediately. Only once every instance is confirmed running the new code (nothing left depending on the old schema) does "contract" remove what's no longer needed. SeparateDatabaseAndState is a narrower, more mechanical tool for the cases where the REAL SQL needed (e.g. a table rename, which is one ALTER statement) does not map cleanly onto what Django's migration operations would otherwise generate (which might look like drop-and-recreate, losing data) — it lets you say "run THIS SQL, but tell Django's own bookkeeping the model now looks like THIS," decoupling the two.

python

migrations.SeparateDatabaseAndState(
    database_operations=[migrations.RunSQL(sql="...", reverse_sql="...")],
    state_operations=[migrations.CreateModel(...), migrations.AlterField(...)],
)

What we're doing: Rename a table without a naive RenameModel, using SeparateDatabaseAndState so a table rename's real SQL and Django's state tracking can be expressed independently — a real use case for when a rename is genuinely the right move (not always the safest, but sometimes necessary).

core/migrations/0010_rename_table.pypython

operations = [
    migrations.SeparateDatabaseAndState(
        database_operations=[
            migrations.RunSQL(
                sql="ALTER TABLE core_book_authors RENAME TO core_authorbook",
                reverse_sql="ALTER TABLE core_authorbook RENAME TO core_book_authors",
            ),
        ],
        state_operations=[
            migrations.CreateModel(name="AuthorBook", fields=[...]),
        ],
    ),
]

3: database_operations is the REAL SQL that actually runs — a single, cheap RENAME TO statement, not a drop-and-recreate.
8: state_operations tells Django's own bookkeeping the model is now called AuthorBook — completely independent of what the real SQL did, since a plain rename has no equivalent "CreateModel" concept in reality.

Why this works: Without SeparateDatabaseAndState, expressing this change through Django's normal migration operations would likely generate a DROP + CREATE sequence (since Django has no single "just rename the table" operation for this specific case), which is far more expensive and risks data loss on a large table — splitting the real SQL from the state Django tracks lets the actual database operation stay as cheap and safe as a native RENAME.

Using a plain RenameField/RenameModel during a rolling deployment, breaking old code still running against the old name

Wrong

python

migrations.RenameField("Person", "name", "full_name")
# the column is renamed IMMEDIATELY — any old code instance still running,
# reading/writing "name", breaks the moment this migration applies

Better

python

# expand-and-contract instead: add full_name, dual-write, backfill, switch reads,
# THEN remove name — only once no running instance references it anymore

What you see: During a rolling deployment, some server instances are still running OLD code (which references the old field name) while the migration has already renamed the column — those old instances start raising errors on every request touching that field, for the entire window until they are all replaced by new code.

Why: A direct rename is instantaneous at the database level, but a rolling deployment is NOT instantaneous — old and new code coexist for a real window of time, and a direct rename assumes that window does not exist. Expand-and-contract exists specifically to make every step of a schema change safe to run WHILE both old and new code are simultaneously live, which a single atomic rename operation cannot guarantee.

Expand-and-contract, across a rolling deployment

1
Expand
add full_name alongside name — nothing removed yet
2
Dual-compatible code
new code writes to BOTH fields
3
Backfill
copy existing name values into full_name
4
Switch
reads/writes move to full_name exclusively
5
Contract
remove name — only once nothing depends on it

The expand-and-contract sequence

The expand-and-contract sequence
Step	What happens
1. Expand	add the new schema alongside the old — nothing is removed yet
2. Deploy dual-compatible code	new code understands both old and new schema
3. Backfill	populate the new schema from the old, for existing data
4. Switch	reads/writes move to the new schema exclusively
5. Contract	remove the old schema — only once nothing depends on it

Together

python

# renaming "name" to "full_name" without downtime:
# 1. AddField("Person", "full_name", ...) — expand
# 2. deploy code writing to BOTH name and full_name
# 3. RunPython backfill — copy existing "name" values into "full_name"
# 4. deploy code reading ONLY from full_name
# 5. RemoveField("Person", "name") — contract, once no code reads "name" anymore

Remember: Expand-and-contract: add the new schema alongside the old, deploy dual-compatible code, backfill, switch over, THEN remove the old schema — only once nothing depends on it. A rolling deployment has a real window where old and new code coexist; a direct rename/removal breaks whichever side is still on the old assumption. SeparateDatabaseAndState decouples the real SQL from what Django's state tracking believes happened, for changes (like a table rename) that don't map cleanly onto normal migration operations.