Enable integrity checking with sqlite in django

Question

In my django project, I use mysql db for production, and sqlite for tests.

Problem is, some of my code rely on model integrity checking. It works well with mysql, but integrity errors are not thrown when the same code is executed in tests.

I know that foreign keys checking must be activated in sqlite :

PRAGMA foreign_keys = 1;

However, I don't know where is the best way to do this activation (same question here).

Moreover, the following code won't work :

def test_method(self):
    from django.db import connection
    cursor = connection.cursor()
    cursor.execute('PRAGMA foreign_keys = ON')
    c = cursor.execute('PRAGMA foreign_keys')
    print c.fetchone()
    >>> (0,)

Any ideas?

Alrzc

Why aren't you using the same DB for the tests that is used in production ? Isn't the aim of the test environnement to reproduce the production environnement ?* – Amadaamadas

Testing a single app, with time python manage.py test products. Mysql : 0m58.232s. Sqlite : 0m5.153s. – Alrzc

Not a single beginning of a solution here but looks like a lot of discussion (and patches) are going on on this ticket about the kind of issue you're raising here code.djangoproject.com/ticket/11665 – Singe

@Thibault J: I knew that would be the reason, however in testing, isn't it more important that tests fit to real situation ? I'm currently in the same situation, different DBMS between test and prod, and I had some serious WTF, even if I'm using an ORM. – Amadaamadas

@Clement That is very important indeed. But I'm using TDD, and I cannot wait 60s to run a single test every line of code. On my continuous integration plateforme, tests are run under mysql. – Alrzc

Answer 1

So, if finally found the correct answer. All I had to do was to add this code in the __init__.py file in one of my installed app:

from django.db.backends.signals import connection_created


def activate_foreign_keys(sender, connection, **kwargs):
    """Enable integrity constraint with sqlite."""
    if connection.vendor == 'sqlite':
        cursor = connection.cursor()
        cursor.execute('PRAGMA foreign_keys = ON;')

connection_created.connect(activate_foreign_keys)

Alrzc

This doesn't seem to work on the initial data that's added during syncdb. – Eke

Be sure to remove old migration files which does not create foreign keys, otherwise foreign keys will not be created. I was trapped by this for a while. – Bakke

Although I removed my DB file, removed all the previous migrations, and created new migrations, this made no difference for me. The tables were not created with "on delete cascade" as expected. I wonder why that's not happening. – Climate

Answer 2

You could use django signals, listening to post_syncdb.

from django.db.models.signals import post_syncdb

def set_pragma_on(sender, **kwargs):
    "your code here"

post_syncdb.connect(set_pragma_on)

This ensures that whenever syncdb is run (syncdb is run, when creating the test database), that your SQLite database has set 'pragma' to 'on'. You should check which database you are using in the above method 'set_pragma_on'.

Funeral

Your answer is not perfectly correct, but it guided me toward the correct solution (see my answer on same page). Muchos gracias. – Alrzc

This won't have the expected effect: the post_syncdb command only runs after syncdb, not for any other connections (and the pragma does not persist beyond the current connection). – Cherida