SQLite spent years being dismissed as a toy database for local development, and then a wave of tools (Turso, Litestream, better-sqlite3) showed that "single file, no server process" is a legitimate production architecture for more workloads than most developers assumed.
SQLite is a serverless, embedded relational database — the entire database lives in a single file, with no separate database server process to run, configure, or maintain. It's the most widely deployed database engine in the world by installation count, embedded in every major browser, mobile OS, and countless applications, precisely because "just a file" is such a simple, reliable primitive.
Why SQLite Matters (and When to Skip It)
No server process means no network latency for queries, no connection pool to manage, and no separate infrastructure to provision — for single-instance applications, local-first apps, or edge functions with a small dataset, this simplicity is a genuine architectural advantage, not just a development convenience.
Skip SQLite for applications needing concurrent writes from many separate processes/machines simultaneously against the same database — SQLite's file-level locking model handles concurrent reads well but serializes writes, which becomes a real bottleneck for high write-concurrency, multi-server workloads. That's exactly the gap tools like Turso are built to close.
Getting Started with SQLite
Using better-sqlite3, a fast, synchronous Node.js driver:
import Database from "better-sqlite3";
const db = new Database("app.db");
db.exec(`
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
email TEXT UNIQUE NOT NULL,
created_at TEXT DEFAULT CURRENT_TIMESTAMP
)
`);
const insert = db.prepare("INSERT INTO users (email) VALUES (?)");
insert.run("suhail@example.com");
const users = db.prepare("SELECT * FROM users").all();
Core SQLite Concepts Every Developer Should Know
WAL (Write-Ahead Logging) mode significantly improves concurrent read performance. The default rollback journal mode blocks readers during a write; WAL mode allows readers to continue concurrently with a single writer, which is the setting nearly every production SQLite deployment should enable:
db.pragma("journal_mode = WAL");
A single writer at a time is a hard constraint, not a tuning knob. SQLite serializes writes regardless of configuration — this is fine for low-to-moderate write volume from a single application instance, and a real limitation for high-concurrency multi-writer scenarios.
Prepared statements matter for both performance and safety, exactly as in any SQL database — always use parameterized queries, never string-concatenate user input into SQL:
const getUser = db.prepare("SELECT * FROM users WHERE email = ?");
const user = getUser.get(email); // safe from SQL injection
Backup is just copying the file — but do it safely. SQLite's .backup API (or a tool like Litestream for continuous streaming backup to object storage) handles this correctly even while the database is in active use, which naive file-copying doesn't guarantee.
Common SQLite Mistakes and How to Fix Them
Mistake 1: not enabling WAL mode. Running with the default journal mode under any real concurrent load causes unnecessary write/read contention. Fix: set journal_mode = WAL as one of the first things you do after opening a production SQLite database.
Mistake 2: deploying SQLite on ephemeral storage in a serverless/containerized environment. A container or serverless function with no persistent volume loses the entire database file on redeploy or restart. Fix: use a persistent volume, or move to a distributed SQLite platform (Turso) or a traditional server-based database for that deployment target.
Mistake 3: assuming SQLite can't handle real production traffic. This is a common but outdated assumption — SQLite handles a genuinely high volume of reads and moderate writes well, especially with WAL mode. Fix: benchmark against your actual workload before ruling it out on reputation alone.
When Should You Use SQLite Instead of Postgres?
Use SQLite for single-instance applications, local-first/offline-capable apps, embedded/mobile contexts, CLI tools needing local storage, and low-to-moderate write-concurrency web apps where simplicity is a real win. Use Postgres when you need genuine multi-writer concurrency at scale, advanced relational features, or a traditional client-server database architecture across multiple application instances.
SQLite in Production
Set up continuous backup via Litestream (or equivalent) if SQLite is your production database — streaming the WAL to object storage gives you near-real-time durability without the operational overhead of a traditional database server. Also be honest about your write-concurrency needs before choosing SQLite for a multi-instance deployment; if you're scaling to multiple application servers writing to the same database, that's the point to seriously consider Turso or a traditional database instead.
If your app is a single-instance service, an internal tool, or has a genuinely small team of concurrent writers, don't dismiss SQLite by reputation — benchmark it against your actual workload before reaching for a heavier database by default.