[BUG]: transaction not commiting

[mooyg]

What version of drizzle-orm are you using?

0.29.1

What version of drizzle-kit are you using?

0.20.7

Describe the Bug

import { env } from "@locus/env"
import { drizzle } from "drizzle-orm/postgres-js"
import postgres from "postgres"
import * as schema from "./schema/schema"

export const queryClient = postgres(env.DATABASE_URL)

export const db = drizzle(queryClient, {
  schema: schema,
  logger: true,
})

this is how I have my client setup

import { sql } from "drizzle-orm"
import { db } from "../.."
import { family } from "../../schema/family"
import { member } from "../../schema/member"

export const insertFamily = async (name: string, userId: string) => {
  return await db.transaction(async (tx) => {
    const familyData = await tx
      .insert(family)
      .values({
        name,
        adminId: userId,
      })
      .returning()
    await tx.insert(member).values({
      userId,
      isAdmin: true,
      familyId: familyData[0].id,
    })
    db.execute(sql`commit`)
    return familyData[0]
  })
}

this is my insert query Doesn't save the data up, familyData[0] is returned fine though. the logs -> https://srcb.in/3TQV8W2v4J

Expected behavior

Should save the data after the transaction ends.

Environment & setup

Node version v20.4.0

[lxia1220]

tx.execute(sql`commit`)

also, it commit automatically when transaction logic is done.

https://github.com/drizzle-team/drizzle-orm/blob/d535e0b667bec3aad6d238a71cebc23de30c455f/drizzle-orm/src/node-postgres/session.ts#L139

[mooyg]

tx.execute(sql`commit`)

also, it commit automatically when transaction logic is done.

https://github.com/drizzle-team/drizzle-orm/blob/d535e0b667bec3aad6d238a71cebc23de30c455f/drizzle-orm/src/node-postgres/session.ts#L139

This was just an attempt. Even without it the data isnt being saved

[Angelelz]

@mooyg Can you create a reproduction repo? I would like to investigate this.

[NickKelly1]

Just had a similar issue using node-postgres (pg), not sure if related.

My issue was that Drizzle's Pool check when entering a transaction uses instanceof on the pg Pool instance but I'm using the pg-native Pool instance instead. The Pool instanceof check returns false so my pool is treated by Drizzle as a standalone client. pool.query is called to enter the transaction and execute transaction statements, causing each statement to be executed by a different pool client instead of the pool client actually in the transaction.

[Angelelz]

Just had a similar issue using node-postgres (pg), not sure if related.

It's very likely that the issue reported is very similar. It's very important for transactions to work properly that all the queries run in the same connection. You already figured it out, I would really like to see a repro repo to figure out what's going on here.

[NickKelly1]

It's very likely that the issue reported is very similar

FYI I've created a new issue #1707 and PR with fix #1708 for pg

[joshsmith]

I'm using drizzle-orm/postgres-js and have transactions that otherwise have had no issues not committing unless specifically calling tx.execute(sql`commit`).

[Angelelz]

This issue might be due to the fact that users might be using more than one connection to send the queries of the transaction. This can happen if you use the db object instead of the tx that the transaction callback provides. This can be the first place to look at when attempting to debug.

[nhat-nguyen]

Edited

I worked through the issue. Anyone using supabase with session mode, please make sure that you have prepare mode off when instantiating the postgres client:

postgres(connectionString, {
      // IMPORTANT:
      // Disable prefetch as it is not supported for "Transaction" pool mode
      // See:
      // https://supabase.com/docs/guides/database/connecting-to-postgres#connecting-with-drizzle
      prepare: false,
    });

---

The rest is the original post.

@Angelelz I'm running into the same issue where queries inside a transaction don't commit.

I have a working repro here: https://github.com/nhat-nguyen/drizzle-tx

Setup

Place DB_URL=connection_string in .env.local (I'm using a supabase free instance), then run npm install and npm run dev to run the app.

App

So the app is a very barebone next.js app that has 3 sections:

• "Insert new rows" is used to add new rows to the table for testing.
• "Update current row, no transactions" lists all the rows while also allowing changing the value; this updates the table directly without using transaction.

                await db
                  .update(drizzleTransactions)
                  .set({ value: formData.get('value') as string })
                  .where(
                    eq(drizzleTransactions.id, formData.get('id') as string),
                  );

• "Update current row, with transactions" does the same as above but wraps the update inside a transaction.

                await db.transaction(async (tx) => {
                  await tx
                    .update(drizzleTransactions)
                    .set({ value: formData.get('value') as string })
                    .where(
                      eq(drizzleTransactions.id, formData.get('id') as string),
                    );
                });

(see code in src/app/page.tsx)

Steps to reproduce

1. Add a few rows first
2. Update the values using "no transactions", notice that the values are updated in realtime
3. Update the values with transactions, notice that the values aren't updated
   • I also noticed that the very first update using transaction after starting up the app using npm run dev always works; but subsequent updates no longer apply. Closing the server and reopening again triggers the same behaviour. So this does seem session / pooling - related. I don't use any custom pooling like @NickKelly1 though.
   • Adding await tx.execute(sql`commit`) at the end of the transaction block does seem to help with the commit, but I get the warning log

{
  severity_local: 'WARNING',
  severity: 'WARNING',
  code: '25P01',
  message: 'there is no transaction in progress',
  file: 'xact.c',
  line: '3960',
  routine: 'EndTransactionBlock'
}

Supabase connection pooling

I managed to narrow this down to the type of pooling used when connecting to supabase:

https://supabase.com/docs/guides/database/connecting-to-postgres#how-connection-pooling-works

If I use Transaction mode, then the above always reproduces. Changing to Session mode and drizzle transaction works again.