[BUG]: Problem with the transaction

[jeffminsungkim]

What version of drizzle-orm are you using?

0.27.2

What version of drizzle-kit are you using?

0.19.11

Describe the Bug

I recently switched from Prisma to Drizzle in the T3 stack, but during this process, I encountered some issues with transactions, so I decided to report it.

When using the drizzle-orm/planetscale-serverless package along with mysql2 and drizzle-orm/mysql2 packages, the results were different. Let me first explain the first scenario when drizzle-orm/planetscale-serverless was used:

// ./src/server/db.ts

import { connect } from "@planetscale/database";
import { drizzle } from "drizzle-orm/planetscale-serverless";
import { schema } from "@/db"; // just an object from multiple schema files e.g. { ...auth, ...channel }

const connection = connect({
  host: env.DATABASE_HOST,
  username: env.DATABASE_USERNAME,
  password: env.DATABASE_PASSWORD,
});

export const db = drizzle(connection, {
  logger: env.NODE_ENV !== "production",
  schema,
});

Here is an example code of a transaction that increments the values in two tables by 1 each:

The queries work whether using Prepared Statements or not. However, I couldn't confirm if they were executed as transactions through the query logs. I would appreciate it if the logging could be improved to be more intuitive.

export const increaseViewCount = async (params: PageCounterParams) => {
  const { channelId, userIp, currentDate: visitDate } = params;

  await db.transaction(
    async (tx) => {
      const p1 = tx
        .update(channelStatistics)
        .set({ viewCount: sql`${channelStatistics.viewCount} + 1` })
        .where(eq(channelStatistics.channelId, channelId))
        .prepare();

      const p2 = tx
        .insert(channelVisitCount)
        .values({ channelId, visitDate, visitCount: 1 })
        .onDuplicateKeyUpdate({
          set: { visitCount: sql`${channelVisitCount.visitCount} + 1` },
        })
        .prepare();

      await p1.execute();
      await p2.execute();
    },
    { isolationLevel: "read committed" } // It didn't throw any errors.
  );
};

Here is what the logs show:

Query: update `channel_statistics` set `view_count` = `channel_statistics`.`view_count` + 1 where `channel_statistics`.`channel_id` = ? -- params: ["CiU2SIvMOHCMDb5mRAPpMaJCO"]
Query: insert into `channel_visit_count` (`channel_id`, `visit_date`, `visit_count`) values (?, ?, ?) on duplicate key update `visit_count` = `channel_visit_count`.`visit_count` + 1 -- params: ["CiU2SIvMOHCMDb5mRAPpMaJCO", "2023-07-29", 1]

Now, I will explain the situation when connected using mysql2 and drizzle-orm/mysql2.

// ./src/server/db.ts

import { drizzle } from "drizzle-orm/mysql2";
import mysql, { type Pool } from "mysql2/promise";

import { env } from "@/env.mjs";
import { schema } from "@/db";

const globalForMySQL = globalThis as unknown as { poolConnection: Pool };

const poolConnection =
  globalForMySQL.poolConnection ||
  mysql.createPool({
    host: env.DATABASE_HOST,
    user: env.DATABASE_USERNAME,
    password: env.DATABASE_PASSWORD,
    database: env.DATABASE_NAME,
    ssl: { rejectUnauthorized: true },
  });

if (env.NODE_ENV !== "production") {
  globalForMySQL.poolConnection = poolConnection;
}

export const db = drizzle(poolConnection, {
  logger: env.NODE_ENV !== "production",
  schema,
});

If the increaseViewCount function from the previous example has the isolationLevel set, the query doesn't work, and you can observe the following log:

Query: ?? -- params: ["set transaction ", "isolation level read committed"]

If I remove the isolationLevel, the query will execute successfully, and you'll be able to see the following log.

Query: begin
Query: update `channel_statistics` set `view_count` = `channel_statistics`.`view_count` + 1 where `channel_statistics`.`channel_id` = ? -- params: ["CyGKvjFHhjYVoK9YZrekKafkG"]
Query: insert into `channel_visit_count` (`channel_id`, `visit_date`, `visit_count`) values (?, ?, ?) on duplicate key update `visit_count` = `channel_visit_count`.`visit_count` + 1 -- params: ["CyGKvjFHhjYVoK9YZrekKafkG", "2023-07-29", 1]
Query: commit

Expected behavior

When using the mysql2 driver, regardless of whether the isolation level option is set or not, the queries should function correctly, and it would be great to have consistent and improved transaction logging.

Perhaps something like this

TRANSACTION BEGIN
...
TRANSACTION COMMIT

Environment & setup

mysql2 v3.5.2
macOS v12.6
node v18.12
next v13.4.10
react v18.2

[Angelelz]

Can you create a reproduction repository to investigate?

[dcolthorp]

I'm also seeing this in drizzle 0.28 and 0.29 with regular mysql via mysql2

[zettoy]

Any updates on this issue? Seems like it's still present in version 0.30.4. Or is there a work around?

[PhilippDehler]

This bug still exists in 0.36.4.

await drizzle.transaction(
                async tx => {
                      // some stuff.
                },
                {
                     isolationLevel: 'serializable',
                 }
            );
// will throw 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`set transaction `' at line 1'