Skip to content

Latest commit

 

History

History
69 lines (50 loc) · 3.05 KB

2023-06-18-sqlite-in-production.md

File metadata and controls

69 lines (50 loc) · 3.05 KB
Raw
tags
SQLite

SQLite in Production

I was inspired by a talk at DjangoCon that I attended in 2023, so I decided to look a bit more into SQLite in combination with Django.

Here are the key points that were made in the YouTube video:

  • 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 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",
    }
}

Or you can use dj-database-url to configure your DATABASES setting with a DATABASE_URL:

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

Activate Write Ahead Log

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;'

Reduce the synchronous level

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)

References