Comment by MyOutfitIsVague

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.

pdimitar 2 months ago

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.

Reply View 1 reply

RedShift1 2 months ago

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.

Reply View | 0 replies

justsomehnguy 2 months ago

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.

MyOutfitIsVague 2 months ago

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 View | 0 replies