This post covers some settings I have discovered about SQLite these days that help it perform better without losing consistency.

The official reference and the sources of my discoveries will be listed in the References section below.

This will be a short one, mainly to help future me, but also for anyone planning to use SQLite in production (including me).

Configuring SQLite

A few days ago I thought SQLite didn’t need any configuration but oh boy was I wrong.

To configure SQLite you use the PRAGMA command in the DB:

  • You can query the current value of a setting, with PRAGMA setting;
  • Or set a new value, using PRAGMA setting=value;

The tips

A one-liner with all settings will be at the end.

Journal mode

PRAGMA journal_mode=WAL;

This changes the default journaling mode fron DELETE to WAL (Write-Ahead Log) which allows reading from the DB while also writing to it as one of its features.

This feature can be used with SQLite 3.7.0 (2010-07-21) or later.

Documentation

Synchronous

PRAGMA synchronous=1;

The default value of 2 (FULL) is not needed if WAL is enabled, and changing it to 1 (NORMAL) improves speed while not losing safety.

Documentation

Busy timeout

PRAGMA busy_timeout=5000;

This sets the time write transactions will wait to start at maximum (in ms). If unset, they would fail immediately if the database is locked.

Documentation

Foreign keys

PRAGMA foreign_keys=ON;

SQLite does not enforce FKs by default, so this enables its enforcement as it’s highly recommended.

Please use foreign keys.

Documentation

One-liner

For a database stored in db.sqlite3:

sqlite3 db.sqlite3 "PRAGMA journal_mode=WAL; PRAGMA synchronous=1; PRAGMA busy_timeout=5000; PRAGMA foreign_keys=ON;"

Other important commands

Some other useful commands that can be used in the SQLite shell:

  • VACUUM; - Rebuilds the DB file, useful to shrink it after deletions. (Reclaims disk space)
  • ANALYZE; - Updates the query planner’s statistics.
  • PRAGMA integrity_check; - Checks the integrity of the DB.
  • PRAGMA foreign_key_check; - Checks the FK constraints.

References

And another nice post covering similar configurations that I found after writing mine.

Thanks for reading!

Done with 🖤 by Appu.