Enable Write-Ahead Logging

[falkben]

Enable Write-Ahead Logging (WAL)

This effectively allows concurrency with sqlite. It gets applied to the database file.

The command to enable WAL is:

sqlite3 database.sqlite 'PRAGMA journal_mode=WAL;'

Resources:

• https://sqlite.org/wal.html
• https://til.simonwillison.net/sqlite/enabling-wal-mode

[falkben]

I've seen this in some SO posts, but setting it once on the database file should be enough. The following will run on every database connection. Which, for sqlite, is every query. So this seems to be overkill -- we just need a way to run it once, perhaps on app startup.

# connect event on instance of Engine
@event.listens_for(engine.sync_engine, "connect")
def my_on_connect(dbapi_con, connection_record):
    print("New DBAPI connection:", dbapi_con)
    cursor = dbapi_con.cursor()
    cursor.execute("PRAGMA journal_mode=WAL;")

Note that:

sqlite3 database.sqlite 'PRAGMA journal_mode'

Will return the current journal mode. So after setting one time, you can confirm the journal mode after executing multiple queries.

If we have to, we could run the command outside of sqlalchemy with just subprocess.run()

[falkben]

Probably won't want to enable WAL mode during testing, when using in-memory database. If going with the event version, will want the ability to switch it off.