Contact

The SQLite database: a complete overview, pros, cons and limits

What SQLite is used for, whether it is good for a website and where a server database wins: WAL mode, pros and cons, a comparison with PostgreSQL and MySQL, tools, SQL examples, limits and tips.

Stack and technologies Updated

In short

SQLite is a database in a single file: a small library inside the application, with no server, no users and no setup. By its authors’ estimate it is the most widely deployed database in the world — it lives in every phone, every browser and countless programs. For a website it is a serious option: with WAL mode many readers work in parallel with a writer, transactions are fully reliable, and there is full-text search, JSON, RETURNING and strict tables. Its limit is one writer at a time and one machine: for a busy shop with many simultaneous orders or several app servers, take PostgreSQL or MySQL.

SQLite at a glance

The main facts in one table — what SQLite is, how it stores data and where it lives.

Type
An embedded relational database: a library, not a server
History
D. Richard Hipp, 2000; the file format of version 3 is stable since 2004
License
Public domain — no licence at all
Storage
The whole database is one file that can be copied like any other
Transactions
ACID; in WAL mode readers do not block the writer
Writes
One writer at a time; the rest wait their turn
SQL
Window functions, CTEs, upsert, RETURNING, generated columns, STRICT tables
Built in
JSON functions, FTS5 full-text search, R*Tree for coordinates
Limits
Up to 281 TB per file in theory; in practice — one machine
Where it lives
Android and iOS, every major browser, desktop programs, devices
In languages
Built into PHP, Python and Node.js (node:sqlite); drivers for the rest

What SQLite is used for: 8 areas

From phones to production websites. Under each area — the tools it usually comes with.

  1. 01

    Sites and blogs

    Content, forms and an admin panel: most sites write rarely and read a lot — SQLite’s strong case.

    PHP PDOWAL

  2. 02

    Small services and internal tools

    One server, one file, nothing extra to administer.

    GoRails 8Laravel

  3. 03

    Mobile apps

    The default local database on Android and iOS.

    RoomCore DataExpo SQLite

  4. 04

    Desktop programs

    Settings, history and documents of applications are often stored in SQLite.

    ElectronTauri

  5. 05

    Browser extensions

    SQLite compiled to WebAssembly keeps data right in the browser.

    sqlite-wasmOPFS

  6. 06

    Data analysis

    Load a CSV and query it with SQL in seconds, without a server.

    DatasettePython

  7. 07

    Tests and prototypes

    A database in memory is created for each test and disappears after it.

    :memory:

  8. 08

    Distributed SQLite

    Copies near the user and replication to storage — through separate projects.

    LitestreamTursoCloudflare D1

Pros and cons of SQLite

SQLite trades a server for simplicity. What you gain and what you give up both come from that.

Pros · 8

  • Nothing to administer

    No server, users, ports or passwords — the database starts with the application.

  • Very fast reads

    No network between the code and the data: a query is a function call.

  • A backup is a copy of a file

    The whole database moves and backs up as one file.

  • Reliable

    Full transactions; the code is tested more thoroughly than almost any software.

  • Modern SQL

    Window functions, upsert, RETURNING, JSON and strict tables.

  • Search out of the box

    FTS5 gives full-text search with ranking without a separate engine.

  • Free forever

    Public domain and a file format the authors promise to support until 2050.

  • Cheaper hosting

    No separate database server — one machine is enough for the whole project.

Cons · 8

  • One writer at a time

    Many simultaneous writes queue up — busy shops and services feel it.

  • One machine

    Several app servers cannot share one file safely over a network drive.

  • No users and rights

    Access is decided by file permissions, not by database accounts.

  • Lax types by default

    Without STRICT a text can quietly end up in a number column.

  • Limited ALTER TABLE

    Some schema changes mean rebuilding the table by hand.

  • No built-in replication

    Copies and failover come from separate tools like Litestream.

  • Search without morphology

    FTS5 does not know word forms out of the box; prefix queries help.

  • File permissions matter

    In WAL mode even reading needs write access to the -wal and -shm files next to the database.

SQLite compared with PostgreSQL and MySQL

A file versus a server — the comparison is about the model, not about benchmarks.

CriterionSQLitePostgreSQLMySQL
Model a library and a file a server a server
Setup none yes, plus a pooler yes
Simultaneous writes one at a time many many
Several app servers no yes yes
Read speed on one machine very high, no network high high
Users and rights file permissions roles and rights users and rights
Replication external tools built in built in
Best for sites, small services, apps services with complex data CMS and web apps

When to choose SQLite — and when not to

Twelve typical tasks with a verdict. Where SQLite is not the best choice, the alternative is named.

  • Corporate site or blog

    Best fit

    Few writes, many reads — exactly SQLite’s case.

  • Landing page with a form

    Best fit

    A server database would be overkill.

  • Internal tool for a team

    Best fit

    One server, one file, nothing to administer.

  • Mobile or desktop app

    Best fit

    The standard local database.

  • Tests and prototypes

    Best fit

    A fresh database in memory for each run.

  • Catalogue of a few thousand items

    Best fit

    Reads and search work well; imports go in one transaction.

  • Small online shop

    Works

    Fine at a modest number of orders; plan the move when it grows.

  • SaaS at the start

    Works

    Possible on one server with WAL; PostgreSQL is safer for growth.

  • Busy shop with many orders at once

    Pick another

    PostgreSQL or MySQL: many writers at a time.

  • Several app servers

    Pick another

    A server database they all connect to.

  • Strict access rights for many users

    Pick another

    PostgreSQL with roles.

  • Geodata and vector search at scale

    Pick another

    PostgreSQL with PostGIS and pgvector.

The SQLite ecosystem: tools for common tasks

The middle column is what comes with SQLite itself.

