SQLite: the most underrated database

SQLite gets dismissed as a toy database. Something for mobile apps and prototypes, not for "real" work. That is wrong. SQLite handles more traffic than most people realize. Every Android phone, every iOS device, every Firefox and Chrome browser, every macOS machine uses SQLite internally. It is the most deployed database in the world.

What makes SQLite different

SQLite is an embedded database. There is no server process. The database is a single file. Your application reads and writes to it directly. No network connections, no authentication, no configuration.

import Database from "better-sqlite3";
 
const db = new Database("app.db");
 
db.exec(`
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL
  )
`);
 
const user = db.prepare("SELECT * FROM users WHERE id = ?").get(1);

No connection strings. No connection pool configuration. No database container to manage. Just a file.

When SQLite is the right choice

Single-server web applications. If your app runs on one server (which covers the vast majority of web applications), SQLite is faster than PostgreSQL for read-heavy workloads because there is no network overhead between the application and the database.

Embedded applications. Desktop apps, CLI tools, mobile apps. Anywhere you need persistent structured data without requiring the user to install a database server.

Development and testing. SQLite is perfect for local development and test suites. Tests run faster because there is no database server to start and stop. The database is a file that you can delete and recreate instantly.

Configuration and state. Many applications use SQLite as a better alternative to config files or JSON stores. Vaultwarden stores its vault in SQLite. Many Electron apps use it for local data.

When PostgreSQL is the better choice

Multiple application servers. SQLite does not handle concurrent writes from multiple processes well. If you have multiple servers behind a load balancer, you need a client-server database.

Complex queries. PostgreSQL has more advanced query features: CTEs with write operations, window functions with more options, full-text search, JSONB indexing, and PostGIS for geospatial data.

Replication. If you need read replicas, streaming replication, or logical replication, PostgreSQL has mature solutions. SQLite has Litestream for replication but it is not as battle-tested.

Performance

SQLite is surprisingly fast. For a typical web application reading data:

• SQLite read: ~0.01ms (no network, no query planning overhead for simple queries)
• PostgreSQL read: ~0.5-2ms (network round trip + query processing)

That is a 50-200x difference for simple queries. The gap narrows for complex queries where PostgreSQL's query planner shines, but for CRUD operations, SQLite is hard to beat.

Backups

Backing up SQLite is copying a file. You can also use the .backup command for a consistent copy while the database is in use:

sqlite3 app.db ".backup backup.db"

For continuous backups, Litestream replicates SQLite changes to S3 in near real-time:

litestream replicate app.db s3://my-bucket/app.db

The bottom line

SQLite is not a compromise. For single-server applications, it is often the best choice. Less infrastructure, fewer moving parts, faster reads, simpler backups. Do not reach for PostgreSQL by default. Think about whether you actually need a client-server database, and if you do not, SQLite is right there.

Sources

• SQLite
• Litestream
• better-sqlite3