Building a Persistent Key-Value Store for CLI Tools with Python sqlite3

CLI utilities rarely stay stateless for long. A Brisbane developer writing a daily nginx-log helper quickly wants it to remember yesterday's findings; a stock-fetching script caches responses between AEST cron runs; a deployment wrapper tracks which hosts already received the latest release. JSON files bring familiar headaches around atomicity, locking, and silent corruption when a process dies mid-write.

Python's bundled sqlite3 module ships with the standard library and exposes the full SQL surface of SQLite with no extra dependency. The database is a single file you can copy, back up, or inspect with the same sqlite3 binary available on macOS, Linux, and modern Windows. For a maintainer whose users run a mix of operating systems from Sydney to Perth, that portability matters more than any benchmark.

The rest of this piece walks through turning sqlite3 into a generic key-value layer rather than a relational database. The goal is a small, dependable store a CLI tool can lean on, fitting the same engineering conventions as utilities like DenyHosts and Scratchy already on this site.

Why JSON files and shelve fall short

The lowest-friction way to persist a Python dictionary is json.dump, and the lowest-friction path for arbitrary objects is shelve. Both work on the happy path, but neither survives contact with a tool people invoke hundreds of times a day. A kill -9 on the running process is enough to leave a JSON file half-written; the next read either crashes or returns a truncated object that the script treats as valid.

shelve builds on dbm and inherits its own troubles, including a file format that has shifted across Python versions and concurrent access errors that look mysterious at first glance. Anyone running an Australian tech shop with several CI runners in the same Sydney region has seen two jobs collide while refreshing the same cached lookup. SQLite relies on a writer lock and a transactional log, so a crash mid-update rolls back to the previous good state.

Designing a portable schema for arbitrary values

SQLite is loosely typed by default, a quiet gift when a key-value table needs to store strings, integers, floats, and small blobs side by side. A minimal schema is CREATE TABLE kv (key TEXT PRIMARY KEY, value BLOB, type TEXT, updated_at INTEGER), where type records whether the blob should be unpickled back into an int, a list, or a dict. Keeping the original Python type alongside the serialised value avoids awkward ambiguity when inferring meaning from a JSON-shaped string.

Namespaces tidy things up. A thin wrapper around the connection exposes set(namespace, key, value), get(namespace, key), and delete(namespace, key), hiding the SQL from the rest of the application. Configuration values, cache entries, and user-saved notes share the database without key prefixes, and each namespace evolves independently. The separation also lines up with the Australian Privacy Principles guidance to keep different categories of personal data logically distinct.

Handling concurrency without losing sleep

A CLI tool is not a long-running server, but it is also not a single-process program. Users run it from terminals, cron, launchd on macOS, and systemd timers on Linux boxes in a Melbourne data centre, and several invocations eventually race the same database file. Setting a sensible journal mode and a busy timeout turns most of those races into quiet waits rather than OperationalError exceptions.

PRAGMA journal_mode=WAL is the biggest win for concurrent readers and a single writer, allowing the tool to keep reading from the previous snapshot while the writer commits. Pairing WAL with PRAGMA busy_timeout=5000 retries the lock for up to five seconds, which absorbs bursts of concurrent cron jobs without frustrating the user. Inside one process, sqlite3 opens connections with check_same_thread=True, so the cleanest pattern is one connection per thread, or a thread-local pool behind the wrapper API.

Encryption and the local privacy bar

Storing secrets in plaintext inside a SQLite file is a familiar antipattern, and one the Australian Cyber Security Centre has been increasingly vocal about in its Essential Eight guidance. A CLI tool that persists API keys, OAuth tokens, or internal hostnames should encrypt the value column before it hits disk, ideally deriving the key from a passphrase supplied on first run, much like pass or git-crypt.

Python's cryptography package slots in cleanly. Derive a 32-byte key with Argon2id or scrypt from the passphrase plus a per-installation salt, then use AES-GCM to seal and unseal each blob. Salt, nonce, and iteration count live in a small secrets_meta table so they can be rotated without rewriting every row. For tools targeting Australian public-sector clients, the Notifiable Data Breaches scheme means key material on a developer's laptop is potentially reportable if it leaks, which makes encrypt-by-default a defensible choice.

Migrations and backward compatibility

Every persistent store accumulates schema debt. A column is added, an index is dropped, and somewhere along the line an older release of the tool has to keep working with a newer database. Treating the schema the way a web framework treats its database migrations pays off quickly.

A practical approach is a schema_version row in a small meta table alongside numbered migration scripts in the package source. On startup the tool checks the current version, runs the missing migrations in a single transaction, and continues. sqlite3 supports both ATTACH DATABASE and CREATE TABLE IF NOT EXISTS, so a single shared connection covers most schemas without heavier machinery. The downside is forgetting the downgrade path: a developer in Adelaide shipping a tool installed by hundreds of teams cannot assume every user runs the latest version forever, especially in regulated environments with change-advisory boards, so additive migrations keep the long tail healthy.

Shipping a SQLite-backed CLI tool

The final piece is distribution. Python wheels can declare pysqlite3-binary or rely on the bundled module, but the database file itself is a separate question: should it live inside the package, in the user's home directory, or somewhere configurable? Embedding an empty initial database makes sense for read-only reference data, but for state a generated file under $XDG_STATE_HOME respects each Unix user's conventions.

For tools that package data alongside code, shipping the SQLite file inside a wheel with a recorded SHA-256 lets users verify integrity against PyPI mirrors, including the Australian mirror operated by AARNet that many local universities point their caches at. The same checksum can be checked in a one-line assert at startup so the tool refuses to run against a tampered database.