gajus/slonik

Slonik

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

Principles

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

Read: Stop using Knex.js

Features

Runtime validation.
Assertions and type safety.
Safe connection handling.
Safe transaction handling.
Safe value interpolation.
Transaction nesting.
Transaction events.
Transaction retrying.
Query retrying.
Detailed logging.
Asynchronous stack trace resolution.
Middlewares.
Mapped errors.
ESLint plugin.

Slonik

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

"Słonik" is a Polish diminutive of "słoń," meaning “little elephant” or “baby elephant.” The word "słoń" itself comes from Proto-Slavic *slonъ, which was likely borrowed from a Germanic language and may ultimately trace back to Latin.

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.typeAlias("id")`
    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.typeAlias("id")`
    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.typeAlias("id")`
  SELECT id
  FROM foo
  WHERE bar = ${bar}
`);

await connection.query(sql.typeAlias("void")`
  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.typeAlias("foo")`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.typeAlias("foo")`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.typeAlias("foo")`SELECT foo()`);
  });
};

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

Resetting connection state

After the connection is released, Slonik resets the connection state. This is to prevent connection state from leaking between queries.

The default behaviour is to execute DISCARD ALL command. This behaviour can be adjusted by configuring resetConnection routine, e.g.

import { createPool, sql } from "slonik";
import { createPgDriverFactory } from "@slonik/pg-driver";

const pool = await createPool("postgres://", {
  driverFactory: createPgDriverFactory(),
  resetConnection: async (connection) => {
    await connection.query("DISCARD ALL");
  },
});

Note

Resetting a connection is a heavy operation. Depending on the application requirements, it may make sense to disable connection reset, e.g.

import { createPool } from "slonik";
import { createPgDriverFactory } from "@slonik/pg-driver";

const pool = await createPool("postgres://", {
  driverFactory: createPgDriverFactory(),
  resetConnection: async () => {},
});

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.typeAlias("void")`INSERT INTO foo (bar) VALUES ('baz')`);
  await transactionConnection.query(sql.typeAlias("void")`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.

Note

If you receive an error UnexpectedForeignConnectionError, then you are trying to execute a query using a connection that is not associated with the transaction. This error is thrown to prevent accidental unsafe transaction handling, e.g.

pool.transaction(async (transactionConnection) => {
  await pool.query(sql.typeAlias("void")`INSERT INTO foo (bar) VALUES ('baz')`);
});

In this example, the query is executed using the connection that is not associated with the transaction. This is unsafe because the query is not part of the transaction and will not be rolled back if the transaction is aborted. This behaviour can be disabled by setting dangerouslyAllowForeignConnections to true in the ClientConfiguration.

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.unsafe`SELECT 1`);

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

connection.query(sql.unsafe`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.unsafe`
  SELECT ${sql.identifier(["foo", "a"])}
  FROM (
    VALUES
    (
      ${sql.join(
        [
          sql.join(["a1", "b1", "c1"], sql.fragment`, `),
          sql.join(["a2", "b2", "c2"], sql.fragment`, `),
        ],
        sql.fragment`), (`,
      )}
    )
  ) foo(a, b, c)
  WHERE foo.b IN (${sql.join(["c1", "a2"], sql.fragment`, `)})
`);

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.

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`
`options`	`options`
`sslcert`	The location of the certificate.	-
`sslkey`	The location of the certificate.	-
`sslmode`	`sslmode` (supported values: `disable`, `no-verify`, `require`)	`disable`
`sslrootcert`	The location of the root certificate file.

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

Unix-domain socket connection is chosen if the host part is either empty or looks like an absolute path name.

postgresql:///dbname?host=/var/lib/postgresql
postgresql://%2Fvar%2Flib%2Fpostgresql/dbname

Other configurations are available through the clientConfiguration parameter.

Dynamic passwords

The password configuration option supports async callbacks, enabling IAM-based database authentication (AWS RDS IAM, GCP Cloud SQL IAM, Azure AD tokens) where credentials expire and must be refreshed per connection.

import { createPool } from "slonik";
import { createPgDriverFactory } from "@slonik/pg-driver";
import { generateRdsAuthToken } from "./iam.js";

const pool = await createPool(
  "postgres://iam_user@mydb.us-east-1.rds.amazonaws.com:5432/mydb?sslmode=require",
  {
    driverFactory: createPgDriverFactory(),
    password: async () => generateRdsAuthToken(),
  },
);

When provided, the password option overrides any password in the connection URI. It accepts a static string or a callback returning a string (sync or async). The callback is invoked by the underlying pg driver each time a new connection is established.

Create connection

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

import { createPool } from "slonik";
import { createPgDriverFactory } from "@slonik/pg-driver";

const pool = await createPool("postgres://", {
  driverFactory: createPgDriverFactory(),
});

Note: If you are new to Slonik, then you should read Integrating Slonik with Express.js.

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

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

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.typeAlias("id")`SELECT 1 AS id`);

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";
import { createPgDriverFactory } from "@slonik/pg-driver";

