@pgkit/client


A strongly-typed postgres client for node.js. Lets you execute SQL, without abstractions, safely.

Introduction

@pgkit/client is a PostgreSQL client, to be used in any application with a PostgreSQL database.

``ts
import {createClient} from '@pgkit/client'

const {sql} = createClient('postgresql://')

const profiles = await sql
select * from profile where email = ${getEmail()}

`

The basic idea is this: PostgreSQL is well designed. SQL as as language has been refined over decades, and its strengths, weaknesses and tradeoffs are widely known. You shouldn't let an ORM, or a query builder, introduce an unnecessary abstraction between your application and your database.

@pgkit/client allows you to write SQL queries - no matter how complex they may be, and whatever niche PostgreSQL features they may use. You will get precise TypeScript types for the results, without sacrificing the protection against SQL injection attacks that ORMs offer. See the types and protections sections for more details on how.

The API design is based on slonik - which remains an excellent SQL client. The reasons for using @pgkit/client over an ORM like prisma, or a query builder like knex.js, are the same as for slonik. For why this library exists, and why you might want to use @pgkit/client over slonik see the comparison with slonik section. The driver for @pgkit/client is pg-promise.

Ecosystem

@pgkit/client is the basis for these libraries:


- @pgkit/typegen - Automatically generates typescript types from SQL queries
- @pgkit/schemainspect - SQL Schema Inspection for PostgreSQL
- @pgkit/migra - A CLI to generate PostgeSQL schema diff scripts
- @pgkit/migrator - PostgeSQL migration tool
- @pgkit/admin - A zero-config PostgeSQL admin server, with schema inspection and autocomplete.


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.

Contents


- Introduction
- Ecosystem
- Protections
- Protection against SQL injection
- Protection against hanging connections
- Protection against hanging transactions
- Get started
- sql API
- sql.array
- sql.identifier
- sql.unnest
- jsonb_populate_recordset
- jsonb_to_recordset
- sql.join
- sql.fragment
- nested sql tag
- sql.interval
- sql.binary
- sql.json
- sql.jsonb
- JSON.stringify
- sql.literalValue
- transaction savepoints
- sql.type
- sql.type with valibot
- sql.type with custom error message
- createSqlTag + sql.typeAlias
- await sql - createSqlTag
- await sql - clientStorage
- await sql - bad type
- Types
- Automatic type generation
- Avoiding nullables
- Zod
- Recipes
- Inserting many rows with sql.unnest
- Query logging
- query timeouts
- switchable clients
- mocking
- Comparison with slonik
- Added features/improvements
- sql
- sql.raw
- Non-readonly output types
- Errors
- one error
- maybeOne error
- many error
- syntax error
- Missing features
- connection.stream
- Interceptors
- 👽 Future


Protections

$3

@pgkit/client uses a tagged template literal, usually imported as sql, to prevent SQL injection attacks. In a library like pg, or pg-promise (which are dependencies of this library, and this is _not_ the recommended way to use them!), you would be able to do something like this:

`ts
await pgPromise.query(
update profile
set name = '${req.body.name}'
)
`

Which will work for some users. Until an evil user sends a POST request looking something like { "name": "''; drop table profile" }. (In fact, with the above code, even an innocent user with an apostrophe in their name might be unable to update their profile!)

By contrast, here's how you would have to write this query in @pgkit/client:

`ts
await client.query(sql
update profile
set name = ${req.body.name}
)
`

@pgkit/client will handle Bobby Tables and Adrian O'Grady without any problem, because the query that's actually run in the underlying layer is:

`ts
await pgPromise.query('update profile set name = $1', req.body.name)
`

The above is more like the recommended way of using pg-promise. @pgkit/client doesn't let you pass a string to its various query methods - you must pass the result returned by the sql tag.

The idea is to make it _easy to do the right thing_, and _hard to do the wrong thing_.

(Note: if you really have a use case for avoiding the template tag, it's your foot and, your gun. See the docs on sql.raw for more info).

$3

Here's how you might connect to a pool in pg (taken directly from their docs):

`ts
await pg.connect()

