[BUG]: Cloudflare D1 transaction not supported about drizzle-orm HOT 8 OPEN

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),
]);

from drizzle-orm.

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

from drizzle-orm.

@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

from drizzle-orm.

facing same error how could i fix these?

from drizzle-orm.

@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.

from drizzle-orm.

@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.

from drizzle-orm.

@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.

from drizzle-orm.

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

from drizzle-orm.