Create a new query client

Indicates if the client is currently connected to the database

The current session metadata

Close the connection to the database

Resets the transaction session metadata

Every client must initialize their connection previously to the execution of any statement

Transactions are a powerful feature that guarantees safe operations by allowing you to control the outcome of a series of statements and undo, reset, and step back said operations to your liking

In order to create a transaction, use the createTransaction method in your client as follows:

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

await transaction.begin();
// All statements between begin and commit will happen inside the transaction
await transaction.commit(); // All changes are saved

All statements that fail in query execution will cause the current transaction to abort and release the client without applying any of the changes that took place inside it

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

await transaction.begin();
await transaction.queryArray`INSERT INTO MY_TABLE (X) VALUES ${"some_value"}`;
try {
  await transaction.queryArray`SELECT []`; // Invalid syntax, transaction aborted, changes won't be applied
}catch(e){
  await transaction.commit(); // Will throw, current transaction has already finished
}

This however, only happens if the error is of execution in nature, validation errors won't abort the transaction

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

await transaction.begin();
await transaction.queryArray`INSERT INTO MY_TABLE (X) VALUES ${"some_value"}`;
try {
  await transaction.rollback("unexistent_savepoint"); // Validation error
} catch(e) {
  await transaction.commit(); // Transaction will end, changes will be saved
}

A transaction has many options to ensure modifications made to the database are safe and have the expected outcome, which is a hard thing to accomplish in a database with many concurrent users, and it does so by allowing you to set local levels of isolation to the transaction you are about to begin

Each transaction can execute with the following levels of isolation:

• Read committed: This is the normal behavior of a transaction. External changes to the database will be visible inside the transaction once they are committed.

• Repeatable read: This isolates the transaction in a way that any external changes to the data we are reading won't be visible inside the transaction until it has finished

  import { Client } from "https://deno.land/x/postgres/mod.ts";
  const client = new Client();
  const transaction = await client.createTransaction("my_transaction", { isolation_level: "repeatable_read" });
  

• Serializable: This isolation level prevents the current transaction from making persistent changes if the data they were reading at the beginning of the transaction has been modified (recommended)

  import { Client } from "https://deno.land/x/postgres/mod.ts";
  const client = new Client();
  const transaction = await client.createTransaction("my_transaction", { isolation_level: "serializable" });
  

Additionally, each transaction allows you to set two levels of access to the data:

• Read write: This is the default mode, it allows you to execute all commands you have access to normally

• Read only: Disables all commands that can make changes to the database. Main use for the read only mode is to in conjuction with the repeatable read isolation, ensuring the data you are reading does not change during the transaction, specially useful for data extraction

  import { Client } from "https://deno.land/x/postgres/mod.ts";
  const client = new Client();
  const transaction = await client.createTransaction("my_transaction", { read_only: true });
  

Last but not least, transactions allow you to share starting point snapshots between them. For example, if you initialized a repeatable read transaction before a particularly sensible change in the database, and you would like to start several transactions with that same before the change state you can do the following:

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_1");

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/tutorial-transactions.html https://www.postgresql.org/docs/14/sql-set-transaction.html

Closing your PostgreSQL connection will delete all non-persistent data that may have been created in the course of the session and will require you to reconnect in order to execute further queries

Execute queries and retrieve the data as array 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.queryArray(
  "SELECT ID, NAME FROM CLIENTS"
); // Array<unknown[]>

const { rows: rows2 } = await my_client.queryArray<[number, string]>(
  "SELECT ID, NAME FROM CLIENTS"
); // Array<[number, 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 } = 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}`;