What SQLite Actually Is

A C library that happens to speak SQL

SQLite is a C library implementing a small, fast, self-contained, high-reliability, full-featured SQL database engine. Each adjective matters:

Serverless — no separate server process. The library reads and writes ordinary disk files. No setup, no admin, no daemon.
Zero-configuration — no installation step. No config files, no ports, no users. Open a file and go.
Single-file — the entire database (tables, indexes, triggers, views) lives in one cross-platform file on disk. Copy the file, you've backed up the database.
Transactional — every statement runs in a transaction. Crash mid-write, the database survives intact. Fully ACID.
Self-contained — about 900 KB of compiled code with minimal OS dependencies. Smaller than most JavaScript framework bundles.

That bundle of properties is what makes SQLite so different from client-server databases like Postgres or MySQL. There is no separate server. There is no network protocol. There is no port. SQLite runs inside your application's process, sharing your app's memory and file descriptors.

Principle: The unit of deployment for SQLite is the file. Backup is cp. Migration is replace-the-file. Replication is rsync. The mental model collapses to filesystem operations — that simplicity is the feature.

Code

Spin up a database with one command·sql

-- Open (and implicitly create) myapp.db
sqlite3 myapp.db

-- You're now in an interactive SQL session
CREATE TABLE notes (
  id         INTEGER PRIMARY KEY,
  body       TEXT NOT NULL,
  created_at TEXT DEFAULT (datetime('now'))
);

INSERT INTO notes (body) VALUES ('Hello, SQLite!');
SELECT * FROM notes;
-- 1|Hello, SQLite!|2026-05-03 12:00:00

Same thing, three lines of Python·python

import sqlite3

conn = sqlite3.connect('myapp.db')
conn.execute('CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, body TEXT)')
conn.execute('INSERT INTO notes(body) VALUES (?)', ('Hello from Python',))
conn.commit()
for row in conn.execute('SELECT * FROM notes'):
    print(row)
conn.close()

Exercise

Compare deployment surface area: write down everything you would need to deploy a Postgres-backed feature versus a SQLite-backed feature for a small product (say, a personal note-taking app). Include OS packages, network config, secrets, backup story, and observability. Where does SQLite simplify and where does it shift work elsewhere?

A C library that happens to speak SQL

Code

External links

Exercise

Progress

Comments 0