If you need to scale writes.
If you need to scale writes.
I'd argue writes scale better wtih sqlite than postgresql.
Also I often need to read-after-write during the same request, using transactions.
And rails apps are often CRUDy.
Each batch item can be a combination of read/write/update that happen in sequence and therefore can give you the same semantics as a traditional transaction/rollback. eg:
- read -> Does the account have enough funds?
- write -> transfer 100$ from this user to another account
This is also much simpler to write than in other databases as you don't have to worry about n+1.
To go with your example, take something like
1) add $100 to this user's account 2) add $100 to the service fees account 3) deduct $101 from the other user's account to cover these
Must all happen or none.
You do have to write your batch items so that they check their own constraints though. I.e check the accounts have funds etc.
I need to bne able to rollback just the queries of a single request.
Ah you're doing request response? sqlite/single writer fits much better with a CQRS still approach, so in my experience you move away from request/response to something push based. That being said even in a request/response model wrapping a request in a transaction is not a great idea. The minute you have anything that takes some time (slow third party) that transaction is going to sit open and cause trouble.