Local State and SQLite in Python CLIs

Most CLIs start stateless and then, one feature at a time, begin to remember things. A sync tool records what it has already uploaded. An API client caches responses so it does not hit rate limits. A deployment tool keeps a history of what was deployed where. A task runner remembers which inputs it has processed. The first version of each of these is usually a JSON file, and for a handful of values that is fine. But JSON files have no queries, no indexes, no partial updates and no concurrency control: every change rewrites the whole file, two concurrent runs need a lock, and "show me the last 20 runs that failed" means loading everything into memory.

SQLite solves all of that and ships with Python. One file, transactional writes, safe concurrent access from several processes, SQL for queries, and no server to install. This topic covers using it well from a CLI: where the database belongs, the connection settings that matter, transactions, evolving the schema between releases, caching and history as two common uses, and testing. It sits in the CLI Runtime & Systems Integration section next to filesystem paths and atomic writes, which covers the file-level foundations.

TL;DR

• Reach for SQLite when state is queried, grows, or is written by concurrent runs. Keep JSON or TOML for small, human-edited configuration.
• Put the database in the user's state or cache directory, never in the current directory or next to the code.
• Open connections with a few pragmas: WAL journal mode, a busy timeout, foreign keys on. Close them explicitly.
• Wrap each logical change in one transaction and keep transactions short.
• Version the schema with PRAGMA user_version and apply numbered migrations at startup, inside a transaction.
• Test against a real SQLite file in tmp_path, not mocks; it is fast enough and catches real locking and migration bugs.

When SQLite is the right store

Not all state belongs in a database. The deciding questions are who edits it, how big it gets, and whether more than one process writes it.

Configuration that users edit by hand belongs in TOML or YAML, read as described in reading TOML config with tomllib. A database is the wrong place for anything a person should open in an editor.

A few machine-written values — a last-run timestamp, a cursor — fit comfortably in a small JSON file written atomically. The overhead of a schema is not worth it.

Anything that grows or is queried — history, inventories, processed-item markers, caches with expiry, queues — belongs in SQLite. The point where a JSON file becomes painful arrives earlier than people expect: as soon as you want "only the entries from last week", or two cron jobs touch the same file.

Where the database lives

A CLI's database is per-user application data. Use platformdirs to find the right directory on each platform, and choose between two of them deliberately:

• State directory (user_state_path) for data the user would be upset to lose: history, records of what has been synced, queued work.
• Cache directory (user_cache_path) for data that can be rebuilt: HTTP response caches, downloaded indexes. Users and cleanup tools may delete it at any time, and your tool must cope.

Never put the database in the current working directory — it litters every project — and never inside the installed package, which may be read-only or replaced on upgrade. Let an environment variable override the path (MYTOOL_DB=/tmp/test.db); tests and power users both rely on it.

SQLite in WAL mode keeps two companion files next to the database, -wal and -shm. They are normal and must stay with the main file; a tool that copies or backs up its database should use SQLite's backup API (conn.backup()) rather than copying files mid-write.

Opening a connection properly

Python's sqlite3 defaults are conservative and date from a time when SQLite was used differently. A CLI should set a few things on every connection:

# src/mytool/db.py
from __future__ import annotations

import os
import sqlite3
from pathlib import Path

from platformdirs import user_state_path


def db_path() -> Path:
    override = os.environ.get("MYTOOL_DB")
    return Path(override) if override else user_state_path("mytool") / "mytool.db"


def connect(path: Path | None = None) -> sqlite3.Connection:
    path = path or db_path()
    path.parent.mkdir(parents=True, exist_ok=True)
    conn = sqlite3.connect(path, timeout=10.0)       # wait up to 10 s for a lock
    conn.row_factory = sqlite3.Row                    # rows behave like dicts
    conn.execute("PRAGMA journal_mode = WAL")         # readers do not block the writer
    conn.execute("PRAGMA foreign_keys = ON")          # off by default in SQLite
    conn.execute("PRAGMA synchronous = NORMAL")       # safe with WAL, much faster
    return conn

Each line earns its place. The timeout makes a connection wait for a lock instead of failing immediately with database is locked when another run is writing. WAL mode lets readers and one writer proceed at the same time, which is what you want when a long history query overlaps a running sync. Foreign keys are off by default for backward compatibility and should be on for any schema that uses them. synchronous = NORMAL in WAL mode keeps the database consistent after a crash while avoiding an fsync on every commit — a large speed-up for tools that write many small transactions.

