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;