Hacker News Reader

Comment by MyOutfitIsVague | Hacker News Reader

For my extremely specialized case, I use a SQLite database as a message queue. It absolutely wouldn't scale, but it doesn't need to. It works extremely well for what I need it to do.

View on HN

Have you written up about it? I'd love to read it if so. Thought of using SQLite several times like this but never mustered the courage to try.

I use SQLite as an offline buffer for telemetry data, basically one thread does INSERT of the payloads and another thread does just SELECT and then DELETE when it has successfully transmitted the payload.

I would join in asking for more details.

I have an idea of a project where even MySql/Maria is too much of admin burden.

There's very little to it, really, just a messages table with a id INTEGER PRIMARY KEY AUTOINCREMENT column (autoincrement to prevent id reuse, which is otherwise legal), a payload TEXT NOT NULL column (which is usually JSON encoded), and, in my case, a TEXT json annotations column, with some computed indexes. A publisher just pushes rows in, and a subscriber takes an exclusive lock to grab the first row matching the annotations that it cares about (I use DELETE RETURNING, but you can make it work however you need).

You can use `PRAGMA data_version` on a dedicated thread to watch for changes and notify other waiters via a condition variable. It's not the nicest solution, because it's just a loop around a query, but it gets the job done.

A req-rep pattern can be done by doing a `INSERT ... RETURNING id` and having the the other side re-push into the same or a different message queue with an annotation referring to that id. Alternatively, you could have a table with a req, rep, and status column to coordinate it all.

It's far from everything you'd need from a complete, robust message broker, but for small single or multi-process message queue with a max of a few dozen readers and writers, it gets the job done nicely. In a single process, you can even replace the data_version loop thread with `sqlite3_commit_hook` on writers to notify readers that something has changed via the condition_variable.

Reply on news.ycombinator.com