const res = await pg.query('SELECT NOW()')
await pg.end()
`

This works fine for the above example, because you can be pretty sure that SELECT NOW() will always succeed. But what about when your query throws an error? The call to await client.end() will never run, and the connection will be left open. You can solve this by using try/finally every time you connect, but you might find your codebase quickly littered with boilerplate along these lines. And you are responsible for never forgetting to! Here's the equivalent in @pgkit/client:

`ts
const res = await client.connect(async connection => {
return connection.query(sqlSELECT NOW())
})
`

This will automatically release the connection when the callback _either_ resolves or throws.

$3

Similarly, transactions are automatically rolled back when they fail, and ended when they succeed:

`ts
await client.transaction(async tx => {
await tx.query(sqlupdate profile set name = ${req.body.name})
await tx.query(sqlupdate foo set bar = ${req.body.baz})
})
`

See sub-transactions and transaction savepoints for more involved examples.

Get started

Install as a dependency using npm (or yarn, or pnpm, or bun):

`
npm install @pgkit/client
`

Create a client and start querying with it:

`ts
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(sqlselect * 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})
)
})
}
`

sql API

The export you'll work with the most is the sql tag. This doubles as a a tagged template function, as well as a collection of helpers exposed as properties on the sql export.

The simplest usage is as above, just using sql as a tagged template function:

`ts
await client.query(sqlinsert into profile (id, name) values (1, 'one'))
`

Here's a usage example for each of the sql... methods:

$3

`typescript
const result = await client.any(sql
select *
from usage_test
where name = any(${sql.array(['one', 'two'], 'text')})
)
expect(result).toEqual([
{id: 1, name: 'one'},
{id: 2, name: 'two'},
])
`

$3

String parameters are formatted in as parameters. To use dynamic strings for schema names, table names, etc. you can use sql.identifier.

`typescript
const result = await client.oneFirst(sql
select count(1)
from ${sql.identifier(['public', 'usage_test'])}
)

expect(Number(result)).toEqual(3)
`

$3

sql.unnest lets you add many rows in a single query, without generating large SQL statements. It also lets you pass arrays of rows, which is more intuitive than arrays of columns.

`typescript
const values = [
{id: 11, name: 'eleven'},
{id: 12, name: 'twelve'},
{id: 13, name: 'thirteen'},
{id: 14, name: 'fourteen'},
]
const result = await client.any(sql
insert into usage_test(id, name)
select *
from ${sql.unnest(
values.map(({id, name}) => [id, name]),
['int4', 'text'],
)}
returning *
)

expect(result).toEqual([
{id: 11, name: 'eleven'},
{id: 12, name: 'twelve'},
{id: 13, name: 'thirteen'},
{id: 14, name: 'fourteen'},
])
`

$3

jsonb_populate_recordset is a PostgreSQL-native alternative to sql.unnest which can be used to insert multiple records without re-specify the column names/types.

`typescript
const records = [
{id: 11, name: 'eleven'},
{id: 12, name: 'twelve'},
{id: 13, name: 'thirteen'},
{id: 14, name: 'fourteen'},
]
const result = await client.any(sql
insert into usage_test
select *
from jsonb_populate_recordset(
null::usage_test,
${JSON.stringify(records, null, 2)}
)
returning *
)

expect(result).toEqual([
{id: 11, name: 'eleven'},
{id: 12, name: 'twelve'},
{id: 13, name: 'thirteen'},
{id: 14, name: 'fourteen'},
])
`

$3

jsonb_to_recordset is a PostgreSQL-native alternative to sql.unnest. It may have a slight performance advantage over jsonb_populate_recordset, at the cost of some manual typing, since it requires explicit columns.

`typescript
const records = [
{id: 11, name: 'eleven'},
{id: 12, name: 'twelve'},
{id: 13, name: 'thirteen'},
{id: 14, name: 'fourteen'},
]
const result = await client.any(sql
insert into usage_test
select *
from jsonb_to_recordset(
${JSON.stringify(records, null, 2)}
) as x(id int, name text)
returning *
)

