How do I execute raw SQL in a django migration

Question

I am aware of the cursor object in Django. Is there any other preferred way to execute raw SQL in migrations? I want to introduce postgresql partitioning for one of my models tables. The partition logic is a bunch of functions and triggers that have to be added to the database on setup which I'd like to automate.

Answer 1

One way:

The best way I found to do this is using RunSQL:

Migrations contains the RunSQL class. To do this:

1. ./manage.py makemigrations --empty myApp
2. edit the created migrations file to include:

operations = [ migrations.RunSQL('RAW SQL CODE') ]

As Nathaniel Knight mentioned, RunSQL also accepts a reverse_sql parameter for reversing the migration. See the docs for details

Another way

The way I solved my problem initially was using the post_migrate signal to call a cursor to execute my raw SQL.

What I had to add to my app was this:

in the __init__.py of myApp add:

default_app_config = 'myApp.apps.MyAppConfig'

Create a file apps.py:

from django.apps import AppConfig
from django.db.models.signals import post_migrate
from myApp.db_partition_triggers import create_partition_triggers


class MyAppConfig(AppConfig):
    name = 'myApp'
    verbose_name = "My App"

    def ready(self):
        post_migrate.connect(create_partition_triggers, sender=self)

New file db_partition_triggers.py:

from django.db import connection


def create_partition_triggers(**kwargs):
    print '  (re)creating partition triggers for myApp...'
    trigger_sql = "CREATE OR REPLACE FUNCTION...; IF NOT EXISTS(...) CREATE TRIGGER..."
    cursor = connection.cursor()
    cursor.execute(trigger_sql)
    print '  Done creating partition triggers.'

Now on every manage.py syncdb or manage.py migrate this function is called. So make sure it uses CREATE OR REPLACE and IF NOT EXISTS. So it can handle existing functions.

Answer 2

I would recommend django-migrate-sql-deux https://pypi.org/project/django-migrate-sql-deux/

This way you can manage database objects like views, functions, triggers in declarative way like models in Django. Then you need generate changes into Django migrations through makemigrations. And apply them via migrate. So the development and deploy flow is pretty same.

It would be awesome if Django would have this system for raw SQL "models" and handle migrations and dependencies automatically in makemigrations and migrate commands like django-migrate-sql-deux.