SQLite

What it is

Think of a single document file you open and edit directly, with no separate program running in the background to manage it. SQLite is like that for a database: it is a self-contained, serverless relational database that keeps the whole database in one 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 (Structured Query Language) support with transactions and foreign keys.
• Great for tests, prototypes, edge runtimes, and embedded use.

Trade-offs

• Limited concurrent writes. Like one pen shared by a group, only one person can write at a time, though others can still read (one writer at a time; WAL — write-ahead logging, a mode that lets reads and a write happen together — 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 (Command-Line Interface) 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;