SQLite: The Powerhouse Hiding in Plain Sight

“That’s Not a Real Database”

That’s what I said the first time someone suggested SQLite.

I’d just spent weeks learning MySQL—setting up servers, configuring users, managing connections. Real database stuff. Then a colleague showed me SQLite:

sqlite3 myapp.db

That’s it. No server to install. No daemon running in the background. No configuration file. No port. No username. No password. Just a command and a filename.

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO users (name, email) VALUES ('Saurav', 'saurav@example.com');

SELECT * FROM users;

1|Saurav|saurav@example.com|2019-03-15 10:30:00

Same SQL I already knew. Same tables, same inserts, same selects. But the entire database was a single file on my filesystem. I could ls -la it:

ls -la myapp.db
-rw-r--r--  1 saurav  staff  12288 Mar 15 10:30 myapp.db

12 kilobytes. My entire database was 12 kilobytes. I could email it. I could put it in Dropbox. I could copy it to another machine and it would just work. No export, no import, no dump files.

“That’s cute,” I thought. “But it’s not a real database.”

I was so, so wrong.

Where SQLite Actually Runs

After dismissing it, I kept running into SQLite in unexpected places. Then I looked up the numbers and my jaw dropped.

Every iPhone runs SQLite. Contacts, messages, photos, app data—all stored in SQLite databases. Every single iOS app that stores structured data locally probably uses SQLite.

Every Android phone runs SQLite. Same story. The default local storage for Android apps.

Every web browser runs SQLite. Chrome, Firefox, Safari—they all use SQLite internally for bookmarks, history, cookies, and local storage.

Every Mac and Linux machine ships with SQLite. It’s pre-installed. Right now, on your machine, there are probably dozens of SQLite databases you didn’t know about.

Python ships with SQLite. import sqlite3—it’s in the standard library. No pip install needed.

PHP ships with SQLite. Built into every PHP installation.

The SQLite website claims it’s the most deployed database engine in the world. Not MySQL. Not PostgreSQL. Not Oracle. SQLite. By a factor of thousands.

A “toy database” doesn’t ship on four billion devices.

Why I Started Taking It Seriously

I was building a side project—a personal bookmarking tool. Just for me. The stack was overkill for what it needed: a full MySQL server running 24/7 on my laptop so I could save bookmarks.

Then I thought: what if I just used SQLite?

import Database from 'better-sqlite3';

const db = new Database('bookmarks.db');

db.exec(`
  CREATE TABLE IF NOT EXISTS bookmarks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    url TEXT NOT NULL,
    title TEXT,
    tags TEXT,
    notes TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  )
`);

const insert = db.prepare(
  'INSERT INTO bookmarks (url, title, tags, notes) VALUES (?, ?, ?, ?)'
);

insert.run(
  'https://sqlite.org',
  'SQLite Home Page',
  'database,sqlite,reference',
  'Official SQLite documentation'
);

const bookmarks = db.prepare('SELECT * FROM bookmarks ORDER BY created_at DESC').all();
console.log(bookmarks);

No connection string. No database server to start. No “is MySQL running?” debugging. The database starts when my app starts and stops when my app stops.

I deployed it to a $5 VPS. The entire application was one binary and one .db file. Deployment was scp two files. That’s it.

The bookmarking tool has been running for months. Zero database maintenance. Zero downtime. Zero connection pool tuning. The .db file is 2MB and handles everything I throw at it without breaking a sweat.

The Performance That Shocked Me

I assumed SQLite would be slow. It’s just a file, right? No fancy server optimizations.

Wrong. SQLite is fast. Astonishingly fast for reads.

I ran a quick test. 100,000 rows, simple lookups by indexed column:

-- Insert 100,000 users
-- (generated with a script)

-- Create an index
CREATE INDEX idx_users_email ON users(email);

-- Lookup by email
SELECT * FROM users WHERE email = 'user50000@example.com';

Result: under 1 millisecond. On a laptop. With a file-based database.

Here’s why: SQLite has zero network overhead. MySQL receives a query over TCP, parses it, executes it, serializes the result, and sends it back over TCP. SQLite skips all of that. The query goes directly from your application to the database engine in the same process. No network. No serialization. No IPC.

For read-heavy workloads, this is a massive advantage. SQLite doesn’t just compete with client-server databases on reads—it often beats them.

MySQL query lifecycle:
  App → TCP → MySQL Server → Parse → Execute → Serialize → TCP → App
  Overhead: ~1-5ms per query (network + protocol)

