lapis
lapis copied to clipboard
leafo
Transactions
Hi! I'm very new to Lua + Lapis too, but I didn't find anything about transactions.
I tested with this code below and worked:
db.query('START TRANSACTION')
db.query("INSERT INTO users (name, mail, pass) VALUES ('Foo', '[email protected]' , '123')")
db.query('COMMIT')
Is there a way to start, commit or rollback a transaction with Lapis db or with the Model class?
Thank you!
At the moment we don't have any special accommodation for transactions, you would need to write any necessary queries yourself.
Keep in mind the code you wrote above can leave the db connection in a bad state, for example if there's a runtime error between starting and committing the transaction, the db connection may be returned to the pool with a transaction open and could mess up all further queries that are sent through it.
At the moment we don't have any special accommodation for transactions, you would need to write any necessary queries yourself.
Keep in mind the code you wrote above can leave the db connection in a bad state, for example if there's a runtime error between starting and committing the transaction, the db connection may be returned to the pool with a transaction open and could mess up all further queries that are sent through it.
Even if I use a pcall?
At the moment we don't have any special accommodation for transactions, you would need to write any necessary queries yourself.
Keep in mind the code you wrote above can leave the db connection in a bad state, for example if there's a runtime error between starting and committing the transaction, the db connection may be returned to the pool with a transaction open and could mess up all further queries that are sent through it.
I think returning a db connection to the pool with an open transactions should be considered an illegal state, maybe there should be a way to configure some function that handles it.
At the moment we don't have any special accommodation for transactions, you would need to write any necessary queries yourself. Keep in mind the code you wrote above can leave the db connection in a bad state, for example if there's a runtime error between starting and committing the transaction, the db connection may be returned to the pool with a transaction open and could mess up all further queries that are sent through it.
Even if I use a
pcall?
I assume the db interface uses postgres wire protocol, in that case, I think you could make a wrapper that always closes the transaction with a commit or on error with a rollback or something.
The database connection has its own state independent of your application code, so it's a matter of either manually synchronizing the state between connection and your application (which can be error prone), or finding a way to query the state of the connection before returning to the pool. The issue with this is that then you're adding overhead at the end of each request.
Just adding some additional info here:
I highly recommend collecting all the queries you want to run atomically into an array, then concatenating them into a single string with ; and sending it in a single call to query. This will ensure that the database connection does not get put into a bad state because of a failing to end an open transaction.
There are no plans to add support for a transaction syntax for multiple calls to query in Lua at this time.