SQLite Is Enough for More Projects Than You Think

Most small projects don't need a database server. Yet many of us reach for one by default.

You start a side project, bot, or internal tool. Before the first table exists, you've provisioned Postgres: a development container, a managed production instance, a connection string to protect, and a backup plan to maintain. None of that is especially difficult. But it may be more infrastructure than the project needs.

SQLite takes a different approach. It is a database engine inside your application, with data stored in a file. There is no separate server to install, no database network hop, and no database service to administer. The SQLite project describes its competition not as client/server databases, but as fopen()—a simple way to store data in a file.

The rule of thumb: start with SQLite unless you can name a requirement that calls for a server. Common reasons include direct access from multiple machines, write contention that cannot be handled by queuing, or data and availability needs that call for a centralized database.

A quick-start path

For a small app running on one machine, start with five defaults:

1. Use a patched SQLite build. As of 17 October 2026, 3.54.1 is current; the WAL-reset fix is in 3.51.3 and later, with earlier backports available.

2. Use local, persistent storage. Avoid network filesystems and ephemeral disks.

3. Enable WAL once during initialization. The setting persists.

4. Configure every connection. Set foreign keys and a busy timeout; choose synchronous = NORMAL only if its durability trade-off is acceptable.

5. Keep writes short. Use BEGIN IMMEDIATE, keep slow network work outside transactions, and test a backup restore.

What SQLite actually is

SQLite is a database engine your program calls directly, not a server your program connects to. With a conventional database such as Postgres, your app opens a network connection, authenticates, sends SQL, and waits for results. With SQLite, your code calls library functions that read and write a local database file.

The trade-offs look like this:

A query is a function call rather than a network round trip, so many small queries can be less expensive than they would be against a remote server. SQLite's maintainers have reported about 200 SQL statements on a dynamic page of their website, historically running on a single virtual machine. Treat that as an example, not a benchmark for your workload.

SQLite supports tables, indexes, joins, and transactions. Python includes the sqlite3 module in its standard library, so there is usually no extra database package or service to install. The trade-off is centralized access and write concurrency—not relational features.

What it can handle—and the limit that matters

SQLite supports unlimited simultaneous readers but only one writer at a time per database file. For many apps, short write transactions mean writers can queue and take turns without users noticing.

Three limits are useful to understand.

Traffic. SQLite's maintainers say sites with fewer than about 100,000 hits per day should generally be fine, describing it as a conservative guideline, not a ceiling. That averages roughly 1.2 HTTP hits per second, but hits are not database operations: requests can perform very different amounts of work. Measure your workload and peak write rate rather than relying on the average.

Size. The technical maximum is 281 terabytes, assuming the filesystem can support a file that large. In practice, SQLite recommends considering a client/server database well before that—around the point where the database approaches a terabyte.

Writes. Writers take turns. If each transaction is short, this can work very well. If many writers need to make progress at once and cannot tolerate waiting, a client/server database will usually be a better fit. Benchmark with your real queries, transaction sizes, indexes, and storage rather than treating a theoretical transaction time as a performance promise.

What WAL changes

The default rollback-journal mode can make readers and writers block each other. Write-ahead logging (WAL) allows readers to continue while a writer commits, and lets the writer proceed while readers are active. There is still only one writer at a time.

WAL has important boundaries: all processes using the database must be on the same host, and the database should not live on a network filesystem. It also creates companion -wal and -shm files while connections are open, so keep those files with the database during backups and moves. Long-running readers can prevent checkpoints from finishing and cause the WAL file to grow.

One recent version detail is worth calling out. A rare WAL-reset bug could cause corruption when multiple connections wrote or checkpointed at just the wrong moment. SQLite says it affects versions 3.7.0 through 3.51.2 and is fixed in 3.51.3 and later, with backports in 3.50.7 and 3.44.6. The maintainers consider it unlikely in common use but recommend upgrading. An independent reproducer appeared in August 2026; read Phil Eaton's investigation alongside the official WAL documentation for details.

Check the SQLite library your application actually uses:

import sqlite3

print(sqlite3.sqlite_version)

That version can differ from the Python version because some Python builds bundle SQLite while others use a system library.

A practical production setup

A few settings cover many small applications. The important distinction is that WAL mode persists in the database file, while the other settings should be applied to each new connection.

NORMAL is a trade-off, not a universal recommendation: in WAL mode, a power loss or hard reset can roll back recent transactions. Choose a stricter durability setting if that risk is unacceptable.

