When to Use SQLite: Use Cases, Limitations, and When to Skip It

The Core Question

SQLite isn't a smaller version of Postgres. It's a different shape of tool. The whole database is a file on disk, and your application talks to it through a library linked into the same process — no server, no network, no users. That design makes SQLite brilliant for some jobs and a poor fit for others.

The deciding question is almost never "is SQLite fast enough?" (it usually is). It's "does my application's shape match what SQLite is good at?" Two things mostly determine that: where the data lives, and how many things write to it at once.

Use SQLite When the Data Lives With the App

SQLite shines when the database belongs to one application on one machine. The file sits next to your code, your app opens it directly, and that's the whole architecture.

-- A typical "data lives with the app" schema:
-- a desktop note-taking app's local store.
CREATE TABLE notes (
    id        INTEGER PRIMARY KEY,
    title     TEXT NOT NULL,
    body      TEXT,
    created   TEXT DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO notes (title, body) VALUES
    ('Groceries', 'milk, bread, coffee'),
    ('Ideas',     'sqlite tutorial outline');

SELECT id, title, created FROM notes;

That tiny schema could be the entire backend of a real app. A few cases where this pattern fits naturally:

• Desktop and mobile apps. Every iOS and Android app that needs structured local storage uses SQLite under the hood. So do Firefox, Chrome, and most browsers.
• CLI tools. git, package managers, and dotfile managers all use embedded databases.
• Embedded devices. Routers, cars, planes — anything that needs a database in a few hundred kilobytes of footprint.
• Test suites. A fresh SQLite file (or :memory: database) per test is faster and more isolated than spinning up Postgres.

If your data doesn't need to be shared across multiple machines, SQLite is probably the right answer.

Use SQLite for Read-Heavy Websites

SQLite handles reads beautifully. Many concurrent readers, no contention, microsecond query times for indexed lookups. If your site is mostly people reading content — blogs, documentation, marketing sites, small SaaS dashboards — SQLite will hold up fine, often better than a network-attached Postgres because there's no round-trip.

-- A blog's posts table.
CREATE TABLE posts (
    slug      TEXT PRIMARY KEY,
    title     TEXT NOT NULL,
    body      TEXT NOT NULL,
    published TEXT
);

CREATE INDEX idx_posts_published ON posts(published DESC);

INSERT INTO posts VALUES
    ('hello-world', 'Hello, World', 'First post.', '2024-01-01'),
    ('sqlite-rocks', 'Why SQLite',  'Single-file DB.', '2024-02-15');

SELECT slug, title FROM posts ORDER BY published DESC LIMIT 10;

The catch is writes. SQLite serializes writes — only one writer can hold the lock at a time. For a blog with a few authors, that's invisible. For a chat app with thousands of users posting messages every second, it's a problem.

WAL mode (write-ahead logging, covered later) raises the ceiling considerably by letting readers and the writer work in parallel. Plenty of production sites serve millions of requests a day on SQLite + WAL.

Use SQLite for Local Caches and Scratch Data

Any time you need structured local storage — something more than a JSON file but less than a full database server — SQLite is hard to beat.

-- A cache table for an HTTP client.
CREATE TABLE http_cache (
    url        TEXT PRIMARY KEY,
    body       BLOB NOT NULL,
    expires_at INTEGER NOT NULL
);

INSERT INTO http_cache (url, body, expires_at)
    VALUES ('https://api.example.com/users/1', x'7b2269...', 1735689600);

DELETE FROM http_cache WHERE expires_at < strftime('%s', 'now');
SELECT count(*) FROM http_cache;

Logs, analytics buffers, ETL staging, machine-learning feature stores, IDE indexes — all natural homes for SQLite. The file is portable, the query language is full SQL, and there's no server to babysit.

Don't Use SQLite for Many Concurrent Writers

This is the limitation that catches people. SQLite uses a database-level write lock: one writer, full stop. Two processes trying to insert at the same instant means one waits.

For most apps that doesn't matter — writes are rare and quick. But if your workload looks like:

• A multi-tenant SaaS with thousands of users all writing simultaneously,
• A high-throughput message queue or event log,
• A real-time game or chat backend with constant state changes,

then SQLite will become the bottleneck. Postgres and MySQL use row-level locking and were built for exactly this. Reach for them.

-- The error you'll see when writers pile up:
Error: database is locked

If you're seeing that under normal load, SQLite is the wrong tool — not a bug to work around.

Don't Use SQLite Across Multiple Machines

SQLite is an in-process library. The application reads and writes the file directly, which means the file has to be on a disk the application can read() and write() to with normal filesystem calls.

That rules out:

• Multiple application servers behind a load balancer, all wanting to share one database. (Network filesystems like NFS technically work but corrupt the file under load. Don't.)
• Serverless functions where each invocation runs on a different machine.
• Kubernetes pods that need a shared database — unless you're using a single-pod deployment with a persistent volume.

If your architecture has more than one process on more than one machine writing to the database, you need a client-server database. That's the line.

Don't Use SQLite When You Need Database-Level Users

SQLite has no concept of users, roles, or permissions. The database is a file — whoever can read the file can read everything; whoever can write it can write everything. Access control is the operating system's job.

For a single-user desktop app, that's fine. For a multi-tenant system where different humans need different privileges inside the database, you want Postgres or MySQL.

A Quick Decision Checklist

Walk down this list. If you answer "yes" to all of them, SQLite is probably a great choice:

• The database lives on the same machine as the application.
• One process (or a handful, mostly reading) talks to it.
• Total data fits comfortably on one disk — terabytes are fine, but it's still one disk.
• You don't need database-level user permissions.
• Concurrent writes are occasional, not the dominant workload.

If any answer is "no," look at Postgres or MySQL instead. The next two pages compare them directly.

What You Take Away

• SQLite is the right call when the database is local to the application: desktop, mobile, embedded, CLI tools, test suites, read-heavy sites, local caches.
• It's production-grade — billions of devices ship with it. The constraints are architectural, not about quality.
• Skip it when you need many concurrent writers, multi-machine access, or per-user database permissions.

Next: Installing SQLite

Enough theory. The next page walks through getting SQLite onto your machine — it's already there on macOS and most Linux distros, and a single download on Windows — and verifying the install from the command line.