In a Django migration, when would you use RunSQL instead of RunPython, and what do its reverse_sql and state_operations arguments do?

middleshould knowfreq 35%

answer

set-based versus row by row
features the ORM does not model
project state is not the database
the undo statement

basics

~10 s

RunSQL runs raw SQL: use it for set-based updates or database features Django does not model. reverse_sql is the SQL run on unapply; state_operations tells Django's project state what schema the SQL created.

solid answer

~40 s

RunSQL executes raw SQL inside a migration. I reach for it when one set-based UPDATE beats looping rows through Python, for example splitting full_name on millions of rows, or when I need something Django's operations do not express, like a trigger, a function or an extension. reverse_sql is what runs when the migration is unapplied; leave it out and the operation is irreversible, or pass RunSQL.noop to make rollback do nothing. state_operations matters when the SQL changes schema: it is a list of normal operations such as AddField that Django applies to its project state, so the autodetector knows the column exists and does not try to add it again. The costs are portability, hard-coded table names and SQL that bypasses the historical models.

`RunSQL` executes raw SQL inside a migration. I reach for it when one set-based `UPDATE` beats looping rows through Python, for example splitting `full_name` on millions of rows, or when I need something Django's operations do not express, like a trigger, a function or an extension. `reverse_sql` is what runs when the migration is unapplied; leave it out and the operation is irreversible, or pass `RunSQL.noop` to make rollback do nothing. `state_operations` matters when the SQL changes schema: it is a list of normal operations such as `AddField` that Django applies to its project state, so the autodetector knows the column exists and does not try to add it again. The costs are portability, hard-coded table names and SQL that bypasses the historical models.

code

python · 18 lines

python

from django.db import migrations


class Migration(migrations.Migration):
    dependencies = [("crm", "0007_customer_first_name_customer_last_name")]

    operations = [
        migrations.RunSQL(
            sql="""
                UPDATE crm_customer
                SET first_name = split_part(full_name, ' ', 1),
                    last_name = substr(full_name, length(split_part(full_name, ' ', 1)) + 2)
                WHERE first_name = '';
            """,
            reverse_sql=migrations.RunSQL.noop,
            hints={"model_name": "customer"},
        ),
    ]

go deeper

for a junior

Know that RunSQL runs raw SQL in a migration and needs reverse_sql to be undoable.

for a middle

Choose between RunSQL and RunPython for a given change, and explain reverse_sql, RunSQL.noop and state_operations with the makemigrations consequence.

for a senior

Weigh portability and review cost against speed, keep project state in sync with hand-written schema, and handle transaction and router hints correctly.

for a principal

Decide how much database-specific SQL the codebase accepts in migrations and who reviews it, given it ties the project to one backend.

Two ways to script a change

RunPython runs a Python function against historical models; it is portable across backends and reads like normal ORM code, but it loads rows into Python unless you write set-based queryset update() calls. RunSQL sends raw SQL straight to the database through the schema editor; it is as fast as the database can make it and can use anything the database offers, but it is tied to one SQL dialect and knows nothing about your models.

Aspect	`RunPython`	`RunSQL`
Language	Python with historical models	raw SQL strings
Portability	across Django backends	usually one database
Large updates	row loops unless you use `update()`	one set-based statement
Schema changes	via `schema_editor`, discouraged	possible, but pair with `state_operations`
Undo	`reverse_code`	`reverse_sql`
Shared options	`hints`, `elidable`	`hints`, `elidable`

When RunSQL is the better tool

Big set-based data changes: one UPDATE crm_customer SET ... over 30 million rows avoids streaming every row through Python.
Database objects Django does not model: triggers, stored functions, extensions, custom collations or check logic Django cannot express.
SQL you already have from a DBA review, where rewriting it in the ORM would add risk.

Stick to RunPython when the logic is easier to express and review in Python, when the project must run on more than one backend, or when the rule needs model knowledge such as choices or relations.

The arguments

RunSQL(sql, reverse_sql=None, state_operations=None, hints=None, elidable=False):

sql: a string, a list of strings, or a list of (sql, params) 2-tuples, parameterised like cursor.execute(). When you pass parameters, literal percent signs in the query must be doubled.
reverse_sql: the same shapes, run on unapply. None (the default) makes the operation irreversible, so rolling back raises IrreversibleError; RunSQL.noop makes it reversible with no action.
state_operations: a list of ordinary operations, such as AddField("customer", "search_key", models.CharField(...)), applied to Django's in-memory project state but not to the database. Without it, a column you created by hand is invisible to the autodetector, and the next makemigrations proposes adding it again.
hints: a dict passed as **hints to each database router's allow_migrate(), so multi-database setups can decide whether the SQL runs on a given alias. Django's docs suggest passing model_name when the SQL affects one model.
elidable: declares the operation safe to drop when the history is squashed.

Pitfalls

