drizzle-orm: [BUG]: Transactions rollback doesn't work

What version of `drizzle-orm` are you using?

0.29.2

What version of `drizzle-kit` are you using?

0.20.9

Describe the Bug

I wrote a pretty simple transaction but when the error appears rollback doesn’t work. I have 3 steps and when the error happens on the second step, the first change is still in DB.

Expected behavior

I expect that if the error happens at some point in the transaction the entire transaction rolls back. But it doesn’t.

Even when I try to run rolling back manually I get an error: [DrizzleError: Rollback] { name: 'DrizzleError', cause: undefined }

Environment & setup

It is running on Vercel and the project is on Next.js 14.

import {sql} from '@vercel/postgres'
import {drizzle} from 'drizzle-orm/vercel-postgres'

export const db = drizzle(sql, {schema})

const updatedUser = await db.transaction(async (trx) => {
  const [updatedUser] = await trx
    .update(UsersTable)
    .set({
      name: input.name,
      email: input.email,
    })
    .where(eq(UsersTable.id, user.id))
    .returning();

  

    // ERROR happens here
  await trx.insert(KeyTable).values({
    id: 'some-id',
    userId: user.id,
    hashedPassword: 'hashed-password',
  });
  await trx.delete(KeyTable).where(eq(KeyTable.id, 'oldKeyId'));

  return updatedUser
});

About this issue

Original URL
State: open
Created 6 months ago
Reactions: 4
Comments: 15

Most upvoted comments

turns out I was doing this wrong, works fine for postgres, you just have to be sure to throw within transaction and can’t suppress it because it depends on catching the error in the underlying code in order to perform the rollback.

So this works and rolls back:

db.transaction(async (tx) => {
  const user = await tx.insert(users).values({...}).returning();
  tx.rollback(); // this throws an error, which causes drizzle (really the postgres client) to auto-rollback)
});

but this won’t:

db.transaction(async (tx) => {
  try {
    const user = await tx.insert(users).values({...}).returning();
    tx.rollback();
  } catch (error) {
     //.. suppressing it, but needs to throw to rollback automatically
   }
});

Probably obvious, but just in case that trips anyone else up.

breesetposh on Feb 15, 2024

Did you find a workaround? Throwing an error inside the transaction function doesn’t seem to trigger the auto-rollback. tx.rollback() doesn’t work either.

PS: using bun sqlite driver

acerbisgianluca on Apr 4, 2024