@silverhand/slonik

v31.0.0-beta.2

Published

9 months ago

A Node.js PostgreSQL client with strict types, detailed logging and assertions.

Downloads

2,004

0High
0Medium
0Low

gaosun

postgresql promise types

Slonik

A battle-tested Node.js PostgreSQL client with strict types, detailed logging and assertions.

Tailing Slonik logs

(The above GIF shows Slonik producing query logs. Slonik produces logs using Roarr. Logs include stack trace of the actual query invocation location and values used to execute the query.)

Principles

Promotes writing raw SQL.
Discourages ad-hoc dynamic generation of SQL.

Read: Stop using Knex.js

Note: Using this project does not require TypeScript. It is a regular ES6 module. Ignore the type definitions used in the documentation if you do not use a type system.

Features

About Slonik

Battle-Tested

Slonik began as a collection of utilities designed for working with node-postgres. It continues to use node-postgres driver as it provides a robust foundation for interacting with PostgreSQL. However, what once was a collection of utilities has since grown into a framework that abstracts repeating code patterns, protects against unsafe connection handling and value interpolation, and provides a rich debugging experience.

Slonik has been battle-tested with large data volumes and queries ranging from simple CRUD operations to data-warehousing needs.

Origin of the name

Slonik

The name of the elephant depicted in the official PostgreSQL logo is Slonik. The name itself is derived from the Russian word for "little elephant".

Read: The History of Slonik, the PostgreSQL Elephant Logo

Repeating code patterns and type safety

Among the primary reasons for developing Slonik, was the motivation to reduce the repeating code patterns and add a level of type safety. This is primarily achieved through the methods such as one, many, etc. But what is the issue? It is best illustrated with an example.

Suppose the requirement is to write a method that retrieves a resource ID given values defining (what we assume to be) a unique constraint. If we did not have the aforementioned helper methods available, then it would need to be written as:

import {
  sql,
  type DatabaseConnection
} from 'slonik';

type DatabaseRecordIdType = number;

const getFooIdByBar = async (connection: DatabaseConnection, bar: string): Promise<DatabaseRecordIdType> => {
  const fooResult = await connection.query(sql`
    SELECT id
    FROM foo
    WHERE bar = ${bar}
  `);

  if (fooResult.rowCount === 0) {
    throw new Error('Resource not found.');
  }

  if (fooResult.rowCount > 1) {
    throw new Error('Data integrity constraint violation.');
  }

  return fooResult[0].id;
};

oneFirst method abstracts all of the above logic into:

const getFooIdByBar = (connection: DatabaseConnection, bar: string): Promise<DatabaseRecordIdType> => {
  return connection.oneFirst(sql`
    SELECT id
    FROM foo
    WHERE bar = ${bar}
  `);
};

oneFirst throws:

NotFoundError if query returns no rows
DataIntegrityError if query returns multiple rows
DataIntegrityError if query returns multiple columns

In the absence of helper methods, the overhead of repeating code becomes particularly visible when writing routines where multiple queries depend on the proceeding query results. Using methods with inbuilt assertions ensures that in case of an error, the error points to the source of the problem. In contrast, unless assertions for all possible outcomes are typed out as in the previous example, the unexpected result of the query will be fed to the next operation. If you are lucky, the next operation will simply break; if you are unlucky, you are risking data corruption and hard-to-locate bugs.

Furthermore, using methods that guarantee the shape of the results allows us to leverage static type checking and catch some of the errors even before executing the code, e.g.

const fooId = await connection.many(sql`
  SELECT id
  FROM foo
  WHERE bar = ${bar}
`);

await connection.query(sql`
  DELETE FROM baz
  WHERE foo_id = ${fooId}
`);

Static type check of the above example will produce a warning as the fooId is guaranteed to be an array and binding of the last query is expecting a primitive value.

Protecting against unsafe connection handling

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

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

// This is not valid Slonik API

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

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

  await connection.release();
};

In this example, if SELECT foo() produces an error, then connection is never released, i.e. the connection hangs indefinitely.

A fix to the above is to ensure that connection#release() is always called, i.e.

// This is not valid Slonik API

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

  let lastExecutionResult;

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

  return lastExecutionResult;
};

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

const main = () => {
  return pool.connect((connection) => {
    return connection.query(sql`SELECT foo()`);
  });
};

Using this pattern, we guarantee that connection is always released as soon as the connect() routine resolves or is rejected.

Protecting against unsafe transaction handling

Just like in the unsafe connection handling example, Slonik only allows to create a transaction for the duration of the promise routine supplied to the connection#transaction() method.

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

This pattern ensures that the transaction is either committed or aborted the moment the promise is either resolved or rejected.

Protecting against unsafe value interpolation

