[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();

[dcalsky]

@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();

@duducpp It's the drawback of Cloudflare D1 rather than drizzle. Even if you switch to prisma, this transaction feature is still unsupported. They (cloudflare team) have a workaround may help you: https://blog.cloudflare.com/whats-new-with-d1

[sameerdewan]

@emmanuelchucks

This is a curious situation where batch is not a true workaround, as one action against the database in a batch "tx" may have return data needed for the following action in the batch call.

[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();

@duducpp It's the drawback of Cloudflare D1 rather than drizzle. Even if you switch to prisma, this transaction feature is still unsupported. They (cloudflare team) have a workaround may help you: https://blog.cloudflare.com/whats-new-with-d1

This is the only workaround, thanks for the tip.

[sameerdewan]

@duducpp I did see this last night as well: https://blog.cloudflare.com/whats-new-with-d1

Are you aware if we can instead of workers-qb, use drizzle in this pattern? I reached out to the authors as well.

[emmanuelchucks]

Cloudflare never shipped the stored procedure feature mentioned in that blog post. It was just something they were looking into at the time. See here: https://community.cloudflare.com/t/d1-stored-procedures-can-not-use/649207

[swarajbachu]

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.

I am having this error

[verespej]

I found a feature request to add transaction support to D1 in Cloudflare's workers SDK repo. That seems likely to have the latest status on this. Unfortunately, no updates on the issue for nearly a year.

cloudflare/workers-sdk#2733