How to Run a Django Migration “By Hand”
Normally your Django project’s deploy process runs the
migrate command, and that takes care of updating your database as necessary. Especially on smaller databases, Django’s migration system can “just do it” for you.
But sometimes it can be necessary to run migrations “by hand” in your database’s SQL console. I have found this to be the case with larger, busy databases, and when using tools like
pt-online-schema-change to apply schema changes. In this post we’ll cover the process for running a migration by hand, and adapting it to reversing migrations.
Run a Migration “By Hand”
Hold onto your butts…
1. Find the SQL to run
We write Django migrations in Python, but they ultimately end up running a series of SQL statements. To run a migration by hand, you need those SQL statements, so you can run them yourself.
You can display the SQL for a migration with Django’s
sqlmigrate command like:
$ ./manage.py sqlmigrate <app> <prefix>
This outputs each migration operation with a commented header describing what it does and then its actual statements. Replace
<app> with the label of the app that the migration lives in. Replace
<prefix> with a unique prefix of the migration’s name - normally a four digit number like
For example, to show the SQL for the
core app’s migration with name starting “0003”, you would run:
$ ./manage.py sqlmigrate core 0003 BEGIN; -- -- Add field page_count to book -- ALTER TABLE "core_book" ADD COLUMN "page_count" integer NULL; COMMIT;
You’ll only see the bookending with
COMMIT on databases that support transactional schema changes (SQLite and PostgreSQL, of Django’s built-in backends). These may be disabled per-migration, when necessary.
If any operations cannot be run as SQL, they will have the message
THIS OPERATION CANNOT BE WRITTEN AS SQL (from Django 4.1, older versions have slightly different wording). This normally means use of the
RunPython operation. For such operations, will need to figure out how to run them by hand separate to the process I’m covering here. You could also consider splitting them into their own migration.
A small note: it’s best to run
sqlmigrate against your production database. For certain operations, Django queries the database to find names of objects, patricularly index names when migrating older index definitions. Depending on how your various environments’ databases were created and migrated over time, these names can be different. Thus, the SQL that Django generates on your staging server may be different to that on production. But on the other hand, using your production settings with an unexecuted migration isn’t always easy, so you may just want to beware of this issue and adjust SQL when necessary.
2. Execute the SQL statement-by-statement
Open up your database’s SQL shell on the target environment with Django’s
$ ./manage.py dbshell
Here you can run the migration’s SQL statements from
sqlmigrate, one by one. Skip the comments from
sqlmigrate (the lines starting
--), and make sure you copy whole SQL statements that end with
For example, running the above on PostgreSQL:
$ ./manage.py dbshell psql (14.4, server 13.5 (Debian 13.5-1.pgdg110+1)) Type "help" for help. example=# BEGIN; BEGIN example=*# ALTER TABLE "core_book" ADD COLUMN "page_count" integer NULL; ALTER TABLE example=*# COMMIT; COMMIT example=#
Whilst running the migration, you should keep an eye on your database’s key metrics with whatever monitoring tools you use. You may also want to use a second
dbshell to run some administrative commands, for example in my recent PostgreSQL post I covered finding and stopping queries that block an
One note for databases supporting transactional schema changes (SQLite, PostgreSQL). If the migration has a single schema-changing statement like
ALTER TABLE, you can drop
COMMIT. This means less SQL to run, and you’ll hold schema locks for slightly less time, reducing the risk of the migration affecting your busy production database.
3. Record migration as executed
After you’ve run your migration’s statements by hand, you need to record the migration as executed. If you don’t, the
migrate command will try to again execute that migration, which could end catastrophically (but will normally just result in an error like “column already exists”).
Django’s migration systems keeps a record of executed migrations in a table called
django_migrations. You can add a record by using the
--fake option, like:
$ ./manage.py migrate --fake <app> <prefix>
But, I’ve normally found this isn’t an option when running migrations by hand, as it requires the migration file to be on your production system. Normally to put the migration into production, you need to run a deploy, which runs migrations automatically, ruling out
migrate --fake (unless you copy over the migration file).
Under the hood,
migrate --fake only inserts one row, which you can do manually instead with this query template:
INSERT INTO django_migrations (app, name, applied) VALUES (<app>, <name>, NOW());
<app> with the label of the app that the migration lives in. Replace
<name> with the full name of the migration, which is the migration’s filename without the
.py extension. (Be careful not to use just a prefix like
For example, to mark our example migration as complete:
example=# INSERT INTO django_migrations (app, name, applied) VALUES ('core', '0003_book_page_count', NOW()); INSERT 0 1
You can check your entry looks right by visually comparing it with others:
SELECT * FROM django_migrations ORDER BY applied DESC;
…and just like that, you’re done applying the migration!
migrate (proabably by deploying)
When you run
migrate, Django sends its
post_migrate signal, which in turn runs various receiver functions. A few are built-in to Django, such as the synchronization of newly created models to content types. You may have others in your project or third party packages. After running a migration by hand, you’ll want to still run
migrate to run such signal receivers.
Most Django projects should run
migrate as an automatic step in their deployment process. So, it’s likely all you need to do here is deploy the changes that include the migration.
Reverse a Migration “By Hand”
You can use the above process to reverse a migration “by hand” with a couple of changes.
In step one, use the
--backwards flag to
sqlmigrate to generate the SQL statements to undo the migration. Use this template:
$ ./manage.py sqlmigrate --backwards <app> <prefix>
And in step three, you’ll want to delete from, rather than insert into, the
django_migrations table. Use this template:
DELETE FROM django_migrations WHERE app = <app> AND name = <name>;
Hopefully you don’t need to roll back migrations often!
Make your development more pleasant with Boost Your Django DX.
One summary email a week, no spam, I pinky promise.
- How to optimize PostgreSQL queries from Django using pgMustard
- New Testing Features in Django 4.0
- Introducing django-upgrade, a tool for upgrading your Django projects