SQLite: when a single-file database is the right choice

SQLite is often regarded as a «toy» database, fine for exercises and to be replaced as soon as a project becomes serious. That is a mistake. SQLite is present in almost every phone, in all the major browsers and in a great many desktop programs, and it is one of the most thoroughly tested pieces of software around. It is not suitable for everything, but for a very wide range of cases it is the simplest and most reliable choice.

What makes it different

Most relational databases work as a server: a separate process that programs connect to over the network, with users, passwords and ports. SQLite, by contrast, is a library that the program embeds. The database is a single file on disk, and the program reads and writes it directly.

The consequences are significant:

• no installation or configuration: there is no service to start or update;
• no network latency: a query is a function call, not a round trip to a server;
• simple backup and transfer: the database is a file that can be copied, with the precautions described below;
• full SQL: ACID transactions, joins, indexes, views, triggers, window functions, common table expressions.

In Python, for example, it is already included in the standard library:

import sqlite3

with sqlite3.connect("inventory.db") as conn:
    conn.execute("""CREATE TABLE IF NOT EXISTS items (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        quantity INTEGER NOT NULL DEFAULT 0
    )""")
    conn.execute("INSERT INTO items (name, quantity) VALUES (?, ?)", ("HDMI cable", 12))

When SQLite is the right choice

• Desktop and mobile applications that need to store data locally.
• Command-line tools and scripts that accumulate results over time, instead of CSV or JSON files reread every time.
• Websites and web applications with modest traffic, where writes are relatively few compared with reads.
• Data analysis of medium size: importing a CSV into SQLite and querying it in SQL is often more convenient than working on it in a spreadsheet.
• Exchange format: an SQLite file can hold several related tables, with types and indexes, and be opened from any language.
• Automated tests, provided the code does not use features specific to the production database.

The limits to know

One writer at a time

SQLite allows many concurrent readers, but only one write transaction at a time across the whole database. The others wait. For an application with few, short writes this is not a problem; for a system with many users writing continuously it becomes a bottleneck.

No network access

The file must live on a local disk. Putting it in a shared network folder accessed by several computers is discouraged by SQLite's own documentation, because the locking mechanisms of network file systems are not always reliable and the risk is data corruption.

No user management

There are no users or permissions: whoever can read the file can read the whole database. Security relies on operating system permissions.

The settings to know right away

WAL mode

In the default mode, a write also blocks readers. Enabling write-ahead log mode lets readers carry on reading while a write is in progress:

PRAGMA journal_mode = WAL;

The setting is saved in the file. In this mode two additional files appear next to the database, with the -wal and -shm extensions; they are part of the database and must not be deleted.

Foreign keys

For compatibility with historical versions, SQLite does not enforce foreign key constraints unless you explicitly ask it to, on every new connection:

PRAGMA foreign_keys = ON;

Forgetting this means you can insert orders pointing to non-existent customers without any error.

Flexible types

SQLite uses dynamic typing: a column declared INTEGER can also accept text. Since version 3.37 you can create STRICT tables, which reject values of the wrong type, as other databases do.

Busy timeout

When a connection finds the database busy with a write, by default it may return an error immediately. Setting a timeout, for example with PRAGMA busy_timeout = 5000;, makes it wait up to five seconds before giving up.

Backing up correctly

Copying the file while a program is modifying it can produce an inconsistent copy, especially in WAL mode. The safe methods are the command-line tool's .backup command, the backup API available in many languages, or the statement VACUUM INTO 'copy.db', which creates a compact, consistent copy.

In short

• SQLite is a library that manages a database in a single file, with no server.
• It suits local applications, scripts, low-write websites, analysis and tests.
• It allows one writer at a time and should not be used on shared network folders.
• Enable WAL, foreign_keys and a busy_timeout, and consider STRICT tables.
• Back up with .backup or VACUUM INTO, not by copying a file in use.