pg-client-helper v1.1.0-beta.0

pg-client-helper

A Postgres pg client based helper which:

• provides async query functions explicitly returning multiple rows, a single row, a scalar value or nothing
• allows you to write SQL queries with named parameters (instead of positional parameters)
• handles your connections
• supports transactions (since v1.1.0)

Logging

pg-client-helper logs out certain events. To control the level use the PG_CLIENT_HELPER_LOGLEVEL environment variable. Valid values are:

• DEBUG
• INFO
• WARN
• ERROR
• SILENT (default)

Query Functions

pg-client-helper provides the following async query functions:

Named Parameters

You can provide named parameters in your query and the params object. These parameters must start with a Dollar sign ($).

Example:

import * as PG from "pg-client-helper";

const query = `
    INSERT INTO public.users (
        user_name
        , email_address
        , active
    )
    VALUES (
        $user_name
        , $email_address
        , $active
    )`;

const params = {
  $user_name: "John Doe",
  $email_address: "john_doe@some.tld",
  $active: true,
};

PG.query(query, params);

pg-client-helper

• takes your params object
• iterates through all properties sorted descending by the length of their names
• builds up the params array as expected by the pg client
• replaces all occurences of the property name with the index expected by the pg client

So ultimately the query run with pg will be:

pg.query(
  `    INSERT INTO public.users (
        user_name
        , email_address
        , active
    )
    VALUES (
        $2
        , $1
        , $3
    )`,
  ["john_doe@some.tld", "John Doe", true]
);

If an attribute of your params object is an Array, pg-client-helper will spread the content of the array into the parameter list.

Example:

import * as PG from "pg-client-helper";

const query = `SELECT * FROM public.users WHERE id_users IN ($id_users)`;

const params = {
  $id_users = [13, 666, 1337]
};

PG.queryMultiple(query, params);

pg-client-helper spreads the content of the $id_users array into the parameter list.

So ultimately the query run with pg will be:

pg.query(
  `SELECT * FROM public.users WHERE id_users IN ($1, $2, $3)`,
  [13, 666, 1337]
);

pg-client-helper is backwards compatible to the default pg parameter list:

import * as PG from "pg-client-helper";

const query = `
    INSERT INTO public.users (
        user_name
        , email_address
        , active
    )
    VALUES (
        $1
        , $2
        , $3
    )`;

PG.query(query, ["John Doe", "john_doe@some.tld", true]);

Connection Handling

pg-client-helper manages your database connection by utilizing connection pooling. It also supports connections to AWS RDS via IAM using AWS Signer.

If you want to use transactions, please use the following approach:

import * as PG from "pg-client-helper";

async function myfunc() {
  const client: any = PG.beginTransaction();  // begins the transaction and returns a client to be used for ALL 

  try {
    // 1st query
    const $id_mytable1 = await PG.query(
      `INSERT INTO mytable1 (val1) VALUES 'foo' RETURNING id_mytable1`
    );

    // 2nd query
    const $id_mytable2 = await PG.query(
      `INSERT INTO mytable2 (id_mytable1, val2) VALUES ($id_mytable1, 'bar')`,
      { $id_mytable1 }
    );

    // 3rd query
    await PG.query(
      `UPDATE mytable3 SET val3 = 'baz' WHERE id_mytable1 = $id_mytable1 AND id_mytable2 = $id_mytable2`,
      { $id_mytable1, $id_mytable2 }
    );

    await PG.commitTransaction(client); // commits all changes made since beginTransaction
  } catch (error) {
    if (client) {
      await PG.rollbackTransaction(client); // we faced an error after beginTransaction, roll back all changes since then
    }
  }
}