expect(result).toEqual([
{id: 11, name: 'eleven'},
{id: 12, name: 'twelve'},
{id: 13, name: 'thirteen'},
{id: 14, name: 'fourteen'},
])
`

$3

sql.join lets you join multiple SQL fragments with a separator.

`typescript
const [result] = await client.any(sql
update usage_test
set ${sql.join([sqlname = 'one hundred', sqlid = 100], sql, )}
where id = 1
returning *
)

expect(result).toEqual({id: 100, name: 'one hundred'})
`

$3

Use sql.fragment to build reusable pieces which can be plugged into full queries.

`typescript
const idGreaterThan = (id: number) => sql.fragmentid > ${id}
const result = await client.any(sql
select * from usage_test where ${idGreaterThan(1)}
)

expect(result).toEqual([
{id: 2, name: 'two'},
{id: 3, name: 'three'},
])
`

$3

You can also use sql... to create a fragment of SQL, but it's recommended to use sql.fragment instead for explicitness. Support for type-generation is better using sql.fragment too.

`typescript
const idGreaterThan = (id: number) => sqlid > ${id}
const result = await client.any(sql
select * from usage_test where ${idGreaterThan(1)}
)

expect(result).toEqual([
{id: 2, name: 'two'},
{id: 3, name: 'three'},
])
`

$3

A strongly typed helper for creating a PostgreSQL interval. Note that you could also do something like '1 day'::interval, but this way avoids a cast and offers typescript types.

`typescript
const result = await client.oneFirst(sql
select '2000-01-01T12:00:00Z'::timestamptz + ${sql.interval({days: 1, hours: 1})} as ts
)
expect(result).toBeInstanceOf(Date)
expect(result).toMatchInlineSnapshot("2000-01-02T13:00:00.000Z")

const interval = await client.oneFirst(sqlselect ${sql.interval({days: 1})})
expect(interval).toMatchInlineSnapshot("1 day")
`

$3

Pass a buffer value from JavaScript to PostgreSQL.

`typescript
const result = await client.oneFirst(sql
select ${sql.binary(Buffer.from('hello'))} as b
)
expect(result).toMatchInlineSnapshot("\\\\x68656c6c6f")
`

$3

`typescript
await client.query(sql
drop table if exists jsonb_test;
create table jsonb_test (id int, data jsonb);
)

const insert = await client.one(sql
insert into jsonb_test values (1, ${sql.json({foo: 'bar'})})
returning *
)

expect(insert).toEqual({data: {foo: 'bar'}, id: 1})
`

$3

`typescript
const insert = await client.one(sql
insert into jsonb_test values (1, ${sql.jsonb({foo: 'bar'})})
returning *
)

expect(insert).toEqual({data: {foo: 'bar'}, id: 1})
`

$3

`typescript
const insert = await client.one(sql
insert into jsonb_test values (1, ${JSON.stringify({foo: 'bar'})})
returning *
)

expect(insert).toEqual({data: {foo: 'bar'}, id: 1})
`

$3

Use sql.literal to inject a raw SQL string into a query. It is escaped, so safe from SQL injection, but it's not parameterized, so should only be used where parameters are not possible, i.e. for non-optimizeable SQL commands.

From the PostgreSQL documentation:

>PL/pgSQL variable values can be automatically inserted into optimizable SQL commands, which are SELECT, INSERT, UPDATE, DELETE, MERGE, and certain utility commands that incorporate one of these, such as EXPLAIN and CREATE TABLE ... AS SELECT. In these commands, any PL/pgSQL variable name appearing in the command text is replaced by a query parameter, and then the current value of the variable is provided as the parameter value at run time. This is exactly like the processing described earlier for expressions.

For other statements, such as the below, you'll need to use sql.literalValue.

`typescript
const result = await client.transaction(async tx => {
await tx.query(sqlset local search_path to ${sql.literalValue('abc')})
return tx.one(sqlshow search_path)
})

