batch

Execute a batch of SQL queries against the database in an atomic way.

Usage

Access or destructure batch from the SQLocal client.

import { SQLocal } from 'sqlocal';

export const { batch } = new SQLocal('database.sqlite3');

NOTE

If you are using the Kysely Query Builder or Drizzle ORM for type-safe queries, you will initialize the client with a child class of SQLocal. See the corresponding setup page. Usage is the same otherwise.

The batch function takes a group of SQL queries, passes them to the database all together, and executes them inside a transaction. If any of the queries fail, batch will throw an error, and the transaction will be rolled back automatically. If all queries succeed, the transaction will be committed and the results from each query will be returned.

Provide a function to batch that returns an array of SQL queries constructed using the sql tagged template function passed to it. This sql tag function works similarly to the sql tag function used for single queries, but the queries passed to batch should not be individually awaited. Await the call to batch, and each query will be executed against the database in order.

const senderId = 1;
const receiverId = 2;
const coins = 4856;

// Ensures that these 3 queries either all succeed
// or get rolled back
await batch((sql) => [
	sql`INSERT INTO transfer (senderId, receiverId, coins) VALUES (${senderId}, ${receiverId}, ${coins})`,
	sql`UPDATE player SET coins = coins - ${coins} WHERE id = ${senderId}`,
	sql`UPDATE player SET coins = coins + ${coins} WHERE id = ${receiverId}`,
]);

// Results from queries will also be returned as
// items in an array, one item per query
const [players, transfers] = await batch((sql) => [
	sql`SELECT * FROM player WHERE id = ${senderId} OR id = ${receiverId}`,
	sql`SELECT * FROM transfer WHERE senderId = ${senderId}`,
]);

batch ensures atomicity and isolation for the queries it executes, but if you also need to execute other logic between queries, you should use the transaction method.