Slonik

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

[!NOTE] NEW! Use Slonik with eslint-plugin-slonik to validate your SQL queries against your database schema.

(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 events.
• 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
    • Events
  • 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
    • sql.uuid
    • sql.prepared
  • 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

"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: Data