SQLite in production

People say SQLite is a database that is unsuitable for production use because of low performance, inability to handle multiple concurrent writes, and other misconceptions. This article is inspired by a talk at DjangoCon 2023 and an episode of The Changelog.

Video summary

SQLite is already in production. You can already see how many applications use SQLite on your computer with this command:

  find / \( -name "*.db" -o -name "*.sqlite" -o -name "*.sqlite3" \) -type f -exec file {} \; 2>/dev/null | grep SQLite

11:20 it is a serverless database, which means you don’t need to configure an extra host, manage access by your application server, etc.
14:25 No performance loss because of network latency
15:31 No N + 1 Query problems
17:31 Concurrent writes are not a problem if you turn Write-Ahead Logging for your SQLite database. For this, you can execute sqlite3 db.sqlite3 'PRAGMA journal_mode=wal;' in your shell.
19:02 Speed up writes even more by reducing the synchronous level
20:53 You can easily back up the SQLite database with Litestream

Set up Django to run with SQLite

When creating a new Django project, it comes with following default setting for DATABASES:

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.sqlite3",
        "NAME": BASE_DIR / "db.sqlite3",
    }
}

You can also use dj-database-url to configure your DATABASES setting. In this case, the DATABASE_URL might look like this:

DATABASE_URL=sqlite:///path/to/your/database/file.sqlite3

Now, before you start your django project for the first time, which automatically creates a SQLite database for you, you should create it yourself first, by running:

sqlite3 db.sqlite3 'PRAGMA journal_mode=wal; PRAGMA busy_timeout = 5000;'

The PRAGMA synchronous setting is a little bit more tricky, since it does not apply to the database, but to the database connection. Hence, you have to change this setting for all connections opened by the Django app:

from django.db.backends.signals import connection_created

def activate_foreign_keys(sender, connection, **kwargs):
    if connection.vendor == 'sqlite':
        cursor = connection.cursor()
        cursor.execute('PRAGMA synchronous=1;')

connection_created.connect(activate_foreign_keys)

SQLite in production

Video summary

Set up Django to run with SQLite

How to configure Litestream to back up your Database to S3

Further reading