SQLite: The Database That's Just a File — FM India

SQLite is, by sheer count, the most deployed database in the world. It's in every Android and iOS phone, every major browser, countless desktop apps, and plenty of servers. People sometimes ask whether it's a "real" database; it is, and it's also one of the most thoroughly tested pieces of software ever written. It just works completely differently from Postgres and MySQL, and understanding that difference tells you exactly when to use it.

What it is

Postgres and MySQL are servers: a separate process your app connects to over a socket. SQLite is not a server. It's a small library your app embeds, and your entire database is a single file on disk. There's no process to run, no port, no connection string, no setup. You open a file and run SQL against it.

That's it. The SQL is standard (everything from the hands-on lessons works), but there's no server in the picture at all.

Why "just a file" is a superpower

Functional · what it does

Zero setup. No server to install, configure, or run. Open a file, query it.
Real SQL. Full transactions, indexes, joins — the relational model in one file.
Embeddable anywhere. Ships inside the app: phones, browsers (this page!), desktop tools, edge functions.

Non-functional · what it must survive

Incredibly reliable. Among the most thoroughly tested software in existence.
Fast for local reads. No network hop; it's a function call into a file.
Tiny and portable. The whole database is one file you can copy, ship, or back up.

When to use it

SQLite shines wherever the database lives with the application rather than serving many clients over a network:

On-device storage: mobile apps, desktop apps, browser storage.
Local development and tests: a throwaway database with zero setup.
Small-to-medium websites: a surprising number of low-to-moderate-traffic sites run happily on SQLite, especially read-heavy ones.
Edge and embedded: it's the engine behind tools like Turso and libSQL for edge databases.

When not to use it

The limit is concurrent writers

SQLite allows many simultaneous readers but only one writer at a time. In the default journal mode, a write locks the whole file, so readers wait too. Turning on WAL mode (write-ahead logging) is the standard fix: readers no longer block writers and writers no longer block readers, so a busy read-heavy app gets much smoother. But the single-writer rule still holds. A second writer that arrives mid-write either waits or gets back a SQLITE_BUSY error, which is the source of the infamous "database is locked" message.

For a write-heavy app with many servers all writing at once, that ceiling is a real bottleneck, and the single-file model doesn't fit a setup where several machines need to share the data over a network anyway. That's exactly the job Postgres and MySQL exist for. Rule of thumb: one machine, embedded data, mostly reads, SQLite; many machines sharing data, heavy concurrent writes, a database server.

You've already used it

This very platform stores its data in SQLite during development, and the in-browser query playground is Postgres compiled to WebAssembly, but the same WASM trick is how SQLite has run in browsers for years. The fact that a full SQL database can be a single file you embed anywhere is precisely why it's everywhere.

The one-liner

SQLite is a relational SQL database that's just a single file with no server. That makes it perfect for on-device, local, and small-site use, and unsuitable when many machines must share data or hammer it with concurrent writes. Same SQL you already know, radically simpler deployment.

Test yourself

Questions· say the answer out loud before you open it. If you can't, the chapter isn't done.

QHow is SQLite fundamentally different from Postgres and MySQL?+

Postgres and MySQL are servers your app connects to over a socket. SQLite is a library embedded in your app, with the whole database as a single file on disk. No server, no port, no connection string: you open a file and run SQL against it.

QWhat is SQLite's main limitation?+

Concurrent writes. It allows many simultaneous readers but only one writer at a time. WAL mode lets reads and writes proceed together (no more reader-blocks-writer), but a second writer still has to wait or gets a SQLITE_BUSY "database is locked" error. And being a single file, it doesn't fit setups where several machines need to share the data over a network.

QWhen is SQLite a great choice?+

When the database lives with the application rather than serving many networked clients: mobile and desktop apps, browser storage, local development and tests, edge/embedded use, and small-to-medium read-heavy websites. One machine, embedded data, mostly reads is the sweet spot.