Durable queues, streams, pub/sub, and a cron scheduler –...

Durable queues, streams, pub/sub, and a cron scheduler – All Inside Your SQLite File

Q: How can I use SQLite as a durable queue for background jobs?

By creating a messages table with a status column and using an atomic UPDATE … WHERE status='pending' LIMIT 1 RETURNING * statement to claim jobs. The ACID nature of SQLite guarantees the job isn’t lost if the process crashes.

Q: Is SQLite suitable for high‑throughput pub/sub streams?

For low‑to‑moderate traffic (up to a few thousand msgs/sec) SQLite’s append‑only log and WAL mode handle it well; beyond that a dedicated broker is advisable.

Q: Can I replace Zapier or n8n with a SQLite‑based scheduler?

Yes, for simple “trigger → action” flows you can store cron expressions and payloads in a table, poll it locally, and execute the payloads with a few lines of code—perfect for personal automation or edge devices.

Q: What are the security benefits of keeping automation logic inside a SQLite file?

No network‑exposed service means fewer attack vectors; data is encrypted at rest (using SQLCipher) and all workflow definitions stay on‑premise, meeting strict compliance requirements.

Q: How do I migrate an existing RabbitMQ queue to SQLite?

Export messages to CSV, import them into the messages table, and switch your consumer code to use the SQLite dequeue query. Because SQLite is file‑based, the transition can be done with zero downtime.

Over 70 % of developers say that managing external message‑broker services is the biggest bottleneck in their automation pipelines. What if you could replace those moving parts with one tiny, zero‑maintenance SQLite file that handles durable queues, event streams, pub/sub topics, and even cron‑style scheduling? Imagine building a Zapier‑like workflow that never leaves your laptop, never costs a cent, and survives power‑loss without a single line of extra infrastructure.

Why SQLite Is the Unsung Hero of Automation

SQLite is the quiet star behind countless apps, from Android phones to desktop editors. It’s embedded, zero‑config, and runs in a single shared file. In a world where you often need a separate broker for every tiny workflow, SQLite gives you ACID guarantees without the overhead. Benchmarks show that enqueue/dequeue operations can hit sub‑millisecond latency, outpacing many cloud brokers when traffic stays under a few thousand messages per second. When you combine that with the fact that a single file can be copied, encrypted, or version‑controlled, the case for using SQLite in automation pipelines becomes hard to ignore.

Building a Durable Queue Inside SQLite

**Schema design** ```sql CREATE TABLE IF NOT EXISTS queues ( id INTEGER PRIMARY KEY, name TEXT UNIQUE NOT NULL ); CREATE TABLE IF NOT EXISTS messages ( id INTEGER PRIMARY KEY AUTOINCREMENT, queue_id INTEGER NOT NULL REFERENCES queues(id), payload BLOB NOT NULL, status TEXT NOT NULL DEFAULT 'pending', visibility_ts INTEGER, created_at INTEGER DEFAULT (strftime('%s','now')), CONSTRAINT uq_msg UNIQUE(queue_id, id) ); ``` **Enqueue** – a simple insert is enough. **Dequeue** – use an atomic `UPDATE … WHERE … RETURNING *` to lock a message. ```sql UPDATE messages SET status = 'processing', visibility_ts = strftime('%s','now', '+30') WHERE id = ( SELECT id FROM messages WHERE queue_id = ? AND status = 'pending' ORDER BY id LIMIT 1 FOR UPDATE ) RETURNING *; ``` Because SQLite writes in a single transaction, you never lose a job if the process crashes mid‑dequeue. If a worker dies before finishing, the `visibility_ts` expires and the message becomes visible again, ensuring **at‑least‑once** delivery. **Failure handling** – a background thread or scheduled job can scan for messages where `visibility_ts < now()` and reset their status to `pending`. That way, stuck jobs are automatically retried.

Streams & Pub/Sub Made Simple with Triggers

A **stream** is just an append‑only table with an auto‑incrementing ID. ```sql CREATE TABLE IF NOT EXISTS events ( id INTEGER PRIMARY KEY AUTOINCREMENT, type TEXT NOT NULL, data BLOB NOT NULL, ts INTEGER DEFAULT (strftime('%s','now')) ); ``` Subscribers keep track of their **last seen offset** in a `subscriptions` table. ```sql CREATE TABLE IF NOT EXISTS subscriptions ( id INTEGER PRIMARY KEY, name TEXT UNIQUE NOT NULL, last_seen INTEGER DEFAULT 0 ); ``` When a new event is inserted, a trigger can push a notification to a local WebSocket or HTTP endpoint. ```sql CREATE TRIGGER IF NOT EXISTS notify_subscribers AFTER INSERT ON events BEGIN SELECT notify_websocket(id, type, data, ts) FROM inserted; END; ``` SQLite itself can’t push external network traffic, but you can hook into the `wal_hook` API in your application language to fire a lightweight HTTP request or publish to a local message bus. The beauty is that all state lives in the same file – you can ship the whole workflow with a single copy.

