Question about Transaction Optimization in Drizzle ORM

[hayata-suenaga]

In the documentation examples for transactions using Drizzle ORM, each statement or query within a transaction is awaited. I have a scenario where I need to execute multiple insert and update statements within a single transaction, but their order of execution doesn't matter. I have three questions:

1. Is it safe and/or beneficial to not await these statements individually if I don't need them to execute in any particular order?

2. If I don't await these statements individually, will Drizzle ORM optimize the transaction by sending a single, batched transaction statement to the database instead of several separate statements?

3. Does Drizzle ORM provide any built-in methods for batch operations within a transaction that might be more suitable for this use case? In other words, I want to send a transaction via a singe network call as a single statement.

The following is the example transaction code found on the official Drizzle doc.

const db = drizzle(...)
await db.transaction(async (tx) => {
  await tx.update(accounts).set({ balance: sql`${accounts.balance} - 100.00` }).where(eq(users.name, 'Dan'));
  await tx.update(accounts).set({ balance: sql`${accounts.balance} + 100.00` }).where(eq(users.name, 'Andrew'));
});

[gpxl-dev]

I found db.batch which I think is working for me, but am curious to the answer to your 3 questions as well.

[vcheeze]

Curious to know what the differences between transaction and batch are. Is it simply that transaction provides finer control?

[81reap]

A batch is a group of queries that are sent to the db to be executed. Some may pass, some may succeed, but they will all resolve and try to make changes to your db.

A transaction is a group of db calls that all need to be executed successfully otherwise all transactions fail. For example, when deleting something from two tables and the second delete fails, then the first delete will be rolled back and the db state would be as if none of the deletes happened. It's kinda like git where all your db changes will be staged together and committed to the db together.

[81reap]

You could try batching them inside the transaction and see what happens
#2677 (reply in thread)