expect(result).toEqual({search_path: 'abc'})
const result2 = await client.one(sqlshow search_path)
expect(result2?.search_path).toMatch(/\bpublic\b/)
`

$3

A sub-transaction can be created from within another transaction. Under the hood, @pgkit/client will generate savepoint statements, so that the sub-transactions can roll back if necessary.

`typescript
const log = vi.fn() // mock logger
await client.transaction(async t1 => {
await t1.query(sqldelete from usage_test)
await t1.query(sqlinsert into usage_test(id, name) values (10, 'ten'))
log('count 1', await t1.oneFirst(sqlselect count(1) from usage_test))

await t1
.transaction(async t2 => {
await t2.query(sqlinsert into usage_test(id, name) values (11, 'eleven'))

log('count 2', await t2.oneFirst(sqlselect count(1) from usage_test))

throw new Error(Uh-oh)
})
.catch(e => {
log('error', e)
})
})

log('count 3', await client.oneFirst(sqlselect count(1) from usage_test))

expect(log.mock.calls).toEqual([
['count 1', 1], // after initial insert
['count 2', 2], // after insert in sub-transaction
['error', expect.objectContaining({message: 'Uh-oh'})], // error causing sub-transaciton rollback
['count 3', 1], // back to count after initial insert - sub-transaction insert was rolled back to the savepoint
])

const newRecords = await client.any(sqlselect * from usage_test where id >= 10)
expect(newRecords).toEqual([{id: 10, name: 'ten'}])
`

$3

sql.type lets you use a zod schema (or another type validator) to validate the result of a query. See the Zod section for more details.

`typescript
const StringId = z.object({id: z.string()})
await expect(client.any(sql.type(StringId)select id::text from usage_test)).resolves.toMatchObject([
{id: '1'},
{id: '2'},
{id: '3'},
])

const error = await client.any(sql.type(StringId)select id from usage_test).catch(e => e)

expect(error).toMatchInlineSnapshot(
[QueryError]: [select-usage_test_8729cac]: Parsing rows failed: ✖ Expected string, received number → at id
Caused by: [StandardSchemaV1Error]: Standard Schema error - details in \issues\.
)
`

$3

`typescript
const StringId = v.object({id: v.string()})
await expect(client.any(sql.type(StringId)select id::text from usage_test)).resolves.toMatchObject([
{id: '1'},
{id: '2'},
{id: '3'},
])

const error = await client.any(sql.type(StringId)select id from usage_test).catch(e => e)

expect(error).toMatchInlineSnapshot(
[QueryError]: [select-usage_test_8729cac]: Parsing rows failed: ✖ Invalid type: Expected string but received 1 → at id
Caused by: [StandardSchemaV1Error]: Standard Schema error - details in \issues\.
)
`

$3

Wrap the query function to customize the error message

`typescript
client = createClient(client.connectionString(), {
...client.options,
pgpOptions: {
...client.options.pgpOptions,
connect: {
...client.options.pgpOptions?.connect,
application_name: 'impatient',
},
},
})
const StringId = z.object({id: z.string()})

const error = await client.any(sql.type(StringId)select id from usage_test).catch(e => e)

expect(error).toMatchInlineSnapshot(
[QueryError]: [select-usage_test_8729cac]: Parsing rows failed: ✖ Expected string, received number → at id
Caused by: [StandardSchemaV1Error]: Standard Schema error - details in \issues\.
)
`

$3

createSqlTag lets you create your own sql tag, which you can export and use instead of the deafult one, to add commonly-used schemas, which can be referred to by their key in the createSqlTag definition.

`typescript
const sql = createSqlTag({
typeAliases: {
Profile: z.object({
name: z.string(),
}),
},
})

