SQLiteRunsProductionAppsatScale.It'sTimetoTakeItSeriously

The Database Everyone Has But Nobody Respects

There's a file sitting on your phone right now. It holds your text messages, your contacts, your call history, maybe your health data. It's managed by SQLite — a database engine so embedded in daily life that it runs on roughly five billion devices at any given moment. And yet, the moment someone proposes using SQLite for a web application or a backend service, the room goes quiet and someone eventually says, "Yeah, but that's not really a production database."

I've heard that dismissal more times than I can count. And I've watched it send teams toward PostgreSQL or MySQL setups that required connection poolers, replica configurations, and ops overhead that their actual workload never justified. SQLite got written off as a toy — something for prototypes, for local development, for embedded systems in fridges. That framing is increasingly wrong, and the evidence against it is stacking up fast.

The conversation around SQLite changed dramatically when Fly.io, the cloud platform, built their entire distributed database product LiteFS on top of it. When Turso launched a SQLite-native edge database that developers actually use in production. When the Rails team added first-class SQLite support in Rails 8. When Cloudflare shipped D1, their serverless database, built on SQLite. These aren't hobbyist experiments. These are serious infrastructure decisions made by serious engineers. It's time to catch up with what they already know.

What SQLite Actually Is (And Isn't)

Most people's mental model of SQLite is frozen somewhere around 2008. They picture it as a flat file that kind of works like a database, fine for storing settings in a desktop app or powering a mobile game's save data. That model is outdated. SQLite is a full-featured, ACID-compliant relational database engine — it supports transactions, complex joins, foreign keys, full-text search, JSON operations, window functions, and more. It just happens to store everything in a single file and requires no separate server process to run.

That "no server process" part is actually the key insight. Think of it like the difference between a generator you wheel out on demand versus running power lines to a utility company. PostgreSQL is the utility company — powerful, shared, always on, requires infrastructure. SQLite is the generator — self-contained, immediately available, zero configuration. Neither is universally better. They solve different shapes of problems.

The "serverless" nature (no server process, not the cloudy meaning) means SQLite lives inside your application process. Reads and writes happen through direct system calls. There's no network round trip between your app and the database, no authentication handshake, no connection pool to manage. For applications that are largely read-heavy or have modest concurrent write demands, this architecture isn't a compromise — it's a genuine performance advantage.

The WAL Mode Revolution Nobody Talks About

If you looked at SQLite five or six years ago and decided it couldn't handle concurrent access, you were right at the time. The default journal mode would lock the entire database file for writes, meaning concurrent readers would get blocked. That was a real, legitimate limitation.

Then WAL mode happened. WAL stands for Write-Ahead Logging — it's a technique where changes are first written to a separate log file before being committed to the main database. The practical result is that readers never block writers and writers never block readers. Concurrent reads can happen while a write is in progress. This single change transformed SQLite from a "one person at a time" database into something that handles real concurrent workloads gracefully.

Enabling WAL mode takes one line of SQL and you do it once, usually when you open the database connection. Here's how that looks in Python using the standard library:

import sqlite3

def get_db_connection(db_path: str) -> sqlite3.Connection:
    conn = sqlite3.connect(db_path)
    
    # Enable WAL mode for concurrent read performance
    conn.execute("PRAGMA journal_mode=WAL")
    
    # Optional: set a reasonable busy timeout in milliseconds
    # so writes don't immediately error under light contention
    conn.execute("PRAGMA busy_timeout=5000")
    
    # Enforce foreign keys (disabled by default in SQLite)
    conn.execute("PRAGMA foreign_keys=ON")
    
    conn.row_factory = sqlite3.Row  # returns dict-like rows
    return conn

Those three PRAGMAs — SQLite's term for configuration directives — transform the database's behavior meaningfully. WAL mode for concurrency, a busy timeout so write contention fails gracefully instead of immediately, and foreign key enforcement so your data integrity constraints actually work. I've found that skipping these defaults is where most people's bad experiences with SQLite actually start. They're running a misconfigured engine and blaming the engine.

The Scale Numbers That Should Surprise You

Let's talk about some real numbers, because the abstract argument gets tiresome without them. The SQLite documentation itself describes deployments handling hundreds of thousands of requests per day on commodity hardware. For a massive portion of applications — the ones that don't land on the Hacker News front page and don't serve ten million users — that ceiling is simply never reached.

Ben Johnson, the author of the BoltDB embedded database and later LiteFS, has written about SQLite handling tens of thousands of writes per second with proper configuration. Expensify, the expense management company, famously built their entire backend on MySQL for years but has openly discussed how much of their read volume could have been handled by simpler embedded databases. DHH, the creator of Rails, ran benchmarks showing that a single SQLite database on a modern SSD can handle more requests per second than most teams' databases ever see in production.

The SSD is actually the critical piece of modern context here. The original architectural concerns about SQLite assumed spinning disk storage where random I/O was painfully slow. Modern NVMe SSDs have latencies measured in microseconds — the same order of magnitude as network calls inside a data center. The fundamental physics of storage changed, and SQLite's single-file design benefits enormously from that change.

Where SQLite Genuinely Shines in Production

The workload that fits SQLite like a glove is what I'd call the single-tenant application model. Think of it this way: if you're building a software product where each customer gets their own isolated data store — think a note-taking app, a project management tool, a developer tool that runs locally — then SQLite gives you an incredible architecture. One database file per customer. No cross-customer query interference. Backups are literal file copies. Restoring a single customer's data is moving a file. The operational simplicity is genuinely hard to overstate.

Turso (the edge database company) built their entire product pitch around this model. They call it "multi-tenancy done right" — give every tenant their own SQLite database, replicate those files to edge locations, and suddenly your database latency drops because the data is physically close to the user who owns it. It's an elegant inversion of the traditional "one big database with tenant_id columns everywhere" approach.

Beyond multi-tenancy, SQLite is exceptional for read-heavy workloads with infrequent writes. A blog, a documentation site, a product catalog, a configuration service — these write rarely and read constantly. SQLite with WAL mode will serve reads from multiple goroutines or threads simultaneously with no locking, no connection pool overhead, and latency that's essentially RAM speed once the relevant pages are in the OS page cache. Here's a simple benchmark harness in Go that illustrates how you'd test this:

package main

import (
    "database/sql"
    "fmt"
    "sync"
    "time"

    _ "github.com/mattn/go-sqlite3"
)

func main() {
    db, err := sql.Open("sqlite3", "file:bench.db?_journal_mode=WAL&_busy_timeout=5000")
    if err != nil {
        panic(err)
    }
    defer db.Close()

    // SQLite with WAL performs well with a small read pool
    // Writers should use a single connection to avoid contention
    db.SetMaxOpenConns(1)  // for writes
    
    // Seed some data
    db.Exec(`CREATE TABLE IF NOT EXISTS articles (
        id INTEGER PRIMARY KEY,
        title TEXT NOT NULL,
        body TEXT NOT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )`)
    
    for i := 0; i < 10000; i++ {
        db.Exec("INSERT INTO articles (title, body) VALUES (?, ?)",
            fmt.Sprintf("Article %d", i),
            "Lorem ipsum dolor sit amet, consectetur adipiscing elit.")
    }

    // Simulate concurrent reads
    var wg sync.WaitGroup
    start := time.Now()
    
    for i := 0; i < 100; i++ {
        wg.Add(1)
        go func(id int) {
            defer wg.Done()
            var title string
            row := db.QueryRow("SELECT title FROM articles WHERE id = ?", id%10000+1)
            row.Scan(&title)
        }(i)
    }
    
    wg.Wait()
    fmt.Printf("100 concurrent reads completed in %v\n", time.Since(start))
}

Run that and you'll see sub-millisecond aggregate completion times for concurrent reads. That's not because Go is magic. That's because there's no network socket involved, no authentication layer, no query planner cache miss across a TCP connection. The data is just there.

The Honest Limitations You Should Know

I'm not going to pretend SQLite is the right answer for everything — that would be dishonest and ultimately unhelpful. There are real scenarios where it's the wrong tool, and understanding them is what makes the rest of the argument credible.

High write concurrency is the genuine Achilles heel. SQLite serializes writes — only one write transaction can execute at a time, even in WAL mode. If your application has many processes or threads hammering the database with writes simultaneously, you'll hit contention. Not data corruption (SQLite's transaction guarantees are solid), but latency. A write-heavy social platform, a real-time analytics ingestor, a high-frequency trading system — these are not SQLite workloads. PostgreSQL's multi-version concurrency control is genuinely better for those patterns.

