slonik

Slonik

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

Features

• Convenience methods with built-in assertions
• Anonymous, named and tagged template literal value placeholders
• Middleware support
• Syntax highlighting (Atom plugin compatible with Slonik)
• SQL injection guarding (ESLint plugin compatible with Slonik)
• Detail logging
• Built-in asynchronous stack trace resolution
• Flow types
• Mapped errors
• Transactions

---

Documentation

• Slonik
  • Features
  • Documentation
  • Usage
    • Configuration
  • Interceptors
    • beforeQuery
    • afterQuery
  • Non-standard behaviour
  • Conventions
    • No multiline values
  • Value placeholders
    • Anonymous placeholders
    • A value set
    • Multiple value sets
    • Named placeholders
    • Tagged template literals
  • Query methods
    • any
    • anyFirst
    • insert
    • many
    • manyFirst
    • maybeOne
    • maybeOneFirst
    • one
    • oneFirst
    • query
    • transaction
  • Error handling
    • Handling NotFoundError
    • Handling DataIntegrityError
    • Handling NotNullIntegrityConstraintViolationError
    • Handling ForeignKeyIntegrityConstraintViolationError
    • Handling UniqueIntegrityConstraintViolationError
    • Handling CheckIntegrityConstraintViolationError
  • Types
  • Debugging
    • Logging
    • Log stack trace
  • Syntax highlighting
    • Atom

Usage

Slonik exports two factory functions:

• createPool
• createConnection

The API of the query method is equivalent to that of pg.

Refer to query methods for documentation of Slonik-specific query methods.

Configuration

Both functions accept the same parameters:

• connectionConfiguration
• clientConfiguration

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).
 * @property onConnect A new connection handler. Executed after a connection is established, but before allowing the connection to be used by any clients.
 */
type ClientConfigurationType = {|
  +interceptors?: $ReadOnlyArray<InterceptorType>,
  +onConnect?: (connection: DatabaseConnectionType) => MaybePromiseType<void>
|};

Example:

import {
  createPool
} from 'slonik';

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

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

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 = {|
  +beforeQuery?: (query: QueryType) => Promise<QueryResultType<QueryResultRowType>> | Promise<void> | QueryResultType<QueryResultRowType> | void,
  +afterQuery?: (query: QueryType, result: QueryResultType<QueryResultRowType>) => Promise<void> | void
|};

Interceptors are configured using client configuration, e.g.

import {
  createPool
} from 'slonik';

const interceptors = [];

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

There are 2 functions that an interceptor can implement:

• beforeQuery
• afterQuery

Interceptors are executed in the order they are added.

beforeQuery

beforeQuery is the first interceptor function executed.

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

afterQuery

afterQuery is the last interceptor function executed.

Non-standard behaviour

• timestamp and timestamp with time zone returns UNIX timestamp in milliseconds.

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'})`);

Value placeholders

Anonymous placeholders

Slonik enables use of question mark (?) value placeholders, e.g.

await connection.query('SELECT ?', [
  1
]);

Question mark value placeholders are converted to positional value placeholders before they are passed to the pg driver, i.e. the above query becomes:

SELECT $1

Note: Mixing anonymous and position placeholders in a single query will result in an error.

A value set

A question mark is interpolated into a value set when the associated value is an array, e.g.

await connection.query('SELECT ?', [
  [
    1,
    2,
    3
  ]
]);

Produces:

SELECT ($1, $2, $3)

Multiple value sets

A question mark is interpolated into a list of value sets when the associated value is an array of arrays, e.g.

await connection.query('SELECT ?', [
  [
    [
      1,
      2,
      3
    ],
    [
      1,
      2,
      3
    ]
  ]
]);

Produces:

SELECT ($1, $2, $3), ($4, $5, $6)

Named placeholders

A :[a-zA-Z] regex is used to match named placeholders.

await connection.query('SELECT :foo', {
  foo: 'FOO'
});

Produces:

SELECT $1

Tagged template literals

Query methods can be executed using sql tagged template literal, e.g.

import {
  sql
} from 'slonik'

connection.query(sql`
  INSERT INTO reservation_ticket (reservation_id, ticket_id)
  VALUES ${values}
`);

Arguments of a tagged template literal invocation are replaced with an anonymous value placeholder, i.e. the latter query is equivalent to:

connection.query(sql`
  INSERT INTO reservation_ticket (reservation_id, ticket_id)
  VALUES ?
`, [
  values
]);

Creating dynamic delimited identifiers

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 ${'foo'}
  FROM ${sql.identifier(['bar', 'baz'])
}`;

// {
//   sql: 'SELECT ? FROM "bar"."baz"',
//   values: [
//     'foo'
//   ]
// }

Guarding against accidental unescaped input

When using tagged template literals, it is easy to forget to add the sql tag, i.e.

Instead of:

connection.query(sql`
  INSERT INTO reservation_ticket (reservation_id, ticket_id)
  VALUES ${values}
`);

Writing

connection.query(`
  INSERT INTO reservation_ticket (reservation_id, ticket_id)
  VALUES ${values}
`);

This would expose your application to SQL injection.

Therefore, I recommend using eslint-plugin-sql no-unsafe-query rule. no-unsafe-query warns about use of SQL inside of template literals without the sql tag.

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.

# Logs query parameter values
export SLONIK_LOG_VALUES=true

# Logs normalised query and input values
export SLONIK_LOG_NORMALISED=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.