const result = await client.one(sql.typeAlias('Profile')select 'Bob' as name)
expectTypeOf(result).toEqualTypeOf<{name: string}>()
expect(result).toEqual({name: 'Bob'})

const err = await client.any(sql.typeAlias('Profile')select 123 as name).catch(e => e)
expect(err).toMatchInlineSnapshot(
[QueryError]: [select_245d49b]: Parsing rows failed: ✖ Expected string, received number → at name
Caused by: [StandardSchemaV1Error]: Standard Schema error - details in \issues\.
)
`

$3

`typescript
const {sql} = client

const x = await sql<{a: number; b: number}>select 1 as a, 2 as b

expect(x).toEqual([{a: 1, b: 2}])
expect(x.one).toEqual({a: 1, b: 2})
expect(x.oneFirst).toEqual(1)
expect(x.maybeOneFirst).toEqual(1)
expect(x.many).toEqual([{a: 1, b: 2}])
expect(x.manyFirst).toEqual([1])
expectTypeOf(x).toMatchTypeOf<{a: number; b: number}[]>()
expectTypeOf(x.one).toEqualTypeOf<{a: number; b: number}>()

const Type = z.object({a: z.number(), b: z.number()})
const y = await sql.type(Type)select 1 as a, 2 as b
expect(y).toEqual([{a: 1, b: 2}])
expect(y.one).toEqual({a: 1, b: 2})
expect(y.oneFirst).toEqual(1)
expect(y.maybeOneFirst).toEqual(1)
expect(y.many).toEqual([{a: 1, b: 2}])
expect(y.manyFirst).toEqual([1])

expectTypeOf(y.manyFirst).toEqualTypeOf()
`

$3

`typescript
// you can't await sql... without providing a client first:
await expect(async () => await sqlselect 1).rejects.toMatchInlineSnapshot(
[Error]: No client provided to sql tag - either use \createSqlTag({client})\ or provide it with \pgkitStorage.run({client}, () => ...)\,
)

const results = await pgkitStorage.run({client}, async () => {
const x = await sql<{a: number; b: number}>select 1 as a, 2 as b

expect(x).toEqual([{a: 1, b: 2}])
expect(x.one).toEqual({a: 1, b: 2})
expect(x.oneFirst).toEqual(1)
expect(x.maybeOneFirst).toEqual(1)
expect(x.many).toEqual([{a: 1, b: 2}])
expect(x.manyFirst).toEqual([1])
expectTypeOf(x).toMatchTypeOf<{a: number; b: number}[]>()
expectTypeOf(x.one).toEqualTypeOf<{a: number; b: number}>()

const Type = z.object({a: z.number(), b: z.number()})
const yy = await sql.type(Type)select 1 as a, 2 as b
expect(yy).toEqual([{a: 1, b: 2}])
expect(yy.one).toEqual({a: 1, b: 2})
expect(yy.oneFirst).toEqual(1)
expect(yy.maybeOneFirst).toEqual(1)
expect(yy.many).toEqual([{a: 1, b: 2}])
expect(yy.manyFirst).toEqual([1])

expectTypeOf(yy.manyFirst).toEqualTypeOf()

return {xx: x, yy}
})

expect(results).toMatchInlineSnapshot(
{
"xx": [
{
"a": 1,
"b": 2
}
],
"yy": [
{
"a": 1,
"b": 2
}
]
}
)
`

$3

`typescript
const sql = createSqlTag({client})

const Foo = z.object({foo: z.number()})

await expect(async () => await sql.type(Foo)select 1 as bar).rejects.toMatchInlineSnapshot(

[QueryError]: [select_96972d5]: Parsing rows failed: ✖ Required → at foo
Caused by: [StandardSchemaV1Error]: Standard Schema error - details in \issues\.
,
)
`


Types

You can define the type for a query:

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

It is also possible to supply a generic type argument to the .any<...> method, but it's better to apply it to the query itself (i.e. sql<...>select ... ) since that decouples it from the query method you use:

`ts
type Profile = {id: string; name: string}
const profileQuery = sqlselect id, name from profile

