• Home

  • Custom Ecommerce
  • Application Development
  • Database Consulting
  • Cloud Hosting
  • Systems Integration
  • Legacy Business Systems
  • Security & Compliance
  • GIS

  • Expertise

  • About Us
  • Our Team
  • Clients
  • Blog
  • Careers

  • VisionPort

  • Contact
  • Our Blog

    Ongoing observations by End Point Dev people

    Executing Custom SQL in Django Migrations

    Szymon Lipiński

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

    django postgres python


    Comments