Slonik Utilities

Utilities for manipulating data in PostgreSQL database using Slonik.

Contents

• Slonik Utilities
  • Contents
  • Usage
    • update
    • updateDistinct
    • upsert

Usage

update

1import {
2  update
3} from 'slonik-utilities';
4
5/**
6 * @param connection Instance of Slonik connection.
7 * @param {string} tableName Target table name.
8 * @param {Object.<string, ValueExpression>} namedValueBindings Object describing the desired column values.
9 * @param {Object.<string, EqualPredicate>} [booleanExpressionValues] Object describing the boolean expression used to construct WHERE condition.
10 * @returns {UpdateResultType}
11 */
12update;
13

Constructs and executes UPDATE query.

Example: Update all rows

Operation:

1update(
2  connection,
3  'user',
4  {
5    givenName: 'foo'
6  }
7);
8

Is equivalent to:

1UPDATE "user"
2SET
3  "given_name" = $1;
4

Example: Update rows matching a boolean WHERE condition

Operation:

1update(
2  connection,
3  'user',
4  {
5    givenName: 'foo'
6  },
7  {
8    lastName: 'bar'
9  }
10);
11

Is equivalent to:

1UPDATE "user"
2SET
3  "given_name" = $1
4WHERE
5  "last_name" = $2;
6

updateDistinct

1import {
2  updateDistinct
3} from 'slonik-utilities';
4
5/**
6 * @param connection Instance of Slonik connection.
7 * @param {string} tableName Target table name.
8 * @param {Object.<string, ValueExpression>} namedValueBindings Object describing the desired column values.
9 * @param {Object.<string, EqualPredicate>} [booleanExpressionValues] Object describing the boolean expression used to construct WHERE condition.
10 * @returns {UpdateDistinctResultType}
11 */
12updateDistinct;
13

Constructs and executes UPDATE query matching only rows with distinct values.

Example: Update all rows

Operation:

1update(
2  connection,
3  'user',
4  {
5    givenName: 'foo'
6  }
7);
8

Is equivalent to:

1UPDATE "user"
2SET
3  "given_name" = $1
4WHERE
5  "given_name" IS DISTINCT FROM $1;
6

Example: Update rows matching a boolean WHERE condition

Operation:

1update(
2  connection,
3  'user',
4  {
5    givenName: 'foo'
6  },
7  {
8    lastName: 'bar'
9  }
10);
11

Is equivalent to:

1UPDATE "user"
2SET
3  "given_name" = $1
4WHERE
5  "last_name" = $2 AND
6  "given_name" IS DISTINCT FROM $1;
7

upsert

1import {
2  upsert
3} from 'slonik-utilities';
4
5/**
6 * @typedef Configuration~Upsert
7 * @property identifierName column name. Default: "id".
8 */
9
10/**
11 * @param connection Instance of Slonik connection.
12 * @param {string} tableName Target table name.
13 * @param {Object.<string, ValueExpression>} namedValueBindings Object describing the desired column values.
14 * @param {string[]} [uniqueConstraintColumnNames] Names of columns that describe a unique constraint on the table. Defaults to property names of `namedValueBindings`.
15 * @param {Configuration~Upsert} [configuration]
16 */
17upsert;
18

Inserts a new record to the database. If there is a conflicting unique constraint, updates the existing row.

Example: Named value bindings equal to the unique constraint column names

Table schema:

1CREATE TABLE user (
2  id SERIAL PRIMARY KEY,
3  email_address text NOT NULL
4);
5
6CREATE UNIQUE INDEX user_email_idx ON user(email_address text_ops);
7

Operation:

1upsert(
2  connection,
3  'user',
4  {
5    emailAddress: 'gajus@gajus.com'
6  }
7);
8

Behaviour:

If user table already contains a record describing the input email, then the following query will be evaluted:

1SELECT "id"
2FROM "user"
3WHERE (
4  "email_address" = $1
5);
6

If user table does not contain a record describing the input email, then the following queries will be evaluated:

1SELECT "id"
2FROM "user"
3WHERE (
4  "email_address" = $1
5);
6
7INSERT INTO "user" ("email_address")
8VALUES ($1)
9ON CONFLICT ("email_address")
10DO NOTHING
11RETURNING "id";
12
13-- This query will not be evaluted if the preceeding query returns result.
14SELECT "id"
15FROM "user"
16WHERE (
17  "email_address" = $1
18);
19

Example: Named value bindings different than the unique constraint column names

Table schema:

1CREATE TABLE user (
2  id SERIAL PRIMARY KEY,
3  email_address text NOT NULL,
4  password text NOT NULL,
5  given_name text NOT NULL,
6  family_name text NOT NULL
7);
8
9CREATE UNIQUE INDEX user_email_idx ON user(email_address text_ops);
10

Operation:

1upsert(
2  connection,
3  'user',
4  {
5    emailAddress: 'gajus@gajus.com',
6    familyName: 'Kuizinas',
7    givenName: 'Gajus'
8  },
9  [
10    'email_address'
11  ]
12);
13

Behaviour:

If user table already contains a record describing the input email, then the following query will be evaluted:

1SELECT "id"
2FROM "user"
3WHERE (
4  "email_address" = $1 AND
5  "family_name" = $2 AND
6  "given_name" = $3
7);
8

If user table does not contain a record describing the input email, then the following queries will be evaluated:

1SELECT "id"
2FROM "user"
3WHERE (
4  "email_address" = $1 AND
5  "family_name" = $2 AND
6  "given_name" = $3
7);
8
9INSERT INTO "user" ("email_address", "family_name", "given_name")
10VALUES ($1, $2, $3)
11ON CONFLICT ("email_address")
12DO UPDATE SET
13  "family_name" = "excluded"."family_name",
14  "given_name" = "excluded"."given_name"
15RETURNING "id"
16

Example: SQL tags as values

Named value binding values can be SQL tokens, e.g.

1upsert(
2  connection,
3  'user',
4  {
5    emailAddress: 'gajus@gajus.com',
6    createdAt: sql.raw('to_timestamp($1)', [1555595070])
7  }
8);
9

Given the above example, queries equivalent to the following will be evaluated:

1SELECT "id"
2FROM "user"
3WHERE (
4  "email_address" = $1 AND
5  "created_at" = to_timestamp($2)
6);
7
8-- ...
9