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

https://www.postgresql.org/docs/14/sql-begin.html

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

https://www.postgresql.org/docs/14/sql-commit.html

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

https://www.postgresql.org/docs/14/sql-rollback.html

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

https://www.postgresql.org/docs/14/sql-savepoint.html