SQLite in production: what I learned running it for an LLM proxy

Stockyard stores everything in SQLite. Traces, costs, audit logs, cached responses, module configs, encrypted provider keys. One file, one database, running in production on Railway.

When I tell people this, the first reaction is usually skepticism. SQLite is for mobile apps and prototypes. It is not a real production database. You cannot handle concurrent writes. What about backups?

I have been running this in production for months now. Here is what I actually learned.

Why SQLite in the first place

The decision was not about SQLite being the best database. It was about SQLite being the best database for this workload at this scale.

Stockyard is a single-binary LLM proxy. The whole selling point is that you download one file, run it, and get a working proxy with tracing, caching, and guardrails. If the first thing the install script said was "now set up Postgres," the entire value proposition would collapse.

I needed a database that ships inside the binary. SQLite is the only credible option for that. It is not a compromise. It is the correct choice for an embedded application database.

WAL mode is the key

The biggest misconception about SQLite is that it cannot handle concurrent access. That is true in its default journal mode. In WAL (Write-Ahead Logging) mode, it handles concurrent reads and writes without readers blocking writers or writers blocking readers.

Stockyard enables WAL mode on startup. This is the single most important configuration decision. Without it, the proxy would lock the database on every write and block incoming reads. With it, the proxy handles concurrent requests without contention.

-- Stockyard runs these on startup
PRAGMA journal_mode=WAL;
PRAGMA busy_timeout=5000;
PRAGMA synchronous=NORMAL;
  

busy_timeout tells SQLite to wait up to 5 seconds before returning a "database locked" error instead of failing immediately. synchronous=NORMAL is a small durability trade-off that significantly improves write throughput. In WAL mode, NORMAL is safe against process crashes (you only lose data if the OS itself crashes without flushing).

Pure Go, no CGO

Stockyard uses modernc.org/sqlite, a pure-Go SQLite implementation. This means no C compiler in the build chain, no CGO cross-compilation headaches, and a statically linked binary that runs anywhere a Go binary runs.

The pure-Go driver is slower than the C-based mattn/go-sqlite3. I have not measured the exact difference for Stockyard's workload, but it has not mattered. The database is not the bottleneck. The upstream LLM provider is. A typical API call takes 1-30 seconds waiting for the model to respond. The SQLite read/write for tracing that request takes single-digit milliseconds.

What the database actually stores

This is everything Stockyard puts in one SQLite file:

-- Request traces (model, tokens, cost, latency)
-- Cost records (per-request, per-model, per-provider)
-- Audit ledger entries (hash-chained, tamper-evident)
-- Cached responses (exact match and semantic)
-- Module configurations (76 modules, runtime-toggleable)
-- Provider API keys (AES-256-GCM encrypted)
-- Model alias mappings
-- Product state for all 150 tools
-- Rate limiter counters
-- Circuit breaker state
  

Money values are stored as integer cents to avoid floating-point arithmetic. Provider keys are encrypted before they touch disk.

Backups

This is the part that surprises people the most. Backing up the entire platform is:

cp /data/stockyard.db /backups/stockyard-$(date +%Y%m%d).db
  

That is it. One file. You can copy it while the server is running because WAL mode handles concurrent access safely for readers. For stronger consistency guarantees, SQLite's backup API or a tool like Litestream can provide continuous replication. In practice, file copies have worked reliably for Stockyard's workload, but it is worth knowing the options.

Restoring is the reverse:

cp /backups/stockyard-20260328.db /data/stockyard.db
  

No pg_dump. No managed snapshots. No point-in-time recovery configuration. No connection string to remember. The file is the database.

Schema migrations

Stockyard runs migrations automatically on startup. The binary checks the current schema version and applies any new migrations in sequence. This means upgrading the database is just upgrading the binary. There is no separate migration step, no version coordination between the app and the database, and no migration tool to install.

This works because there is only one process accessing the database. Migration conflicts are not possible when there is one writer.

What does not work

SQLite is a single-node database. There is no replication, no clustering, and no horizontal scaling. If you need to run Stockyard on multiple nodes sharing the same data, SQLite is not the right choice. This is a real limitation, and it is why the single-binary page explicitly says Stockyard is designed for solo developers and small teams on one to a few nodes.

Write throughput has an upper bound. SQLite serializes writes through a single writer. For Stockyard's workload this has not been a problem, because write volume is bounded by LLM API call volume, and those calls are slow. But if you were building a system that needed thousands of concurrent writes per second with sub-millisecond latency, SQLite would not be the right choice.

The database file grows over time. Traces and cost records accumulate. Stockyard does not yet have automatic data retention policies, so the file grows until you manually clean it. This is on the roadmap but not shipped yet.

Would I choose SQLite again

Yes. Without hesitation.

The operational simplicity is not a nice-to-have. It is the product. Stockyard's value proposition is that you install one binary and everything works. SQLite makes that possible. Postgres would make it impossible.

If Stockyard grows to the point where SQLite is genuinely the bottleneck, that will be a great problem to have. And the migration path is well-understood: move the data to Postgres when you need to, not before.