SQLite: The database in one file

In short: SQLite is a small, embedded SQL database that needs no server at all and stores all data in a single file.

In more detail: Most databases (PostgreSQL, MySQL) run as a server of their own to which programs connect. SQLite is a library that sits directly inside the program and reads and writes a file. It is the most widely used database in the world: in every smartphone, browser and in many applications.

In Depth

Properties

• No installation, no configuration, no server.
• One database file (.db, .sqlite), easy to copy and back up.
• Supports largely normal SQL, transactions (ACID), indexes, triggers, views, JSON functions.
• Very fast for reads and small to medium amounts of data (up to many gigabytes).
• Public domain, very well tested.

Example

sqlite3 test.db
sqlite> CREATE TABLE animal (id INTEGER PRIMARY KEY, name TEXT, age INT);
sqlite> INSERT INTO animal (name, age) VALUES ('Mia', 4);
sqlite> SELECT * FROM animal;

In Python sqlite3 is part of the standard library:

import sqlite3
con = sqlite3.connect('test.db')
print(con.execute('SELECT name FROM animal').fetchall())

Limits

SQLite allows many readers but only one writer at a time (with WAL mode readers continue meanwhile). For many simultaneous writes from several servers a real database server is better. There is no user management of its own; protection lies in the file system. Types are flexible (“type affinity”), not strict, except with STRICT tables. Network file systems are risky for the file.

Uses

Mobile apps, desktop programs, browsers (bookmarks, history), configuration and cache storage, small to medium websites, tests, data analysis with files and prototypes. Modern variants and extensions such as libSQL/Turso and Litestream (replication) bring SQLite into server environments too. When an application grows, SQL can often be transferred to PostgreSQL.

See also: SQL, PostgreSQL, Transactions and ACID, MySQL