Here is a small setup you can adapt. It enables WAL during initialization rather than trying to change journal mode every time a connection opens:

import sqlite3
from contextlib import contextmanager


def initialize_database(path: str) -> None:
    conn = sqlite3.connect(path, timeout=5)
    try:
        mode = conn.execute("PRAGMA journal_mode = WAL").fetchone()[0]
        if mode.lower() != "wal":
            raise RuntimeError(f"Could not enable WAL mode (got {mode!r})")

        conn.execute("""
            CREATE TABLE IF NOT EXISTS reminders (
                id        INTEGER PRIMARY KEY,
                user_id   INTEGER NOT NULL,
                remind_at INTEGER NOT NULL,  -- Unix seconds, UTC
                body      TEXT NOT NULL
            ) STRICT
        """)
        conn.commit()
    finally:
        conn.close()


def connect(path: str) -> sqlite3.Connection:
    # timeout=5 configures the busy timeout; isolation_level=None
    # lets us issue BEGIN and COMMIT explicitly.
    conn = sqlite3.connect(path, timeout=5, isolation_level=None)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA synchronous = NORMAL")
    conn.execute("PRAGMA foreign_keys = ON")
    return conn


@contextmanager
def write_tx(conn: sqlite3.Connection):
    conn.execute("BEGIN IMMEDIATE")  # reserve the writer slot up front
    try:
        yield conn
        conn.execute("COMMIT")
    except BaseException:
        conn.execute("ROLLBACK")
        raise


initialize_database("app.db")
conn = connect("app.db")
try:
    with write_tx(conn):
        conn.execute(
            "INSERT INTO reminders (user_id, remind_at, body) VALUES (?, ?, ?)",
            (42, 1760000000, "stand up"),
        )
finally:
    conn.close()

Three habits matter as much as the settings:

• Keep transactions short. Do slow work, such as network requests, outside the transaction. Long-running reads can also stop WAL checkpoints from finishing.

• Take the write lock first. BEGIN IMMEDIATE claims the writer slot at the start, instead of discovering halfway through a write that another transaction got there first.

• Keep the database files together on local storage. In WAL mode, separating the database from its -wal file can lose committed transactions or corrupt the database. Never place the file on a network share.

More built in than you might expect

Before adding another service, check whether SQLite already has the feature you need.

Strict typing. SQLite's flexible typing can be convenient, but it can also allow surprising values in columns. Since version 3.37.0, STRICT tables enforce lossless type conversion. Supported declared types are INT, INTEGER, REAL, TEXT, BLOB, and ANY. See the STRICT Tables documentation.

CREATE TABLE reminders (
    id        INTEGER PRIMARY KEY,
    user_id   INTEGER NOT NULL,
    remind_at INTEGER NOT NULL,
    body      TEXT NOT NULL,
    payload   TEXT
) STRICT;

JSON. JSON functions and the -> / ->> operators are built in by default from SQLite 3.38.0, although a custom build can omit them. Store JSON as text when appropriate and query selected fields without adding a separate search service:

SELECT id, payload ->> '$.channel' AS channel
FROM reminders
WHERE payload ->> '$.priority' = 'high';

Full-text search. The FTS5 module supports ranked search and highlighted snippets without a separate search server. Check that your specific SQLite build includes FTS5 before relying on it:

CREATE VIRTUAL TABLE notes USING fts5(title, body);

SELECT title, highlight(notes, 1, '<b>', '</b>')
FROM notes
WHERE notes MATCH 'sqlite'
ORDER BY rank;

Upserts and returning rows. SQLite can insert-or-update a record and return the resulting value in a single statement:

CREATE TABLE counters (
    name TEXT PRIMARY KEY,
    n INTEGER NOT NULL
) STRICT;

INSERT INTO counters (name, n) VALUES ('sent', 1)
ON CONFLICT (name) DO UPDATE SET n = n + 1
RETURNING n;

Window functions and generated columns are available too. Check the documentation before assuming a feature you know from Postgres requires a second system.

Backups, migrations, and deploys

A file-based database simplifies operations, but it does not remove the need for them.

Backups

Do not copy a live database file with cp. In WAL mode, recent commits may still be in the -wal file. A copy that separates the database from that file can lose committed transactions. Use a SQLite-aware method instead:

A snapshot with Python is straightforward:

src = connect("app.db")
try:
    dst = sqlite3.connect("backup.db")
    try:
        src.backup(dst)
    finally:
        dst.close()
finally:
    src.close()