SQL injections are one of the most well known attack vectors. Some of the biggest data leaks were the consequence of improper user-input handling. In general, SQL injections are easily preventable by using parameterization and by restricting database permissions, e.g.

// This is not valid Slonik API

connection.query('SELECT $1', [
  userInput
]);

In this example, the query text (SELECT $1) and parameters (userInput) are passed separately to the PostgreSQL server where the parameters are safely substituted into the query. This is a safe way to execute a query using user-input.

The vulnerabilities appear when developers cut corners or when they do not know about parameterization, i.e. there is a risk that someone will instead write:

// This is not valid Slonik API

connection.query('SELECT \'' + userInput + '\'');

As evident by the history of the data leaks, this happens more often than anyone would like to admit. This security vulnerability is especially a significant risk in Node.js community, where a predominant number of developers are coming from frontend and have not had training working with RDBMSes. Therefore, one of the key selling points of Slonik is that it adds multiple layers of protection to prevent unsafe handling of user input.

To begin with, Slonik does not allow running plain-text queries.

// This is not valid Slonik API

connection.query('SELECT 1');

The above invocation would produce an error:

TypeError: Query must be constructed using sql tagged template literal.

This means that the only way to run a query is by constructing it using sql tagged template literal, e.g.

connection.query(sql`SELECT 1`);

To add a parameter to the query, user must use template literal placeholders, e.g.

connection.query(sql`SELECT ${userInput}`);

Slonik takes over from here and constructs a query with value bindings, and sends the resulting query text and parameters to PostgreSQL. There is no other way of passing parameters to the query – this adds a strong layer of protection against accidental unsafe user input handling due to limited knowledge of the SQL client API.

As Slonik restricts user's ability to generate and execute dynamic SQL, it provides helper functions used to generate fragments of the query and the corresponding value bindings, e.g. sql.identifier, sql.join and sql.unnest. These methods generate tokens that the query executor interprets to construct a safe query, e.g.

connection.query(sql`
  SELECT ${sql.identifier(['foo', 'a'])}
  FROM (
    VALUES
    (
      ${sql.join(
        [
          sql.join(['a1', 'b1', 'c1'], sql`, `),
          sql.join(['a2', 'b2', 'c2'], sql`, `)
        ],
        sql`), (`
      )}
    )
  ) foo(a, b, c)
  WHERE foo.b IN (${sql.join(['c1', 'a2'], sql`, `)})
`);

This (contrived) example generates a query equivalent to:

SELECT "foo"."a"
FROM (
  VALUES
    ($1, $2, $3),
    ($4, $5, $6)
) foo(a, b, c)
WHERE foo.b IN ($7, $8)

This query is executed with the parameters provided by the user.

To sum up, Slonik is designed to prevent accidental creation of queries vulnerable to SQL injections.

Documentation

Usage

Connection URI

Slonik client is configured using a custom connection URI (DSN).

postgresql://[user[:password]@][host[:port]][/database name][?name=value[&...]]

Supported parameters:

|Name|Meaning|Default| |---|---|---| |application_name|application_name|| |sslmode|sslmode (supported values: disable, no-verify, require)|disable|

Note that unless listed above, other libpq parameters are not supported.

Examples of valid DSNs:

postgresql://
postgresql://localhost
postgresql://localhost:5432
postgresql://localhost/foo
postgresql://foo@localhost
postgresql://foo:bar@localhost
postgresql://foo@localhost/bar?application_name=baz

Other configurations are available through the clientConfiguration parameter.

Create connection

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

import {
  createPool,
} from 'slonik';

const pool = await 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.

End connection pool

Use pool.end() to end idle connections and prevent creation of new connections.

The result of pool.end() is a promise that is resolved when all connections are ended.

import {
  createPool,
  sql,
} from 'slonik';

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

const main = async () => {
  await pool.query(sql`
    SELECT 1
  `);

  await pool.end();
};

main();

Note: pool.end() does not terminate active connections/ transactions.

Describing the current state of the connection pool

Use pool.getPoolState() to find out if pool is alive and how many connections are active and idle, and how many clients are waiting for a connection.

import {
  createPool,
  sql,
} from 'slonik';

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

const main = async () => {
  pool.getPoolState();

  // {
  //   activeConnectionCount: 0,
  //   ended: false,
  //   idleConnectionCount: 0,
  //   waitingClientCount: 0,
  // }

  await pool.connect(() => {
    pool.getPoolState();

    // {
    //   activeConnectionCount: 1,
    //   ended: false,
    //   idleConnectionCount: 0,
    //   waitingClientCount: 0,
    // }
  });

  pool.getPoolState();

  // {
  //   activeConnectionCount: 0,
  //   ended: false,
  //   idleConnectionCount: 1,
  //   waitingClientCount: 0,
  // }

  await pool.end();

  pool.getPoolState();

  // {
  //   activeConnectionCount: 0,
  //   ended: true,
  //   idleConnectionCount: 0,
  //   waitingClientCount: 0,
  // }
};

