SQL 事务是一组或多个 SQL 语句的集合,这些语句与数据库交互。 整个事务可以作为一个单一的逻辑单元提交给数据库,或者作为一个单一的逻辑单元回滚(撤销)。
Drizzle ORM 提供了运行 SQL 语句的事务 API:
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')); });
Drizzle ORM 支持带有嵌套事务 API 的 savepoints:
savepoints
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")); }); });
您可以将业务逻辑嵌入事务中,并在需要时回滚:
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) { // This throws an exception that rollbacks the transaction. 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')); });
您可以从事务中返回值:
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; });
您可以使用事务与 关系查询 一起使用:
const db = drizzle(..., { schema }) await db.transaction(async (tx) => { await tx.query.users.findMany({ with: { accounts: true } }); });
我们提供了特定于方言的事务配置 API:
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", deferrable: true, } ); interface PgTransactionConfig { isolationLevel?: | "read uncommitted" | "read committed" | "repeatable read" | "serializable"; accessMode?: "read only" | "read write"; deferrable?: boolean; }
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, } ); interface MySqlTransactionConfig { isolationLevel?: | "read uncommitted" | "read committed" | "repeatable read" | "serializable"; accessMode?: "read only" | "read write"; withConsistentSnapshot?: boolean; }
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")); }, { behavior: "deferred", } ); interface SQLiteTransactionConfig { behavior?: 'deferred' | 'immediate' | 'exclusive'; }