Replication is useful if the disk dies, but it is not the same as instant failover; an abrupt loss can cost you the latest changes. Whatever backup method you choose, test a restore before you need one.

Migrations

Use PRAGMA user_version to record a schema version in the database file. Keep migrations in order, append new ones instead of editing old entries, and run each migration in a transaction so failures can roll back cleanly.

MIGRATIONS = [
    "CREATE TABLE reminders (id INTEGER PRIMARY KEY, body TEXT NOT NULL) STRICT",
    "ALTER TABLE reminders ADD COLUMN remind_at INTEGER",
]


def migrate(conn: sqlite3.Connection) -> None:
    (version,) = conn.execute("PRAGMA user_version").fetchone()
    for n, sql in enumerate(MIGRATIONS[version:], start=version + 1):
        with write_tx(conn):
            conn.execute(sql)
            conn.execute(f"PRAGMA user_version = {n}")

Deploying

In WAL mode, processes accessing the same database must run on the same host. Several worker processes on one machine can share the database, but two application servers on different machines should not share one SQLite file over a network filesystem.

Use a persistent local volume on platforms with ephemeral disks, or the database may disappear on redeploy. An in-memory database (:memory:) is useful for isolated tests. If you need to keep serving traffic through the loss of a whole machine, remember that backups and replication provide recovery, not automatic failover.

When SQLite stops being enough

Use these questions as a practical decision guide. A “yes” in any of the first three rows is a strong signal to consider a client/server database.

SQLite can serve busy websites and hold large databases. The question is whether its one-writer, one-host, one-file model fits your requirements.

Moving later is manageable if you keep SQL portable and isolate data access behind a module or repository layer. That is usually a better trade than paying the operational cost of a server before you know you need one.

Production checklist

Before relying on SQLite in production, confirm that:

• The runtime SQLite library includes the WAL-reset fix: version 3.51.3 or newer, or a patched backport. As of 17 October 2026, the latest release is 3.54.1.

• WAL is enabled and all database files remain together on local, persistent storage.

• Each connection sets synchronous and foreign_keys deliberately, with a suitable busy timeout.

• Write transactions are short and start with BEGIN IMMEDIATE when appropriate.

• Tables use STRICT where suitable for the schema.

• Backups run on a schedule or are replicated, and at least one restore has been tested.

• Migrations are tracked and repeatable.

• You can name the requirement that would trigger a move to a server: multiple machines, write contention, data size, or availability.

SQLite is not a smaller Postgres: it is a different design, and the missing server is often the advantage. Measure the real limits—one writer at a time, one host for WAL, and a single database file—against your workload. Thoughtful settings, short transactions, persistent storage, tested backups, and version updates turn a convenient file into a dependable database.

Start with the file. Move to a server when a concrete requirement—not habit—says it is time.

Frequently Asked Questions

When should I choose SQLite over a client/server database like Postgres?

Start with SQLite unless you need direct access from multiple machines, cannot tolerate write contention, or have data size/availability requirements that demand a centralized server.

What are the default settings recommended for a small SQLite production setup?

Enable WAL (journal_mode = WAL) once during initialization, set synchronous = NORMAL per connection, turn foreign_keys = ON per connection, and configure a busy timeout (e.g., timeout = 5 seconds).

How does Write-Ahead Logging (WAL) affect concurrency in SQLite?

WAL allows readers to continue while a writer commits and lets the writer proceed while readers are active, but only one writer can hold the lock at a time and all processes must be on the same host with the database on local storage.

What are the practical limits of SQLite for traffic, database size, and write concurrency?

SQLite handles unlimited simultaneous readers but only one writer at a time; sites with fewer than ~100,000 HTTP hits per day are generally fine, and while the technical max size is 281 TB, SQLite recommends moving to a client/server database as the file approaches about 1 TB.

How should I back up a SQLite database that uses WAL mode?

Use a SQLite‑aware method such as Connection.backup() or VACUUM INTO to capture a consistent snapshot; copying the live file with cp is unsafe because recent commits may reside in the -wal file.

What is the recommended way to manage schema migrations in SQLite?

Store a schema version with PRAGMA user_version, keep migrations in order, append new migrations rather than editing old ones, and run each migration inside a write transaction so failures can be rolled back.

What does the STRICT table option do in SQLite?

STRICT tables enforce lossless type conversion for declared column types (INT, INTEGER, REAL, TEXT, BLOB, ANY), preventing surprising values and providing stricter typing than the default flexible mode.