python / expert
Snippet
Django Database Migration Custom Operation for Zero-Downtime Schema Changes
This custom migration operation safely adds columns with defaults to production databases without causing table-level locks. It uses a three-phase approach: add column with default (fast on PostgreSQL), backfill NULL values, then add NOT NULL constraint. This pattern is essential for zero-downtime deployments where long-running table locks are unacceptable.
snippet.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
from django.db import migrationsclass AddColumnWithDefaultOperation(migrations.Operation):"""Safe column addition that avoids table locks on production databases."""def __init__(self, model_name, column_name, default_value, column_type='varchar(255)'):self.model_name = model_nameself.column_name = column_nameself.default_value = default_valueself.column_type = column_typedef state_forwards(self, app_label, state):new_field = migrations.CharField(max_length=255, default=self.default_value)new_field.set_attributes_from_name(self.column_name)state.add_model(app_label, self.model_name)setattr(state.models[app_label, self.model_name], self.column_name, new_field)def database_forwards(self, app_label, schema_editor, connection):if connection.vendor == 'postgresql':with connection.cursor() as cursor:cursor.execute(f"""ALTER TABLE {self.model_name}ADD COLUMN IF NOT EXISTS {self.column_name} {self.column_type} DEFAULT %s""", [self.default_value])cursor.execute(f"""UPDATE {self.model_name} SET {self.column_name} = %s WHERE {self.column_name} IS NULL""", [self.default_value])cursor.execute(f"""ALTER TABLE {self.model_name} ALTER COLUMN {self.column_name} SET NOT NULL""")elif connection.vendor == 'mysql':with connection.cursor() as cursor:cursor.execute(f"""ALTER TABLE {self.model_name} ADD COLUMN {self.column_name} {self.column_type}""")cursor.execute(f"""UPDATE {self.model_name} SET {self.column_name} = %s WHERE {self.column_name} IS NULL""", [self.default_value])cursor.execute(f"""ALTER TABLE {self.model_name} MODIFY COLUMN {self.column_name} {self.column_type} NOT NULL""")def describe(self):return f"Add column {self.column_name} to {self.model_name} with default {self.default_value}"
django
Breakdown
1
class AddColumnWithDefaultOperation(migrations.Operation)
Custom Operation subclass defining reversible migration for column addition
2
def database_forwards(self, app_label, schema_editor, connection)
Vendor-specific SQL implementation for PostgreSQL and MySQL databases
3
ADD COLUMN IF NOT EXISTS ... DEFAULT %s
PostgreSQL: adds column with default value without locking reads/writes on existing rows
4
UPDATE ... SET ... WHERE ... IS NULL
Backfills existing rows with default value before adding NOT NULL constraint
5
ALTER COLUMN ... SET NOT NULL
Adds NOT NULL constraint after all existing rows are backfilled with valid data