Embedding a Cron Scheduler in the Same File (Practical Walkthrough)

Below is a **Python** snippet that turns a SQLite file into a fully‑featured cron engine. ```python import sqlite3, time, requests from croniter import croniter from datetime import datetime DB = "automation.db" conn = sqlite3.connect(DB, isolation_level=None) # autocommit # 1️⃣ Initialise tables (run once) conn.executescript(""" CREATE TABLE IF NOT EXISTS cron_jobs ( id INTEGER PRIMARY KEY, cron TEXT NOT NULL, next_run INTEGER NOT NULL, url TEXT NOT NULL, payload TEXT ); """) def schedule_job(cron_expr, url, payload=None): next_ts = int(croniter(cron_expr, datetime.utcnow()).get_next()) conn.execute( "INSERT INTO cron_jobs (cron, next_run, url, payload) VALUES (?,?,?,?)", (cron_expr, next_ts, url, payload) ) def run_due_jobs(): now = int(time.time()) for row in conn.execute( "SELECT id, cron, url, payload FROM cron_jobs WHERE next_run <= ?", (now,) ): job_id, cron_expr, url, payload = row # ---- Simulated action (e.g., Zapier webhook) ---- requests.post(url, json=payload or {}) # ---- Reschedule ---- next_ts = int(croniter(cron_expr, datetime.utcnow()).get_next()) conn.execute("UPDATE cron_jobs SET next_run = ? WHERE id = ?", (next_ts, job_id)) # Example: schedule a test job (run once) schedule_job("*/1 * * * *", "https://httpbin.org/post", {"msg": "hello from SQLite!"}) # 2️⃣ Main loop – the embedded cron scheduler while True: run_due_jobs() time.sleep(30) # poll twice a minute ``` This tiny loop continuously polls for jobs whose `next_run` time has passed, executes a simple HTTP POST, and then updates the next run timestamp. Because everything lives in the same SQLite file, your cron jobs survive restarts, migrations, or even being copied to another machine.

Real‑World Impact: From n8n/Zapier Alternatives to Edge‑Ready Workflows

* **Cost reduction** – SaaS automation platforms can charge $20–$50 per month for a few thousand events. A single SQLite file costs nothing. * **Security & privacy** – No network‑exposed broker means fewer attack vectors. For GDPR‑heavy workflows, keeping data in‑process is a huge win. * **Portability** – Move the file across devices, containers, or even embed it in a mobile app. The same code you wrote on your laptop will run on a Raspberry Pi, a desktop, or a serverless function. In my experience, building a small Edge device that syncs sensor data to a cloud dashboard just required a few SQLite tables and a handful of Python scripts. No external broker, no extra services, and still reliable delivery.

Actionable Takeaways & Next Steps

1. **Add the queue schema** – start with the `queues` and `messages` tables. 2. **Implement enqueue/dequeue helpers** – write thin wrappers around the `UPDATE … RETURNING` pattern. 3. **Set up a stream table** – use an `events` table and a `subscriptions` table to track offsets. 4. **Define cron jobs** – create a `cron_jobs` table and run a poller loop. 5. **Test with a simple n8n‑style workflow** – chain an event trigger, a queue job, and a scheduled action. Tooling suggestions: `sqlite-utils` for quick CLI manipulation, `sqlx` if you're in Rust, `better-sqlite3` for Node, or the built‑in `sqlite3` module in Python. If you like to prototype, Honker.dev’s deep‑dive article offers a ready‑to‑run GitHub starter repo.

Frequently Asked Questions

How can I use SQLite as a durable queue for background jobs?

By creating a messages table with a status column and using an atomic UPDATE … WHERE status='pending' LIMIT 1 RETURNING * statement to claim jobs. The ACID nature of SQLite guarantees the job isn’t lost if the process crashes.

Is SQLite suitable for high‑throughput pub/sub streams?

For low‑to‑moderate traffic (up to a few thousand msgs/sec) SQLite’s append‑only log and WAL mode handle it well; beyond that a dedicated broker is advisable.

Can I replace Zapier or n8n with a SQLite‑based scheduler?

Yes, for simple “trigger → action” flows you can store cron expressions and payloads in a table, poll it locally, and execute the payloads with a few lines of code—perfect for personal automation or edge devices.

What are the security benefits of keeping automation logic inside a SQLite file?

No network‑exposed service means fewer attack vectors; data is encrypted at rest (using SQLCipher) and all workflow definitions stay on‑premise, meeting strict compliance requirements.

How do I migrate an existing RabbitMQ queue to SQLite?

Export messages to CSV, import them into the messages table, and switch your consumer code to use the SQLite dequeue query. Because SQLite is file‑based, the transition can be done with zero downtime.

Code & Crumbs

Search This Blog