slonik

Slonik

A PostgreSQL client with strict types, detail logging and assertions.

Features

• Convenience methods with built-in assertions.
• Middleware support.
• SQL injection guarding.
• Set interpolation.
• Detail logging.
• Parsing and logging of the auto_explain logs.
• Built-in asynchronous stack trace resolution.
• Safe connection pooling.
• Flow types.
• Mapped errors.
• Transactions.
• Atom plugin.
• ESLint plugin.

---

Documentation

• Slonik
  • Features
  • Documentation
  • Usage
    • API
    • Checking out a client from the connection pool
  • Interceptors
    • Interceptor methods
  • Built-in interceptors
    • Field name transformation interceptor
    • Query normalization interceptor
  • Recipes
    • Logging auto_explain
    • Using sql.raw to generate dynamic queries
• Logs query parameter values
  • Conventions
    • No multiline values
  • Syntax highlighting
    • Atom

Usage

Use createPool to create a connection pool, e.g.

import {
  createPool
} from 'slonik';

const pool = createPool('postgres://');

Instance of Slonik connection pool can be then used to create a new connection, e.g.

pool.connect(async (connection) => {
  await connection.query(sql`SELECT 1`);
});

The connection will be kept alive until the promise resolves (the result of the method supplied to connect()).

Refer to query method documentation to learn about the connection methods.

If you do not require having a persistent connection to the same backend, then you can directly use pool to run queries, e.g.

pool.query(sql`SELECT 1`);

Beware that in the latter example, the connection picked to execute the query is a random connection from the connection pool, i.e. using the latter method (without explicit connect()) does not guarantee that multiple queries will refer to the same backend.

API

createPool(connectionConfiguration: DatabaseConfigurationType, clientConfiguration: ClientConfigurationType): DatabasePoolType;

type DatabaseConnectionUriType = string;

type DatabaseConfigurationType =
  DatabaseConnectionUriType |
  {|
    +database?: string,
    +host?: string,
    +idleTimeoutMillis?: number,
    +max?: number,
    +password?: string,
    +port?: number,
    +user?: string
  |};

/**
 * @property interceptors An array of [Slonik interceptors](https://github.com/gajus/slonik#slonik-interceptors).
 */
type ClientConfigurationType = {|
  +interceptors?: $ReadOnlyArray<InterceptorType>
|};

Example:

import {
  createPool
} from 'slonik';

const pool = createPool('postgres://localhost');

await pool.query(sql`SELECT 1`);

Checking out a client from the connection pool

Slonik only allows to check out a connection for the duration of the promise routine supplied to the connect() method.

import {
  createPool
} from 'slonik';

const pool = createPool('postgres://localhost');

const result = await pool.connect(async (connection) => {
  await connection.query(sql`SELECT 1`);
  await connection.query(sql`SELECT 2`);

  return 'foo';
});

result;
// 'foo'

Connection is released back to the pool after the promise produced by the function supplied to connect() method is either resolved or rejected.

The primary reason for implementing only this connection pooling method is because the alternative is inherently unsafe, e.g.

// Note: This example is using unsupported API.

const main = async () => {
  const connection = await pool.connect();

  await connection.query(sql`SELECT produce_error()`);

  await connection.release();
};

In this example, the error causes early rejection of the promise and a hanging connection. A fix to the above is to ensure that connection#release() is always called, i.e.

// Note: This example is using unsupported API.

const main = async () => {
  const connection = await pool.connect();

  let lastExecutionResult;

  try {
    lastExecutionResult = await connection.query(sql`SELECT produce_error()`);
  } finally {
    await connection.release();
  }

  return lastExecutionResult;
};

Slonik abstracts the latter pattern into pool#connect() method.

Interceptors

Functionality can be added to Slonik client by adding interceptors.

Each interceptor can implement several functions which can be used to change the behaviour of the database client.

type InterceptorType = {|
  +afterPoolConnection?: (connection: DatabasePoolConnectionType) => MaybePromiseType<void>,
  +afterQueryExecution?: (
    queryExecutionContext: QueryExecutionContextType,
    query: QueryType,
    result: QueryResultType<QueryResultRowType>
  ) => MaybePromiseType<QueryResultType<QueryResultRowType>>,
  +beforePoolConnectionRelease?: (connection: DatabasePoolConnectionType) => MaybePromiseType<void>,
  +beforeQueryExecution?: (
    queryExecutionContext: QueryExecutionContextType,
    query: QueryType
  ) => MaybePromiseType<QueryResultType<QueryResultRowType>> | MaybePromiseType<void>,
  +transformQuery?: (
    queryExecutionContext: QueryExecutionContextType,
    query: QueryType
  ) => MaybePromiseType<QueryType>
|};

