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.
The best way I found to do this is using RunSQL:
Migrations contains the RunSQL class. To do this:
./manage.py makemigrations --empty myApp
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
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.