What's the best way to load stored procedures into the Django unit test database?

Question

I've got some Postgres stored procedures that my selenium tests will depend on. In development, I load them with a line in a script:

cat stored_procedures.sql | python manage.py dbshell

This doesn't work when unit testing, since a fresh database is created from scratch. How can I load stored procedures saved in a file into the test database before unit tests are run?

Answer 1

I think you have few ways to make this. In my opinion, the best solution - to add migration with your custom SQL. In future, you'll need that migration not only at development, but also at production stage. So, It would be not clear deploy procedure, if you'll store change to DB in few places.

Other way - just to add execution of your SQL to setUp method of testCase.

Additional migration

You should create a new empty migration ./manage.py makemigrations --empty myApp

Add your SQL code to operations list

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

Answer 2

Another solution to this is to create a management command that executes the required SQL Query and then just execute this command at the very beginning of your tests. Below is my case: Management command:

import os
from django.core.management import BaseCommand
from django.db import connection

from applications.cardo.utils import perform_query
from backend.settings import BASE_DIR


class Command(BaseCommand):
    help = 'Loads all database scripts'

    def handle(self, **options):
        db_scripts_path = os.path.join(BASE_DIR, 'scripts', 'database_scripts')
        utils_path = os.path.join(db_scripts_path, 'utils.sql')
        with open(utils_path, mode='r') as f:
            sql_query = f.read()
            with connection.cursor() as cursor:
                cursor.execute(sql_query)

Now you can just go to the terminal and type

python manage.py load_database_scripts

and the scripts will be loaded.

With a helper function like this one

def load():
    with django_db_blocker.unblock():
        call_command('load_database_scripts')

You just call this load function before the test suite runs.