main();

Note: pool.end() does not terminate active connections/ transactions.

API

/**
 * @param connectionUri PostgreSQL [Connection URI](https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING).
 */
createPool(
  connectionUri: string,
  clientConfiguration: ClientConfiguration
): DatabasePool;

/**
 * @property captureStackTrace Dictates whether to capture stack trace before executing query. Middlewares access stack trace through query execution context. (Default: false)
 * @property connectionRetryLimit Number of times to retry establishing a new connection. (Default: 3)
 * @property connectionTimeout Timeout (in milliseconds) after which an error is raised if connection cannot be established. (Default: 5000)
 * @property idleInTransactionSessionTimeout Timeout (in milliseconds) after which idle clients are closed. Use 'DISABLE_TIMEOUT' constant to disable the timeout. (Default: 60000)
 * @property idleTimeout Timeout (in milliseconds) after which idle clients are closed. Use 'DISABLE_TIMEOUT' constant to disable the timeout. (Default: 5000)
 * @property interceptors An array of [Slonik interceptors](https://github.com/gajus/slonik#slonik-interceptors).
 * @property maximumPoolSize Do not allow more than this many connections. Use 'DISABLE_TIMEOUT' constant to disable the timeout. (Default: 10)
 * @property PgPool Override the underlying PostgreSQL Pool constructor.
 * @property queryRetryLimit Number of times a query failing with Transaction Rollback class error, that doesn't belong to a transaction, is retried. (Default: 5)
 * @property ssl [tls.connect options](https://nodejs.org/api/tls.html#tlsconnectoptions-callback)
 * @property statementTimeout Timeout (in milliseconds) after which database is instructed to abort the query. Use 'DISABLE_TIMEOUT' constant to disable the timeout. (Default: 60000)
 * @property transactionRetryLimit Number of times a transaction failing with Transaction Rollback class error is retried. (Default: 5)
 * @property typeParsers An array of [Slonik type parsers](https://github.com/gajus/slonik#slonik-type-parsers).
 */
type ClientConfiguration = {
  captureStackTrace?: boolean,
  connectionRetryLimit?: number,
  connectionTimeout?: number | 'DISABLE_TIMEOUT',
  idleInTransactionSessionTimeout?: number | 'DISABLE_TIMEOUT',
  idleTimeout?: number | 'DISABLE_TIMEOUT',
  interceptors?: Interceptor[],
  maximumPoolSize?: number,
  PgPool?: new (poolConfig: PoolConfig) => PgPool,
  queryRetryLimit?: number,
  ssl?: Parameters<tls.connect>[0],
  statementTimeout?: number | 'DISABLE_TIMEOUT',
  transactionRetryLimit?: number,
  typeParsers?: TypeParser[],
};

Example:

import {
  createPool
} from 'slonik';

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

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

Default configuration

Default interceptors

None.

Check out slonik-interceptor-preset for an opinionated collection of interceptors.

Default type parsers

These type parsers are enabled by default:

|Type name|Implementation| |---|---| |date|Produces a literal date as a string (format: YYYY-MM-DD).| |int8|Produces an integer.| |interval|Produces interval in seconds (integer).| |numeric|Produces a float.| |timestamp|Produces a unix timestamp (in milliseconds).| |timestamptz|Produces a unix timestamp (in milliseconds).|

To disable the default type parsers, pass an empty array, e.g.

createPool('postgres://', {
  typeParsers: []
});

You can create default type parser collection using createTypeParserPreset, e.g.

import {
  createTypeParserPreset
} from 'slonik';

createPool('postgres://', {
  typeParsers: [
    ...createTypeParserPreset()
  ]
});

Default timeouts

There are 4 types of configurable timeouts:

|Configuration|Description|Default| |---|---|---| |connectionTimeout|Timeout (in milliseconds) after which an error is raised if connection cannot be established.|5000| |idleInTransactionSessionTimeout|Timeout (in milliseconds) after which idle clients are closed. Use 'DISABLE_TIMEOUT' constant to disable the timeout.|60000| |idleTimeout|Timeout (in milliseconds) after which idle clients are closed. Use 'DISABLE_TIMEOUT' constant to disable the timeout.|5000| |statementTimeout|Timeout (in milliseconds) after which database is instructed to abort the query. Use 'DISABLE_TIMEOUT' constant to disable the timeout.|60000|

