Executing Custom SQL in Django Migrations
By Szymon Lipiński
September 17, 2016
Since version 1.7, Django has natively supported database migrations similar to Rails migrations. The biggest difference fundamentally between the two is the way the migrations are created: Rails migrations are written by hand, specifying changes you want made to the database, while Django migrations are usually automatically generated to mirror the database schema in its current state.
Usually, Django’s automatic schema detection works quite nicely, but occasionally you will have to write some custom migration that Django can’t properly generate, such as a functional index in PostgreSQL.
Creating an empty migration
To create a custom migration, it’s easiest to start by generating an empty migration. In this example, it’ll be for an application called blog:
$ ./manage.py makemigrations blog --empty -n create_custom_index Migrations for 'blog': 0002_create_custom_index.py:
This generates a file at blog/migrations/0002_create_custom_index.py that will look something like this:
# -*- coding: utf-8 -*- # Generated by Django 1.9.4 on 2016-09-17 17:35 from __future__ import unicode_literals from django.db import migrations class Migration(migrations.Migration): dependencies = [ ('blog', '0001_initial'), ] operations = [ ]
Adding Custom SQL to a Migration
The best way to run custom SQL in a migration is through the migration.RunSQL operation. RunSQL allows you to write code for migrating forwards and backwards—that is, applying migrations and unapplying them. In this example, the first string in RunSQL is the forward SQL, the second is the reverse SQL.
# -*- coding: utf-8 -*- # Generated by Django 1.9.4 on 2016-09-17 17:35 from __future__ import unicode_literals from django.db import migrations class Migration(migrations.Migration): dependencies = [ ('blog', '0001_initial'), ] operations = [ migrations.RunSQL( "CREATE INDEX i_active_posts ON posts(id) WHERE active", "DROP INDEX i_active_posts" ) ]
Unless you’re using Postgres for your database, you’ll need to install the sqlparse library, which allows Django to break the SQL strings into individual statements.
Running the Migrations
Running your migrations is easy:
$ ./manage.py migrate Operations to perform: Apply all migrations: blog, sessions, auth, contenttypes, admin Running migrations: Rendering model states... DONE Applying blog.0002_create_custom_index... OK
Unapplying migrations is also simple. Just provide the name of the app to migrate and the id of the migration you want to go to, or “zero” to reverse all migrations on that app:
$./manage.py migrate blog 0001 Operations to perform: Target specific migration: 0001_initial, from blog Running migrations: Rendering model states... DONE Unapplying blog.0002_create_custom_index... OK
Hand-written migrations can be used for many other operations, including data migrations. Full documentation for migrations can be found in the Django documentation.
(This post originally covered South migrations and was updated by Phin Jensen to illustrate the now-native Django migrations.)