@pgkit/client

A strongly-typed postgres client for node.js

Get started

npm install @pgkit/client

import {sql, createClient} from '@pgkit/client'

const client = createClient('postgres://postgres:postgres@localhost:5432/postgres')

export const getProfile = async (id: string) => {
    const profile = await client.one(sql`select * from profile where id = ${id}`)
    return {
        name: profile.name,
    }
}

export const updateProfileName = (id: string, name: string) => {
    await client.transaction(async tx => {
        const profile = await tx.one(sql`
            update profile set name = ${name} where id = ${id} returning *
        `)
        await tx.query(sql`
            insert into some_other_table (foo) values (${profile.foo})
        `)
    })
}

Types

The companion library @pgkit/typegen will add typescript types to your queries. This offers a pretty unique developer experience. You get the type-safety of an ORM, but without any of the tradeoffs: no vendor lock-in, no having to learn how to use the ORM rather than PostgreSQL, no non-performant queries, no limitations on the queries you can run.

Check out the typegen package for more details, but essentially it will analyse your SQL queries, and map PostgreSQL types to TypeScript, to transform code like this:

const profiles = await client.any(sql`select * from profile`)

Into this:

const profiles = await client.any(sql<queries.Profile>`select * from profile`)

declare namespace queries {
    // Generated by @pgkit/typegen

    export interface Profile {
        id: string
        name: string | null
    }
}

Alternatively, you can manually define the type for a query:

const profiles = await client.any(sql<{id: string; name: string}>`select * from profile`)
//    👆 will have type `Array<{id: string; name: string}>`

Zod

If you like, you can use zod to parse query results:

const Profile = z.object({id: z.string(), name: z.string()})

const profiles = await client.any(sql.type(Profile)`select * from profile`)

This will use zod to validate each row returned by your query.

Note that zod is not a dependency of this library, nor even a peer dependency. In fact, you could use a different library entirely, as long as you provide a "type" which has a parse method:

import * as v from 'valibot'

const ProfileSchema = v.object({id: v.string(), name: v.string()})
const Profile = {
    parse: (input: unknown) => v.parse(ProfileSchema, input),
}

const profiles = await client.any(sql.type(Profile)`select * from profile`)

Comparison with slonik

• The API is inspired by Slonik, or rather what Slonik used to be/I wish it still were. The "driver" for the client is pg-promise. But the query API and sql tag design is from Slonik. So, mostly, you can use this as a drop-in replacement for slonik.

Generally, usage of a client (or pool, to use the slonik term), should be identical. Initialization is likely different. Some differences which would likely require code changes if migrating from slonik:

• Most slonik initialization options are removed. I haven't come across any abstractions which invented by slonik which don't have simpler implementations in the underlying layer or in pg-promise. Specifically:

• type parsers: just use pg.types.setTypeParser. Some helper functions to achieve parity with slonik, and this library's recommendations are available, but they're trivial and you can just as easily implement them yourself.

• interceptors: these don't exist. Instead you can wrap the core query function this library calls. For the other slonik interceptors, you can use pg-promise events.

• custom errors: when a query produces an error, this library will throw an error with the corresponding message, along with a tag for the query which caused it. Slonik wraps each error type with a custom class. From a few years working with slonik, the re-thrown errors tend to make the useful information in the underlying error harder to find (less visible in Sentry, etc.). The purpose of the wrapper errors is to protect against potentially changing underlying errors, but there are dozens of breaking changes in Slonik every year, so pgkit opts to rely on the design and language chosen by PostgreSQL instead.

Added features/improvements

sql

Interestingly, slonik removed the ability to use the sql tag directly, when Gajus decided he wanted to start using zod parsers. There were many attempts to point out other use-case and options, but to no avail.

In slonik, you need to use sql.unsafe, which is untyped. Note that recommendation is never to use it, and there's been some indication that even this will go away, but there are many examples of its usage in the slonik readme:

const profile = await client.one(sql.unsafe`select * from profile`)
//    👆 has type `any`                    👆 no generic typearg supported

In @pgkit/client:

const profile = await client.one(sql<Profile>`select * from profile`)
//    👆 has type `Profile`

sql.raw

Slonik doesn't let you do this, but there are certain cases when you need to run a SQL query directly. Note that, as the name implies, this is somewhat dangerous. Make sure you trust the query you pass into this.

const query = 'select 1 as foo'
await client.query(sql.raw(query))

(P.S., even though slonik claims to not allow this - it doesn't actually have a a way to stop you. here's how in practice people achieve the same in slonik - it's more confusing to look at, but lacks the "raw" label despite being equally dangerous, which is why pgkit opts to support it):

const query = 'select 1 as foo'

const result = await pool.one({
    parser: z.any(),
    sql: query,
    type: 'SLONIK_TOKEN_QUERY',
    values: [],
})

Non-readonly output types

This one is my fault, really. I was the one who ported slonik to TypeScript, and I carried over some FlowType readonlys. Unfortunately, slonik's output types are marked readonly, which means they're quite awkward to work with. For example, you can't pass them to a normal utility function which hasn't marked its inputs as readonly (even if it doesn't mutate the array). For example:

const groupBy = <T>(list: T[], fn: (item: T) => string) => {
    // ...groupBy implementation
}
const profiles = await client.any(sql.type(Profile)`select * from profile`)

const byEmailHost = groupBy(profiles, p => p.email.split('@')[1])
//                          👆 type error: `profiles` is readonly, but groupBy accepts a regular array.

It's fixable by making sure all utility functions take readonly inputs, but this is a pain, and sometimes even leads to unnecessary calls to .slice(), or dangerous casting, in practice.

Ecosystem

@pgkit/client is the basis for these libraries:

• @pgkit/admin - A zero-config admin UI for running queries against PostgreSQL database, with autocomplete for tables, columns, views, functions etc.
• @pgkit/migra - A port of @djrobstep's python migra library.
• @pgkit/migrator - A cli migration tool for postgres, using pgkit.
• @pgkit/schemainspect - A port of @djrobstep's python schemainspect library.
• @pgkit/typegen - A library that uses pgkit to generate typescript interfaces based on your sql

Note that @pgkit/migra and @pgkit/schemainspect are pure ports of their Python equivalents. They are fantastically useful, and hopefully more and more can be built on top of them in the future.

👽 Future

Some features that will be added to @pgkit/client at some point, that may or may not be in slonik too:

• an analogue of the QueryFile concept in pg-promise. This will be integrated with @pgkit/typegen to enable an API something like await client.any(sql.file('my-file.sql'))
• an analogue of the PerparedStatement concept in pg-promise.
• support for specifying the types of parameters in SQL queries as well as results. For example, sql`select * from profile where id = ${x} and name = ${y} - we can add type-safety to ensure that x is a number, and y is a string (say)
• first-class support for query naming. Read Show Your Query You Love It By Naming It from the fantastic HoneyComb blog for some context on this.
• a pgkit monopackage, which exposes this client, as well as the above packages, as a single dependency. TBD on how to keep package size manageable - or whether that's important given this will almost always be used in a server environment