SQLite

What it is

SQLite is a self-contained, serverless relational database that stores an entire database in a single file. There is no separate process to run or manage; your application links the library directly and reads and writes the file. It is the most widely deployed database in the world, embedded in phones, browsers, and countless apps.

Strengths

• Zero configuration and no server to operate.
• The whole database is one portable file, easy to copy or back up.
• Fast for local reads and moderate write workloads.
• Full SQL support with transactions and foreign keys.
• Great for tests, prototypes, edge runtimes, and embedded use.

Trade-offs

• Limited concurrent writes (one writer at a time; WAL mode helps).
• Not built for many networked clients or high write throughput.
• Fewer advanced types and no native user/permission system.
• Scaling beyond a single host needs a different store or a layer like Turso/LiteFS.

When to use it

Use SQLite for local apps, CLI tools, desktop and mobile software, test suites, and small-to-medium sites where reads dominate. It is also increasingly viable at the edge via hosted replicas.

Vibe coding fit

When directing AI, ask it to enable safe defaults first: turn on PRAGMA foreign_keys = ON and WAL journaling, since SQLite leaves foreign keys off by default. Have it use parameterized statements and keep schema migrations in versioned .sql files. A useful tip: tell the AI that writes are serialized, so it should batch inserts inside a single transaction and avoid long-running write locks. Because the database is just a file, ask the AI to include a simple backup step (copy the file or use the .backup command) in your tooling.

PRAGMA journal_mode = WAL;
PRAGMA foreign_keys = ON;

CREATE TABLE notes (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  title      TEXT NOT NULL,
  body       TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Batch many inserts in one transaction for speed
BEGIN;
INSERT INTO notes (title, body) VALUES (?, ?);
INSERT INTO notes (title, body) VALUES (?, ?);
COMMIT;