Drizzle Team•16mo ago

Is it possible to run javascript between database transactions?

In my authentication flow, I need to do the following things on signup 1. Check if user exists 2. Check if email verification token exists 3. If not, create new verification token So.... instead of hitting the database 3 times, I thought it'd be a good idea to hit it once via a transaction. My understanding is that even if just querying twice and updating once, it's still better to do it all in one transaction. But wait.... Inbetween the db calls, I throw errors if the user or token already exists. When I think about it, if the queries are being sent to the database as one transaction and the erros thrown are written in javascript and inbetween those transactions, doesn't that mean the database won't run the code inbetween the transactions? Can someone confirm that this will indeed need to be 3 transactions in order for me to throw properly. I can't do all this in one transaction because the errors won't throw right? Because the database won't receive the javascript. For example (pseudo-code below):

await db.transaction(async tx => {
  const hasUser = await tx.query.users.findFirst(...); 
  if (hasUser) {
    throw new Error("User already exists"); 
  }
  // ^ This doesn't work right?

  const hasToken = await tx.query.token.findFirst(...);
  if (hasToken) {
    throw new Error("Token already exists");
  } 
  // ^ Neither does this?

  const token = await tx.insert(token).values(...);
});

await db.transaction(async tx => {
  const hasUser = await tx.query.users.findFirst(...); 
  if (hasUser) {
    throw new Error("User already exists"); 
  }
  // ^ This doesn't work right?

  const hasToken = await tx.query.token.findFirst(...);
  if (hasToken) {
    throw new Error("Token already exists");
  } 
  // ^ Neither does this?

  const token = await tx.insert(token).values(...);
});

4 Replies

Angelelz•16mo ago

A transaction is a concept in which you either run all the queries or none of them. It doesn't mean that all the queries are going to be sent or be run at once. It just means that if one of the queries fail, the database won't accept any of the changes, even if only the last one fails. In your case, if the user exists you don't want to continue the auth flow, you want to cancel each and every one of the queries. In Drizzle if you throw an error in a transaction it will get rolledback Or if you just call tx.rollback() (which basically only just throws and error under the hood)

PaulOP•16mo ago

Ohh that makes sense. Thank you. So a transaction doesn't have to do with one call or three calls, moreso one failing is all failing. But I do have to ask... if a transaction has 3 consecutive transactions like the one below, does that become one db call? Or it's not clear still.

await db.transaction(async (tx) => {
  await tx.update(accounts).set({ balance: sql`${accounts.balance} - 100.00` }).where(eq(users.name, 'Dan'));
  await tx.update(accounts).set({ balance: sql`${accounts.balance} + 100.00` }).where(eq(users.name, 'Andrew'));
  await tx.update(accounts).set({ balance: sql`${accounts.balance} + 140.00` }).where(eq(users.name, 'Andrew'));
});

await db.transaction(async (tx) => {
  await tx.update(accounts).set({ balance: sql`${accounts.balance} - 100.00` }).where(eq(users.name, 'Dan'));
  await tx.update(accounts).set({ balance: sql`${accounts.balance} + 100.00` }).where(eq(users.name, 'Andrew'));
  await tx.update(accounts).set({ balance: sql`${accounts.balance} + 140.00` }).where(eq(users.name, 'Andrew'));
});

Angelelz•16mo ago

Your example will basically do 5 queries. 1. Begin transaction 2. first update 3. second update 4. third update 5. Commit transaction

PaulOP•16mo ago

Oh wow. Even without any other javascript code, it's 5 queries. Okay. Thank you. This is very helpful!

Gaming

Programming

Is it possible to run javascript between database transactions?