SQLite

SQLite is an embedded, serverless relational database engine implemented as a compact C library that reads and writes a complete SQL database stored in a single ordinary file. Among database systems, it differs fundamentally from client-server products such as MySQL, MariaDB and PostgreSQL: there is no separate server process, and the application calls the engine directly through function calls.

SQLite is public domain software, supports most of standard SQL and provides ACID transactions. It is built into mobile operating systems, web browsers, desktop applications and countless devices, which makes it one of the most widely deployed database engines in existence.

How SQLite works

An application links the SQLite library and opens a database file. SQL statements are compiled by the library into bytecode for an internal virtual machine, which executes them against B-tree structures stored in fixed-size pages of the file. Tables and indexes are each B-trees, and the default page size is 4,096 bytes, configurable from 512 to 65,536.

Atomic commits rely on a journal. In the default rollback journal mode, original pages are copied to a separate file before being changed, so an interrupted transaction can be undone. In write-ahead logging (WAL) mode, changes are appended to a log file and later checkpointed into the database, which lets readers continue while a writer works.

Concurrency is controlled by file locks. Any number of connections can read at the same time, but only one writer at a time can commit. This single-writer design keeps the engine simple and reliable, and it is the main factor that determines where SQLite fits.

Key characteristics and limits

SQLite has a number of distinctive properties that matter when choosing and configuring it. The table summarizes the most important ones.

Type affinity surprises many developers. A column declared as INTEGER will, by default, still store a text value that cannot be converted, because SQLite attaches types to values rather than strictly to columns. Declaring tables with the STRICT keyword, available since version 3.37, restores conventional type checking.

SQLite compared with client-server databases

SQLite does not compete directly with client-server systems; it replaces custom file formats more often than it replaces a database server. The comparison shows where the boundaries lie.

Because a query in SQLite is a function call rather than a network round trip, many small queries are cheap. This reverses a common rule of client-server design and makes patterns such as issuing hundreds of simple queries per page acceptable in SQLite.

When to use SQLite

SQLite is the right choice when data lives close to a single application and write traffic is moderate. It is weaker when many independent clients must write to shared data over a network.

For server workloads, a few settings make a large difference: enable WAL mode, set a busy timeout so that writers wait instead of failing immediately, keep write transactions short and batch many inserts in one transaction, which can be orders of magnitude faster than committing each row separately.

Pitfalls and misconceptions

• Network file systems: placing the database file on a network share can corrupt it because file locking is often unreliable there.
• Copying a live database file: use the backup API or the VACUUM INTO command instead of a plain file copy during writes.
• Forgetting foreign key enforcement: constraints are ignored until the pragma is enabled on each connection.
• Assuming SQLite is a toy: its test suite and stability make it suitable for critical local storage.
• Using it as a shared multi-writer server: frequent database locked errors signal a workload that needs a client-server system.

The engine itself is exceptionally reliable; most problems come from the environment or configuration. Respecting the single-writer model and file system requirements avoids nearly all of them.

Stripped to the essentials

SQLite is a complete database packed into one file that lives inside an app. There is no separate database program to install or keep running; the app opens the file and reads or saves information directly.

It is like keeping a well-organized notebook in your own bag instead of storing papers in a central archive across town. You can look things up instantly and carry it anywhere. Only one person can write in it at a time, which is perfect for personal use but awkward when a whole office wants to write at once.

Where it is applied

A team builds a field inspection app for engineers who check bridges in remote areas with no reliable mobile signal. Each inspection includes checklists, measurements and photo references, and the engineers must be able to save and search their work offline.

The app stores everything in a local SQLite database of a few hundred megabytes. Each completed inspection is written in a single transaction, so a sudden battery failure never leaves a half-saved record. Searches across thousands of past inspections return in a few milliseconds thanks to indexes on location and date.

When a connection becomes available, a sync process sends new records to the company’s central PostgreSQL server. SQLite handles local storage, while the server handles shared data, and the engineers lose no work even after days without signal.