import { Transaction } from "https://deno.land/x/postgres@v0.16.1/query/transaction.ts";
Constructors
Methods
The begin method will officially begin the transaction, and it must be called before any query or transaction operation is executed in order to lock the session
import { Client } from "../client.ts";
const client = new Client();
const transaction = client.createTransaction("transaction_name");
await transaction.begin(); // Session is locked, transaction operations are now safe
// Important operations
await transaction.commit(); // Session is unlocked, external operations can now take place
The commit method will make permanent all changes made to the database in the current transaction and end the current transaction
import { Client } from "../client.ts";
const client = new Client();
const transaction = client.createTransaction("transaction");
await transaction.begin();
// Important operations
await transaction.commit(); // Will terminate the transaction and save all changes
The commit method allows you to specify a "chain" option, that allows you to both commit the current changes and start a new with the same transaction parameters in a single statement
import { Client } from "../client.ts";
const client = new Client();
const transaction = client.createTransaction("transaction");
// Transaction operations I want to commit
await transaction.commit({ chain: true }); // All changes are saved, following statements will be executed inside a transaction
await transaction.queryArray`DELETE SOMETHING FROM SOMEWHERE`; // Still inside the transaction
await transaction.commit(); // The transaction finishes for good
This method will search for the provided savepoint name and return a reference to the requested savepoint, otherwise it will return undefined
This method will list you all of the active savepoints in this transaction
This method returns the snapshot id of the on going transaction, allowing you to share the snapshot state between two transactions
import { Client } from "../client.ts";
const client_1 = new Client();
const client_2 = new Client();
const transaction_1 = client_1.createTransaction("transaction");
const snapshot = await transaction_1.getSnapshot();
const transaction_2 = client_2.createTransaction("new_transaction", { isolation_level: "repeatable_read", snapshot });
// transaction_2 now shares the same starting state that transaction_1 had
https://www.postgresql.org/docs/14/functions-admin.html#FUNCTIONS-SNAPSHOT-SYNCHRONIZATION
This method allows executed queries to be retrieved as array entries. It supports a generic interface in order to type the entries retrieved by the query
import { Client } from "../client.ts";
const client = new Client();
const transaction = client.createTransaction("transaction");
const {rows} = await transaction.queryArray(
"SELECT ID, NAME FROM CLIENTS"
); // Array<unknown[]>
You can pass type arguments to the query in order to hint TypeScript what the return value will be
import { Client } from "../client.ts";
const client = new Client();
const transaction = client.createTransaction("transaction");
const { rows } = await transaction.queryArray<[number, string]>(
"SELECT ID, NAME FROM CLIENTS"
); // Array<[number, string]>
It also allows you to execute prepared stamements with template strings
import { Client } from "../client.ts";
const client = new Client();
const transaction = client.createTransaction("transaction");
const id = 12;
// Array<[number, string]>
const { rows } = await transaction.queryArray<[number, string]>`SELECT ID, NAME FROM CLIENTS WHERE ID = ${id}`;
This method allows executed queries to be retrieved as object entries. It supports a generic interface in order to type the entries retrieved by the query
import { Client } from "../client.ts";
const client = new Client();
const transaction = client.createTransaction("transaction");
{
const { rows } = await transaction.queryObject(
"SELECT ID, NAME FROM CLIENTS"
); // Record<string, unknown>
}
{
const { rows } = await transaction.queryObject<{id: number, name: string}>(
"SELECT ID, NAME FROM CLIENTS"
); // Array<{id: number, name: string}>
}
You can also map the expected results to object fields using the configuration interface. This will be assigned in the order they were provided
import { Client } from "../client.ts";
const client = new Client();
const transaction = client.createTransaction("transaction");
{
const { rows } = await transaction.queryObject(
"SELECT ID, NAME FROM CLIENTS"
);
console.log(rows); // [{id: 78, name: "Frank"}, {id: 15, name: "Sarah"}]
}
{
const { rows } = await transaction.queryObject({
text: "SELECT ID, NAME FROM CLIENTS",
fields: ["personal_id", "complete_name"],
});
console.log(rows); // [{personal_id: 78, complete_name: "Frank"}, {personal_id: 15, complete_name: "Sarah"}]
}
It also allows you to execute prepared stamements with template strings
import { Client } from "../client.ts";
const client = new Client();
const transaction = client.createTransaction("transaction");
const id = 12;
// Array<{id: number, name: string}>
const {rows} = await transaction.queryObject<{id: number, name: string}>`SELECT ID, NAME FROM CLIENTS WHERE ID = ${id}`;
Rollbacks are a mechanism to undo transaction operations without compromising the data that was modified during the transaction
A rollback can be executed the following way
import { Client } from "../client.ts";
const client = new Client();
const transaction = client.createTransaction("transaction");
// Very very important operations that went very, very wrong
await transaction.rollback(); // Like nothing ever happened
Calling a rollback without arguments will terminate the current transaction and undo all changes, but it can be used in conjuction with the savepoint feature to rollback specific changes like the following
import { Client } from "../client.ts";
const client = new Client();
const transaction = client.createTransaction("transaction");
// Important operations I don't want to rollback
const savepoint = await transaction.savepoint("before_disaster");
await transaction.queryArray`UPDATE MY_TABLE SET X = 0`; // Oops, update without where
await transaction.rollback(savepoint); // "before_disaster" would work as well
// Everything that happened between the savepoint and the rollback gets undone
await transaction.commit(); // Commits all other changes
The rollback method allows you to specify a "chain" option, that allows you to not only undo the current transaction but to restart it with the same parameters in a single statement
import { Client } from "../client.ts";
const client = new Client();
const transaction = client.createTransaction("transaction");
// Transaction operations I want to undo
await transaction.rollback({ chain: true }); // All changes are undone, but the following statements will be executed inside a transaction as well
await transaction.queryArray`DELETE SOMETHING FROM SOMEWHERE`; // Still inside the transaction
await transaction.commit(); // The transaction finishes for good
However, the "chain" option can't be used alongside a savepoint, even though they are similar
A savepoint is meant to reset progress up to a certain point, while a chained rollback is meant to reset all progress and start from scratch
import { Client } from "../client.ts";
const client = new Client();
const transaction = client.createTransaction("transaction");
// @ts-expect-error
await transaction.rollback({ chain: true, savepoint: "my_savepoint" }); // Error, can't both return to savepoint and reset transaction
This method will generate a savepoint, which will allow you to reset transaction states to a previous point of time
Each savepoint has a unique name used to identify it, and it must abide the following rules
- Savepoint names must start with a letter or an underscore
- Savepoint names are case insensitive
- Savepoint names can't be longer than 63 characters
- Savepoint names can only have alphanumeric characters
A savepoint can be easily created like this
import { Client } from "../client.ts";
const client = new Client();
const transaction = client.createTransaction("transaction");
const savepoint = await transaction.savepoint("MY_savepoint"); // returns a `Savepoint` with name "my_savepoint"
await transaction.rollback(savepoint);
await savepoint.release(); // The savepoint will be removed
All savepoints can have multiple positions in a transaction, and you can change or update
this positions by using the update
and release
methods
import { Client } from "../client.ts";
const client = new Client();
const transaction = client.createTransaction("transaction");
const savepoint = await transaction.savepoint("n1");
await transaction.queryArray`INSERT INTO MY_TABLE VALUES (${'A'}, ${2})`;
await savepoint.update(); // The savepoint will continue from here
await transaction.queryArray`DELETE FROM MY_TABLE`;
await transaction.rollback(savepoint); // The transaction will rollback before the delete, but after the insert
await savepoint.release(); // The last savepoint will be removed, the original one will remain
await transaction.rollback(savepoint); // It rolls back before the insert
await savepoint.release(); // All savepoints are released
Creating a new savepoint with an already used name will return you a reference to the original savepoint
import { Client } from "../client.ts";
const client = new Client();
const transaction = client.createTransaction("transaction");
const savepoint_a = await transaction.savepoint("a");
await transaction.queryArray`DELETE FROM MY_TABLE`;
const savepoint_b = await transaction.savepoint("a"); // They will be the same savepoint, but the savepoint will be updated to this position
await transaction.rollback(savepoint_a); // Rolls back to savepoint_b