Updated : 17 Mar 2025
Published : 05 Jul 2024
Since its introduction, Sqlite has not been considered as a viable alternative to other RDBMS like MySQL and PotgreSQL. Main reason behind this is its bad performance under concurrency.
But with the introduction of WAL mode and various other improvements in the recent times, things are changing for Sqlite. For some use cases, it is now considered on par or even better than the alternatives because of its simple setup, low maintenance cost and low latency.
Django comes with Sqlite as the default database. But there used to be a warning in the official docs against using it in production. It was meant to be only for development. That warning is not there anymore. It means Django now considers it good enough.
But Sqlite is not tuned for performance by default. It requires some configurations for optimal performance. And Django also, by default, does not do it for us. We need to do those configurations ourselves.
Sqlite provides something called PRAGMA statements which can be used to finetune the database according to our needs. By enabling the following PRAGMAs, we can make our sqlite database ready for production.
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA foreign_keys = ON;
PRAGMA journal_size_limit = 67108864; -- 64 megabytes
PRAGMA mmap_size = 134217728; -- 128 megabytes
PRAGMA cache_size = 2000;
PRAGMA busy_timeout = 5000;
The purpose of this article is to show how to configure these PRAGMAs in Django so we will not go in to details of what these PRAGMAs do. If you are interested in knowing more about these PRAGMAs, I recommend you to read the following resources:
Sqlite Pragma Cheatsheet for Performance and Consistency
Note that except for the journal_mode and synchronous pragma, the other pragma values are provided only for reference and you can change them according to your needs.
Now let’s see how to configure these PRAGMAs in Django.
We will be setting it for the default database db.sqlite3 which is in the root directory of the project.
DATABASES = {
"default": {
"ENGINE": "django.db.backends.sqlite3",
"NAME": BASE_DIR / "db.sqlite3",
"OPTIONS": {
"timeout": 20,
"transaction_mode": "IMMEDIATE",
"init_command": """
PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;
PRAGMA journal_size_limit = 67108864;
PRAGMA mmap_size = 134217728;
PRAGMA cache_size = 2000;
""",
},
}
}
Note that we did not set foreign_keys pragma because it is enabled by default in Django. And we did not set busy_timeout pragma because Django provides the timeout option for the same purpose.
DATABASES = {
"default": {
"ENGINE": "django.db.backends.sqlite3",
"NAME": BASE_DIR / "db.sqlite3",
"OPTIONS": {
"timeout": 20,
"transaction_mode": "IMMEDIATE"
}
}
}
from Django.apps import AppConfig
from Django.db.backends.signals import connection_created
class Mainapp(AppConfig):
default_auto_field = "Django.db.models.BigAutoField"
name = "mainapp"
def ready(self):
def enable_Sqlite_pragmas(sender, connection, **kwargs):
"""Enable the necessary Sqlite pragmas."""
cursor = connection.cursor()
cursor.execute("PRAGMA journal_mode = WAL;")
cursor.execute("PRAGMA synchronous = NORMAL;")
cursor.execute("PRAGMA journal_size_limit = 67108864;")
cursor.execute("PRAGMA mmap_size = 134217728;")
cursor.execute("PRAGMA cache_size = 2000;")
connection_created.connect(enable_Sqlite_pragmas)
This ensures that these PRAGMAs are set whenever a database connection is created.
This is all we need to finetune Sqlite for production.
The following videos do a good job of explaining why Sqlite should be considered for production
DjangoCon Europe 2023 | Use Sqlite in production
GopherCon 2021: Ben Johnson - Building Production Applications Using Go & Sqlite