Drizzle Team•15mo ago

Transaction + prepared statement

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 inside
async 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 inside
async 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 statements
const 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 statements
const p1 = db.update()...prepare();
const p2 = db.insert()...prepare();

async function test2() {
  await db.transaction(async tx => {
    p1.execute();
    p2.execute();
  });
}

GitHub

Issues · drizzle-team/drizzle-orm

TypeScript ORM that feels like writing SQL. Contribute to drizzle-team/drizzle-orm development by creating an account on GitHub.

15 Replies

Angelelz•15mo ago

The advantage of the prepared statement is being able to use the same pre-compiled template to be used several times, saving time by compiling it only once It doesn't do anything at the database level The tx object passed to the transaction function is just a convenience. You can use db interchangeably, it won't make a difference It only has a rollback() method which only throws an error. Inside the transaction, it stops execution and rollsback To answer your questions 1) Yes, you are right. That's exactly how it's supposed to be 2) It doesn't make a difference to use tx or db

3noixOP•15mo ago

Thanks a lot for your answer, everything is clear now!

nk•9mo ago

Does this mean I can put the transactions wherever and call methods like eg. transferFunds() which do not have the transaction code in them, aka. the caller provides it, if you know what I mean tx { transfer_funds(...) other stuff } The actions in transfer_funds will also be reverted simply because they are wrapped in the caller's transaction Is what I'm asking

Angelelz•9mo ago

I was completely wrong back then. I haven't been able to test properly but not using the tx object might make the query run in a different connection, hance, outside the transaction I'm a little rusty with drizzle right now, and super busy in other stuff but if you explain what you mean and ping me, I'll do my best to help

nk•9mo ago

is using a transaction in a Promise.all still broken? it doesn't seem like it rolls back if an error is thrown also, is there a type exported for the db type with the rollback method

nk•9mo ago

Angelelz•9mo ago

Try not catching the error. All tx.rollback does is throw an error

nk•9mo ago

I did that because it wasn't rolling it back I just removed the Promise.all and awaited it so it's fine

Angelelz•9mo ago

Can you show what you did?

nk•9mo ago

I changed it to this if that's what you mean

nk•9mo ago

It's kinda complex and that function also has another Promise.all inside of it

Angelelz•9mo ago

My understanding of transactions is tickling telling me that attempting to paralellize inside the transaction won't do anything The point of the transaction is to run queries sequentially and stop and rollback if there is a problem at some point of the process

nk•9mo ago

Yeah I just removed them all, that makes sense lol

Angelelz•9mo ago

But I haven't tested this at all, and I might be completely wrong I've been wrong about transactions in the past so I wouldn't trust in my words at all lol

nk•9mo ago

https://discord.com/channels/1043890932593987624/1177409434000031775/1177409434000031775 would've probably made more sense but it didn't seem like it was working properly although it was executing the code

Gaming

Programming

Transaction + prepared statement