ZipKit
Glossary

SQLite

SQLite is a small, serverless SQL database engine that runs inside your application and stores a whole database in one ordinary file. There is no separate server process to install or configure, because the program reads and writes the database file directly. The code is in the public domain. This page covers the engine and its file format; the query language itself is covered under SQL.

How it works

Your application links the SQLite library, opens a file such as app.db, and runs SQL against it. Python's sqlite3 module works this way. The complete state of a database is usually that single main file. During a transaction SQLite also writes a second file, a rollback journal or, in WAL mode, a write-ahead log.

Concurrency is simple: any number of connections can read at the same time, but only one can write at any instant. Writers queue up, so SQLite suits applications whose writes are short. The SQLite documentation points to client/server engines when many clients must share a database over a network.

The file starts with a 100-byte header. The first 16 bytes are the text SQLite format 3 plus a zero byte, and bytes 16 and 17 hold the page size as a big-endian number. A page size is a power of two from 512 to 32768, with the stored value 1 meaning 65536. The default page size has been 4096 bytes since version 3.12.0 (2016-03-29).

Typing is flexible. A value has one of five storage classes: NULL, INTEGER, REAL, TEXT or BLOB. A column's declared type only sets an affinity, a preference for converting values, so a column declared INTEGER can still hold text. Since version 3.37.0 (2021-11-27) you can add the STRICT keyword to a table to get rigid type enforcement.

>>> CREATE TABLE loose (a INTEGER);  INSERT INTO loose VALUES (42), ('42'), ('forty-two'), (4.5), (NULL)
[(42, 'integer'), (42, 'integer'), ('forty-two', 'text'), (4.5, 'real'), (None, 'null')]
>>> CREATE TABLE strict_t (a INTEGER) STRICT;  INSERT INTO strict_t VALUES ('forty-two')
IntegrityError cannot store TEXT value in INTEGER column strict_t.a
>>> first 16 bytes of the file, page size at offset 16
b'SQLite format 3\x00' 4096

The text '42' was converted to the integer 42 by the column's affinity, while 'forty-two' could not be converted and stayed text. These outputs came from SQLite 3.45.1 through the Python sqlite3 module.

What are the size limits of SQLite?

By default a string or BLOB can hold at most 1,000,000,000 bytes, a table can have 2,000 columns, and an SQL statement can be 1,000,000,000 bytes long. These limits come from compile-time settings, and the same values were read back at run time:

SQLITE_LIMIT_COLUMN      2000
SQLITE_LIMIT_LENGTH      1000000000
SQLITE_LIMIT_ATTACHED    10
SQLITE_LIMIT_SQL_LENGTH  1000000000
PRAGMA max_page_count    4294967294

A database can have at most 4,294,967,294 pages, which has been the default since version 3.45.0 (2024-01-15). With 4096-byte pages that is about 17.6 terabytes, and with 65536-byte pages about 281 terabytes. Up to 10 databases can be attached to one connection by default.

Common pitfalls

  • Foreign keys are off by default: inserting a child row that points at a missing parent succeeds silently. Run PRAGMA foreign_keys = ON on every new connection; once on, the same insert fails with FOREIGN KEY constraint failed.
  • Setting the pragma inside a transaction: PRAGMA foreign_keys does nothing while a transaction is open. In Python's sqlite3 module an earlier INSERT can leave one open, so commit before you set it.
  • Trusting column types: a column declared INTEGER accepts 'forty-two'. Use STRICT tables, or add CHECK (typeof(a) = 'integer'), when type errors must be caught.
  • Expecting concurrent writers: a second writer has to wait for the first to finish. Keep write transactions short.
  • Using a network share: SQLite relies on file locking. The SQLite documentation warns that network filesystems, NFS in particular, can have buggy locking, and concurrent access then risks corruption.
  • Missing standard syntax: FETCH FIRST 2 ROWS ONLY is rejected with a syntax error; use LIMIT 2.

Related terms

  • SQL — the language you use to talk to SQLite.
  • ACID — SQLite transactions are atomic, consistent, isolated and durable, even after a crash or power failure.
  • Primary key — a column declared exactly INTEGER PRIMARY KEY becomes an alias for the 64-bit rowid.
  • JSON — SQLite ships built-in JSON functions for working with JSON text.
  • Unix timestamp — SQLite has no date storage class, so dates are kept as TEXT, REAL or INTEGER, and INTEGER means Unix time.

See also