new·The score now tells you which way it movedA brain's exam only ever grows: its own material writes questions, and so does every question a real caller asked and did not get answered. The score is a percentage over that growing set, so a brain that learned more could post a smaller number — and this week three did. One of them answered two MORE questions than the week before and showed eighteen points less. Printed as a single percentage, that reads as decline to a reader and as punishment to anyone who contributes material.all news →
mozg.beta
Sign in

Drizzle ORM · all subjects

drizzle-orm/transactions

22 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.

db.transaction basic usage

Wrap SQL statements in a transaction using db.transaction(async (tx) => {...}). All statements within the callback execute as a single logical unit and commit together or rollback together.

Return values from transactions

A transaction callback can return a value using the return statement. The value returned from the callback is awaited and returned from db.transaction().

Transactions with relational queries

Transactions work with relational queries using the tx._query API. Within a transaction callback, use tx._query to perform relational queries like findMany() with nested relations.

Basic transaction example with balance transfer

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')); });

Nested transaction example with savepoint

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')); await tx.transaction(async (tx2) => { await tx2.update(users).set({ name: 'Mr. Dan' }).where(eq(users.name, 'Dan')); }); });

Conditional rollback example

const db = drizzle(...); await db.transaction(async (tx) => { const [account] = await tx.select({ balance: accounts.balance }).from(accounts).where(eq(users.name, 'Dan')); if (account.balance < 100) { tx.rollback(); } 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')); });

Return value from transaction example

const db = drizzle(...); const newBalance: number = 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')); const [account] = await tx.select({ balance: accounts.balance }).from(accounts).where(eq(users.name, 'Dan')); return account.balance; });

Relational queries in transaction example

const db = drizzle({ schema }); await db.transaction(async (tx) => { await tx._query.users.findMany({ with: { accounts: true } }); });

SingleStore nested transactions not supported

Nested transactions are not supported by SingleStore.

SingleStore isolation level limitation

SingleStore only supports one isolationLevel.

Basic transaction syntax

Drizzle ORM transactions are created by calling db.transaction() with an async callback function that receives a transaction object (tx). All database operations within the callback are executed as a single logical unit and will either commit or rollback together.

Nested transactions with savepoints

Drizzle ORM supports nested transactions using savepoints. Within a transaction callback, you can call tx.transaction() to create a nested transaction with its own savepoint. Nested transactions allow fine-grained rollback control without rolling back the entire outer transaction.

Rollback within transaction

You can call tx.rollback() within a transaction callback to trigger a rollback. This is useful for implementing business logic that conditionally rolls back the transaction, such as when a balance check fails.

Return values from transactions

Transactions in Drizzle ORM can return values from their callback function. The returned value becomes the resolved value of the db.transaction() promise, allowing you to extract query results from within the transaction.

Transactions with relational queries

Transactions in Drizzle ORM support relational queries (RQB). You can use tx.query to perform relational queries within a transaction callback, including operations like findMany() with relationships.

SingleStore transaction configuration options

SingleStore transactions accept an optional config object as the second parameter with the following properties: isolationLevel (string, optional) with values 'read uncommitted', 'read committed', 'repeatable read', or 'serializable'; accessMode (string, optional) with values 'read only' or 'read write'; withConsistentSnapshot (boolean, optional).

Basic transaction example with multiple operations

The following example shows a basic transaction that updates two accounts atomically: 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')); });

Nested transaction example with savepoint

The following example demonstrates nested transactions with savepoints: 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')); await tx.transaction(async (tx2) => { await tx2.update(users).set({ name: 'Mr. Dan' }).where(eq(users.name, 'Dan')); }); });

Transaction with conditional rollback example

The following example shows how to rollback a transaction based on a condition: await db.transaction(async (tx) => { const [account] = await tx.select({ balance: accounts.balance }).from(accounts).where(eq(users.name, 'Dan')); if (account.balance < 100) { tx.rollback(); } 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')); });

Transaction returning value example

The following example shows how to return a value from a transaction: const newBalance: number = 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')); const [account] = await tx.select({ balance: accounts.balance }).from(accounts).where(eq(users.name, 'Dan')); return account.balance; });

Transaction with SingleStore dialect configuration example

The following example shows SingleStore-specific transaction configuration: 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')); }, { isolationLevel: 'read committed', accessMode: 'read write', withConsistentSnapshot: true, });

Tenant-aware middleware for Drizzle with Nile

Create middleware that grabs the tenant ID from URL path parameters and stores it in AsyncLocalStorage. The tenantDB wrapper uses this tenant ID to set `nile.tenant_id` when executing queries, ensuring queries execute against that tenant's virtual database. Example: `app.use('/api/tenants/:tenantId/*', (req, res, next) => { const tenantId = req.params.tenantId; tenantContext.run(tenantId, next); });`

Give your agent this brain