TransactionRollbackError with BetterSQLite3

[john-prutton]

When performing a rollback inside a transaction I get an error:

- error TransactionRollbackError [DrizzleError]: Rollback
    at BetterSQLiteTransaction.rollback (webpack-internal:///(rsc)/./node_modules/.pnpm/[email protected]_@[email protected][email protected]/node_modules/drizzle-orm/session-001d24ad.mjs:1622:15)
    at Function.eval (webpack-internal:///(rsc)/./src/lib/db/createWorkout.ts:21:18)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async createWorkout (webpack-internal:///(rsc)/./src/lib/db/createWorkout.ts:15:17)
    at async handler (webpack-internal:///(rsc)/./src/app/api/workouts/create/route.ts:32:17)       
    at async eval (webpack-internal:///(rsc)/./node_modules/.pnpm/[email protected]_@[email protected][email protected][email protected]/node_modules/next/dist/server/future/route-modules/app-route/module.js:254:37)

Packages:

"drizzle-kit": "^0.19.13",
"drizzle-orm": "^0.28.5",
"better-sqlite3": "^8.5.2",
"next": "13.4.19",

Code snippet:

// Start transaction
  const res = await db.transaction(async (tx) => {

    // Insert workout
    const [{ id: workoutId }] = await tx
      .insert(workouts)
      .values({
        date: workout.date,
        userId,
      }).returning()

    await tx.rollback()
    return
}

Expected results:

• a new workout should not be inserted

Actual results:

• a new workout is inserted

[devrav]

Hey I also faced the same issue, did you find any solution ?

[john-prutton]

Not yet. I might create an issue.

[techgerm]

I'm experiencing the same issue with Postgres - any luck finding a solution?

[iabdulhannan]

Facing this issue too with Postgres

[Angelelz]

You don't need to await the rollback. Can you check if your code works without awaiting the transaction?
Also, the mechanism of rolling back a transaction is literally throwing an error. You should expect all you database calls to throw error and handle them appropriately.

If your code still inserts the workout after rolling back, can you put together a reproduction repo to investigate?

[john-prutton]

Thanks! Here's a reproduction using a drizzle example as a starter: drizzle-transaction-reproduction

Please let me know if there is any mistakes. Otherwise, is this enough grounds to open an issue?

# Drizzle transaction bug with BetterSQLite 3

## Usage

npm install
npm run generate
npm run migrate
npm run dev


## Output

state before transaction:
[]
>> seeded users: 1
>> rolling back...
Transaction error: Rollback
state after transaction:
[ { id: 1, fullName: 'User_1698843448743' } ]


## Expected Output

state before transaction:
[]
>> seeded users: 1
>> rolling back...
Transaction error: Rollback
state after transaction:
[]

[Angelelz]

I think that the issue is that BetterSQLite3 is a synchronous sqlite library.
You don't need to await anything, just use the all, run, get and values methods available in all the query builders.

Please see the changes I made to your index.ts file:

import Database from "better-sqlite3";
import { drizzle } from "drizzle-orm/better-sqlite3";
import { users } from "./schema";

const sqlite = new Database("./sqlite.db");
const db = drizzle(sqlite);

function seed(database: typeof db) {
  const { changes } = database
    .insert(users)
    .values([
      {
        fullName: "User_" + Date.now().toString(),
      },
    ]).run()

  const insertedUser = database.select().from(users).get();

  console.log(`>> seeded users: ${changes}, ${insertedUser.fullName}`);
}

function query(database: typeof db) {
  const allUsersAndProjects = database.select().from(users).all();
  console.log(allUsersAndProjects);
}

async function transact() {
  // clear db
  db.delete(users).run();

  // query before transaction
  console.log("state before transaction:");
  query(db);

  try {
    db.transaction((tx) => {
      // add a user
      seed(tx);

      // rollback user addition
      console.log(">> rolling back...");
      tx.rollback();
    });
  } catch (error) {
    console.log(`Transaction error: ${error.message}`);
  }

  // query after
  console.log("state after transaction:");
  query(db);
}

transact();

And the result was:

state before transaction:
[]
>> seeded users: 1, User_1698848462703
>> rolling back...
Transaction error: Rollback
state after transaction:
[]

Now, I do think there's an issue here with either the docs or the way you're supposed to use the asynchronous API with betterSqlite3.
I didn't have time to test too much, but I couldn't figure out how to make the asynchronous code work.

Can you create an issue to investigate what's going on with the asynchronous API for BetterSqlite3?

[john-prutton]

Thanks for the quick fix and explanation! Would I be correct in assuming that issue would be towards drizzle-orm and not better-sqlite3?

[Angelelz]

Yes, I think it's our issue because in the docs they showcase asynchronous code.

[nonua]

Hey @Angelelz, I faced the same issue and following your reply I tried it myself, but after some time testing things I still am unable to rollback the transaction before the update and the delete. Do you know how I could fix that? Or am I missing a point?

function modifyExpense({ expenseID, input }) {
    return db.transaction((tx) => {
      const [expense] = tx
        .update(expenses)
        .set({
          categoryId: input.categoryID,
          description: input.description,
          groupId: input.groupID,
          date: validISODate(input.date),
        })
        .where(eq(expenses.id, expenseID))
        .returning()
        .all();

      if (input.dues.length) {
        tx.delete(expense_dues)
          .where(eq(expense_dues.expenseId, expenseID))
          .run();

        // rolling back for testing purposes
        tx.rollback();

        tx.insert(expense_dues).values(input.dues).run();
      }

      return expense;
    });
  },

[Angelelz]

If you put together a quick reproduction I can take a look and help you.

[nonua]

@Angelelz By doing the reproduction I found out that the rollback does not occur when the db.transaction callback is async, even if there are only synchronous sqlite calls inside it.

Example:

db.transaction(async (tx) => {
  tx.delete(example)
      .where(eq(example.id, exampleId))
      .run();

   tx.rollback();
})

That is totally my bad, because the snippet I did put was simplified and async was removed.
Though, would it be possible to add this specificity to the docs or elsewhere to clarify this behavior?

Thank you!

[Angelelz]

Yes, I agree that the docs should be updated. Can you create an issue to track it?

[nonua]

@Angelelz Thank you, I created the issue here