slonik

Slonik

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

• Usage
• Non-standard behaviour
• Conventions
  • No multiline values
• Value placeholders
  • A value set
  • Multiple value sets
  • Named placeholders
  • Tagged template literals
    • Guarding against accidental unescaped input
• Query methods
  • any
  • anyFirst
  • insert
  • many
  • manyFirst
  • maybeOne
  • maybeOneFirst
  • one
  • oneFirst
  • query
  • transaction
• Overriding Error Constructor
• Error handling
  • Handling NotFoundError
  • Handling DataIntegrityError
  • Handling UniqueViolationError
• Utilities
• Types
• Debugging
  • Logging
  • Long stack traces
• Syntax highlighting
  • Atom

Usage

import {
  createPool
} from 'slonik';

const connection = createPool({
  host: '127.0.0.1'
});

await connection.query('SELECT 1');

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(`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(`INSERT INTO foo (bar) VALUES (?)`, [
  '\n'
]);

Value 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

Do not mix question mark and positional value placeholders in a single query.

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('INSERT INTO reservation_ticket (reservation_id, ticket_id) VALUES ?', [
  values
]);

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.

Similar to #query except that it returns rows without fields information.

Example:

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

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('SELECT foo');

insert

Designed to use when inserting 1 row.

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

Example:

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

many

Returns result rows.

• Throws NotFoundError if query returns no rows.

Example:

const rows = await connection.many('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('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('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('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('SELECT foo');

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

Note:

I've got asked "How is 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('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.

Overriding Error Constructor

Overriding the error constructor used by Slonik allows you to map database layer errors to your application errors.

import {
  createPool
} from 'slonik';

class NotFoundError extends Error {};

createPool('postgres://', {
  errors: {
    NotFoundError
  }
});

The following error types can be overridden:

• NotFoundError

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) => {
  transactionConnection.query(`INSERT INTO foo (bar) VALUES ('baz')`);
  transactionConnection.query(`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('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('SELECT foo');
} catch (error) {
  if (error instanceof DataIntegrityError) {
    console.error('There is more than one row matching the select criteria.');
  } else {
    throw error;
  }
}

Handling UniqueViolationError

UniqueViolationError is thrown when Postgres responds with unique_violation (23505) 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 row = await connection
    .one('SELECT id FROM country WHERE code = ? LIMIT 2', [
      code
    ]);

  return Number(row.id);
};

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

Long stack traces

Slonik conditionally uses Bluebird when BLUEBIRD_DEBUG=1 is configured.

When Bluebird is run in a debug mode, then the stack trace is appended information about the origin of the query invocation, e.g.

error: duplicate key value violates unique constraint "public_movie_name_cinema_id0_idx"
    at Connection.parseE (/src/node_modules/slonik/node_modules/pg/lib/connection.js:553:11)
    at Connection.parseMessage (/src/node_modules/slonik/node_modules/pg/lib/connection.js:378:19)
    at Socket.<anonymous> (/src/node_modules/slonik/node_modules/pg/lib/connection.js:119:22)
    at Socket.emit (events.js:182:13)
    at addChunk (_stream_readable.js:277:12)
    at readableAddChunk (_stream_readable.js:262:11)
    at Socket.Readable.push (_stream_readable.js:217:10)
    at TCP.onread (net.js:638:20)
From previous event:
    at query (/src/node_modules/slonik/src/index.js:107:30)
    at one (/src/node_modules/slonik/src/index.js:162:13)
    at oneFirst (/src/node_modules/slonik/src/index.js:218:21)
    at Object.oneFirst (/src/node_modules/slonik/src/utilities/mapTaggedTemplateLiteralInvocation.js:17:14)
    at _default (/src/queries/insertCinemaMovieName.js:18:6)
    at upsertCinemaMovieName (/src/routines/uploadData.js:154:33)

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.