SQLite

A database library, considered "lightweight" for two reasons: it stores its data on a single file on disk, and it is a library that is embedded in your application rather than a client–server program like, say, Postgres. Noted for its comprehensive approach to testing.

Cheatsheet

Output format

-- Turn on headers
> .headers on

-- Turn on line mode
> .mode line

Import a CSV

WARNING: this doesn't really work with existing tables

-- make sure *not* to put a semicolon at the end
> .import --csv <file> <table>

CSV files don't distinguish between the empty string and NULL, so you may need to import into a temporary table and use NULLIF(TRIM(col), '') to fix the column.

Export a table to CSV

$ sqlite3 -csv -header DATABASE "SELECT * FROM table" > mytable.csv

Schema

-- List tables
> SELECT name FROM sqlite_master WHERE type='table';

-- See columns of table
> PRAGMA table_info(<table>)

Copy from one table to another

INSERT INTO table1(c1, c2) SELECT c1, c2 FROM table2;

Read-only connection

sqlite3.connect("file:/path/to/db?mode=ro", uri=True)

Tips

• Run PRAGMA foreign_keys = 1 to turn on foreign-key enforcement.
• Use isolation_level=None in Python to disable auto-magic transactions.
• CREATE TABLE whatever(...) STRICT to turn on strict typing.

Posts

• "Be careful exporting from Postgres to SQLite" (Nov 2024)
• "isqlite: An improved Python interface to SQLite" (Oct 2021)
• "Using SQLite effectively in Python" (Oct 2021)

Bibliography

• "SQLite is not a toy database" (Anton Zhiyanov, 2021)
• "What to do about SQLITE_BUSY errors despite setting a timeout" (Bert Hubert, 2025) – one of the most common SQLite errors
  • See also Simon Willison's SQLITE_BUSY bibliography
• PRAGMAs from a Hacker News commenter (mickeyp, 2025)