Skip to content
←

필사 모드: SQLite Is Everywhere (and It's Amazing)

English
0%
정확도 0%
💡 왼쪽 원문을 읽으면서 오른쪽에 따라 써보세요. Tab 키로 힌트를 받을 수 있습니다.

Introduction — The Quietest Ruler in the World

When people talk about databases, they usually reach for PostgreSQL, MySQL, Oracle, or these days a cloud data warehouse. Yet the most widely deployed database engine on Earth is none of those. The answer is SQLite.

SQLite is probably the most-run database in the world. Conservative estimates put the number of active instances in the trillions. Why? Because it is everywhere. It is in the Android phone and the iPhone in your pocket, it runs inside every major browser like Chrome, Safari, and Firefox, it stores the settings and caches of desktop apps, and it even shows up in the avionics of commercial airliners. And yet SQLite is quiet. No server process, no administrator, no port number. It simply links into your app as a library and does its work without a sound.

This post is about how this quiet ruler is designed such that it spread this far, when SQLite actually beats a heavy client-server database, and the practical knowledge you need to run it well in production.

No Server — The Radical Single-File Design

The first key to understanding SQLite is that it is serverless — in a different sense than the trendy "serverless cloud." Here, serverless means SQLite has no separate database server process running at all.

Picture a typical database. If you use PostgreSQL, a postgres server process is running somewhere, and your application connects to it over a network socket (usually TCP port 5432) to exchange SQL. The server manages connections, handles authentication, and coordinates concurrent access. This architecture is powerful but heavy. You have to install the server, run it, configure it, secure it, and monitor it so it does not die.

SQLite removes this entire layer.

  Typical client-server DB
  +-------------+     network      +------------------+     +----------+
  | application | <--- socket ---> | DB server process | --> | disk     |
  +-------------+                  +------------------+     +----------+

  SQLite
  +-----------------------------------+     +------------+
  | application (SQLite linked in as   | --> | single file |
  |  a library, accessed via calls)    |     | (.sqlite)   |
  +-----------------------------------+     +------------+

In SQLite, the entire database is a single file on disk. Tables, indexes, views, triggers — all of it lives inside that one file. The application links SQLite in as a library, and executing SQL is just a function call, not a network round trip. "Connecting" to the database means simply opening that file.

This simplicity is the fundamental reason SQLite can be everywhere. There is no server to deploy, so installing the app installs the database with it. A backup is just a file copy, and moving to another machine is just copying the file. The operational burden is effectively zero.

When SQLite Beats a Client-Server Database

The SQLite docs have a famous line: "SQLite does not compete with things like PostgreSQL — it competes with fopen()." In other words, SQLite's real alternative is not another database but opening a file directly to read and write it. From that angle, it becomes clear when SQLite is the best choice.

Cases where SQLite is especially strong:

  • Application-local data: settings, state, cache, and offline data for desktop and mobile apps. Each user has their own file. A network database here is overkill.
  • Read-heavy workloads: SQLite is very fast at reads. A website whose content is mostly read can serve surprising amounts of traffic with SQLite alone.
  • Edge and embedded: IoT devices, vehicles, avionics — environments where you cannot stand up a DB server. Linking it in as a library fits perfectly.
  • File-format replacement: instead of inventing a complex custom binary format, use a SQLite file as your application's storage format. You get schema, transactions, and queries for free.
  • Testing and prototyping: as covered below, in-memory SQLite is ideal for tests.

Conversely, the cases where SQLite is a poor fit are just as clear:

  • High concurrent writes: SQLite serializes writes. If many clients write thousands of times per second concurrently, a client-server database is better.
  • Access from multiple machines: if several servers need to attach to one database over the network, the file-based model does not fit.
  • Very large scale plus complex management features: if you need fine-grained permissions, replication topologies, or massively parallel analytics, a dedicated DB is better.

The key insight is this: many applications reach for a client-server database out of habit, but if you look at the actual workload, an enormous number of them would be fine — and simpler — with SQLite. Shift your thinking from "a heavy DB by default" to "a heavy DB only when needed," and your systems get a lot lighter.

The Heart of Concurrency — WAL Mode

The most common misconception about SQLite is that "its concurrency is weak." That is half true, but the perception is usually based on the old default mode. Turn on WAL (Write-Ahead Logging) mode and the story changes considerably.

In the traditional rollback-journal mode, reads tended to block while a write was in progress, and writes blocked while reads were in progress, because a coarse lock applied to the whole database file.

WAL mode changes this structure. Instead of writing changes directly into the main database file, it first appends them to a separate WAL file. The core benefits of this approach are:

  • Reads and writes do not block each other. Multiple reads proceed concurrently even while one write is in progress. Readers see the last committed, consistent snapshot, and the writer appends to the WAL.
  • Commits are fast. Appending changes sequentially to the end of the WAL is much faster than randomly modifying scattered parts of a file.

That said, SQLite writes are still one at a time. Even in WAL mode there is only one writer at a time, and other writes wait their turn. But reads flow in parallel regardless. Given that most web applications follow a "read-heavy, write-light" pattern, this property of WAL mode fits real-world use extremely well.

You enable WAL mode with one line. Once set, it persists in the database permanently.

-- Enable WAL mode (persisted in the database file)
PRAGMA journal_mode = WAL;

-- A common setting balancing durability and performance
PRAGMA synchronous = NORMAL;

synchronous = NORMAL is a frequent companion to WAL mode. It syncs to disk less often than full FULL, making it much faster, while keeping a level of safety where a power loss will not corrupt the database (in the worst case you might lose only the last few transactions). If you run SQLite in production, these two PRAGMAs are close to a standard configuration.

If you want to poke at real SQLite right here in your browser, the SQL playground lets you run WAL, window functions, and more directly. If you want to experiment with analytics-flavored SQL, open the DuckDB data analysis playground alongside it and compare.

The Testing Secret Weapon — In-Memory Databases

One of SQLite's most beloved practical uses is testing. Give it a special name instead of a file path, and SQLite builds the entire database in memory rather than on disk.

import sqlite3

# A database purely in memory, not on disk
conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)")
conn.execute("INSERT INTO users (name) VALUES ('Alice')")
row = conn.execute("SELECT name FROM users WHERE id = 1").fetchone()
print(row)  # ('Alice',)

