Skip to content

Utilities for manipulating data in PostgreSQL database using Slonik.

License

Notifications You must be signed in to change notification settings

gajus/slonik-utilities

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

56 Commits
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

Slonik Utilities

Coveralls NPM version Canonical Code Style Twitter Follow

Utilities for manipulating data in PostgreSQL database using Slonik.

Contents

Usage

update

import {
  update
} from 'slonik-utilities';

/**
 * @param connection Instance of Slonik connection.
 * @param {string} tableName Target table name.
 * @param {Object.<string, ValueExpression>} namedValueBindings Object describing the desired column values.
 * @param {Object.<string, EqualPredicate>} [booleanExpressionValues] Object describing the boolean expression used to construct WHERE condition.
 * @returns {UpdateResultType}
 */
update;

Constructs and executes UPDATE query.

Example: Update all rows

Operation:

update(
  connection,
  'user',
  {
    givenName: 'foo'
  }
);

Is equivalent to:

UPDATE "user"
SET
  "given_name" = $1;

Example: Update rows matching a boolean WHERE condition

Operation:

update(
  connection,
  'user',
  {
    givenName: 'foo'
  },
  {
    lastName: 'bar'
  }
);

Is equivalent to:

UPDATE "user"
SET
  "given_name" = $1
WHERE
  "last_name" = $2;

updateDistinct

import {
  updateDistinct
} from 'slonik-utilities';

/**
 * @param connection Instance of Slonik connection.
 * @param {string} tableName Target table name.
 * @param {Object.<string, ValueExpression>} namedValueBindings Object describing the desired column values.
 * @param {Object.<string, EqualPredicate>} [booleanExpressionValues] Object describing the boolean expression used to construct WHERE condition.
 * @returns {UpdateDistinctResultType}
 */
updateDistinct;

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

Example: Update all rows

Operation:

update(
  connection,
  'user',
  {
    givenName: 'foo'
  }
);

Is equivalent to:

UPDATE "user"
SET
  "given_name" = $1
WHERE
  "given_name" IS DISTINCT FROM $1;

Example: Update rows matching a boolean WHERE condition

Operation:

update(
  connection,
  'user',
  {
    givenName: 'foo'
  },
  {
    lastName: 'bar'
  }
);

Is equivalent to:

UPDATE "user"
SET
  "given_name" = $1
WHERE
  "last_name" = $2 AND
  "given_name" IS DISTINCT FROM $1;

upsert

import {
  upsert
} from 'slonik-utilities';

/**
 * @typedef Configuration~Upsert
 * @property identifierName column name. Default: "id".
 */

/**
 * @param connection Instance of Slonik connection.
 * @param {string} tableName Target table name.
 * @param {Object.<string, ValueExpression>} namedValueBindings Object describing the desired column values.
 * @param {string[]} [uniqueConstraintColumnNames] Names of columns that describe a unique constraint on the table. Defaults to property names of `namedValueBindings`.
 * @param {Configuration~Upsert} [configuration]
 */
upsert;

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:

CREATE TABLE user (
  id SERIAL PRIMARY KEY,
  email_address text NOT NULL
);

CREATE UNIQUE INDEX user_email_idx ON user(email_address text_ops);

Operation:

upsert(
  connection,
  'user',
  {
    emailAddress: '[email protected]'
  }
);

Behaviour:

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

SELECT "id"
FROM "user"
WHERE (
  "email_address" = $1
);

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

SELECT "id"
FROM "user"
WHERE (
  "email_address" = $1
);

INSERT INTO "user" ("email_address")
VALUES ($1)
ON CONFLICT ("email_address")
DO NOTHING
RETURNING "id";

-- This query will not be evaluted if the preceeding query returns result.
SELECT "id"
FROM "user"
WHERE (
  "email_address" = $1
);

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

Table schema:

CREATE TABLE user (
  id SERIAL PRIMARY KEY,
  email_address text NOT NULL,
  password text NOT NULL,
  given_name text NOT NULL,
  family_name text NOT NULL
);

CREATE UNIQUE INDEX user_email_idx ON user(email_address text_ops);

Operation:

upsert(
  connection,
  'user',
  {
    emailAddress: '[email protected]',
    familyName: 'Kuizinas',
    givenName: 'Gajus'
  },
  [
    'email_address'
  ]
);

Behaviour:

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

SELECT "id"
FROM "user"
WHERE (
  "email_address" = $1 AND
  "family_name" = $2 AND
  "given_name" = $3
);

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

SELECT "id"
FROM "user"
WHERE (
  "email_address" = $1 AND
  "family_name" = $2 AND
  "given_name" = $3
);

INSERT INTO "user" ("email_address", "family_name", "given_name")
VALUES ($1, $2, $3)
ON CONFLICT ("email_address")
DO UPDATE SET
  "family_name" = "excluded"."family_name",
  "given_name" = "excluded"."given_name"
RETURNING "id"

Example: SQL tags as values

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

upsert(
  connection,
  'user',
  {
    emailAddress: '[email protected]',
    createdAt: sql.raw('to_timestamp($1)', [1555595070])
  }
);

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

SELECT "id"
FROM "user"
WHERE (
  "email_address" = $1 AND
  "created_at" = to_timestamp($2)
);

-- ...

About

Utilities for manipulating data in PostgreSQL database using Slonik.

Topics

Resources

License

Stars

Watchers

Forks

Packages

No packages published