thesarfo

Note

Running Custom SQL in a Migration

Creating an empty migration and using RunSQL to run arbitrary SQL, with a matching reverse operation.

views 0

Sometimes we need full control over generating or updating database schema. It is very easy in django. All we need to do is to create an empty migration and there we can write any arbitrary sequel code. see below

Terminal window
python manage.py makemigrations store --empty

A new migration file will be created in your store app, in which the operations array will be empty. OVer there you can use the RunSQL command to run custom sql.

The command takes 2 arguments, first argument is for modifying or updating the the second one is for deleting. Even though the second argument is optional, you have to add it else you can’t undo the change

class Migration(migrations.Migration):


    dependencies = [
        ('store', '0001_initial'),
    ]


    operations = [
        migrations.RunSQL("""
            INSERT INTO store_collection (title)
            VALUES ('collection1')
        """, """
        DELETE FROM store_collection
        WHERE title='collection1'
        """)
    ]

Save the changes, and in your terminal…run

Terminal window
python manage.py migrate

If you open the store_collection table in datagrip you will see the update.

If you want to unapply the migration simply migrate the store app to the migration number you want.

Terminal window
python manage.py migrate store 0001

Now let’s unapply the migration