transaction
Execute SQL transactions against the database.
Usage
Access or destructure transaction
from the SQLocal
client.
import { SQLocal } from 'sqlocal';
export const { transaction } = 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 transaction
method provides a way to execute a transaction on the database, ensuring atomicity and isolation of the SQL queries executed within it. transaction
takes a callback that is passed a tx
object containing a sql
tagged template for executing SQL within the transaction.
This sql
tag function passed in the tx
object works similarly to the sql
tag function used for single queries, but it ensures that the queries are executed in the context of the open transaction. Any logic can be carried out in the callback between the queries as needed.
If any of the queries fail or any other error is thrown within the callback, transaction
will throw an error and the transaction will be rolled back automatically. If the callback completes successfully, the transaction will be committed.
The callback can return any value desired, and if the transaction succeeds, this value will be returned from transaction
.
const productName = 'rice';
const productPrice = 2.99;
const newProductId = await transaction(async (tx) => {
const [product] = await tx.sql`
INSERT INTO groceries (name) VALUES (${productName}) RETURNING *
`;
await tx.sql`
INSERT INTO prices (groceryId, price) VALUES (${product.id}, ${productPrice})
`;
return product.id;
});
Drizzle
Drizzle queries can also be used with transaction
by passing them to the tx
object's query
function. query
will execute the Drizzle query as part of the transaction and its return value will be typed according to Drizzle.
This is the recommended way to execute transactions when using Drizzle with SQLocal. The transaction
method provided by Drizzle does not ensure isolation, so queries executed outside of the Drizzle transaction at the same time may create a data inconsistency.
const productName = 'rice';
const productPrice = 2.99;
const newProductId = await transaction(async (tx) => {
const [product] = await tx.query(
db.insert(groceries).values({ name: productName }).returning()
);
await tx.query(
db.insert(prices).values({ groceryId: product.id, price: productPrice })
);
return product.id;
});
Kysely
Kysely queries can be used with transaction
by calling Kysely's compile
method on the queries and passing them to the tx
object's query
function. query
will execute the Kysely query as part of the transaction and its return value will be typed according to Kysely.
Functionally, SQLocal's transaction
method and Kysely's transaction
method are very similar. Both can ensure atomicity and isolation of the transaction, so either method can be used to the same effect as preferred.
const productName = 'rice';
const productPrice = 2.99;
const newProductId = await transaction(async (tx) => {
const [product] = await tx.query(
db
.insertInto('groceries')
.values({ name: productName })
.returningAll()
.compile()
);
await tx.query(
db
.insertInto('prices')
.values({ groceryId: product.id, price: productPrice })
.compile()
);
return product.id;
});