Jan Mikoláš
Glossary
SQLite
SQLite is a full-featured embedded relational database engine for local data and straightforward operations. Its file-based model reduces complexity while using a different concurrency model from a standalone database server.
Short definition
A relational database as part of the application.
SQLite is a library that the application uses directly within its process. Unlike MySQL or PostgreSQL, it generally does not run as a separate server service, open a database port, or receive remote SQL requests from a client. The database state resides in a file that the engine handles safely through transactions, a journal, and file locks.
This does not mean it is only a testing database or an ordinary data file without rules. SQLite supports SQL, atomic commit and rollback, indexes, UNIQUE, CHECK, NOT NULL, and foreign keys. Its suitability still depends on how frequently the application writes, how many independent processes access the same file, and where the file physically resides.
The problem it solves
Persistent relational data without managing a separate server.
SQLite reduces the number of operational components when one application or device naturally owns the database.
- mobile, desktop, and local applications with offline data
- internal tools, small web projects, and writes serialised by the application
- local analytics over a CSV import or export of operational data
- portable database files for a report or handoff of a specific dataset
- tests that also deliberately account for differences from the production database
Practical example
A local analytics tool working with imported files.
An internal CLI tool downloads a daily order export and stores it in a local SQLite database. A unique source filename prevents duplicate imports, a foreign key links metrics to a specific import run, and an index speeds up a time-based view. The database file can be backed up or distributed with the tool when access is properly controlled.
The application explicitly enables foreign keys whenever it opens a connection. In WAL mode, readers and one writing transaction can proceed concurrently, but a long write or long read still affects operations. The tool therefore imports in batches, keeps transactions short, and does not assume that the same file can serve many parallel writers without limits.
SQL (SQLite)
PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;
CREATE TABLE import_run (
id INTEGER PRIMARY KEY,
source_file TEXT NOT NULL UNIQUE,
imported_at TEXT NOT NULL
);
CREATE TABLE daily_metric (
id INTEGER PRIMARY KEY,
import_run_id INTEGER NOT NULL,
metric_day TEXT NOT NULL,
order_count INTEGER NOT NULL CHECK (order_count >= 0),
FOREIGN KEY (import_run_id)
REFERENCES import_run (id) ON DELETE CASCADE,
UNIQUE (import_run_id, metric_day)
);
CREATE INDEX daily_metric_day_idx ON daily_metric (metric_day);How it works
A file, transactions, and access coordination.
SQLite stores state in a database file but takes consistency seriously: it controls changes through transactions and file locks.
- Opening the database The application opens the file directly through the SQLite library. The database is not a network server, so security primarily covers permissions for the file, its directory, backups, and the process using it.
- Schema and rules Tables, indexes, PRIMARY KEY, UNIQUE, CHECK, and NOT NULL define valid state. FOREIGN KEY is supported, but the application should explicitly enable it on every connection with PRAGMA foreign_keys = ON.
- Transactions SQL statements execute atomically or within an explicit transaction. Commit writes a consistent change, while rollback discards it; the journaling mechanism enables recovery even after an interrupted write.
- Locks and a single writer Only one writing transaction can operate on the same database at a time. This can be very practical for short batch writes, but is an important boundary under high concurrency.
- WAL and checkpoint Write-ahead log mode writes to the WAL file first. It allows readers to run concurrently with one writing transaction, but requires checkpointing, is not designed to work over a network filesystem, and a long read can prevent a checkpoint from progressing.
Main components and principles
Embedded operation does not mean a limited data model.
The most important difference from a database server is not SQL quality, but where the engine runs, the connection model, and how concurrency over one file is coordinated.
Embedded engine and database file
SQLite is a library linked into or loaded by the application. A typical database is one main file, not a remote service with accounts and network clients. The file model makes distribution and backup easier, but requires correct permissions and care when sharing the file among processes or machines.
ACID and transactions
SQLite provides atomic commit and rollback and protects database integrity even during a failure. ACID does not mean a database file can be safely shared on arbitrary storage without careful design, or that one transaction covers a remote API. The transaction boundary remains local database work.
Constraints and foreign keys
PRIMARY KEY, UNIQUE, CHECK, and NOT NULL are expressed directly in the schema. Foreign keys are supported, but are not automatically enforced for backward compatibility: the application should explicitly set PRAGMA foreign_keys = ON after opening each connection and test this state.
Indexes and the SQL dialect
As elsewhere, an index is an auxiliary structure for specific queries and has a write and storage cost. SQLite supports a substantial part of SQL, but not an identical dialect, data types, or behaviour to MySQL or PostgreSQL. A test using SQLite is therefore not a full replacement for an integration test against the production database.
WAL, reads, and writes
WAL improves concurrency by preventing readers from blocking writers and writers from blocking readers. It does not remove the single-writer limit. WAL requires processes on the same host because it uses shared memory; a long-running reader can also delay checkpoints and cause the WAL file to grow.
Benefits and limitations
Operational simplicity in exchange for deliberate concurrency management.
Benefits
- no separate database service to install, update, and monitor
- a relational model, SQL, transactions, indexes, and constraints in one portable file
- suitable for local data, devices, analytics, and many low- to medium-load applications
- WAL allows readers to run concurrently with one writer
Limitations and common mistakes
- dismissing SQLite as a toy or test-only database
- treating it like a conventional network database server
- assuming multiple concurrent writers without design and measurement
- forgetting to enable foreign keys on every connection
- using SQLite tests as the sole proof of compatibility with MySQL or PostgreSQL
- running WAL over a network filesystem
Practical use
A strong choice when the database belongs to one application or device.
SQLite fits well in a mobile application, desktop tool, local cache, internal analytics, or smaller website where writes are short and can be coordinated. The official documentation also describes its use for low- to medium-traffic websites; what matters is not a universal request count, but the specific read-to-write ratio, transaction length, process architecture, and file location.
A client–server system may be more practical for a central application with many independent writing workers, complex database-server role management, or distributed operations. This is not a value judgement about SQLite: it is a choice of a model that matches the real deployment and concurrency boundaries.
What to keep in mind
Verify concurrency and integrity in the environment where the application actually runs.
SQLite is reliable when the application configures its rules explicitly and does not impose assumptions from another database system on it.
- explicitly enable and verify PRAGMA foreign_keys = ON after opening every connection
- keep write transactions short and respond sensibly to SQLITE_BUSY
- verify the file location and checkpointing plan before enabling WAL
- design indexes around actual queries, not merely column names
- also test critical migrations, SQL, and behaviour against the database used in production
- protect the database file, related WAL files, and backups with the correct permissions
Common questions
SQLite in production and testing
Is SQLite only a testing database?
No. It is used in mobile and desktop applications, local tools, analytics, and many server applications. It is useful in tests, but must not conceal differences from the production database.
Is SQLite a database server?
No. SQLite is an embedded database engine used directly by the application, with the database usually stored in a file. The application does not communicate with it through a separate network server as it does with MySQL or PostgreSQL.
Does WAL allow many simultaneous writes?
No. WAL allows readers to run concurrently with one writer, but a database file always has only one writer. Short writes and controlled waiting therefore matter.
Why enable foreign keys explicitly?
It is unsafe to assume they are enforced by default. The application should set PRAGMA foreign_keys = ON for every connection so the declared relationships actually protect the data.
How I work with databases in practice
I choose a data store based on concurrency, operations, and real rules.
In e-commerce and integration applications, I design the data model, transactions, constraints, imports, and the right boundary between local and central storage.