Transaction control: on PostgreSQL and SQLite, only write BEGIN or COMMIT in the SQL of a non-atomic migration (atomic = False), or you break Django's transaction state.
Hard-coded names: the SQL names crm_customer and its columns directly; if db_table or a column name changes later, old migrations still reference the name as it was then, which is usually what you want, but review carefully.
Statement splitting: on most backends other than PostgreSQL, Django splits a single SQL string into separate statements before running them.
Schema drift: SQL that alters tables without matching state_operations desynchronises project state and the real schema, which later autodetected migrations inherit.

In short

RunSQL is the escape hatch for work the ORM does badly or cannot express. reverse_sql keeps it undoable, and state_operations keeps Django's picture of the schema honest.

## Two ways to script a change `RunPython` runs a Python function against **historical models**; it is portable across backends and reads like normal ORM code, but it loads rows into Python unless you write set-based queryset `update()` calls. `RunSQL` sends **raw SQL** straight to the database through the schema editor; it is as fast as the database can make it and can use anything the database offers, but it is tied to one SQL dialect and knows nothing about your models. | Aspect | `RunPython` | `RunSQL` | |---|---|---| | Language | Python with historical models | raw SQL strings | | Portability | across Django backends | usually one database | | Large updates | row loops unless you use `update()` | one set-based statement | | Schema changes | via `schema_editor`, discouraged | possible, but pair with `state_operations` | | Undo | `reverse_code` | `reverse_sql` | | Shared options | `hints`, `elidable` | `hints`, `elidable` | ## When RunSQL is the better tool - **Big set-based data changes**: one `UPDATE crm_customer SET ...` over 30 million rows avoids streaming every row through Python. - **Database objects Django does not model**: triggers, stored functions, extensions, custom collations or check logic Django cannot express. - **SQL you already have** from a DBA review, where rewriting it in the ORM would add risk. Stick to `RunPython` when the logic is easier to express and review in Python, when the project must run on more than one backend, or when the rule needs model knowledge such as choices or relations. ## The arguments `RunSQL(sql, reverse_sql=None, state_operations=None, hints=None, elidable=False)`: 1. **`sql`**: a string, a list of strings, or a list of `(sql, params)` 2-tuples, parameterised like `cursor.execute()`. When you pass parameters, literal percent signs in the query must be doubled. 2. **`reverse_sql`**: the same shapes, run on unapply. `None` (the default) makes the operation irreversible, so rolling back raises `IrreversibleError`; `RunSQL.noop` makes it reversible with no action. 3. **`state_operations`**: a list of ordinary operations, such as `AddField("customer", "search_key", models.CharField(...))`, applied to Django's in-memory **project state** but not to the database. Without it, a column you created by hand is invisible to the autodetector, and the next `makemigrations` proposes adding it again. 4. **`hints`**: a dict passed as `**hints` to each database router's `allow_migrate()`, so multi-database setups can decide whether the SQL runs on a given alias. Django's docs suggest passing `model_name` when the SQL affects one model. 5. **`elidable`**: declares the operation safe to drop when the history is squashed. ## Pitfalls - **Transaction control**: on PostgreSQL and SQLite, only write `BEGIN` or `COMMIT` in the SQL of a non-atomic migration (`atomic = False`), or you break Django's transaction state. - **Hard-coded names**: the SQL names `crm_customer` and its columns directly; if `db_table` or a column name changes later, old migrations still reference the name as it was then, which is usually what you want, but review carefully. - **Statement splitting**: on most backends other than PostgreSQL, Django splits a single SQL string into separate statements before running them. - **Schema drift**: SQL that alters tables without matching `state_operations` desynchronises project state and the real schema, which later autodetected migrations inherit. ## In short `RunSQL` is the escape hatch for work the ORM does badly or cannot express. `reverse_sql` keeps it undoable, and `state_operations` keeps Django's picture of the schema honest.

What are the hints arguments of RunSQL and RunPython for?
They are passed as `**hints` to `allow_migrate()` on each router in `DATABASE_ROUTERS`. A router can use them, for example a `target_db` key or `model_name`, to allow the operation on one database alias and skip it on others. With no router expressing an opinion, the operation runs.
Could you get a set-based update without raw SQL?
Often yes: inside `RunPython`, a queryset `update()` with expressions and database functions runs as one UPDATE statement on the historical model. It stays portable across backends, though complex string logic may be clumsier than in the database's own SQL.

saying these in an interview costs you the question

makemigrations notices columns created by RunSQL on its own
RunSQL without reverse_sql rolls back by doing nothing
RunSQL goes through the ORM, so it is portable across databases
BEGIN and COMMIT are fine inside RunSQL in any migration
Percent signs in RunSQL never need doubling, even with params

In a Django migration, when would you use RunSQL instead of RunPython, and what do its reverse_sql and state_operations arguments do?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~3 min

Two ways to script a change

When RunSQL is the better tool

The arguments

Pitfalls

In short

▾common follow-ups2 questions

▾red flags5 flags

Two ways to script a change

When RunSQL is the better tool

The arguments

Pitfalls

In short

answer

go deeper