Network-distributed access is the other clear no-go. SQLite is designed for a single machine. The file must live on a local filesystem (or a filesystem with reliable locking semantics, which excludes NFS in most configurations). If you have multiple application servers that all need to write to the same database simultaneously and those servers are on different machines, SQLite can't help you directly. Solutions like LiteFS and Turso solve this through replication strategies, but they come with their own trade-offs — mainly that replicas are read-only and writes must route to a primary. That's a perfectly fine trade-off for many apps, but you need to know it going in.

Deploying SQLite Thoughtfully

The operational pattern I've found works best is treating the SQLite file like you'd treat a stateful service replica. Put it on fast local NVMe storage. Set up continuous backup using something like Litestream — a tool that streams SQLite WAL changes to S3 or similar object storage in near-real-time. Your recovery point objective (RPO — the maximum data loss you'd accept if the server died) drops to seconds without any complicated replication setup.

# Install Litestream on your server
wget https://github.com/benbjohnson/litestream/releases/download/v0.3.13/litestream-v0.3.13-linux-amd64.tar.gz
tar -xzf litestream-*.tar.gz
sudo mv litestream /usr/local/bin/

# litestream.yml - minimal config to replicate to S3
# cat > /etc/litestream.yml << EOF
# dbs:
#   - path: /var/app/production.db
#     replicas:
#       - url: s3://your-bucket-name/production.db
# EOF

# Run your app through Litestream so it manages the process
litestream replicate -exec "node server.js"

That single command wraps your application process and continuously streams database changes offsite. Recovery is litestream restore followed by starting your app. No Postgres backups, no WAL archiving configuration, no point-in-time recovery setup. It's genuinely refreshing operational simplicity once you've internalized it.

The Philosophical Shift Worth Making

There's a broader mental model shift underneath all of this. The industry spent the 2010s optimizing for horizontal scale — breaking things into microservices, distributed systems, stateless applications. That architecture solves real problems at companies with Google-sized traffic. For the overwhelming majority of applications that don't have Google-sized traffic, it introduced enormous complexity without proportional benefit.

SQLite represents the opposite philosophy: make the single-machine case as powerful as possible before reaching for distributed complexity. Modern hardware is astoundingly capable. A single server with 64GB of RAM, fast NVMe storage, and a well-tuned SQLite database can serve more users than most teams will ever have. The simplicity isn't a compromise — it's a feature. Less infrastructure means fewer failure modes, faster development, easier debugging, and lower cost.

I've watched teams spend weeks configuring read replicas and connection pools for applications with 500 daily active users. That's not engineering discipline — it's cargo-culting patterns from companies whose scale you don't have. SQLite forces an honest conversation about what your application actually needs versus what feels sophisticated. More often than not, what you actually need is a lot less than you think.

The engineers building LiteFS, Turso, and D1 aren't naïve. They understand distributed systems deeply. They chose SQLite because they understand what it takes to make distributed databases work, and they recognized that for most workloads, the embedded model is genuinely superior. When sophisticated infrastructure engineers keep arriving at the same conclusion, that's not a trend worth dismissing. That's signal worth following.