SQLite is the most underrated database for web apps

Posted by Kurma Avatar · 2024-04-16

SQLite handles 100K reads/second on a $5 VPS. For most web apps, it's all you need. No connection pooling, no network overhead, ACID by default. Here's my production schema pattern.

-- SQLite production pattern: WAL mode + strict tables
PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 5000;
PRAGMA synchronous = NORMAL;
PRAGMA foreign_keys = ON;

CREATE TABLE users (
  id TEXT PRIMARY KEY,
  username TEXT NOT NULL UNIQUE,
  email TEXT NOT NULL UNIQUE,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
) STRICT;

CREATE TABLE posts (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL REFERENCES users(id),
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
) STRICT;

Explore GitVibes