Hi everyone. I was wondering how to correctly use a prepared statement inside a transaction with drizzle-orm. I obviously first looked at the documentation with no success then went to the github issues and found the issue #952 (https://github.com/drizzle-team/drizzle-orm/issues/952). I prepared an answer but eventually found that I was quite off topic and I came here.
1) In his code (issue 952), I don't see the point of using a prepared statement: he prepares a statement each time the function is executed then executes it right away. From what I understand / think, the prepared statement should be a global (with placeholders if needed), then executed in the function.
// version 1: with prepared statements insideasync function test1() { await db.transaction(async tx => { const p1 = tx.update()...prepare(); const p2 = tx.insert()...prepare(); await p1.execute(); await p2.execute(); });}
// version 1: with prepared statements insideasync function test1() { await db.transaction(async tx => { const p1 = tx.update()...prepare(); const p2 = tx.insert()...prepare(); await p1.execute(); await p2.execute(); });}
2) And here comes my issue: if the prepared statements are moved outside, they cannot be defined using "tx". But if they are defined using "db", does the transaction work? I will do some tests when I have time, but I would be interested if you know the answer. And I think this would good to clarify it in the documentation.
// version 2: with global prepared statementsconst p1 = db.update()...prepare();const p2 = db.insert()...prepare();async function test2() { await db.transaction(async tx => { p1.execute(); p2.execute(); });}
// version 2: with global prepared statementsconst p1 = db.update()...prepare();const p2 = db.insert()...prepare();async function test2() { await db.transaction(async tx => { p1.execute(); p2.execute(); });}