Jan 24, 2019

Creating Raw SQL Migrations in Django

I am using Django, which has great support for PostgreSQL-specific functions in it’s ORM. I wanted to use a SearchVectorField inside my application and PostgreSQL recommends creating a trigger for to populate that column. Well that was easy enough but I didn’t want to have a snippet of SQL laying around to have to remember to put that in the database manually. Django has a great migration system – so why not use that? Can we manage a raw SQL entry in a Django migration?

It seems that you can, but no where spells it out step-by-step. I combined a few posts and Django documentation together and figured out how to create the trigger manually. Note that I didn’t make a backwards migration for it – I assumed if I didn’t need this after all, I will remove it myself or write another migration.

First step is to create an empty migration. This is simple: ./manage makemigration <myapp> --empty. It will tell you the file of the empty migration file.

Open that file in your editor, and it will look something like the file below. The entry in the dependencies line will be different based on your app name and what your previous migration was.

from django.db import connection
from django.db import migrations
 
class Migration(migrations.Migration):
 
    dependencies = [
        ('myapp, '0012_auto_20190122_1540'),
    ]
 
    operations = [
 
    ]

Now we need to write a function that will actually run the SQL that we need. Just put that function above the Migration class in the file. Here was mine.

def make_trigger(apps,schema_editor):
 
   with connection.cursor() as cursor:
      cursor.execute("""CREATE TRIGGER caretaker_tsvectorupdate BEFORE INSERT OR UPDATE
ON myapp_myobject FOR EACH ROW EXECUTE procedure
tsvector_update_trigger(search_vector, 'pg_catalog.english', search_txt);
""")

The cursor will execute our command to create our trigger. Of course, you can put any SQL command you want in there.

Now you just need to tell the migration to run that function. That turns out to be a one-liner in the Operations list. The final migration file looked list this:

from django.db import connection
from django.db import migrations
 
def make_trigger(apps,schema_editor):
 
   with connection.cursor() as cursor:
      cursor.execute("""CREATE TRIGGER caretaker_tsvectorupdate BEFORE INSERT OR UPDATE
ON units_caretaker FOR EACH ROW EXECUTE procedure
tsvector_update_trigger(search_vector, 'pg_catalog.english', search_txt);
""")
 
class Migration(migrations.Migration):
 
    dependencies = [
        ('units', '0012_auto_20190122_1540'),
    ]
 
    operations = [
       migrations.RunPython(make_trigger)
 
    ]

Now just run the migration: ./manage.py migrate myapp and now your SQL will be ran in your existing table.

About the Author

Mike Hostetler profile.

Mike Hostetler

Principal Technologist

Mike has almost 20 years of experience in technology. He started in networking and Unix administration, and grew into technical support and QA testing. But he has always done some development on the side and decided a few years ago to pursue it full-time. His history of working with users gives Mike a unique perspective on writing software.

One thought on “Creating Raw SQL Migrations in Django

  1. William AC says:

    Hi, I’m newbie in Django and I’m trying to understand Django philosophy for databases consistency.

    When dealing about consistency in databases, I understand (or understood?) that it refers to constraints, cascades, triggers, etc. at database level, hence a solid database diagram is (or was) very important. But I don’t feel that with Django, it seems that its philosophy is to bring all that consistency to every app in a Django project, and to leave database with relations only.

    I could (hardly) accept that if it was not because while I was using the database API, no rule (defined in models) applied on every record saved into database, then danger alarms sounded.

    Here a paradox: if it were necessary to establish the consistency at db level yet, why would I need define rules in models? is it not redundant?

    Now I was asking to myself, is the concurrency not dealt by database itself if it has consistency? why would I need an extra package like “django-concurrency”?
    I wish to know your expert opinion about these subjects. Thanks in advance.

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Blog Posts
Managing your Helm deployments with Helmfile
As we’ve been using Kubernetes to build software delivery platforms for our clients, we’ve found Helm to be a reasonable solution to templating and managing deployments to Kubernetes.  Although templating alternatives such as Kustomize have […]
AWS CodeBuild Test Reports for Gradle builds
Although AWS documentation has instructions for adding Test Reports for a maven build they currently lack instructions for a gradle build. You can find the maven instructions here: https://aws.amazon.com/blogs/devops/test-reports-with-aws-codebuild/ Assuming you have your gradle wrapper […]
Structuring SwiftUI Previews for API Calls
SwiftUI, together with Combine and Xcode 11+, provide a powerful toolset for quickly creating an app with a native UI. In Xcode 11+, the preview pane was introduced in order to provide live snapshots of […]
Seamlessly Integrating Micro Apps with iFrame
A recent client wanted to upgrade a small portion of their legacy application with a more modern UI and extra functionality, like fuzzy text search. There are a few approaches to incremental upgrades of legacy […]