rqlite is a lightweight, open-source, fault-tolerant relational database built on SQLite and the Raft consensus protocol.
With most databases the answer is an interactive transaction. You open a connection, issue BEGIN, run a statement, look at the result, and decide what to do next. rqlite doesn’t work that way.
Each HTTP request stands alone, and there is no session carrying state from one request to the next. Instead you send all your statements in a single request, set the transaction parameter, and rqlite applies them together or not at all.
That is simple enough when the statements are independent. It’s less obvious when one statement depends on the result of another, and your application can’t see the intermediate results.
So I’ve added a new page to the rqlite documentation: Multi-statement patterns. It collects the common ways of solving this problem in one place, with examples you can run using curl. It covers:
- Replacing a read followed by a write with a single SQL statement.
- Getting generated values back with RETURNING.
- Chaining inserts with last_insert_rowid().
- Using a temporary table to hold a value needed by several statements.
- Using constraints to abort a request, in place of an IF.
- Making requests safe to retry.
- Claiming a row, for work queues and locks.
- Optimistic concurrency, for when a single request really isn’t enough.
None of this is new functionality. It is all standard SQLite, combined with the way rqlite already handles transactions. But until now it hasn’t been written down in one place, and some of the details are easy to get wrong. The page calls those out too.
If you have a pattern you rely on which isn’t on the page, I’d like to hear on the rqlite Slack, or open an issue on GitHub.