This is powerful for testing for several reasons:

  • Extremely fast. There is no disk I/O at all, so tests run in an instant. Thousands of tests can each create a clean DB without strain.
  • Perfect isolation. If each test creates its own fresh in-memory DB, there is no state leakage between tests by construction. When the test ends, it disappears along with the memory.
  • No setup required. You do not have to stand up or tear down a separate database server just to run tests. That is especially valuable in CI.

One caveat is the subtle difference that arises when your production and test databases differ. If you use PostgreSQL in production but test with in-memory SQLite, SQL-dialect differences can let tests pass while production breaks. So the safest combination is to use SQLite in production too, and failing that, to at least run your integration tests against the same database you ship.

SQLite in the Browser — A New Frontier via WASM

SQLite's portability was already remarkable, but WebAssembly took it somewhere entirely new: inside the browser.

SQLite is a well-crafted C codebase, which makes it ideal to compile to WASM. As a result, a real SQLite engine now runs inside a browser tab, with no server. Not an imitation — actual SQLite. The SQL playground linked above works exactly this way.

The possibilities this combination opens up are substantial:

  • Fully client-side apps: you can build offline-first web applications where data never leaves the browser, keeping user data as a SQLite file in the browser's persistent storage.
  • Privacy: analyze sensitive data with SQL directly in the browser without sending it to a server.
  • Education and tooling: ship tools that teach SQL or explore data as a pure static site, with no backend infrastructure.

There is a trend of PostgreSQL-family engines coming to WASM too, but because SQLite was designed as an embedded library from the start, this transition is the most natural for it. SQLite's original philosophy of "putting the database inside the app" carries straight over into the browser as a new kind of app environment.

Durability and Replication — The Elegance of litestream

The first worry that comes up when running SQLite on a production web server is this: "The database is a single local file on the server — what happens to the data if that server dies?"

The traditional answer was to use the replication features of a client-server database. But the SQLite ecosystem has a more elegant approach: tools like litestream.

litestream's idea is clever. Remember the WAL file described earlier? litestream watches the changes appended to that WAL in real time and continuously streams them to an object store like S3. In other words, every change to the database is backed up externally in near real time.

  application
      |
      v (write)
  SQLite (local file + WAL)
      |
      v (litestream continuously watches WAL changes)
  streamed backup to object storage (S3, etc.)
      |
      v (on failure)
  restore to latest state on another server

The benefits of this approach:

  • Near-real-time backup. Unlike periodic snapshots, changes flow outward the moment they occur. Even if the server dies, data loss is minimized.
  • Simple restore. Restoring the database from the object store on a new server brings it back close to its last state.
  • Cheap and simple. No need to operate a separate cluster of replica servers — one cheap object store is enough.

This approach overturns the received wisdom that "SQLite has weak durability." It keeps the simplicity of a single file while gaining robust durability by using cloud object storage as the backup target. Recently the ecosystem has advanced further toward multiple servers sharing SQLite or keeping read replicas, so even the perception that "SQLite is for a single server" is gradually shifting.

A Practical Checklist for Running SQLite Well

Compressing everything above into production-oriented guidance:

  • Turn on WAL mode. For a web application it is almost always right. PRAGMA journal_mode = WAL and PRAGMA synchronous = NORMAL are effectively the standard config.
  • Remember writes are serialized. SQLite serializes concurrent writes into one. Keep write transactions short and do not hold them open for a long time.
  • Set a busy timeout. Instead of failing immediately when a write lock is held, set busy_timeout so it waits briefly — this smooths over momentary contention.
  • Test in memory. Use :memory: databases for fast, isolated tests. Just watch for dialect differences from your production DB.
  • Get durability from a tool like litestream. If you run it on a production web server, lower your data-loss risk with WAL streaming backups.
  • Back up the right way, not by copying the file. Copying a running database with a plain cp can corrupt it. Use SQLite's backup API, VACUUM INTO, or litestream.

Conclusion

SQLite is a rare success story in software engineering. It is not flashy, its marketing is quiet, yet it became the most widely used database on Earth. The secret is radical simplicity. The decision to remove the server and make the database a single file carried SQLite into phones, browsers, and airplanes.

And that simplicity is not a weakness. WAL mode solves much of the concurrency problem, in-memory mode revolutionizes testing, WASM opens up the browser as a new stage, and litestream dissolves the durability worry — so SQLite has fully outgrown the old "toy database" label. It has become a serious option for serious production systems.

The next time you reflexively think "let's just spin up PostgreSQL" for a new project, pause and ask: "Isn't SQLite enough for this workload?" Surprisingly often, the answer is yes. And that choice will make your system a lot simpler and more robust.

References

현재 단락 (1/94)

When people talk about databases, they usually reach for PostgreSQL, MySQL, Oracle, or these days a ...

작성 글자: 0자원문 글자: 11,563자작성 단락: 0/94