Support setting pragma options for SQLite.

[Curtis Maloney]

SQLite, as of 3.7, supports a WAL journal mode ​https://www.sqlite.org/wal.html

It's been my experience that enabling this can dramatically improve performance, especially under heavy writes. I've seen some write-heavy operations go from a couple of minutes to a few seconds.

It can be enabled by executing the command:

PRAGMA journal_mode=wal;

which will return 'wal' on success.

If it fails to apply for any reason, it will return the mode it is in - 'delete' or 'memory'.

I propose to add JOURNAL_MODE as an option to the sqlite backend.

however, some quick tinkering shows that the sqlite backend doesn't ever explicitly create the DB, so it's not immediately apparent how to detect if/when this command should be issued.

I am still investigating.

[Artem Skoretskiy]

Any progress here?

We could add init_command to OPTIONS for sqlite backend like for MySQL.

Then you would be able to use any PRAGMA we need:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
        'OPTIONS': {
            'init_command': 'PRAGMA journal_mode=wal;',
        }
    }
}

[Artem Skoretskiy]

As a workaround, you could use ​https://stackoverflow.com/a/6843199 (use connection_created signal)

[Aaron Linville]

​https://github.com/django/django/pull/14824

I needed the ability to adjust some pragma settings associated with caching but as Artem noted, this will work for any pragma setting.

[Carlton Gibson]

Maybe allowing PRAGMAS to be set is a good idea, in general (e.g. #31765) but the WAL setting is persistent:

The persistence of WAL mode means that applications can be converted to using SQLite in WAL mode without making any changes to the application itself. One has merely to run "PRAGMA journal_mode=WAL;" on the database file(s) using the command-line shell or other utility, then restart the application. ​SQLite Docs.

So narrowly, running that a single time is sufficient, and an entry in the ​SQLite notes may be the quickest route forward (for this narrower issue).

[Afonso Silva]

Based on the previous answers, a workaround for allowing init_command similar to MySQL is to use a signal receiver for connection_created, e.g.:

from django.conf import settings                                                
from django.db.backends.signals import connection_created
from django.dispatcher import receiver
                         
@receiver(connection_created)    
def init_command(sender, connection, **kwargs) -> None:
    command = connection.settings_dict["OPTIONS"].get("init_command")
    if connection.vendor == "sqlite" and command:
        cursor = connection.cursor()
        cursor.execute(command)

This way you could add init_command to your settings.py:

DATABASES = {
     'default': {
         'ENGINE': 'django.db.backends.sqlite3',
         'NAME': BASE_DIR / 'db.sqlite3',
         'OPTIONS': {
             'init_command': 'PRAGMA journal_mode=wal;'
         }
    }
}

I think having this natively in Django would be useful, is there any help needed for the previous linked PR?

[Aaron Linville]

Replying to Afonso Silva:

I think having this natively in Django would be useful, is there any help needed for the previous linked PR?

I updated the PR and modified it with a more compelling example; synchronous=0 will not persist.

[bcail]

@Aaron, are you still wanting to work on this? If so, if you fix the tests you could update the flags on this issue so it goes back in the list of tickets to be reviewed.

[Aaron Linville]

Replying to bcail:

@Aaron, are you still wanting to work on this? If so, if you fix the tests you could update the flags on this issue so it goes back in the list of tickets to be reviewed.

Yes, I've rebased it on latest and fixed the tests I think (a couple are still pending). Assume you mean uncheck the Needs doc and Patch needs improvement?

[bcail]

Yup, exactly - so it shows up on the Patch review page.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 7a05b8a:

Fixed #24018 -- Allowed setting pragma options on SQLite.