SQLite query lifecycle:
  App → Execute
  Overhead: ~0ms (it's a function call)

When your “database query” is a function call in the same process, the overhead disappears.

Where SQLite Shines

After using it for a while, I developed a feel for where SQLite is the right choice:

Single-User Applications

Desktop apps, CLI tools, personal projects, local-first apps. If one user (or one process) is accessing the data, SQLite is perfect. No server to manage. No configuration to maintain.

# A CLI todo app? SQLite.
# A personal finance tracker? SQLite.
# A local development database? SQLite.
# An Electron app's storage? SQLite.

Embedded Systems and Mobile

This is SQLite’s home turf. Every mobile app that needs local storage uses SQLite (or a wrapper around it). It’s lightweight, reliable, and needs zero administration.

Read-Heavy Workloads

Content sites, blogs, reference databases, analytics dashboards that query precomputed data. If your app mostly reads data and rarely writes, SQLite handles it beautifully.

Prototyping and Development

This is where SQLite saved me the most time. New project? sqlite3 dev.db. No Docker compose file. No database container. No waiting for MySQL to start. Just start coding.

// Start a new project with zero database setup
const db = new Database(':memory:'); // In-memory for tests
// or
const db = new Database('dev.db');   // File for development

The :memory: mode is perfect for tests. Database starts fresh every run. No cleanup needed. No test data leaking between runs. Fast.

Configuration and Caching

Some applications use SQLite as a configuration store or cache. Firefox stores your preferences in SQLite. macOS uses SQLite for Spotlight search indexes. It’s the Swiss Army knife of local data storage.

Where SQLite Breaks

It’s not magic. There are real limitations, and I hit some of them.

Concurrent Writes

This is the big one. SQLite uses a file-level lock for writes. One writer at a time. If two processes try to write simultaneously, one waits.

Process A: BEGIN TRANSACTION → writes → COMMIT
Process B: BEGIN TRANSACTION → waits... → waits... → Process A done → writes → COMMIT

For a web server handling 100 simultaneous users all writing data? That queue gets long. MySQL handles this with row-level locking. PostgreSQL handles this with MVCC. SQLite locks the entire file.

WAL mode (Write-Ahead Logging) helps a lot:

PRAGMA journal_mode=WAL;

With WAL, readers don’t block writers and writers don’t block readers. Multiple reads happen concurrently. But writes are still serialized. One at a time.

For many applications, this is fine. A blog with 10 comments per minute? SQLite handles it. A real-time chat app with 1,000 messages per second? Use PostgreSQL.

No Network Access

SQLite has no server. That means no remote connections. Your application and your database must live on the same machine (or the same filesystem).

In the age of microservices where the database server runs on a separate machine from the application server, this is a real constraint. You can’t have three app servers all connecting to one SQLite file on a network share. Well, you can, but you shouldn’t. Network filesystems and file-level locking don’t mix well.

No Built-In User Management

MySQL has CREATE USER, GRANT, REVOKE. Role-based access control. Read-only users. Database-level permissions.

SQLite has none of this. If you can read the file, you can read (and modify) everything in it. Security is filesystem permissions, not database permissions.

Limited Data Types

MySQL has INT, BIGINT, FLOAT, DOUBLE, DECIMAL, DATE, DATETIME, TIMESTAMP, BLOB, TEXT, VARCHAR(n), ENUM, SET…

SQLite has five: NULL, INTEGER, REAL, TEXT, BLOB.

That’s it. No strict VARCHAR(100)—you can store a novel in a TEXT column declared as VARCHAR(10). SQLite is flexible about types, which is either a feature or a footgun depending on your perspective.

-- SQLite lets you do this (MySQL would refuse)
CREATE TABLE demo (age INTEGER);
INSERT INTO demo (age) VALUES ('not a number');
-- No error. "not a number" is stored as TEXT in an INTEGER column.

This “type affinity” system caught me off guard after MySQL’s strict typing. You need to validate data in your application code, not rely on the database to reject bad data.

The New Wave: SQLite in Production

Here’s what surprised me: while I was thinking of SQLite as a development-only tool, the industry quietly started using it in production.

Litestream replicates SQLite databases to S3 continuously. Your database is a file, and Litestream streams changes to cloud storage. Point-in-time recovery for a file-based database.

Turso (formerly libSQL) is a distributed SQLite service. SQLite at the edge, replicated globally. The simplicity of SQLite with the distribution of a cloud database.

Rails 8 made SQLite a first-class production database. DHH and the Rails team built solid_cache, solid_queue, and solid_cable—production-ready caching, background jobs, and WebSockets—all backed by SQLite.

The argument went from “SQLite isn’t for production” to “SQLite might be the only database most apps need.”

What I Wish I’d Known Earlier

1. SQLite is not “MySQL but worse.” It’s a different tool for different problems. Comparing them is like comparing a motorcycle to a truck. Both get you there. Different trade-offs.

2. Turn on WAL mode immediately. PRAGMA journal_mode=WAL; should be the first thing you run on any SQLite database. It dramatically improves concurrent read performance.

3. SQLite is perfect for tests. In-memory databases that spin up in microseconds, need no cleanup, and run your exact schema? It’s the best testing database I’ve used.

4. The file IS the database. Back it up with cp. Move it with scp. Version it with… well, don’t put it in Git, but you get the idea. The simplicity of a single file is genuinely powerful.

5. Don’t dismiss it for production. If your app serves one region, has moderate write traffic, and values simplicity, SQLite might be all you need. The Rails community proved this.

Respect the File

I spent years looking down on SQLite. “No server? Must be for toys.” Meanwhile, it was quietly running on every device I owned, powering apps I used daily, and handling more queries per day than any “real” database I’d ever set up.

SQLite taught me something important: complexity isn’t a feature. A database doesn’t need a server, a daemon, a configuration file, and a DBA to be serious. Sometimes the best architecture is the simplest one that works.

A single file. Zero configuration. Billions of deployments.

Not bad for a “toy database.”

---

P.S. — I once accidentally deleted a MySQL database in development and lost two hours rebuilding the schema. With SQLite, my “backup strategy” is literally cp app.db app.db.backup in a cron job. It runs every hour, costs nothing, and has saved me twice. Sometimes the unsophisticated solution is the best solution.