const profiles = await client.any(profileQuery) // has type Profile[]
const profiles = await client.many(profileQuery) // has type Profile[]
const queryResult = await client.query(profileQuery) // has type {rows: Profile[]}
const profile = await client.one(profileQuery) // has type Profile
const maybeProfile = await client.maybeOne(profileQuery) // has type Profile | null
`

Automatic type generation

The companion library @pgkit/typegen will automatically typescript types to your queries, by analyzing the SQL. This offers a pretty unique developer experience. You get the type-safety of an ORM, but without the tradeoffs: no vendor lock-in, no having to learn how to use the ORM rather than PostgreSQL, no auto-generated slow queries, no arbitrary 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:

`ts
const profiles = await client.any(sqlselect * from profile)
`

Into this:


`ts
const profiles = await client.any(sqlselect * from profile)

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

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

$3

In some cases you will get types returned from the DB that are nullable e.g. name: string | null. By defaults types in postgres are nullable. The type generator tracks columns which are non-null, as well as conditions like name is not null to give a non-nullable type where possible. But in some cases, you might have application logic not known to the DB that a certain column is null. In those cases you can use the .noNulls helper on the client, which will throw an error if any rows contain any columns with nulls. The nulls will be stripped from the row type at compile-time and runtime:

`ts
const profile = await client.noNulls.one(
sqlselect * from profile limit 1,
)

profile.name.slice() // note: no need for a null check here, even though the Profile interface has name as nullable!

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

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

$3

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

`ts
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:

`ts
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)
`

You can also define safeParse, parseAsync or safeParseAsync as long as they match their zod equivalents:

`ts
import * as v from 'valibot'

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

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

You can use any zod features here. For example:


Transform rows:

`typescript
const Row = z.object({
id: z.number(),
label: z.string().nullable(),
location: z
.string()
.regex(/^-?\d+,-?\d+$/)
.transform(s => {
const [lat, lon] = s.split(',')
return {lat: Number(lat), lon: Number(lon)}
}),
})

const result = await client.any(sql.type(Row)
select * from zod_test
)

expectTypeOf(result).toEqualTypeOf<{id: number; label: string | null; location: {lat: number; lon: number}}[]>()

const result2 = await client.any(sql.type(Row)
select * from ${sql.identifier(['zod_test'])}
)

expect(result2).toEqual(result)

expect(result).toMatchInlineSnapshot(
[
{
"id": 1,
"label": "a",
"location": {
"lat": 70,
"lon": -108
}
},
{
"id": 2,
"label": "b",
"location": {
"lat": 71,
"lon": -102
}
},
{
"id": 3,
"label": null,
"location": {
"lat": 66,
"lon": -90
}
}
]
)
`

Refine schemas:

`typescript
const Row = z.object({
id: z.number().refine(n => n % 2 === 0, {message: 'id must be even'}),
name: z.string(),
})

const getResult = () =>
client.any(sql.type(Row)
select * from zod_test
)

await expect(getResult()).rejects.toMatchInlineSnapshot(
{
"message": "[select-zod_test_83bbed1]: Parsing rows failed: ✖ id must be even → at id\\n✖ Required → at name",
"query": {
"name": "select-zod_test_83bbed1",
"sql": "\\n select * from zod_test\\n ",
"token": "sql",
"values": []
},
"cause": {
"issues": [
{
"code": "custom",
"message": "id must be even",
"path": [
"id"
]
},
{
"code": "invalid_type",
"expected": "string",
"received": "undefined",
"path": [
"name"
],
"message": "Required"
}
]
}
}
)
`


Recipes

$3

`typescript
// Pass an array of rows to be inserted. There's only one variable in the generated SQL per column

await client.query(sql
insert into recipes_test(id, name)
select *
from ${sql.unnest(
[
[1, 'one'],
[2, 'two'],
[3, 'three'],
],
['int4', 'text'],
)}
)