sqlite3.Row makes results addressable by column name (row["started_at"]), which keeps query code readable and lets you convert rows to dictionaries for JSON output with dict(row).

Transactions, and the with conn: trap

Every change that must happen completely or not at all belongs in one transaction. The sqlite3 connection is a context manager that commits on success and rolls back on an exception — but it does not close the connection, which surprises almost everyone the first time:

from contextlib import closing

with closing(connect()) as conn:          # closes the connection at the end
    with conn:                            # one transaction: commit or roll back
        conn.execute("INSERT INTO runs (command, started_at) VALUES (?, ?)", ("sync", now))
        conn.execute("UPDATE items SET synced = 1 WHERE id IN (SELECT id FROM pending)")

Keep transactions short. A write transaction holds SQLite's single write lock until it commits; if a command opens one and then spends a minute downloading files, every other run waits — or times out. Do the slow work first, then write the results in one quick transaction. Always use ? placeholders rather than formatting values into SQL; the reasons are the same as for avoiding shell injection.

On Python 3.12 and later, sqlite3.connect(..., autocommit=False) gives PEP 249-compliant transaction handling, where a transaction is always open and commit() is explicit. Either style works; pick one and use it consistently across the codebase.

Evolving the schema between releases

The schema of a CLI's database changes as the tool grows, and users upgrade from any old version to the newest one. You cannot ask them to delete their history. The standard approach uses SQLite's built-in user_version field — an integer stored in the database header — and a list of numbered migrations applied in order at startup.

MIGRATIONS = [
    "CREATE TABLE runs (id INTEGER PRIMARY KEY, command TEXT NOT NULL, started_at TEXT NOT NULL)",
    "ALTER TABLE runs ADD COLUMN exit_code INTEGER",
    "CREATE INDEX runs_started ON runs (started_at)",
]


def migrate(conn: sqlite3.Connection) -> int:
    conn.isolation_level = None                    # explicit transactions, DDL included
    current = conn.execute("PRAGMA user_version").fetchone()[0]
    for number, sql in enumerate(MIGRATIONS[current:], start=current + 1):
        conn.execute("BEGIN IMMEDIATE")
        try:
            conn.execute(sql)
            conn.execute(f"PRAGMA user_version = {number}")
            conn.execute("COMMIT")
        except BaseException:
            conn.execute("ROLLBACK")
            raise
    conn.isolation_level = ""                      # back to the default for normal use
    return len(MIGRATIONS)

Because both the change and the version bump happen in one explicit transaction, a crash mid-migration leaves the database at the previous version, ready to retry. The explicit BEGIN matters: in its default mode Python's sqlite3 does not open a transaction before CREATE or ALTER statements, so with conn: alone would not make a schema change atomic. Migrating a CLI's SQLite schema extends this with table rebuilds, downgrade protection and backups before risky changes.

Two common uses: caches and history

Two patterns account for most CLI databases, and both have their own guides.

An HTTP response cache stores API responses keyed by URL, with a fetched-at time and an expiry. It turns repeated mytool show calls into instant local reads, keeps the tool usable offline, and reduces pressure on rate limits. Expiry, ETag revalidation and size limits are what separate a cache from a slowly growing pile of stale data — see caching HTTP responses on disk in a CLI.

A run history records what the tool did: which command, when, with what result, and a few key details. It answers support questions ("what did the deploy on Tuesday actually do?"), powers mytool history and mytool last, and gives users a way to export their own records. Recording and querying CLI run history builds both sides.

The general recipe for everything else is in storing CLI state in SQLite, which wraps the connection, migrations and queries in a small repository class that commands use without writing SQL themselves.

Concurrency between runs

SQLite allows many readers and one writer at a time. With WAL mode and a busy timeout, that is enough for almost every CLI: two runs that both write will take turns, each waiting up to the timeout for the other's transaction to finish. Problems only appear in three situations. Long write transactions block others — keep them short, as above. Network filesystems such as NFS and SMB do not implement the locking SQLite needs reliably; keep the database on local disk, which platformdirs paths are. And if a write genuinely must not run twice concurrently — two sync runs processing the same queue — a transaction is not enough on its own, because each run could read the same pending items before either writes. Claim work atomically instead:

