SQLite: The Embedded Database

A complete SQL database in a single file and a small library — and how its typing differs from PostgreSQL's.

SQLite is not a server. It is a C library that applications link in, storing the entire database — tables, indexes, everything — in one ordinary file. It is the most widely deployed database engine in the world: it is built into every Android and iOS phone, every major web browser, many cars, cameras and embedded devices, and countless desktop programs.

When to use SQLite

Assuming a column declared INTEGER only contains integers Declare tables STRICT, or add CHECK (typeof(col) = 'integer')

In a normal SQLite table any value can end up in any column; STRICT tables or CHECK constraints make types reliable.

What is type affinity in SQLite?

Each column has a preferred storage class (INTEGER, REAL, TEXT, BLOB, NUMERIC) derived from its declared type. SQLite tries to convert values to it, but stores a value of another type if conversion is not possible (unless the table is STRICT).

Name two situations where SQLite is a better choice than PostgreSQL.

An app's local storage on a phone or desktop, and an embedded device that needs a database without running a server (also test fixtures and single-file data exchange).