expect(sqlProduced).toMatchInlineSnapshot(
[
{
"sql": "\\n insert into recipes_test(id, name)\\n select *\\n from unnest($1::int4[], $2::text[])\\n ",
"values": [
[
1,
2,
3
],
[
"one",
"two",
"three"
]
]
}
]
)
`

$3

`typescript
// Simplistic way of logging query times. For more accurate results, use process.hrtime()
const log = vi.fn()
const client = createClient('postgresql://postgres:postgres@localhost:5432/postgres', {
pgpOptions: {
connect: {
application_name: 'query-logger',
},
},
wrapQueryFn: queryFn => async query => {
const start = Date.now()
const result = await queryFn(query)
const end = Date.now()
log({start, end, took: end - start, query, result})
return result
},
})

await client.query(sqlselect * from recipes_test)

expect(log.mock.calls[0][0]).toMatchInlineSnapshot(
{
start: expect.any(Number),
end: expect.any(Number),
took: expect.any(Number),
},

{
"start": {
"inverse": false
},
"end": {
"inverse": false
},
"took": {
"inverse": false
},
"query": {
"name": "select-recipes_test_8d7ce25",
"sql": "select * from recipes_test",
"token": "sql",
"values": []
},
"result": {
"rows": [
{
"id": 1,
"name": "one"
},
{
"id": 2,
"name": "two"
},
{
"id": 3,
"name": "three"
}
],
"command": "SELECT",
"rowCount": 3,
"fields": [
{
"name": "id",
"tableID": "[tableID]",
"columnID": 1,
"dataTypeID": "[dataTypeID]",
"dataTypeSize": 4,
"dataTypeModifier": -1,
"format": "text"
},
{
"name": "name",
"tableID": "[tableID]",
"columnID": 2,
"dataTypeID": "[dataTypeID]",
"dataTypeSize": -1,
"dataTypeModifier": -1,
"format": "text"
}
]
}
}
,
)
`

$3

`typescript
const shortTimeoutMs = 20
const impatient = createClient(client.connectionString() + '?shortTimeout', {
pgpOptions: {
connect: {
query_timeout: shortTimeoutMs,
},
},
})
const patient = createClient(client.connectionString() + '?longTimeout', {
pgpOptions: {
connect: {
query_timeout: shortTimeoutMs * 3,
},
},
})

const sleepSeconds = (shortTimeoutMs * 2) / 1000
await expect(impatient.one(sqlselect pg_sleep(${sleepSeconds}))).rejects.toThrowErrorMatchingInlineSnapshot(

[QueryError]: [select_9dcc021]: Executing query failed: Query read timeout
{
"message": "[select_9dcc021]: Executing query failed: Query read timeout",
"query": {
"name": "select_9dcc021",
"sql": "select pg_sleep($1)",
"token": "sql",
"values": [
0.04
]
},
"cause": {
"name": "Error",
"message": "Query read timeout",
"query": "select pg_sleep(0.04)"
}
}
,
)
await expect(patient.one(sqlselect pg_sleep(${sleepSeconds}))).resolves.toMatchObject({
pg_sleep: '',
})
`

$3

You can use wrapQueryFn to dynamically choose different clients depending on the query type:

`typescript
const shortTimeoutMs = 20
const impatientClient = createClient(client.connectionString() + '?shortTimeout', {
pgpOptions: {
connect: {
query_timeout: shortTimeoutMs,
application_name: 'impatient',
},
},
})
const patientClient = createClient(client.connectionString() + '?longTimeout', {
pgpOptions: {
connect: {
query_timeout: shortTimeoutMs * 3,
application_name: 'patient',
},
},
})

const appClient = createClient(client.connectionString(), {
pgpOptions: {
connect: {
application_name: 'app',
},
},
wrapQueryFn: _queryFn => {
return async query => {
let clientToUse = patientClient
try {
// use https://www.npmjs.com/package/pgsql-ast-parser - just an example, you may want to do something like route
// readonly queries to a readonly connection, and others to a readwrite connection.
const parsed = pgSqlAstParser.parse(query.sql)
if (parsed.every(statement => statement.type === 'select')) {
// we know this is a select statement, use the client with the short timeout
clientToUse = impatientClient
}
} catch {
// couldn't parse the query, use the default client
}

return clientToUse.query(query)
}
},
})