with conn:
    claimed = conn.execute(
        "UPDATE jobs SET claimed_by = ? WHERE id = (SELECT id FROM jobs "
        "WHERE claimed_by IS NULL ORDER BY id LIMIT 1) RETURNING id, payload",
        (run_id,),
    ).fetchone()

UPDATE ... RETURNING (SQLite 3.35+, included with current Python builds) claims and reads a row in one statement, so two runs can never take the same job.

Backups, resets and damaged files

Once a tool keeps state, users will eventually need to back it up, move it to a new machine, or start over — and occasionally a disk problem or an interrupted copy will leave a file SQLite cannot read. A small db command group covers all of it and saves a lot of support time:

import sqlite3
from contextlib import closing
from pathlib import Path


def backup(src: Path, dest: Path) -> None:
    """Consistent copy, safe while other runs are using the database."""
    with closing(sqlite3.connect(src)) as source, closing(sqlite3.connect(dest)) as target:
        source.backup(target)


def check(path: Path) -> str:
    """Return 'ok' or SQLite's description of the damage."""
    try:
        with closing(sqlite3.connect(path)) as conn:
            return conn.execute("PRAGMA integrity_check").fetchone()[0]
    except sqlite3.DatabaseError as exc:          # "file is not a database"
        return str(exc)


def reset(path: Path) -> Path | None:
    """Move the database aside rather than deleting it; return where it went."""
    if not path.exists():
        return None
    aside = path.with_suffix(".db.bak")
    path.replace(aside)
    for extra in ("-wal", "-shm"):
        Path(f"{path}{extra}").unlink(missing_ok=True)
    return aside

Expose them as mytool db backup FILE, mytool db check and mytool db reset. The backup uses SQLite's online backup API, which produces a consistent snapshot even while another run is writing — unlike copying the file, which can capture a half-applied transaction. reset moves the file aside instead of deleting it, so a user who resets by mistake can recover. And when opening the database fails with sqlite3.DatabaseError during normal use, the error message should name the file and suggest mytool db check and mytool db reset, rather than surfacing a traceback; friendly error messages and tracebacks shows where that handler belongs.

For caches, the policy can be simpler: if the cache database is unreadable, delete it and continue. Nothing in a cache is worth an error message.

Testing database code

Use real SQLite in tests. It runs in-process, a fresh database file in tmp_path costs a millisecond or two, and it exercises the actual SQL, pragmas and migrations — which mocks never do.

import pytest

from mytool import db


@pytest.fixture
def conn(tmp_path, monkeypatch):
    monkeypatch.setenv("MYTOOL_DB", str(tmp_path / "test.db"))
    connection = db.connect()
    db.migrate(connection)
    yield connection
    connection.close()


def test_migrations_reach_latest_version(conn):
    assert conn.execute("PRAGMA user_version").fetchone()[0] == len(db.MIGRATIONS)

Add three kinds of tests beyond ordinary queries. A migration test builds a database at an old version with representative data, runs migrate() and checks the data survived. A concurrency test runs two processes that write at once and asserts nothing was lost, as in the multi-process test from file locking for concurrent CLI runs. And a corruption test points the tool at a file that is not a database and checks the error message tells the user what to do.

Common pitfalls

• Forgetting to close. with conn: manages transactions, not the connection. Use contextlib.closing or an explicit close().
• Formatting values into SQL. Always use ? placeholders. The only exception is identifiers and pragmas you control, such as the migration number.
• Long write transactions. Holding the write lock during network calls makes concurrent runs fail with database is locked.
• Storing the database in the working directory or inside the package.
• No schema version. Without user_version, the first schema change becomes a guessing game about what each user's database looks like.
• Treating the cache as precious. If the tool fails when the cache directory is deleted, the data was not a cache.

Key takeaways

• SQLite gives a CLI transactional, queryable, concurrency-safe state in one file with no extra dependency.
• Keep configuration in TOML, small values in JSON, and anything that grows or is queried in SQLite.
• Open connections with a timeout, WAL mode and foreign keys on, and close them explicitly.
• Version the schema with PRAGMA user_version and apply migrations transactionally at startup.
• Test with real database files, including migrations from old versions and concurrent writers.

Frequently asked questions

Related

• Up: CLI Runtime & Systems Integration
• Down: Storing CLI state in SQLite
• Down: Migrating a CLI's SQLite schema
• Down: Caching HTTP responses on disk in a CLI
• Down: Recording and querying CLI run history
• Sideways: Filesystem paths and atomic writes
• Sideways: Caching expensive work between CLI runs