Slonik sets aggressive timeouts by default. These timeouts are designed to provide safe interface to the database. These timeouts might not work for all programs. If your program has long running statements, consider adjusting timeouts just for those statements instead of changing the defaults.

Known limitations of using pg-native with Slonik

notice logs are not captured in notices query result property (notice event is never fired on connection instance).
cannot combine multiple commands into a single statement (pg-native limitation #88)
does not support streams.

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 pool#connect() method.

import {
  createPool,
} from 'slonik';

const pool = await 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.

Read: Protecting against unsafe connection handling

Mocking Slonik

Slonik provides a way to mock queries against the database.

Use createMockPool to create a mock connection.
Use createMockQueryResult to create a mock query result.

import {
  createMockPool,
  createMockQueryResult,
} from 'slonik';

type OverridesType =
  query: (sql: string, values: PrimitiveValueExpression[],) => Promise<QueryResult<QueryResultRow>>,
};

createMockPool(overrides: OverridesType): DatabasePool;
createMockQueryResult(rows: QueryResultRow[]): QueryResult<QueryResultRow>;

Example:

import {
  createMockPool,
  createMockQueryResult,
} from 'slonik';

const pool = createMockPool({
  query: async () => {
    return createMockQueryResult([
      {
        foo: 'bar',
      },
    ]);
  },
});

await pool.connect(async (connection) => {
  const results = await connection.query(sql`
    SELECT ${'foo'}
  `);
});

How are they different?

pg vs slonik

pg is built intentionally to provide unopinionated, minimal abstraction and encourages use of other modules to implement convenience methods.

Slonik is built on top of pg and it provides convenience methods for building queries and querying data.

Work on pg began on Tue Sep 28 22:09:21 2010. It is authored by Brian Carlson.

pg-promise vs slonik

As the name suggests, pg-promise was originally built to enable use of pg module with promises (at the time, pg only supported Continuation Passing Style (CPS), i.e. callbacks). Since then pg-promise added features for connection/ transaction handling, a powerful query-formatting engine and a declarative approach to handling query results.

The primary difference between Slonik and pg-promise:

Slonik does not allow to execute raw text queries. Slonik queries can only be constructed using sql tagged template literals. This design protects against unsafe value interpolation.
Slonik implements interceptor API (middleware). Middlewares allow to modify connection handling, override queries and modify the query results. Example Slonik interceptors include field name transformation, query normalization and query benchmarking.

Note: Author of pg-promise has objected to the above claims. I have removed a difference that was clearly wrong. I maintain that the above two differences remain valid differences: even though pg-promise might have substitute functionality for variable interpolation and interceptors, it implements them in a way that does not provide the same benefits that Slonik provides, namely: guaranteed security and support for extending library functionality using multiple plugins.

Other differences are primarily in how the equivalent features are implemented, e.g.

|pg-promise|Slonik| |---|---| |Custom type formatting.|Not available in Slonik. The current proposal is to create an interceptor that would have access to the query fragment constructor.| |formatting filters|Slonik tagged template value expressions to construct query fragments and bind parameter values.| |Query files.|Use slonik-sql-tag-raw.| |Tasks.|Use pool.connect.| |Configurable transactions.|Not available in Slonik. Track this issue.| |Events.|Use interceptors.|

When weighting which abstraction to use, it would be unfair not to consider that pg-promise is a mature project with dozens of contributors. Meanwhile, Slonik is a young project (started in March 2017) that until recently was developed without active community input. However, if you do support the unique features that Slonik adds, the opinionated API design, and are not afraid of adopting a technology in its young days, then I warmly invite you to adopt Slonik and become a contributor to what I intend to make the standard PostgreSQL client in the Node.js community.

Work on pg-promise began Wed Mar 4 02:00:34 2015. It is authored by Vitaly Tomilov.

postgres vs slonik

postgres recently gained in popularity due to its performance benefits when compared to pg. In terms of API, it has a pretty bare-bones API that heavily relies on using ES6 tagged templates and abstracts away many concepts of connection pool handling. While postgres API might be preferred by some, projects that already use pg may have difficulty migrating.

However, by using postgres-bridge (postgres/pg compatibility layer), you can benefit from postgres performance improvements while still using Slonik API:

import postgres from 'postgres';
import { createPostgresBridge } from 'postgres-bridge';
import { createPool } from 'slonik';
const PostgresBridge = createPostgresBridge(postgres);
const pool = createPool('postgres://', {
  PgPool: PostgresBridge,
});

Type parsers

Type parsers describe how to parse PostgreSQL types.

type TypeParser = {
  name: string,
  parse: (value: string) => *
};

Example:

{
  name: 'int8',
  parse: (value) => {
    return parseInt(value, 10);
  }
}

Note: Unlike pg-types that uses OIDs to identify types, Slonik identifies types using their names.

