How to enable WAL mode for SQLite

[TimothyAllman]

Hi there

First off, love the work you all have put into Piccolo, the development experience so far has been wonderful and I appreciate all the time and care that has gone into its design and implementation.

I saw this post #1247 (comment) but didnt see any direct WAL instructions, so thought I would create a focused question here.
I really would like to use SQLite with WAL mode enabled.

Ideally I would want to set it when I instatiate the engine, something like

from piccolo.conf.apps import AppRegistry
from piccolo.engine.sqlite import SQLiteEngine

SQLITE_DB_PATH = "piccolodatabase.db"

DB = SQLiteEngine(
    path=SQLITE_DB_PATH,
    timeout=60,
    wal_enabled=True
)

but there isn;t such an argument.
I tried various things like

import asyncio
from piccolo.querystring import QueryString
from piccolo_conf import DB  

async def enable_wal():
    await DB.start_connection_pool()
    
    wal_query = QueryString("PRAGMA journal_mode=WAL;")

    await DB.run_querystring(wal_query)
    
    await DB.close_connection_pool()
   

and then calling this function in the main start up, but couldn't get it working.

I did find a PRAGMA for foreign keys in the codebase so I think it is possible, I just seem to not be able to find it.

How could I go about enabling WAL mode whenever I create my database ?

Many thanks in advance

[dantownsend]

First off, love the work you all have put into Piccolo, the development experience so far has been wonderful and I appreciate all the time and care that has gone into its design and implementation.

Thanks! :)

It looks like the journal_mode PRAGMA has to be run on every connection:

https://sqlite.org/pragma.html#pragma_journal_mode

If you look at the codebase for SQLiteEngine there are a few places where we do something similar (search for PRAGMA foreign_keys = 1).

I think the only solution for now is to override those areas of SQLiteEngine, for example:

# piccolo_conf.py
from piccolo.engine.sqlite import SQLiteEngine


class CustomSQLiteEngine(SQLiteEngine):
    async def get_connection(self) -> Connection:
        connection = await super().get_connection()
        await connection.execute("PRAGMA journal_mode = WAL")
        return connection


DB = CustomSQLiteEngine(path='project.sqlite')

A better solution would be to modify SQLiteEngine to either accept a journal_mode argument, or just generic PRAGMA statements to make it more flexible.