Usage

Setup

db.ts

import { createDb, createSQL } from '@damiandb/pg'

export const db = await createDb({
    connectionString: process.env.DATABASE_URL as string
})

export const sql = createSQL()

SELECT

By column

import { db, sql } from './db'
import { UsersTable } from 'tables'

const { rows } = await db.query(sql(UsersTable)`
    SELECT * FROM ${UsersTable} 
    WHERE ${UsersTable.email} = ${"alice@example.com"}
`)

const user = rows[0]

Pass the table to sql(...) to type rows to that table's shape.

Untyped

const result = await db.query(sql`SELECT 1 AS n`)

Rows come back as any.

IN ARRAY

const { rows } = await db.query(sql(UsersTable)`
    SELECT * FROM ${UsersTable} 
    WHERE ${sql.inArray(UsersTable.id, [1, 2, 3])}
`)

sql.inArray generates safe IN (...) fragments. Empty arrays emit FALSE.

Joins

Type queries joining multiple tables by defining a row schema and combining results:

import { db, sql } from './db'
import { UsersTable, PostsTable } from 'tables'

const { rows } = await db.query(
    sql(UsersTable.schema)`
        SELECT
            ${sql.output(UsersTable).json()},
            ${sql.output(PostsTable).json().array()}
        FROM ${UsersTable}
        INNER JOIN ${PostsTable} 
            ON ${UsersTable.id} = ${PostsTable.user_id}
        GROUP BY ${UsersTable.id}
    `
)

sql.output

Chainable SELECT fragments:

Method	SQL produced
`sql.output(T)`	`"t".*`
`sql.output(T).alias("x")`	`"t".* AS "x"`
`sql.output(T).json()`	`row_to_json("t".*) AS "t"`
`sql.output(T).json().array()`	`array_agg(row_to_json("t".*)) AS "t"`
`sql.output(T).exclude(T.col)`	all columns except `col`

Table aliases

import { db, sql } from './db'
import { PersonTable } from 'tables'

const employee = sql.alias(PersonTable, "employee")
const manager = sql.alias(PersonTable, "manager")

const { rows } = await db.query(
    sql(PersonTable)`
        SELECT 
            ${sql.output(employee).json()}, 
            ${sql.output(manager).json()}
        FROM ${employee}
        INNER JOIN ${manager} 
            ON ${employee.manager_id} = ${manager.id}
    `
)

Required for self-joins. Alias must differ from table name.

INSERT

Single row

import { db, sql } from './db'
import { UsersTable } from 'tables'

const { cols, row } = UsersTable.createRow({
    name: "Alice",
    email: "alice@example.com"
})

await db.query(sql.void`
        INSERT INTO ${UsersTable} ${sql.tuple(cols)} 
        VALUES ${sql.tuple(row)}
`)

createRow returns column identifiers and values. Pass to sql.tuple.

Partial row

const { cols, row } = UsersTable.createPartialRow({ name: "Bob" })

Bulk

const records = [
    { name: "Alice", email: "alice@example.com" },
    { name: "Bob", email: "bob@example.com" }
]

const { cols, rows } = UsersTable.createRows(records)

await db.query(sql.void`
    INSERT INTO ${UsersTable} ${sql.tuple(cols)} 
    VALUES ${sql.tuples(rows)}
`)

Upsert

const { cols, rows } = UsersTable.createRows([userData])

await db.query(
    sql.void`
        INSERT INTO ${UsersTable} ${sql.tuple(cols)}
        VALUES ${sql.tuples(rows)}
        ON CONFLICT (${UsersTable.email}) DO UPDATE
        SET ${sql.excluded(cols, [UsersTable.id])}
    `
)

UPDATE

const user = {
    id: 1,
    name: "Alice Renamed",
}

await db.query(sql.void`
    UPDATE ${UsersTable} 
    SET ${UsersTable.name} = ${user.name} 
    WHERE ${UsersTable.id} = ${user.id}`
)

DELETE

Basic

await db.query(sql.void`
    DELETE FROM ${UsersTable} 
    WHERE ${UsersTable.id} = ${userId}
`)

With RETURNING


const post = {
    title: "Post to delete"
}

const { rows } = await db.query(sql(PostsTable)`
    DELETE FROM ${PostsTable} 
    WHERE ${PostsTable.title} = ${post.title} 
    RETURNING *
`)

Transactions

await db.transaction(async (tx) => {
    await tx.query(sql.void`INSERT INTO ${UsersTable} ...`)
    await tx.query(sql.void`INSERT INTO ${PostsTable} ...`)
})

Throws automatically roll back.

Helpers

sql.join

Join fragments with any separator.

sql.join(frags, sql`, `)
sql.join.comma(frags)
sql.join.and(conditions)
sql.join.or(conditions)

sql.map

Map an array into SQL fragments.

VALUES ${sql.join.comma(sql.map(rows, sql.tuple))}

sql.tuple / sql.tuples

sql.tuple(1, 2, 3)
sql.tuples([[1, 2], [3, 4]])

sql.target / sql.targets

sql.target(UsersTable.email)
sql.targets(UsersTable.id, UsersTable.name)
sql.targets([UsersTable.id, UsersTable.name])

Strip table prefix from identifiers.

sql.identity

const filter = shouldFilter
    ? sql.fragment`${UsersTable.name} = ${"Alice"}`
    : sql.identity("and")

await db.query(sql(UsersTable)`SELECT * FROM ${UsersTable} WHERE ${filter}`)

Safe default for conditional WHERE clauses. Use "or" for OR chains.

Usage

On this page