Django Migration RunSQL Conditional on Database Type

Question

I am trying to use a migrations.RunSQL Django migration to run some arbitrary SQL code. I would like to run this migration for only certain db backends (for example only for postgres).

I would think to use something like this but I don't see the DB connection info in the Migration class.

score 26 · Answer 1 · answered Jul 21 '17 at 08:23

Here is how I solved the problem since I couldn't get RunSQL to work inside RunPython. Thankfully, the schema_editor object has an execute() method.

def forwards(apps, schema_editor):
    if not schema_editor.connection.vendor.startswith('postgres'):
        logger.info('Database vendor: {}'.format(schema_editor.connection.vendor))
        logger.info('Skipping migration without attempting to ADD CONSTRAINT')
        return

    schema_editor.execute('ALTER TABLE my_table ADD CONSTRAINT my_constraint (my_field != \'NaN\';)')


def backwards(apps, schema_editor):
    if not schema_editor.connection.vendor.startswith('postgres'):
        logger.info('Database vendor: {}'.format(schema_editor.connection.vendor))
        logger.info('Skipping migration without attempting to DROP CONSTRAINT')
        return

    schema_editor.execute('ALTER TABLE my_table DROP CONSTRAINT my_constraint;')


class Migration(migrations.Migration):

    dependencies = [
        ...
    ]

    operations = [
        migrations.RunPython(forwards, backwards, atomic=True)
    ]

Be careful with `atomic=False`. If some actions succeed and others fail, your database could end up in an inconsistent state. — PaulMest, Jan 12 '18 at 21:05
The `atomic=False` has to be used at least on MySQL and Oracle, see https://docs.djangoproject.com/en/3.0/ref/migration-operations/#runpython — Michal Čihař, Mar 09 '20 at 14:10

score 8 · Answer 2 · answered May 30 '16 at 08:37

8

I just had the same need. I had to edit a migration that set the initial value of a sequence, which works on postgres but not sqlite. Here's how I wrapped the RunSQL inside a RunPython, following the documentation that Daniel linked to.

from django.db import migrations


def forwards(apps, schema_editor):
    if not schema_editor.connection.vendor == 'postgres':
        return
    migrations.RunSQL(
        "alter sequence api_consumer_id_seq restart with 1000500;")


class Migration(migrations.Migration):
    dependencies = [
        ('api', '0043_auto_20160416_2313'),
    ]

    operations = [
        migrations.RunPython(forwards),
    ]

answered May 30 '16 at 08:37

Matthew

559
7
10

1

My vendor string was `postgresql` and not `postgres` (in Django 1.10.6) – James Hiew May 11 '17 at 09:14
5

The problem with this is that `migrations.RunSQL()` returns an object. It doesn't actually execute any SQL until that object's `_run_sql()` method is called with certain parameters. See PaulMest's response for a solution that also uses the `migrations.RunPython()` method but executes SQL in a way that actually executes SQL. – radicalbiscuit Feb 17 '18 at 21:38

score 6 · Answer 3 · answered Jun 10 '16 at 04:53

I solved a similar problem today -- needing to perform a migration to create a new model, but only for a postgres DB -- and I found this question. However, Matthew's answer did not help me. In fact, I'm not sure it works at all. That is because the line with migrations.RunSQL(...) does not actually run SQL; it creates a new object of type RunSQL which is a Command, and then immediately discards it.

Here's how I ended up solving the problem, in case anyone tries to search for "django conditional migration" in the future:

from __future__ import unicode_literals

import django.contrib.postgres.fields
from django.db import migrations, models


class PostgresOnlyCreateModel(migrations.CreateModel):
    def database_forwards(self, app_label, schema_editor, from_state, to_state):
        if schema_editor.connection.vendor.startswith("postgres"):
            super(PostgresOnlyCreateModel, self).database_forwards(app_label, schema_editor, from_state, to_state)

    def database_backwards(self, app_label, schema_editor, from_state, to_state):
        if schema_editor.connection.vendor.startswith("postgres"):
            super(PostgresOnlyCreateModel, self).database_backwards(app_label, schema_editor, from_state, to_state)


class Migration(migrations.Migration):

    dependencies = [
        ...whatever...
    ]

    operations = [
        PostgresOnlyCreateModel(
            name='...whatever...',
            fields=[...whatever...],
        ),
    ]

score 0 · Answer 4 · answered Sep 03 '15 at 20:16

0

That information is not provided in the Migration class, it is provided in the schema_editor attribute passed to a RunPython operation. See the documentation for some examples on using this.

answered Sep 03 '15 at 20:16

Daniel Roseman

588,541
66
880
895

Doug Bradshaw · Answer 5 · 2021-02-21T00:38:37.087

0

Another option is to have the actual sql depend on db.connection.vendor:

from django.db import connection

CONCURRENTLY = "CONCURRENTLY" if connection.vendor == "postgres" else ""
SQL = f"CREATE INDEX {CONCURRENTLY}..."

At that point you can just use migrations.RunSQL, which is handy, particularly if you use the state_operations argument.

edited Feb 21 '21 at 00:38

answered Feb 21 '21 at 00:30

Doug Bradshaw

1,452
1
16
20

jayvynl · Answer 6 · 2022-03-01T03:23:15.843

If you want to apply migrations depending on app or model, I think the best solution is using django database router.

First define a database router class:

from django.db import connections

class PgOnlyMigrateRouter:

    def allow_migrate(self, db, app_label, model_name=None, **hints):
        if app_label == 'pgonly_app' or model_name == 'pgonly_model':
            return connections[db].vendor.startswith("postgres")
        return None

Then in your setting.py file, add this line:

DATABASE_ROUTERS = ['path.to.PgOnlyMigrateRouter']

As you see, this works for all migrations in specified model or app, not just a single migration operation.

Django Migration RunSQL Conditional on Database Type

6 Answers6