TaskBuilt inTools
Command line sqlite3 —
Backups VACUUM INTO, .backup Litestream
Replication — Litestream, LiteFS, Turso
Full-text search FTS5 —
JSON json_*, ->> —
Vectors — sqlite-vec
In the browser — sqlite-wasm
Viewing data — DB Browser for SQLite, Datasette, DBeaver
In PHP PDO_SQLITE, SQLite3 —
In Node.js node:sqlite better-sqlite3

The limits of SQLite and common mistakes

  1. The default journal mode

    Without WAL a write blocks readers. For a site, turn WAL on once — it is stored in the file.

  2. “database is locked” without a timeout

    Without busy_timeout a request that meets a busy file fails at once instead of waiting a moment.

  3. Wrong owner of the WAL files

    If -wal or -shm belong to another user, the site gets random errors — even on reads.

  4. A file on a network drive

    File locks over NFS are unreliable — the database can be damaged.

  5. Copying a live file

    A plain copy during writes may be broken; use VACUUM INTO or .backup.

  6. Growth without a plan

    When writes or servers multiply, the move to a server database is easier planned in advance.

8 tips for SQLite in production

  1. 01

    WAL mode

    PRAGMA journal_mode = WAL — readers and the writer stop blocking each other.

  2. 02

    busy_timeout

    A few seconds of waiting instead of “database is locked”.

  3. 03

    Foreign keys on

    PRAGMA foreign_keys = ON in every connection — it is off by default.

  4. 04

    STRICT tables

    Types are checked, and wrong data is an error, not a surprise.

  5. 05

    Imports in one transaction

    Thousands of inserts in one transaction are many times faster than one by one.

  6. 06

    One owner for all files

    The database, -wal and -shm belong to the user the site runs as.

  7. 07

    Backups with Litestream

    Continuous copying to storage — restore to any moment.

  8. 08

    Plan the exit

    Standard SQL and a migration tool make a later move to PostgreSQL routine.

What SQLite looks like: 3 SQL examples

Settings for a site with a strict table, full-text search and JSON with an index. Checked in SQLite 3.40 on a database file.

Settings and a strict table

WAL, waiting instead of an error, upsert with RETURNING — and a text in a price column is rejected.

products.sql 
-- settings for a site: readers do not block the writer, and a busy file waits instead of failing
PRAGMA journal_mode = WAL;    -- wal
PRAGMA busy_timeout = 5000;
PRAGMA foreign_keys = ON;

-- STRICT: column types are checked, as in a server database
CREATE TABLE products (
    id    INTEGER PRIMARY KEY,
    sku   TEXT    NOT NULL UNIQUE,
    title TEXT    NOT NULL,
    price INTEGER NOT NULL CHECK (price >= 0)
) STRICT;

INSERT INTO products (sku, title, price) VALUES ('A-100', 'Oak table', 24000)
ON CONFLICT (sku) DO UPDATE SET price = excluded.price
RETURNING id, title, price;   -- 1|Oak table|24000

INSERT INTO products (sku, title, price) VALUES ('A-101', 'Oak chair', 'cheap');
-- error: cannot store TEXT value in INTEGER column products.price

Full-text search in the file

FTS5 finds and highlights the match and sorts by relevance.

search.sql 
-- full-text search right inside the file: no separate search engine
CREATE VIRTUAL TABLE articles USING fts5(title, body);

INSERT INTO articles (title, body) VALUES
    ('How to choose a table', 'Oak and walnut tables for the kitchen'),
    ('Chair care', 'How to clean oak chairs'),
    ('Shelves', 'Pine shelves for a small room');

SELECT title, snippet(articles, 1, '[', ']', '…', 4) AS fragment
FROM articles
WHERE articles MATCH 'oak'
ORDER BY rank;
-- Chair care|How to clean [oak]…
-- How to choose a table|[Oak] and walnut tables…

JSON with an index

A generated column pulls a field out of JSON, and an index makes the search by it fast.

events.sql 
-- JSON in a column and an index on a field inside it
CREATE TABLE events (
    id      INTEGER PRIMARY KEY,
    payload TEXT NOT NULL CHECK (json_valid(payload)),
    kind    TEXT GENERATED ALWAYS AS (payload ->> '$.kind') VIRTUAL
);
CREATE INDEX events_kind_idx ON events (kind);

INSERT INTO events (payload) VALUES ('{"kind": "order", "total": 24000}'), ('{"kind": "visit"}');

SELECT id, payload ->> '$.total' AS total
FROM events
WHERE kind = 'order';   -- 1|24000

Questions about SQLite

Can SQLite be used for a website?

Yes. Most sites read far more than they write, and SQLite in WAL mode handles that very well.

How many visitors can SQLite handle?

It depends on writes, not visits: reads scale well, and the limit is many simultaneous writes.

SQLite or PostgreSQL?

SQLite for a site or service on one server; PostgreSQL when there are many writers, several servers or complex data.

Is SQLite reliable?

Yes: full transactions and one of the most thoroughly tested codebases in the world. Risks come from network drives and copying a live file.

How do I back up SQLite?

VACUUM INTO or .backup for a consistent copy, or Litestream for continuous copying.

Why “database is locked”?

Another connection is writing. Turn on WAL and set busy_timeout so requests wait a moment.

Does SQLite support full-text search?

Yes, FTS5 with ranking and highlighting; it does not know word forms, so prefix queries help.

Is moving from SQLite to PostgreSQL hard?

Not if the SQL is standard: pgloader moves the data, and the code changes little.

Online form

A database
for your scale

I choose the database for the scale of the project: SQLite for sites and small services, PostgreSQL or MySQL where the load and data grow — and move data when it is time. Tell me about the task — I answer within one working day.

Or write to [email protected]