How to Run a Django Migration “By Hand”

You can lead a pegasus to the database, but you can’t force it to migrate.

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 0003.

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 BEGIN and 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 dbshell command:

$ ./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 ALTER TABLE.

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 BEGIN and 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 migrate command’s --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());

Replace <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 0003!)

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!

4. Run migrate (proabably by deploying)

Update (2022-07-06): Added this section thanks to a prompt from Nick Pope.

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!

Fin

May all your migrations run smoothly,

—Adam


Learn how to make your tests run quickly in my book Speed Up Your Django Tests.


Subscribe via RSS, Twitter, Mastodon, or email:

One summary email a week, no spam, I pinky promise.

Related posts:

Tags: