A Background Job Queue in One SQLite File Covers What Most Apps Deploy Redis For
A signup handler has to send a welcome email, and sending it inline puts a third-party API call inside your request. So you move it into a background job, and the usual next step is Redis, a client library, a worker process, a dashboard, and a note in the deploy docs about what happens to queued jobs when Redis restarts without persistence. If the app runs on one machine, you’ve added a second stateful service to look after a table’s worth of data.
That table is the queue. One SQLite file in WAL mode gives you what SQS-style queues give you: leases that expire when a worker dies, retries with backoff, delayed jobs, priorities and a dead-letter state. The version worth building is a small library that does exactly that, refuses the rest, and ships a CLI for looking inside the file. It’s the same bet as the one binary, one file pattern: the constraint is the product.
The Mainstream Already Moved
None of this is new. Rails 8.0 (November 2024) made Solid Queue, a database-backed job queue, the default. Oban for Elixir, River for Go and pg-boss for Node run queues on Postgres with FOR UPDATE SKIP LOCKED, so many workers can pull jobs without blocking each other. On SQLite, goqite does it for Go, and Huey, the Python task queue, can keep its jobs in SQLite.
The gap is narrower than “nobody does this”. The Postgres queues need Postgres, which is fine if you run it and pointless if you don’t, and each SQLite option is tied to one language. A newcomer has to bring what’s usually an afterthought: a crash-simulation test suite you can read, and an inspection CLI that answers “why hasn’t this job run?” without a SQL shell. The queue is an afternoon. Trust takes longer.
A Table, One UPDATE and a Deadline
Postgres queues need SKIP LOCKED because many writers compete for row locks. SQLite admits one writer at a time, so a single UPDATE is already atomic: two workers can’t claim the same row because they can’t run the statement at the same moment.
The model is what SQS calls a visibility timeout. A leased job has no special state; its run_at has simply been pushed into the future. If the worker finishes, it deletes the row. If the worker dies, nothing happens until the deadline passes, and then the job is ready again. No reaper process, no second table.
CREATE TABLE jobs (
id INTEGER PRIMARY KEY,
queue TEXT NOT NULL,
payload BLOB NOT NULL,
priority INTEGER NOT NULL DEFAULT 0,
run_at INTEGER NOT NULL, -- unix ms; invisible until then
attempts INTEGER NOT NULL DEFAULT 0,
max_attempts INTEGER NOT NULL DEFAULT 5,
lease_owner TEXT, -- random token, set on every claim
last_error TEXT,
state TEXT NOT NULL DEFAULT 'ready', -- ready | dead
idem_key TEXT
);
CREATE UNIQUE INDEX jobs_idem ON jobs(queue, idem_key) WHERE idem_key IS NOT NULL;
CREATE INDEX jobs_ready ON jobs(queue, priority DESC, run_at) WHERE state = 'ready';
-- claim: lease the next ready job for :lease_ms
UPDATE jobs
SET attempts = attempts + 1, lease_owner = :token, run_at = :now + :lease_ms
WHERE id = (
SELECT id FROM jobs
WHERE queue = :queue AND state = 'ready'
AND run_at <= :now AND attempts < max_attempts
ORDER BY priority DESC, run_at, id
LIMIT 1
)
RETURNING id, payload, attempts;
attempts goes up when a job is claimed, because the jobs that kill a worker (an out-of-memory kill, a crash in a native library) never get to report a failure, and counting at claim is the only thing that eventually stops them. The attempts < max_attempts check keeps an exhausted job from running again, and a one-line sweep in the same call buries those rows as dead.
lease_owner is a random token per claim, and done, fail and heartbeat all end in WHERE id = ? AND lease_owner = ?. A worker whose lease lapsed and whose job went elsewhere finds its update touches zero rows. It’s the idea behind the fencing tokens in the embedded state engine post, except the thing being protected is the row itself, so plain equality is enough.
fail stores the error, clears the owner, and either reschedules with exponential backoff and jitter or marks the job dead when attempts run out. put uses ON CONFLICT DO NOTHING on the idempotency key, which turns a double submit into a no-op. It should also accept the caller’s own connection: if the app’s data lives in the same file, enqueueing inside the same transaction means the job exists if and only if the signup does, and you never write an outbox table.
At-Least-Once Is the Contract
A worker can finish the side effect and die before done runs, the lease expires, and the job runs again. Every queue with leases lives with this; exactly-once is a promise nobody keeps across a crash. The library can hand each handler a stable job id and attempt number so it can pass an idempotency key to whatever it calls, a payment API or a mail provider. A handler that can’t be made safe to repeat needs a different design, and no queue will fix that.
Lease length is the next knob. Too short, and a slow job goes to a second worker while the first is still running. Too long, and a crashed worker’s job sits invisible for minutes. Use a short lease and have long handlers heartbeat on a timer, with a hard ceiling on total runtime so a wedged handler can’t renew forever. In Python there’s an extra trap: a handler that blocks the event loop also stalls the heartbeat coroutine, so the lease lapses and the job runs twice. The event loop and thread pool post explains why.
One writer is the next limit. A claim or a done is a tiny write, so one writer carries more than people expect, but measure on your own disk. Set busy_timeout so a blocked writer waits instead of failing, and never hold a transaction open while a handler runs: claim in one transaction, run the job with nothing open, report in another. Open multi-statement writes with BEGIN IMMEDIATE, since a deferred transaction that reads first and then writes can fail with SQLITE_BUSY even when a timeout is set.
Durability is a setting, and the default matters. In WAL mode, synchronous=NORMAL survives an application crash, but a power loss can roll back the most recent commits; synchronous=FULL syncs on every commit. Losing the last done means one repeated run, which idempotent handlers absorb. Losing the last put means a job the caller was told was accepted has vanished. So default to FULL and let people opt down when jobs can be regenerated.
Keep the file on a local disk, because SQLite’s locking is unreliable on network filesystems and two machines pointed at one file can both believe they hold the write lock. WAL mode also keeps -wal and -shm sidecar files, so a live backup should use SQLite’s backup API, never cp, and Litestream-style replication to object storage covers the day the disk dies. Last, run_at is wall-clock time, so a clock that steps forward expires leases early. Every worker shares one clock, which beats a distributed queue, but NTP steps and laptop suspends still happen, so take now as a parameter everywhere.
The API is small enough to attack properly. Model the queue as a plain in-memory structure, generate random sequences of put, claim, done, fail, heartbeat, crash and clock advance, and run them against both, checking after every step that no job is lost, no job has two live leases, and attempts never pass the maximum. Assert that the claim’s EXPLAIN QUERY PLAN shows an index search, because a scanning claim passes every small test and degrades as the table grows. Then kill -9 a real worker between claim and done, and run the suite on a test VFS that discards unsynced writes to imitate power loss under each synchronous setting. The target is your use of SQLite’s guarantees. SQLite tests itself.
What Version 0.1 Refuses
Version 0.1 is a library for one language, whichever one you’d deploy the app in: named queues, delayed jobs, priorities, leases with heartbeats, retries, dead letters, idempotency keys, and a CLI. The CLI is what people will use first: counts per queue, one job’s attempts, error and lease, retry and purge for dead jobs, and a live tail.
The refusals matter as much. No pub/sub fan-out: many readers that each keep their own position is a log, which is the job of an embedded event log. No cross-machine distribution. No multi-step processes with waits and branches, which belong in a layer on top, such as an embedded workflow engine that claims its due steps from this queue. No cron. Each refusal keeps the schema at one table and the README at one page.
Add the broker when the table stops being enough.