Create a new transaction with the provided name and options

Get the isolation level of the transaction

Get all the savepoints of the transaction

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 "https://deno.land/x/postgres/mod.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 "https://deno.land/x/postgres/mod.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 "https://deno.land/x/postgres/mod.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 "https://deno.land/x/postgres/mod.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 "https://deno.land/x/postgres/mod.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 "https://deno.land/x/postgres/mod.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 "https://deno.land/x/postgres/mod.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}`;

Use the configuration object for more advance options to execute the query

import { Client } from "https://deno.land/x/postgres/mod.ts";
const my_client = new Client();
const { rows } = await my_client.queryArray<[number, string]>({
  text: "SELECT ID, NAME FROM CLIENTS",
  name: "select_clients",
}); // Array<[number, string]>

Execute prepared statements with template strings

import { Client } from "https://deno.land/x/postgres/mod.ts";
const my_client = new Client();

const id = 12;
// Array<[number, string]>
const {rows} = await my_client.queryArray<[number, string]>`SELECT ID, NAME FROM CLIENTS WHERE ID = ${id}`;

Executed queries and retrieve the data as object entries. It supports a generic in order to type the entries retrieved by the query

import { Client } from "https://deno.land/x/postgres/mod.ts";
const my_client = new Client();

const { rows: rows1 } = await my_client.queryObject(
  "SELECT ID, NAME FROM CLIENTS"
); // Record<string, unknown>

const { rows: rows2 } = await my_client.queryObject<{id: number, name: string}>(
  "SELECT ID, NAME FROM CLIENTS"
); // Array<{id: number, name: string}>

Use the configuration object for more advance options to execute the query

import { Client } from "https://deno.land/x/postgres/mod.ts";
const my_client = new Client();

const { rows: rows1 } = await my_client.queryObject(
  "SELECT ID, NAME FROM CLIENTS"
);
console.log(rows1); // [{id: 78, name: "Frank"}, {id: 15, name: "Sarah"}]

const { rows: rows2 } = await my_client.queryObject({
  text: "SELECT ID, NAME FROM CLIENTS",
  fields: ["personal_id", "complete_name"],
});
console.log(rows2); // [{personal_id: 78, complete_name: "Frank"}, {personal_id: 15, complete_name: "Sarah"}]

Execute prepared statements with template strings

import { Client } from "https://deno.land/x/postgres/mod.ts";
const my_client = new Client();
const id = 12;
// Array<{id: number, name: string}>
const { rows } = await my_client.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.

Calling a rollback without arguments will terminate the current transaction and undo all changes.

import { Client } from "https://deno.land/x/postgres/mod.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

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

Savepoints can be used to rollback specific changes part of a transaction.

import { Client } from "https://deno.land/x/postgres/mod.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

// These are all the same, everything that happened between the savepoint and the rollback gets undone
await transaction.rollback(savepoint);
await transaction.rollback('before_disaster')
await transaction.rollback({ savepoint: 'before_disaster'})

await transaction.commit(); // Commits all other changes

The chain option allows you to undo the current transaction and restart it with the same parameters in a single statement

import { Client } from "https://deno.land/x/postgres/mod.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

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 "https://deno.land/x/postgres/mod.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 "https://deno.land/x/postgres/mod.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 "https://deno.land/x/postgres/mod.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