const sleepSeconds = (shortTimeoutMs * 2) / 1000

await expect(
appClient.one(sql
select pg_sleep(${sleepSeconds})
),
).rejects.toThrowErrorMatchingInlineSnapshot(
[QueryError]: [select_6289211]: Executing query failed: Query read timeout
{
"message": "[select_6289211]: Executing query failed: Query read timeout",
"query": {
"name": "select_6289211",
"sql": "\\n select pg_sleep($1)\\n ",
"token": "sql",
"values": [
0.04
]
},
"cause": {
"name": "Error",
"message": "Query read timeout",
"query": "\\n select pg_sleep(0.04)\\n "
}
}
)
await expect(
appClient.one(sql
with delay as (
select pg_sleep(${sleepSeconds})
)
insert into recipes_test (id, name)
values (10, 'ten')
returning *
),
).resolves.toMatchObject({
id: 10,
name: 'ten',
})
`

$3

You can use wrapQueryFn to easily sub in an entirely different query mechanism:

`typescript
const fakeDb = pgMem.newDb() // https://www.npmjs.com/package/pg-mem
const client = createClient('postgresql://', {
wrapQueryFn: () => {
return async query => {
const formattedSql = pgMem.replaceQueryArgs$(query.sql, query.values)
const result = fakeDb.public.query(formattedSql)
return result as typeof result & {fields: FieldInfo[]}
}
},
})

await client.query(sqlcreate table recipes_test(id int, name text))

const insert = await client.one(sqlinsert into recipes_test(id, name) values (${10}, 'ten') returning *)
expect(insert).toMatchObject({id: 10, name: 'ten'})

const select = await client.any(sqlselect name from recipes_test)
expect(select).toMatchObject([{name: 'ten'}])
`


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 not carried over. I haven't come across any abstractions 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: Instead of interceptors, which require book-keeping in order to do things as simple as tracking query timings, there's an option to wrap the core query function this library calls. The wrapped function will be called for all query methods. 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.
- no stream support yet
- See future for more details/which parity features are planned

$3

#### 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-cases 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.

With slonik:

`ts
const profile = await slonik.one(sql.unsafeselect * from profile)
// 👆 has type any 👆 no generic typearg supported
`

With @pgkit/client:

`ts
const profile = await client.one(sqlselect * 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.

`ts
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):

`ts
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

Unfortunately, slonik's output types are marked readonly, which means they're unnecessarily 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:

`ts
const groupBy = (list: T[], fn: (item: T) => string) => {
const groups = {} as Record
list.forEach(item => (groups[fn(item)] ||= []).push(item))
return groups
}

const profiles = await slonik.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 avoidable by making sure _all_ utility functions take readonly inputs, but this is a pain, and not always practical when the utility function comes from a separate library, and sometimes even leads to unnecessary calls to .slice(), or dangerous casting, in practice.

#### Errors

Errors from the underlying driver are wrapped but the message is not changed. A prefix corresponding to the query name is added to the message.

For errors based on the number of rows returned (for one, oneFirst, many, manyFirst etc.) the query and result are added to the cause property.

$3

#### connection.stream

At time of writing, @pgkit/client does not support streaming queries yet. You can always drop down to the the pg-promise driving client to achieve this though.

#### Interceptors

Instead of interceptors, which require book-keeping in order to do things as simple as tracking query timings, there's an option to wrap the core query function this library calls. The wrapped function will be called for all query methods. For the other slonik interceptors, you can use pg-promise events.

👽 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, sqlselect * 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
- equivalents to the "Community interceptors" in the slonik docs - mostly as an implementation guide of how they can be achieved, and to verify that there's no functional loss from not having interceptors.