Interceptors are configured using client configuration, e.g.

import {
  createPool
} from 'slonik';

const interceptors = [];

const connection = createPool('postgres://', {
  interceptors
});

Interceptors are executed in the order they are added.

Interceptor methods

afterPoolConnection

Executed after a connection is , e.g.

const pool = createPool('postgres://');

// Interceptor is executed here. ↓
pool.connect();

afterQueryExecution

afterQueryExecution must return the result of the query, which will be passed down to the client.

Use afterQuery to modify the query result.

beforeQueryExecution

This function can optionally return a direct result of the query which will cause the actual query never to be executed.

beforePoolConnectionRelease

Executed before connection is released back to the connection pool, e.g.

const pool = await createPool('postgres://');

pool.connect(async () => {
  await 1;

  // Interceptor is executed here. ↓
});

transformQuery

Executed before beforeQueryExecution.

Transforms query.

Built-in interceptors

Field name transformation interceptor

createFormatFieldNameInterceptor creates an interceptor that formats query result field names.

This interceptor removes the necessity to alias field names, e.g.

connection.any(sql`
  SELECT
    id,
    full_name "fullName"
  FROM person
`);

Field name formatter uses afterQuery interceptor to format field names.

API

/**
 * @property format The only supported format is CAMEL_CASE.
 * @property test Tests whether the field should be formatted. The default behaviour is to include all fields that match ^[a-z0-9_]+$ regex.
 */
type ConfigurationType = {|
  +format: 'CAMEL_CASE',
  +test: (field: FieldType) => boolean
|};

(configuration: ConfigurationType) => InterceptorType;

Example usage

import {
  createFieldNameTransformationInterceptor,
  createPool
} from 'slonik';

const interceptors = [
  createFieldNameTransformationInterceptor({
    format: 'CAMEL_CASE'
  })
];

const connection = createPool('postgres://', {
  interceptors
});

connection.any(sql`
  SELECT
    id,
    full_name
  FROM person
`);

// [
//   {
//     id: 1,
//     fullName: 1
//   }
// ]

Query normalization interceptor

Normalizes the query.

API

/**
 * @property stripComments Strips comments from the query (default: true).
 */
type ConfigurationType = {|
  +stripComments?: boolean
|};

(configuration?: ConfigurationType) => InterceptorType;

Recipes

Logging auto_explain

executionTime log property describes how long it took for the client to execute the query, i.e. it includes the overhead of waiting for a connection and the network latency, among other things. However, it is possible to get the real query execution time by using auto_explain module.

There are several pre-requisites:

-- Load the extension.
LOAD 'auto_explain';
-- or (if you are using AWS RDS):
LOAD '$libdir/plugins/auto_explain';

-- Enable logging of all queries.
SET auto_explain.log_analyze=true;
SET auto_explain.log_format=json;
SET auto_explain.log_min_duration=0;
SET auto_explain.log_timing=true;

-- Enables Slonik to capture auto_explain logs.
SET client_min_messages=log;

This can be configured using afterPoolConnection interceptor, e.g.

const pool = await createPool('postgres://localhost', {
  interceptors: [
    {
      afterPoolConnection: async (connection) => {
        await connection.query(sql`LOAD 'auto_explain'`);
        await connection.query(sql`SET auto_explain.log_analyze=true`);
        await connection.query(sql`SET auto_explain.log_format=json`);
        await connection.query(sql`SET auto_explain.log_min_duration=0`);
        await connection.query(sql`SET auto_explain.log_timing=true`);
        await connection.query(sql`SET client_min_messages=log`);
      }
    }
  ]
});

Slonik recognises and parses the auto_explain JSON message; Roarr logger will produce a pretty-print of the explain output, e.g.

[2018-12-31T21:15:21.010Z] INFO (30) (@slonik): notice message
notice:
  level:   notice
  message:
    Query Text: SELECT count(*) FROM actor
    Plan:
      Node Type:           Aggregate
      Strategy:            Plain
      Partial Mode:        Simple
      Parallel Aware:      false
      Startup Cost:        4051.33
      Total Cost:          4051.34
      Plan Rows:           1
      Plan Width:          8
      Actual Startup Time: 26.791
      Actual Total Time:   26.791
      Actual Rows:         1
      Actual Loops:        1
      Plans:
        -
          Node Type:           Seq Scan
          Parent Relationship: Outer
          Parallel Aware:      false
          Relation Name:       actor
          Alias:               actor
          Startup Cost:        0
          Total Cost:          3561.86
          Plan Rows:           195786
          Plan Width:          0
          Actual Startup Time: 0.132
          Actual Total Time:   15.29
          Actual Rows:         195786
          Actual Loops:        1

