Slonik

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

(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.)

Sponsors

If you value my work and want to see Slonik and many other of my Open-Source projects to be continuously improved, then please consider becoming a patron:

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

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

Contents

• 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
    • Checking out a client from the connection pool
  • How are they different?
    • pg vs slonik
    • pg-promise vs slonik
    • postgres vs slonik
  • Type parsers
    • Built-in type parsers
  • Interceptors
    • Interceptor methods
    • Community interceptors
  • Recipes
    • Inserting large number of rows
    • Routing queries to different connections
    • Building Utility Statements
    • Inserting vector data
  • Runtime validation
    • Motivation
    • Result parser interceptor
    • Example use of sql.type
    • Performance penalty
    • Unknown keys
    • Handling schema validation errors
    • Inferring types
    • Transforming results
  • sql tag
    • Type aliases
    • Typing sql tag
  • Value placeholders
    • Tagged template literals
    • Manually constructing the query
    • Nesting sql
  • Query building
    • sql.array
    • sql.binary
    • sql.date
    • sql.fragment
    • sql.identifier
    • sql.interval
    • sql.join
    • sql.json
    • sql.jsonb
    • sql.literalValue
    • sql.timestamp
    • sql.unnest
    • sql.unsafe
  • Query methods
    • any
    • anyFirst
    • exists
    • many
    • manyFirst
    • maybeOne
    • maybeOneFirst
    • one
    • oneFirst
    • query
    • stream
    • transaction
  • Utilities
    • parseDsn
    • stringifyDsn
  • Error handling
    • Original node-postgres error
    • Handling BackendTerminatedError
    • Handling CheckIntegrityConstraintViolationError
    • Handling ConnectionError
    • Handling DataIntegrityError
    • Handling ForeignKeyIntegrityConstraintViolationError
    • Handling NotFoundError
    • Handling NotNullIntegrityConstraintViolationError
    • Handling StatementCancelledError
    • Handling StatementTimeoutError
    • Handling UniqueIntegrityConstraintViolationError
    • Handling TupleMovedToAnotherPartitionError
  • Migrations
  • Types
  • Debugging
    • Logging
    • Capture stack trace
  • Syntax Highlighting
    • Atom Syntax Highlighting Plugin
    • VS Code Syntax Highlighting Extension
  • Development

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

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.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';

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

[!NOTE] Reseting 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';

const pool = createPool('postgres://', {
  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