Sqlite: Packages need a safe way to compose transactions without nested transaction errors

[joeybright]

I'm writing some package code for the still unreleased Sqlite module, and am running into an issue with package design. I want to have some of the functions wrap multiple SQLite calls in a transaction. Which I can using the current API! However, the problem is SQLite will error if you run a transaction within another transaction. This is not ideal for writing code meant to be published and used by others.

I'm forced to choose between two not-so-great options:

1. Use transactions and document that errors might happen if the caller wraps them in another transaction.
2. Don't use transactions and, via documentation, highly encouraging users to wrap them in their own transaction.

Either option is not really a great experience for users.

Ideally, there would be some way for a given database to know it's in a transaction and just skip running the transaction SQL if it's already is. I'm not sure how this would be done, if it's even possible, or if it's at all a desirable bit of functionality. But I wanted to open the issue to point to the problem and see if anything could be done about it!

[robinheghan]

Nice find. We should try to come up with a solution here before releasing the API