const pool = await createPool("postgres://", {
  driverFactory: createPgDriverFactory(),
});

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

  await pool.end();
};

main();

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

Describing the current state of the connection pool

Use pool.state() 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";
import { createPgDriverFactory } from "@slonik/pg-driver";

const pool = await createPool("postgres://", {
  driverFactory: createPgDriverFactory(),
});

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

  // {
  //   acquiredConnections: 0,
  //   idleConnections: 0,
  //   pendingDestroyConnections: 0,
  //   pendingReleaseConnections: 0,
  //   state: 'ACTIVE',
  //   waitingClients: 0,
  // }

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

    // {
    //   acquiredConnections: 1,
    //   idleConnections: 0,
    //   pendingDestroyConnections: 0,
    //   pendingReleaseConnections: 0,
    //   state: 'ACTIVE',
    //   waitingClients: 0,
    // }
  });

  pool.state();

  // {
  //   acquiredConnections: 0,
  //   idleConnections: 1,
  //   pendingDestroyConnections: 0,
  //   pendingReleaseConnections: 0,
  //   state: 'ACTIVE',
  //   waitingClients: 0,
  // }

  await pool.end();

  pool.state();

  // {
  //   acquiredConnections: 0,
  //   idleConnections: 0,
  //   pendingDestroyConnections: 0,
  //   pendingReleaseConnections: 0,
  //   state: 'ENDED',
  //   waitingClients: 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?: ClientConfigurationInput
): Promise<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 dangerouslyAllowForeignConnections Allow using connections that are not associated with the transaction. (Default: false)
 * @property driverFactory Overrides the default DriverFactory. (Default: "pg" driver factory)
 * @property gracefulTerminationTimeout Timeout (in milliseconds) that kicks in after a connection with an active query is requested to end. This is the amount of time that is allowed for query to complete before terminating it. (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#interceptors).
 * @property maximumConnectionAge The maximum age of a connection allowed in the pool. After this age, the connection will be destroyed. Use 'DISABLE_TIMEOUT' constant to disable the timeout. (Default: 30 minutes)
 * @property maximumPoolSize (Deprecated: use maxPoolSize) Do not allow more than this many connections. (Default: 10)
 * @property maxPoolSize Do not allow more than this many connections. (Default: 10)
 * @property minimumPoolSize (Deprecated: use minPoolSize) Ensure that at least this many connections are available in the pool. (Default: 0)
 * @property minPoolSize Ensure that at least this many connections are available in the pool. (Default: 0)
 * @property password Overrides the password in the connection URI. Accepts a string, or a callback (sync or async) that is invoked for every new connection. See [Dynamic passwords](#dynamic-passwords).
 * @property poolName Human-readable identifier for distinguishing multiple pools within the same application, e.g. "read", "write", "replica-us-east-1".
 * @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 resetConnection Routine that is invoked to reset the connection after it is released. (Default: executes `DISCARD ALL`) See [Resetting connection state](#resetting-connection-state).
 * @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 tracing Controls whether Slonik creates OpenTelemetry spans. (Default: false)
 * @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#type-parsers).
 */
type ClientConfigurationInput = {
  captureStackTrace?: boolean,
  connectionRetryLimit?: number,
  connectionTimeout?: number | 'DISABLE_TIMEOUT',
  dangerouslyAllowForeignConnections?: boolean,
  driverFactory?: DriverFactory,
  gracefulTerminationTimeout?: number,
  idleInTransactionSessionTimeout?: number | 'DISABLE_TIMEOUT',
  idleTimeout?: number | 'DISABLE_TIMEOUT',
  interceptors?: readonly Interceptor[],
  maximumConnectionAge?: number | 'DISABLE_TIMEOUT',
  maximumPoolSize?: number, // deprecated, use maxPoolSize
  maxPoolSize?: number,
  minimumPoolSize?: number, // deprecated, use minPoolSize
  minPoolSize?: number,
  password?: string | (() => Promise<string> | string),
  poolName?: string,
  queryRetryLimit?: number,
  resetConnection?: (basicConnection: BasicConnection) => Promise<void>,
  ssl?: tls.ConnectionOptions,
  statementTimeout?: number | 'DISABLE_TIMEOUT',
  tracing?: boolean,
  transactionRetryLimit?: number,
  typeParsers?: readonly DriverTypeParser[],
};

type BasicConnection = {
  readonly query: (query: string) => Promise<void>;
};

Example:

import { createPool } from "slonik";
import { createPgDriverFactory } from "@slonik/pg-driver";

const pool = await createPool("postgres://", {
  driverFactory: createPgDriverFactory(),
});

await pool.query(sql.typeAlias("id")`SELECT 1 AS id`);

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.

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

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

import { createTypeParserPreset } from "slonik";
import { createPgDriverFactory } from "@slonik/pg-driver";

await createPool("postgres://", {
  driverFactory: createPgDriverFactory(),
  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

pg-native is not officially supported by Slonik.

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";
import { createPgDriverFactory } from "@slonik/pg-driver";

const pool = await createPool("postgres://localhost", {
  driverFactory: createPgDriverFactory(),
});

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

  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.

Events

The DatabasePool extends DatabasePoolEventEmitter and exposes the following events:

error: (error: SlonikError) => void – emitted for all errors that happen within the pool.

import { createPool, type DatabasePoolEventEmitter } from "slonik";
import { createPgDriverFactory } from "@slonik/pg-driver";

const pool = await createPool("postgres://localhost", {
  driverFactory: createPgDriverFactory(),
});

pool.on("error", (error) => {
  console.error(error);
});

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.

Type parsers

Type parsers describe how to parse PostgreSQL types.

type DriverTypeParser&l

Slonik

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

Principles

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

Read: Stop using Knex.js

Slonik

About Slonik

Battle-Tested

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

Origin of the name

Repeating code patterns and type safety

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

type DatabaseRecordIdType = number;

const getFooIdByBar = async (
  connection: DatabaseConnection,
  bar: string,
): Promise<DatabaseRecordIdType> => {
  const fooResult = await connection.query(sql.typeAlias("id")`
    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.typeAlias("id")`
    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

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.typeAlias("id")`
  SELECT id
  FROM foo
  WHERE bar = ${bar}
`);

await connection.query(sql.typeAlias("void")`
  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.typeAlias("foo")`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.typeAlias("foo")`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.typeAlias("foo")`SELECT foo()`);
  });
};

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

Resetting connection state

After the connection is released, Slonik resets the connection state. This is to prevent connection state from leaking between queries.

The default behaviour is to execute DISCARD ALL command. This behaviour can be adjusted by configuring resetConnection routine, e.g.

import { createPool, sql } from "slonik";
import { createPgDriverFactory } from "@slonik/pg-driver";

const pool = await createPool("postgres://", {
  driverFactory: createPgDriverFactory(),
  resetConnection: async (connection) => {
    await connection.query("DISCARD ALL");
  },
});

Note

Resetting a connection is a heavy operation. Depending on the application requirements, it may make sense to disable connection reset, e.g.

import { createPool } from "slonik";
import { createPgDriverFactory } from "@slonik/pg-driver";

const pool = await createPool("postgres://", {
  driverFactory: createPgDriverFactory(),
  resetConnection: async () => {},
});

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.typeAlias("void")`INSERT INTO foo (bar) VALUES ('baz')`);
  await transactionConnection.query(sql.typeAlias("void")`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.

Note

pool.transaction(async (transactionConnection) => {
  await pool.query(sql.typeAlias("void")`INSERT INTO foo (bar) VALUES ('baz')`);
});

Protecting against unsafe value interpolation

// This is not valid Slonik API

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

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 + "'");

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.unsafe`SELECT 1`);

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

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

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

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.

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`
`options`	`options`
`sslcert`	The location of the certificate.	-
`sslkey`	The location of the certificate.	-
`sslmode`	`sslmode` (supported values: `disable`, `no-verify`, `require`)	`disable`
`sslrootcert`	The location of the root certificate file.

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

Unix-domain socket connection is chosen if the host part is either empty or looks like an absolute path name.

postgresql:///dbname?host=/var/lib/postgresql
postgresql://%2Fvar%2Flib%2Fpostgresql/dbname

Other configurations are available through the clientConfiguration parameter.

Dynamic passwords

import { createPool } from "slonik";
import { createPgDriverFactory } from "@slonik/pg-driver";
import { generateRdsAuthToken } from "./iam.js";

const pool = await createPool(
  "postgres://iam_user@mydb.us-east-1.rds.amazonaws.com:5432/mydb?sslmode=require",
  {
    driverFactory: createPgDriverFactory(),
    password: async () => generateRdsAuthToken(),
  },
);

Create connection

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

import { createPool } from "slonik";
import { createPgDriverFactory } from "@slonik/pg-driver";

const pool = await createPool("postgres://", {
  driverFactory: createPgDriverFactory(),
});

Note: If you are new to Slonik, then you should read Integrating Slonik with Express.js.

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

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

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.typeAlias("id")`SELECT 1 AS id`);

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";
import { createPgDriverFactory } from "@slonik/pg-driver";

const pool = await createPool("postgres://", {
  driverFactory: createPgDriverFactory(),
});

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

  await pool.end();
};

main();

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

Describing the current state of the connection pool

Use pool.state() 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";
import { createPgDriverFactory } from "@slonik/pg-driver";

const pool = await createPool("postgres://", {
  driverFactory: createPgDriverFactory(),
});

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

  // {
  //   acquiredConnections: 0,
  //   idleConnections: 0,
  //   pendingDestroyConnections: 0,
  //   pendingReleaseConnections: 0,
  //   state: 'ACTIVE',
  //   waitingClients: 0,
  // }

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

    // {
    //   acquiredConnections: 1,
    //   idleConnections: 0,
    //   pendingDestroyConnections: 0,
    //   pendingReleaseConnections: 0,
    //   state: 'ACTIVE',
    //   waitingClients: 0,
    // }
  });

  pool.state();

  // {
  //   acquiredConnections: 0,
  //   idleConnections: 1,
  //   pendingDestroyConnections: 0,
  //   pendingReleaseConnections: 0,
  //   state: 'ACTIVE',
  //   waitingClients: 0,
  // }

  await pool.end();

  pool.state();

  // {
  //   acquiredConnections: 0,
  //   idleConnections: 0,
  //   pendingDestroyConnections: 0,
  //   pendingReleaseConnections: 0,
  //   state: 'ENDED',
  //   waitingClients: 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?: ClientConfigurationInput
): Promise<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 dangerouslyAllowForeignConnections Allow using connections that are not associated with the transaction. (Default: false)
 * @property driverFactory Overrides the default DriverFactory. (Default: "pg" driver factory)
 * @property gracefulTerminationTimeout Timeout (in milliseconds) that kicks in after a connection with an active query is requested to end. This is the amount of time that is allowed for query to complete before terminating it. (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#interceptors).
 * @property maximumConnectionAge The maximum age of a connection allowed in the pool. After this age, the connection will be destroyed. Use 'DISABLE_TIMEOUT' constant to disable the timeout. (Default: 30 minutes)
 * @property maximumPoolSize (Deprecated: use maxPoolSize) Do not allow more than this many connections. (Default: 10)
 * @property maxPoolSize Do not allow more than this many connections. (Default: 10)
 * @property minimumPoolSize (Deprecated: use minPoolSize) Ensure that at least this many connections are available in the pool. (Default: 0)
 * @property minPoolSize Ensure that at least this many connections are available in the pool. (Default: 0)
 * @property password Overrides the password in the connection URI. Accepts a string, or a callback (sync or async) that is invoked for every new connection. See [Dynamic passwords](#dynamic-passwords).
 * @property poolName Human-readable identifier for distinguishing multiple pools within the same application, e.g. "read", "write", "replica-us-east-1".
 * @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 resetConnection Routine that is invoked to reset the connection after it is released. (Default: executes `DISCARD ALL`) See [Resetting connection state](#resetting-connection-state).
 * @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 tracing Controls whether Slonik creates OpenTelemetry spans. (Default: false)
 * @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#type-parsers).
 */
type ClientConfigurationInput = {
  captureStackTrace?: boolean,
  connectionRetryLimit?: number,
  connectionTimeout?: number | 'DISABLE_TIMEOUT',
  dangerouslyAllowForeignConnections?: boolean,
  driverFactory?: DriverFactory,
  gracefulTerminationTimeout?: number,
  idleInTransactionSessionTimeout?: number | 'DISABLE_TIMEOUT',
  idleTimeout?: number | 'DISABLE_TIMEOUT',
  interceptors?: readonly Interceptor[],
  maximumConnectionAge?: number | 'DISABLE_TIMEOUT',
  maximumPoolSize?: number, // deprecated, use maxPoolSize
  maxPoolSize?: number,
  minimumPoolSize?: number, // deprecated, use minPoolSize
  minPoolSize?: number,
  password?: string | (() => Promise<string> | string),
  poolName?: string,
  queryRetryLimit?: number,
  resetConnection?: (basicConnection: BasicConnection) => Promise<void>,
  ssl?: tls.ConnectionOptions,
  statementTimeout?: number | 'DISABLE_TIMEOUT',
  tracing?: boolean,
  transactionRetryLimit?: number,
  typeParsers?: readonly DriverTypeParser[],
};

type BasicConnection = {
  readonly query: (query: string) => Promise<void>;
};

Example:

import { createPool } from "slonik";
import { createPgDriverFactory } from "@slonik/pg-driver";

const pool = await createPool("postgres://", {
  driverFactory: createPgDriverFactory(),
});

await pool.query(sql.typeAlias("id")`SELECT 1 AS id`);

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.

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

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

import { createTypeParserPreset } from "slonik";
import { createPgDriverFactory } from "@slonik/pg-driver";

await createPool("postgres://", {
  driverFactory: createPgDriverFactory(),
  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

Known limitations of using pg-native with Slonik

pg-native is not officially supported by Slonik.

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";
import { createPgDriverFactory } from "@slonik/pg-driver";

const pool = await createPool("postgres://localhost", {
  driverFactory: createPgDriverFactory(),
});

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

  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.

Events

The DatabasePool extends DatabasePoolEventEmitter and exposes the following events:

error: (error: SlonikError) => void – emitted for all errors that happen within the pool.

import { createPool, type DatabasePoolEventEmitter } from "slonik";
import { createPgDriverFactory } from "@slonik/pg-driver";

const pool = await createPool("postgres://localhost", {
  driverFactory: createPgDriverFactory(),
});

pool.on("error", (error) => {
  console.error(error);
});

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`

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.

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.

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

`postgres` vs `slonik`

Type parsers

Type parsers describe how to parse PostgreSQL types.

type DriverTypeParser&l

gajus/slonik（新しいタブで開きます）

README

Slonik

Principles

Features

Contents

About Slonik

Battle-Tested

Origin of the name

Repeating code patterns and type safety

Protecting against unsafe connection handling

Resetting connection state

Protecting against unsafe transaction handling

Protecting against unsafe value interpolation

Usage

Connection URI

Dynamic passwords

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

Events

How are they different?

pg vs slonik

pg-promise vs slonik

postgres vs slonik

Type parsers

README

Slonik

Principles

Features

Contents

`pg` vs `slonik`

`pg-promise` vs `slonik`

`postgres` vs `slonik`