Use this query to find type names:

SELECT typname
FROM pg_type
ORDER BY typname ASC

Type parsers are configured using typeParsers client configuration.

Read: Default type parsers.

Built-in type parsers

|Type name|Implementation|Factory function name| |---|---|---| |date|Produces a literal date as a string (format: YYYY-MM-DD).|createDateTypeParser| |int8|Produces an integer.|createBigintTypeParser| |interval|Produces interval in seconds (integer).|createIntervalTypeParser| |numeric|Produces a float.|createNumericTypeParser| |timestamp|Produces a unix timestamp (in milliseconds).|createTimestampTypeParser| |timestamptz|Produces a unix timestamp (in milliseconds).|createTimestampWithTimeZoneTypeParser|

Built-in type parsers can be created using the exported factory functions, e.g.

import {
  createTimestampTypeParser
} from 'slonik';

createTimestampTypeParser();

// {
//   name: 'timestamp',
//   parse: (value) => {
//     return value === null ? value : Date.parse(value + ' UTC');
//   }
// }

Interceptors

Functionality can be added to Slonik client by adding interceptors (middleware).

Interceptors are configured using client configuration, e.g.

import {
  createPool
} from 'slonik';

const interceptors = [];

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

Interceptors are executed in the order they are added.

Read: Default interceptors.

Interceptor methods

Interceptor is an object that implements methods that can change the behaviour of the database client at different stages of the connection life-cycle

type Interceptor = {
  afterPoolConnection?: (
    connectionContext: ConnectionContext,
    connection: DatabasePoolConnection
  ) => MaybePromise<null>,
  afterQueryExecution?: (
    queryContext: QueryContext,
    query: Query,
    result: QueryResult<QueryResultRow>
  ) => MaybePromise<QueryResult<QueryResultRow>>,
  beforePoolConnection?: (
    connectionContext: ConnectionContext
  ) => MaybePromise<?DatabasePool>,
  beforePoolConnectionRelease?: (
    connectionContext: ConnectionContext,
    connection: DatabasePoolConnection
  ) => MaybePromise<null>,
  beforeQueryExecution?: (
    queryContext: QueryContext,
    query: Query
  ) => MaybePromise<QueryResult<QueryResultRow>> | MaybePromise<null>,
  beforeQueryResult?: (
    queryContext: QueryContext,
    query: Query,
    result: QueryResult<QueryResultRow>
  ) => MaybePromise<null>,
  beforeTransformQuery?: (
    queryContext: QueryContext,
    query: Query
  ) => Promise<null>,
  queryExecutionError?: (
    queryContext: QueryContext,
    query: Query,
    error: SlonikError
  ) => MaybePromise<null>,
  transformQuery?: (
    queryContext: QueryContext,
    query: Query
  ) => Query,
  transformRow?: (
    queryContext: QueryContext,
    query: Query,
    row: QueryResultRow,
    fields: Field[],
  ) => QueryResultRow
};

afterPoolConnection

Executed after a connection is acquired from the connection pool (or a new connection is created), e.g.

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

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

afterQueryExecution

Executed after query has been executed and before rows were transformed using transformRow.

Note: When query is executed using stream, then afterQuery is called with empty result set.

beforeQueryExecution

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

beforeQueryResult

Executed just before the result is returned to the client.

Use this method to capture the result that will be returned to the client.

beforeTransformQuery

Executed before transformQuery. Use this interceptor to capture the original query (e.g. for logging purposes).

beforePoolConnection

Executed before connection is created.

This function can optionally return a pool to another database, causing a connection to be made to the new pool.

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. ↓
});

queryExecutionError

Executed if query execution produces an error.

Use queryExecutionError to log and/ or re-throw another error.

transformQuery

Executed before beforeQueryExecution.

Transforms query.

transformRow

Executed for each row.

Transforms row.

Use transformRow to modify the query result.

Community interceptors

|Name|Description| |---|---| |slonik-interceptor-field-name-transformation|Transforms Slonik query result field names.| |slonik-interceptor-query-benchmarking|Benchmarks Slonik queries.| |slonik-interceptor-query-cache|Caches Slonik queries.| |slonik-interceptor-query-logging|Logs Slonik queries.| |slonik-interceptor-query-normalisation|Normalises Slonik queries.|

Check out slonik-interceptor-preset for an opinionated collection of interceptors.

Recipes

Inserting large number of rows

Use sql.unnest to create a set of rows using unnest. Using the unnest approach requires only 1 variable per every column; values for each column are passed as an array, e.g.

await connection.query(sql`
  INSERT INTO foo (bar, baz, qux)
  SELECT *
  FROM ${sql.unnest(
    [
      [1, 2, 3],
      [4, 5, 6]
    ],
    [
      'int4',
      'int4',
      'int4'
    ]
  )}
`);

