[BUG]: Cloudflare D1 transaction not supported

[duducpp]

What version of drizzle-orm are you using?

0.31.1

What version of drizzle-kit are you using?

0.22.2

Describe the Bug

When trying to execute a transacction with cloudflare d1 adapter it throws the following error:

To execute a transaction, please use the state.storage.transaction() API instead of the SQL BEGIN TRANSACTION or SAVEPOINT statements. The JavaScript API is safer because it will automatically roll back on exceptions, and because it interacts correctly with Durable Objects' automatic atomic write coalescing.

To reproduce create a project using c3

import { drizzle } from 'drizzle-orm/d1';

export default {
    async fetch(request: Request, env: Env, ctx: ExecutionContext): Promise<Response> {
        const db = drizzle(env.DB);

        await db.transaction(async (tx) => {
            // Throws error
        });

        return new Response('OK');
    }
};

Expected behavior

No response

Environment & setup

No response

[emmanuelchucks]

Use the Batch API to achieve 'transactions' in Cloudflare D1

const batchResponse: BatchResponse = await db.batch([
  db.insert(usersTable).values({ id: 1, name: 'John' }).returning({ id: usersTable.id }),
  db.update(usersTable).set({ name: 'Dan' }).where(eq(usersTable.id, 1)),
  db.query.usersTable.findMany({}),
  db.select().from(usersTable).where(eq(usersTable.id, 1)),
  db.select({ id: usersTable.id, invitedBy: usersTable.invitedBy }).from(usersTable),
]);

[Deniffer]

facing same error how could i fix these?

[duducpp]

@emmanuelchucks unfortunately, this workaround will not work in some scenarios like the following:

const [createdUser] = await global.db
.insert(users)
.values({
    name: account.name
})
.returning({
    id: users.id
});

// It may fail if the account already exists, 
// in this case I want to rollback the transaction, removing the previously inserted user
const [createdAccount] = await global.db
.insert(accounts)
.values({
    userId: createdUser.id, // I need to know the id returned from the previous query
    provider: account.provider,
    providerAccountId: account.providerAccountId,
})
.returning();