Using sql.raw to generate dynamic queries

sql.raw can be used to generate fragments of an arbitrary SQL that are interpolated into the main query, e.g.

const uniquePairs = [
  ['a', 1],
  ['b', 2]
];

let placeholderIndex = 1;

const whereConditionSql = uniquePairs
  .map(() => {
    return needleColumns
      .map((column) => {
        return column + ' = 

## Conventions

### No multiline values

Slonik will strip all comments and line-breaks from a query before processing it.

This makes logging of the queries easier.

The implication is that your query cannot contain values that include a newline character, e.g.

```js
// Do not do this
connection.query(sql`INSERT INTO foo (bar) VALUES ('\n')`);

If you want to communicate a value that includes a multiline character, use value placeholder interpolation, e.g.

connection.query(sql`INSERT INTO foo (bar) VALUES (${'\n'})`);

Value placeholders

Tagged template literals

Slonik query methods can only be executed using sql tagged template literal, e.g.

import {
  sql
} from 'slonik'

connection.query(sql`
  SELECT 1
  FROM foo
  WHERE bar = ${'baz'}
`);

The above is equivalent to evaluating:

SELECT 1
FROM foo
WHERE bar = $1

query with 'baz' value binding.

sql.set

(members: $ReadOnlyArray<PrimitiveValueExpressionType>) => SetSqlTokenType;

sql.set is used to create a typed row construct (or a set, depending on the context), e.g.

await connection.query(sql`
  SELECT ${sql.set([1, 2, 3])}
`);

Produces:

{
  sql: 'SELECT ($1, $2, $3)',
  values: [
    1,
    2,
    3
  ]
}

sql.multiset

(sets: $ReadOnlyArray<$ReadOnlyArray<PrimitiveValueExpressionType>>) => MultisetSqlTokenType;

sql.multiset is used to create a comma-separated list of typed row constructs, e.g.

await connection.query(sql`
  SELECT ${sql.multiset([
    [1, 2, 3],
    [4, 5, 6]
  ])}
`);

Produces:

{
  sql: 'SELECT ($1, $2, $3), ($4, $5, $6)',
  values: [
    1,
    2,
    3,
    4,
    5,
    6
  ]
}

sql.identifier

(names: $ReadOnlyArray<string>) => IdentifierTokenType;

Delimited identifiers are created by enclosing an arbitrary sequence of characters in double-quotes ("). To create create a delimited identifier, create an sql tag function placeholder value using sql.identifier, e.g.

sql`
  SELECT 1
  FROM ${sql.identifier(['bar', 'baz'])}
`;

Produces:

{
  sql: 'SELECT 1 FROM "bar"."bar"',
  values: []
}

sql.raw

(rawSql: string, values?: $ReadOnlyArray<PrimitiveValueExpressionType>) => RawSqlTokenType;

Raw/ dynamic SQL can be inlined using sql.raw, e.g.

sql`
  SELECT 1
  FROM ${sql.raw('"bar"')}
`;

Produces:

{
  sql: 'SELECT 1 FROM "bar"',
  values: []
}

The second parameter of the sql.raw can be used to bind values, e.g.

sql`
  SELECT ${sql.raw('$1', [1])}
`;

Produces:

{
  sql: 'SELECT $1',
  values: [
    1
  ]
}

Query methods

any

Returns result rows.

Example:

const rows = await connection.any(sql`SELECT foo`);

#any is similar to #query except that it returns rows without fields information.

anyFirst

Returns value of the first column of every row in the result set.

• Throws DataIntegrityError if query returns multiple rows.

Example:

const fooValues = await connection.anyFirst(sql`SELECT foo`);

insert

Used when inserting 1 row.

Example:

const {
  insertId
} = await connection.insert(sql`INSERT INTO foo SET bar='baz'`);

The reason for using this method over #query is to leverage the strict types. #insert method result type is InsertResultType.

many

Returns result rows.

• Throws NotFoundError if query returns no rows.

Example:

const rows = await connection.many(sql`SELECT foo`);

manyFirst

Returns value of the first column of every row in the result set.

• Throws NotFoundError if query returns no rows.
• Throws DataIntegrityError if query returns multiple rows.

Example:

const fooValues = await connection.many(sql`SELECT foo`);

maybeOne

Selects the first row from the result.

• Returns null if row is not found.
• Throws DataIntegrityError if query returns multiple rows.

Example:

const row = await connection.maybeOne(sql`SELECT foo`);

// row.foo is the result of the `foo` column value of the first row.

maybeOneFirst

Returns value of the first column from the first row.

• Returns null if row is not found.
• Throws DataIntegrityError if query returns multiple rows.
• Throws DataIntegrityError if query returns multiple columns.

Example:

const foo = await connection.maybeOneFirst(sql`SELECT foo`);

// foo is the result of the `foo` column value of the first row.

one

Selects the first row from the result.

• Throws NotFoundError if query returns no rows.
• Throws DataIntegrityError if query returns multiple rows.

Example:

const row = await connection.one(sql`SELECT foo`);

// row.foo is the result of the `foo` column value of the first row.

Note:

I've been asked "What makes this different from knex.js knex('foo').limit(1)?". knex('foo').limit(1) simply generates "SELECT * FROM foo LIMIT 1" query. knex is a query builder; it does not assert the value of the result. Slonik #one adds assertions about the result of the query.

oneFirst

Returns value of the first column from the first row.

• Throws NotFoundError if query returns no rows.
• Throws DataIntegrityError if query returns multiple rows.
• Throws DataIntegrityError if query returns multiple columns.

Example:

const foo = await connection.oneFirst(sql`SELECT foo`);

// foo is the result of the `foo` column value of the first row.

query

API and the result shape are equivalent to pg#query.

transaction

transaction method is used wrap execution of queries in START TRANSACTION and COMMIT or ROLLBACK. COMMIT is called if the transaction handler returns a promise that resolves; ROLLBACK is called otherwise.

transaction method can be used together with createPool method. When used to create a transaction from an instance of a pool, a new connection is allocated for the duration of the transaction.

const result = await connection.transaction(async (transactionConnection) => {
  await transactionConnection.query(sql`INSERT INTO foo (bar) VALUES ('baz')`);
  await transactionConnection.query(sql`INSERT INTO qux (quux) VALUES ('quuz')`);

  return 'FOO';
});

result === 'FOO';

Error handling

All Slonik errors extend from SlonikError, i.e. You can catch Slonik specific errors using the following logic.

import {
  SlonikError
} from 'slonik';

try {
  await query();
} catch (error) {
  if (error instanceof SlonikError) {
    // This error is thrown by Slonik.
  }
}

Handling NotFoundError

To handle the case where query returns less than one row, catch NotFoundError error.

import {
  NotFoundError
} from 'slonik';

let row;

try {
  row = await connection.one(sql`SELECT foo`);
} catch (error) {
  if (!(error instanceof NotFoundError)) {
    throw error;
  }
}

if (row) {
  // row.foo is the result of the `foo` column value of the first row.
}

Handling DataIntegrityError

To handle the case where the data result does not match the expectations, catch DataIntegrityError error.

import {
  NotFoundError
} from 'slonik';

let row;

try {
  row = await connection.one(sql`SELECT foo`);
} catch (error) {
  if (error instanceof DataIntegrityError) {
    console.error('There is more than one row matching the select criteria.');
  } else {
    throw error;
  }
}

Handling NotNullIntegrityConstraintViolationError

NotNullIntegrityConstraintViolationError is thrown when Postgres responds with unique_violation (23502) error.

Handling ForeignKeyIntegrityConstraintViolationError

ForeignKeyIntegrityConstraintViolationError is thrown when Postgres responds with unique_violation (23503) error.

Handling UniqueIntegrityConstraintViolationError

UniqueIntegrityConstraintViolationError is thrown when Postgres responds with unique_violation (23505) error.

Handling CheckIntegrityConstraintViolationError

CheckIntegrityConstraintViolationError is thrown when Postgres responds with unique_violation (23514) error.

Types

This package is using Flow types.

Refer to ./src/types.js.

The public interface exports the following types:

• DatabaseConnectionType
• DatabasePoolConnectionType
• DatabaseSingleConnectionType

Use these types to annotate connection instance in your code base, e.g.

// @flow

import type {
  DatabaseConnectionType
} from 'slonik';

export default async (
  connection: DatabaseConnectionType,
  code: string
): Promise<number> => {
  const countryId = await connection.oneFirst(sql`
    SELECT id
    FROM country
    WHERE code = ${code}
  `);

  return countryId;
};

Debugging

Logging

Slonik uses roarr to log queries.

To enable logging, define ROARR_LOG=true environment variable.

By default, Slonik logs the input query, query execution time and affected row count.

You can enable additional logging details by configuring the following environment variables.

<a name="logs-query-parameter-values"></a>
# Logs query parameter values
export SLONIK_LOG_VALUES=true

Log stack trace

SLONIK_LOG_STACK_TRACE=1 will create a stack trace before invoking the query and include the stack trace in the logs, e.g.

{"context":{"package":"slonik","namespace":"slonik","logLevel":20,"executionTime":"357 ms","queryId":"01CV2V5S4H57KCYFFBS0BJ8K7E","rowCount":1,"sql":"SELECT schedule_cinema_data_task();","stackTrace":["/Users/gajus/Documents/dev/applaudience/data-management-program/node_modules/slonik/dist:162:28","/Users/gajus/Documents/dev/applaudience/data-management-program/node_modules/slonik/dist:314:12","/Users/gajus/Documents/dev/applaudience/data-management-program/node_modules/slonik/dist:361:20","/Users/gajus/Documents/dev/applaudience/data-management-program/node_modules/slonik/dist/utilities:17:13","/Users/gajus/Documents/dev/applaudience/data-management-program/src/bin/commands/do-cinema-data-tasks.js:59:21","/Users/gajus/Documents/dev/applaudience/data-management-program/src/bin/commands/do-cinema-data-tasks.js:590:45","internal/process/next_tick.js:68:7"],"values":[]},"message":"query","sequence":4,"time":1540915127833,"version":"1.0.0"}
{"context":{"package":"slonik","namespace":"slonik","logLevel":20,"executionTime":"66 ms","queryId":"01CV2V5SGS0WHJX4GJN09Z3MTB","rowCount":1,"sql":"SELECT cinema_id \"cinemaId\", target_data \"targetData\" FROM cinema_data_task WHERE id = ?","stackTrace":["/Users/gajus/Documents/dev/applaudience/data-management-program/node_modules/slonik/dist:162:28","/Users/gajus/Documents/dev/applaudience/data-management-program/node_modules/slonik/dist:285:12","/Users/gajus/Documents/dev/applaudience/data-management-program/node_modules/slonik/dist/utilities:17:13","/Users/gajus/Documents/dev/applaudience/data-management-program/src/bin/commands/do-cinema-data-tasks.js:603:26","internal/process/next_tick.js:68:7"],"values":[17953947]},"message":"query","sequence":5,"time":1540915127902,"version":"1.0.0"}

Use @roarr/cli to pretty-print the output.

Syntax highlighting

Atom

Using Atom IDE you can leverage the language-babel package in combination with the language-sql to enable highlighting of the SQL strings in the codebase.

To enable highlighting, you need to:

1. Install language-babel and language-sql packages.
2. Configure language-babel "JavaScript Tagged Template Literal Grammar Extensions" setting to use language-sql to highlight template literals with sql tag (configuration value: sql:source.sql).
3. Use sql helper to construct the queries.

For more information, refer to the JavaScript Tagged Template Literal Grammar Extensions documentation of language-babel package.

• placeholderIndex++; }) .join(' AND '); }) .join(' OR ');

const values = [];

for (const pairValues of uniquePairs) { values.push(...pairValues); }

const query = sql SELECT id FROM foo WHERE ${sql.raw(whereConditionSql, values)};

await connection.any(query);

In the above example, `query` is:

```js
{
  sql: 'SELECT id FROM foo WHERE (a = $1 AND b = $2) OR (a = $3 AND b = $4)',
  values: [
    'a',
    1,
    'b',
    2
  ]
}

Multiple sql.raw fragments can be used to create a query.

Conventions

No multiline values

Slonik will strip all comments and line-breaks from a query before processing it.

This makes logging of the queries easier.

The implication is that your query cannot contain values that include a newline character, e.g.

// Do not do this
connection.query(sql`INSERT INTO foo (bar) VALUES ('\n')`);

If you want to communicate a value that includes a multiline character, use value placeholder interpolation, e.g.

connection.query(sql`INSERT INTO foo (bar) VALUES (${'\n'})`);

⊂⊂C:5⊃⊃

⊂⊂C:6⊃⊃

⊂⊂C:7⊃⊃

⊂⊂C:8⊃⊃

Syntax highlighting

Atom

Using Atom IDE you can leverage the language-babel package in combination with the language-sql to enable highlighting of the SQL strings in the codebase.

To enable highlighting, you need to:

1. Install language-babel and language-sql packages.
2. Configure language-babel "JavaScript Tagged Template Literal Grammar Extensions" setting to use language-sql to highlight template literals with sql tag (configuration value: sql:source.sql).
3. Use sql helper to construct the queries.

For more information, refer to the JavaScript Tagged Template Literal Grammar Extensions documentation of language-babel package.