Produces:

{
  sql: 'INSERT INTO foo (bar, baz, qux) SELECT * FROM unnest($1::int4[], $2::int4[], $3::int4[])',
  values: [
    [
      1,
      4
    ],
    [
      2,
      5
    ],
    [
      3,
      6
    ]
  ]
}

Inserting data this way ensures that the query is stable and reduces the amount of time it takes to parse the query.

Routing queries to different connections

If connection is initiated by a query (as opposed to a obtained explicitly using pool#connect()), then beforePoolConnection interceptor can be used to change the pool that will be used to execute the query, e.g.

const slavePool = await createPool('postgres://slave');
const masterPool = await createPool('postgres://master', {
  interceptors: [
    {
      beforePoolConnection: (connectionContext, pool) => {
        if (connectionContext.query && connectionContext.query.sql.includes('SELECT')) {
          return slavePool;
        }

        return pool;
      }
    }
  ]
});

// This query will use `postgres://slave` connection.
masterPool.query(sql`SELECT 1`);

// This query will use `postgres://master` connection.
masterPool.query(sql`UPDATE 1`);

Building Utility Statements

Parameter symbols only work in optimizable SQL commands (SELECT, INSERT, UPDATE, DELETE, and certain commands containing one of these). In other statement types (generically called utility statements, e.g. ALTER, CREATE, DROP and SET), you must insert values textually even if they are just data values.

In the context of Slonik, if you are building utility statements you must use query building methods that interpolate values directly into queries:

sql.identifier – for identifiers.
sql.literalValue – for values.

Example:

await connection.query(sql`
  CREATE USER ${sql.identifier(['foo'])}
  WITH PASSWORD ${sql.literalValue('bar')}
`);

Runtime validation

Slonik integrates zod to provide runtime query result validation and static type inference.

Runtime validation is added by defining a zod object and passing it to sql.type tagged template.

Motivation

Build-time type safety guarantees that your application will work as expected at the time of the build (assuming that the types are correct in the first place).

The problem is that once you deploy the application, the database schema might change independently of the codebase. This drift may result in your application behaving in unpredictable and potentially dangerous ways, e.g., imagine if table product changed price from numeric to text. Without runtime validation, this would cause a cascade of problems and potential database corruption. Even worse, without runtime checks, this could go unnoticed for a long time.

In contrast, by using runtime checks, you can ensure that the contract between your codebase and the database is always respected. If there is a breaking change, the application fails with a loud error that is easy to debug.

By using zod, we get the best of both worlds: type safety and runtime checks.

Example use of sql.type

Let's assume that you have a PostgreSQL table person:

CREATE TABLE "public"."person" (
  "id" integer GENERATED ALWAYS AS IDENTITY,
  "name" text NOT NULL,
  PRIMARY KEY ("id")
);

and you want to retrieve all persons in the database, along with their id and name:

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

With your knowledge of the database schema, define a zod object:

const personObject = z.object({
  id: z.number(),
  name: z.string(),
});

Update your query to use sql.type tag and pass personObject:

const personQuery = sql.type(personObject)`
  SELECT id, name
  FROM person
`;

Finally, query the database using typed sql tagged template:

const persons = await connection.any(personQuery);

With this information, Slonik guarantees that every member of persons is an object that has properties id and name, which are a non-null number and a non-null string respectively.

Performance penalty

In the context of the network overhead, validation accounts for a tiny amount of the total execution time.

Just to give an idea, in our sample of data, it takes sub 0.1ms to validate 1 row, ~3ms to validate 1,000 and ~25ms to validate 100,000 rows.

Unknown keys

Slonik disallows unknown keys, i.e. query that returns {foo: 'bar', baz: 'qux'} with z.object({foo: z.string()}) schema will produce SchemaValidationError error.

Handling schema validation errors

If query produces a row that does not satisfy zod object, then SchemaValidationError error is thrown.

SchemaValidationError includes properties that describe the query and validation errors:

sql – SQL of the query that produced unexpected row.
row – row data that did not satisfy the schema.
issues – array of unmet expectations.

Whenever this error occurs, the same information is also included in the logs.

In most cases, you shouldn't attempt to handle these errors at individual query level – allow to propagate to the top of the application and fix the issue when you become aware of it.

However, in cases such as dealing with unstructured data, it might be useful to handle these errors at a query level, e.g.

import {
  SchemaValidationError
} from 'slonik';
try {
} catch (error) {
  if (error extends SchemaValidationError) {
    // Handle scheme validation error
  }
}

Inferring types

You can infer the TypeScript type of the query result. There are couple of ways of doing it:

// Infer using z.infer<typeof yourSchema>
// https://github.com/colinhacks/zod#type-inference
type Person = z.infer<typeof personObject>;
// from sql tagged template `zodObject` property
type Person = z.infer<
  personQuery.zodObject
>;

Transforming results

Using zod transform you can refine the result shape and its type, e.g.

const coordinatesType = z.string().transform((subject) => {
  const [
    x,
    y,
  ] = subject.split(',');

  return {
    x: Number(x),
    y: Number(y),
  };
});

const zodObject = z.object({
  foo: coordinatesType,
});

const query = sql.type(zodObject)`SELECT '1,2' as foo`;

const result = await pool.one(query);

expectTypeOf(result).toMatchTypeOf<{foo: {x: number, y: number, }, }>();

t.deepEqual(result, {
  foo: {
    x: 1,
    y: 2,
  },
});

sql tag

sql tag serves two purposes:

It is used to construct queries with bound parameter values (see Value placeholders).
It used to generate dynamic query fragments (see Query building).

sql tag can be imported from Slonik package:

import {
  sql
} from 'slonik';

Sometimes it may be desirable to construct a custom instance of sql tag. In those cases, you can use the createSqlTag factory, e.g.

import {
  createSqlTag
} from 'slonik';

const sql = createSqlTag();

Typing sql tag

sql has a generic interface, meaning that you can supply it with the type that represents the expected result of the query, e.g.

type Person = {
  id: number,
  name: string,
};

const query = sql<Person>`
  SELECT id, name
  FROM person
`;

// onePerson has a type of Person
const onePerson = await connection.one(query);

// persons has a type of Person[]
const persons = await connection.many(query);

As you see, query helper methods (one, many, etc.) infer the result type based on the type associated with the sql tag instance.

However, you should avoid passing types directly to sql and instead use runtime validation. Runtime validation produces typed sql tags, but also validates the results of the query.

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.

Manually constructing the query

Manually constructing queries is not allowed.

There is an internal mechanism that checks to see if query was created using sql tagged template literal, i.e.

const query = {
  sql: 'SELECT 1 FROM foo WHERE bar = $1',
  type: 'SQL',
  values: [
    'baz'
  ]
};

connection.query(query);

Will result in an error:

Query must be constructed using sql tagged template literal.

This is a security measure designed to prevent unsafe query execution.

Furthermore, a query object constructed using sql tagged template literal is frozen to prevent further manipulation.

Nesting sql

sql tagged template literals can be nested, e.g.

const query0 = sql`SELECT ${'foo'} FROM bar`;
const query1 = sql`SELECT ${'baz'} FROM (${query0})`;

Produces:

{
  sql: 'SELECT $1 FROM (SELECT $2 FROM bar)',
  values: [
    'baz',
    'foo'
  ]
}

Query building

Queries are built using methods of the sql tagged template literal.

If this is your first time using Slonik, read Dynamically generating SQL queries using Node.js.

sql.array

(
  values: PrimitiveValueExpression[],
  memberType: TypeNameIdentifier | SqlToken
) => ArraySqlToken;

Creates an array value binding, e.g.

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

Produces:

{
  sql: 'SELECT $1::"int4"[]',
  values: [
    [
      1,
      2,
      3
    ]
  ]
}

sql.array memberType

If memberType is a string (TypeNameIdentifier), then it is treated as a type name identifier and will be quoted using double quotes, i.e. sql.array([1, 2, 3], 'int4') is equivalent to $1::"int4"[]. The implication is that keywords that are often used interchangeably with type names are not going to work, e.g. int4 is a type name identifier and will work. However, int is a keyword and will not work. You can either use type name identifiers or you can construct custom member using sql tag, e.g.

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

Produces:

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

sql.array vs sql.join

Unlike sql.join, sql.array generates a stable query of a predictable length, i.e. regardless of the number of values in the array, the generated query remains the same:

Having a stable query enables pg_stat_statements to aggregate all query execution statistics.
Keeping the query length short reduces query parsing time.

Example:

sql`SELECT id FROM foo WHERE id IN (${sql.join([1, 2, 3], sql`, `)})`;
sql`SELECT id FROM foo WHERE id NOT IN (${sql.join([1, 2, 3], sql`, `)})`;

Is equivalent to:

sql`SELECT id FROM foo WHERE id = ANY(${sql.array([1, 2, 3], 'int4')})`;
sql`SELECT id FROM foo WHERE id != ALL(${sql.array([1, 2, 3], 'int4')})`;

Furthermore, unlike sql.join, sql.array can be used with an empty array of values. In short, sql.array should be preferred over sql.join when possible.

sql.binary

(
  data: Buffer
) => BinarySqlToken;

Binds binary (bytea) data, e.g.

await connection.query(sql`
  SELECT ${sql.binary(Buffer.from('foo'))}
`);

Produces:

{
  sql: 'SELECT $1',
  values: [
    Buffer.from('foo')
  ]
}

sql.date

(
  date: Date
) => DateSqlToken;

Inserts a date, e.g.

await connection.query(sql`
  SELECT ${sql.date(new Date('2022-08-19T03:27:24.951Z'))}
`);

Produces:

{
  sql: 'SELECT $1::date',
  values: [
    '2022-08-19'
  ]
}

sql.identifier

(
  names: string[],
) => IdentifierSqlToken;

Delimited identifiers are created by enclosing an arbitrary sequence of characters in double-quotes ("). To 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"."baz"',
  values: []
}

sql.interval

(
  interval: {
    years?: number,
    months?: number,
    weeks?: number,
    days?: number,
    hours?: number,
    minutes?: number,
    seconds?: number,
  }
) => IntervalSqlToken;

Inserts an interval, e.g.

sql`
  SELECT 1
  FROM ${sql.interval({days: 3})}
`;

Produces:

{
  sql: 'SELECT make_interval("days" => $1)',
  values: [
    3
  ]
}

You can use sql.interval exactly how you would use PostgreSQL make_interval function. However, notice that Slonik does not use abbreviations, i.e. "secs" is seconds and "mins" is minutes.

|make_interval|sql.interval|Interval output| |---|---|---| |make_interval("days" => 1, "hours" => 2)|sql.interval({days: 1, hours: 2})|1 day 02:00:00| |make_interval("mins" => 1)|sql.interval({minutes: 1})|00:01:00| |make_interval("secs" => 120)|sql.interval({seconds: 120})|00:02:00| |make_interval("secs" => 0.001)|sql.interval({seconds: 0.001})|00:00:00.001|

Dynamic intervals without sql.interval

If you need a dynamic interval (e.g. X days), you can achieve this using multiplication, e.g.

sql`
  SELECT ${2} * interval '1 day'
`

The above is equivalent to interval '2 days'.

You could also use make_interval() directly, e.g.

sql`
  SELECT make_interval("days" => ${2})
`

sql.interval was added mostly as a type-safe alternative.

sql.join

(
  members: SqlSqlToken[],
  glue: SqlSqlToken
) => ListSqlToken;

Concatenates SQL expressions using glue separator, e.g.

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

Produces:

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

sql.join is the primary building block for most of the SQL, e.g.

Boolean expressions:

sql`
  SELECT ${sql.join([1, 2], sql` AND `)}
`

// SELECT $1 AND $2

Tuple:

sql`
  SELECT (${sql.join([1, 2], sql`, `)})
`

// SELECT ($1, $2)

Tuple list:

sql`
  SELECT ${sql.join(
    [
      sql`(${sql.join([1, 2], sql`, `)})`,
      sql`(${sql.join([3, 4], sql`, `)})`,
    ],
    sql`, `
  )}
`

// SELECT ($1, $2), ($3, $4)

sql.json

(
  value: SerializableValue
) => JsonSqlToken;

Serializes value and binds it as a JSON string literal, e.g.

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

Produces:

{
  sql: 'SELECT $1:

Published

Vulnerabilities

Links

Maintainers

Keywords

Readme

Slonik

Sponsors

Principles

Features

Contents

About Slonik

Battle-Tested

Origin of the name

Repeating code patterns and type safety

Protecting against unsafe connection handling

Protecting against unsafe transaction handling

Protecting against unsafe value interpolation

Documentation

Usage

Connection URI

Create connection

End connection pool

Describing the current state of the connection pool

API

Default configuration

Default interceptors

Default type parsers

Default timeouts

Known limitations of using pg-native with Slonik

Checking out a client from the connection pool

Mocking Slonik

How are they different?

pg vs slonik

pg-promise vs slonik

postgres vs slonik

Type parsers

Built-in type parsers

Interceptors

Interceptor methods

afterPoolConnection

afterQueryExecution

beforeQueryExecution

beforeQueryResult

beforeTransformQuery

beforePoolConnection

beforePoolConnectionRelease

queryExecutionError

transformQuery

transformRow

Community interceptors

Recipes

Inserting large number of rows

Routing queries to different connections

Building Utility Statements

Runtime validation

Motivation

Example use of sql.type

Performance penalty

Unknown keys

Handling schema validation errors

Inferring types

Transforming results

sql tag

Typing sql tag

Value placeholders

Tagged template literals

Manually constructing the query

Nesting sql

Query building

sql.array

sql.array memberType

sql.array vs sql.join

sql.binary

sql.date

sql.identifier

sql.interval

Dynamic intervals without sql.interval

sql.join

sql.json