SQLite

SQLite is the database powering every runnable example in this track — it's a tiny, serverless engine that runs in-process (here, compiled to WASM in your browser). It's the most-deployed database in the world, embedded in phones, browsers, and apps everywhere. A few of its behaviours differ from the "server" databases.

Dynamic typing (type affinity)

The biggest surprise: in SQLite a column's declared type is only a hint ("affinity"), and a cell can actually store any type. INSERT INTO t(age) VALUES('hello') won't error the way PostgreSQL or SQL Server would. SQLite tries to convert toward the column's affinity but won't reject mismatches. Lean on CHECK constraints if you need strictness (or SQLite's stricter STRICT tables).

Auto-increment and rowid

Every row has an implicit rowid. Declaring INTEGER PRIMARY KEY makes your column an alias for it — ids auto-assign without any extra keyword. The explicit AUTOINCREMENT keyword exists but is rarely needed and slightly slower.

ALTER TABLE limits

Historically SQLite's ALTER TABLE could only add or rename — not drop or modify columns; the workaround was to recreate the table. Recent versions added DROP COLUMN, but migrations still do more table-rebuilding than on other engines.

Concurrency

SQLite allows many concurrent readers but one writer at a time (the whole database locks for writes in a single transaction). That's ideal for embedded and local use, and the reason it's not the pick for a high-write multi-user server.

When SQLite shines

Local apps, mobile, prototypes, tests, small-to-medium sites, and anywhere you want a real SQL database with zero setup — exactly why it runs this course.

Cheat-sheet

Where to go next

See the PostgreSQL and SQL Server appendices for how the bigger server engines differ.