Databases: Part 4 — SQLite

SQLite — an embedded relational database. Unlike MySQL and PostgreSQL, it has no separate server process: it is a C library that is linked into the application, and the entire database is stored in a single file on disk. The application reads and writes this file directly, without network or intermediary.

The SQLite source code has been placed in the public domain, so it can be used in any project, including commercial ones, without licensing restrictions.

What SQLite can do

Despite its compactness, SQLite supports most of standard SQL:

• transactions with ACID properties — changes are either fully committed or not applied at all, even in case of power failure;
• indexes, views, triggers, foreign keys;
• window functions, common table expressions (WITH), including recursive ones;
• working with JSON via built-in functions;
• full-text search via the FTS5 extension.

Maximum database size is about 281 TB; in practice the limit is set by the file system.

Where SQLite is used

Mobile and desktop applications. SQLite is embedded in Android and iOS and is the standard way to store application data on the device. Firefox and Chrome store history, bookmarks and settings in it; Adobe Lightroom Classic stores the photo catalog. No server installation is needed, and data is available without the internet.

Websites and small web services. SQLite developers consider it suitable for sites with up to 100k requests per day, and with careful tuning — several times more. The sqlite.org site itself runs on SQLite and serves 400–500k HTTP requests per day on a single VM. One condition: the site should be read-heavy and have moderate writes.

Embedded systems and IoT. The library occupies a few hundred kilobytes and requires very little memory, so it is used in routers, avionics, and industrial controllers.

File format. A SQLite file is convenient to use as a document or archive format: it can be transferred or copied while still being queried with SQL.

Development and tests. Django creates a project with SQLite by default. For prototypes and automated tests you don’t need to run a separate server.

How SQLite handles writes

The main limitation of SQLite is that at any given time only one connection can write to the database. Others wait until the write finishes. Whether writes block reads depends on the journaling mode.

Default mode (rollback journal). During a write the database is locked and readers wait for the transaction to finish. For a desktop application with a single user this is unnoticeable; for a website it can become a bottleneck.

WAL mode (Write-Ahead Logging). Changes are first written to a separate journal file and later moved into the main database file during a checkpoint. Readers do not block the writer, and the writer does not block readers. It is enabled with a single command, and the mode is stored in the database file:

PRAGMA journal_mode=WAL;

For a server that accesses SQLite from several threads or processes, it’s common to add two more settings per connection:

PRAGMA busy_timeout = 5000;   -- wait up to 5 seconds if the database is busy, instead of failing immediately
PRAGMA synchronous = NORMAL;  -- in WAL mode the database won't be corrupted on crash, but the most recent transactions may be lost

WAL mode has limitations to be aware of in advance:

• there is still a single writer — WAL speeds up reads but does not make writes parallel;
• all processes working with the database must be on the same machine: WAL uses shared memory and therefore does not work on network file systems (NFS, SMB);
• auxiliary files -wal and -shm appear next to the database; they must not be deleted and the database must not be copied without them;
• transactions larger than 100 MB are slow in WAL mode.

Database maintenance

For manual work with the database there is a console utility sqlite3:

sqlite3 /var/lib/app/app.db

.tables                 -- list of tables
.schema orders          -- table structure
.mode box               -- convenient tabular output of results
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 42;  -- does the query use an index

A few operations should be performed periodically:

• PRAGMA optimize; — updates statistics used by SQLite to choose indexes. The documentation recommends running it before closing a long-lived connection or every few hours;
• VACUUM; — rebuilds the file and frees space after mass deletions. The database is locked while it runs, and you need free disk space equal to the size of the database;
• PRAGMA integrity_check; — checks the integrity of the file.

Backups

Simply copying the file of a live database is unsafe: if a write is in progress at the moment of copying, the copy may be inconsistent, and in WAL mode some data may still be in the journal. There are three reliable ways.

The .backup command in the sqlite3 utility. Uses the built-in backup mechanism and makes a consistent copy of a live database:

sqlite3 /var/lib/app/app.db ".backup '/backup/app-$(date +%F).db'"

VACUUM INTO. Creates a new, compact copy of the database without modifying the original (available since 3.27.0):

VACUUM INTO '/backup/app-2026-09-21.db';

Litestream. A separate open-source program that continuously ships changes from the WAL to an object store such as S3 or to a local directory. The application does not need to be changed: Litestream runs as a separate process. This allows recovery to a point a few seconds before a crash, not only to the last nightly copy.

Whatever method is used to create a copy, it should be regularly verified by restoring: sqlite3 copy.db "PRAGMA integrity_check;" should return ok.

Advantages

• No server. You don’t need to install, configure, update, and secure a separate service; no network latency.
• Simple deployment. The database is a single file that moves with the application.
• Reliability. ACID transactions and extensive testing: SQLite is one of the most battle-tested pieces of software in the world.
• Works offline. The application is fully functional without network access.
• Read speed. For small and medium amounts of data, queries to a local file are faster than to a server over the network.
• No licensing restrictions. Public domain.

Disadvantages

• Single writer. With frequent parallel writes, SQLite loses to client-server DBMSs even in WAL mode.
• No network access. If multiple application servers must work with the same database, SQLite is not suitable: you must not keep its file on a network drive.
• No users and access rights. Data access is controlled by the OS file permissions.
• Loose typing. By default a column can store a value of any type. Since 3.37.0 a table can enable strict mode (CREATE TABLE ... STRICT), which enforces types.
• No horizontal scaling. All data is on one disk of one machine.

When to choose SQLite and when not to

SQLite is appropriate if:

• the data is used on the same device or server where the application runs;
• reads are much more frequent than writes;
• the data size is gigabytes rather than terabytes;
• simplicity is important: one process, one file, minimal administration.

A client-server DBMS (PostgreSQL, MySQL) is needed if:

• multiple application servers work with the database or it runs on a separate machine;
• writes are constant and come from many sources simultaneously — for example, intensive order processing;
• you need users with different permissions, replication, and database-level high availability.

A common mistake is to start a project on SQLite, run a second instance of the application on another server, and put the database file on a shared network drive. That scheme leads to locks and data corruption; at that point it’s time to switch to a server DBMS.

SQLite performs well where its limitations do not matter: in mobile and desktop apps, embedded devices, small websites, and internal services with moderate writes. In these conditions it is cheaper and simpler than any server database.