easy-postgres-sql

SQL DDL/DML string builder for use with easy-postgres.

Note: This package is ESM-only. Node.js 18+ is required.

Install

npm install easy-postgres-sql

Quick Example

import { sql, val, ident } from 'easy-postgres-sql'

const table = 'users'
const minAge = 18

const query = sql`SELECT * FROM ${ident(table)} WHERE age > ${val(minAge)}`

console.log(query.stmt)   // SELECT * FROM "users" WHERE age > $1
console.log(query.params) // [18]

API Reference

sql

sql`...`: SqlQuery

Tagged template literal that builds a parameterized SQL query. Returns a SqlQuery with a .stmt string and a .params array. Whitespace in the template is preserved as-is.

Interpolations must use val(), ident(), or another SqlQuery. Bare strings or numbers will throw EasySqlError.

import { sql, val, ident } from 'easy-postgres-sql'

const q = sql`INSERT INTO ${ident('orders')} (user_id, total) VALUES (${val(42)}, ${val(99.95)})`

// q.stmt   = 'INSERT INTO "orders" (user_id, total) VALUES ($1, $2)'
// q.params = [42, 99.95]

val(value)

val(value: unknown): ValWrapper

Wraps a value for safe parameter binding. Each val() call in a template produces the next $N placeholder and appends the value to .params.

const q = sql`SELECT * FROM t WHERE a = ${val('foo')} AND b = ${val(42)}`

// q.stmt   = 'SELECT * FROM t WHERE a = $1 AND b = $2'
// q.params = ['foo', 42]

ident(name)

ident(name: string): IdentWrapper

Wraps an identifier (table name, column name, schema, etc.) for safe inline quoting. The name is wrapped in double-quotes and any embedded double-quotes are escaped by doubling.

const q = sql`SELECT * FROM ${ident('public')}.${ident('users')}`

// q.stmt   = 'SELECT * FROM "public"."users"'
// q.params = []

batchSql

batchSql`...`: BatchSqlBuilder

Tagged template literal for batch operations (e.g. bulk inserts). Returns a BatchSqlBuilder whose .stmt holds the resolved SQL template (with identifiers quoted and $N placeholders intact). Use .withParams() to bind rows of parameters.

Only ident() interpolations are permitted. Passing val() or a SqlQuery throws EasySqlError. Parameter placeholders ($1, $2, …) are written literally in the template — they will be bound per-row at execution time.

import { batchSql, ident } from 'easy-postgres-sql'

const builder = batchSql`INSERT INTO ${ident('products')} (name, price) VALUES ($1, $2)`

// builder.stmt = 'INSERT INTO "products" (name, price) VALUES ($1, $2)'

const query = builder.withParams([
  ['Widget', 9.99],
  ['Gadget', 24.99],
])

// query.stmt   = 'INSERT INTO "products" (name, price) VALUES ($1, $2)'
// query.params = [['Widget', 9.99], ['Gadget', 24.99]]

BatchSqlBuilder.withParams(rows)

withParams(rows: unknown[][]): BatchSqlQuery

Binds an array of rows to the batch template. Each inner array is one row of parameter values. Returns a BatchSqlQuery.

loadSQL(filePath)

loadSQL(filePath: string): Promise<string>

Reads a .sql file from disk and returns its raw contents as a string, including any trailing newline. Useful for keeping complex queries in dedicated files rather than inline template literals. Throws EasySqlError (with the original fs error as .cause) if the file cannot be read.

Call .trim() on the result if your driver is sensitive to trailing whitespace.

import { loadSQL } from 'easy-postgres-sql'
import { resolve } from 'node:path'

const stmt = await loadSQL(resolve('queries', 'find-user.sql'))
// stmt = 'SELECT id, name FROM users WHERE id = $1\n'

// Trim if needed:
const stmtTrimmed = stmt.trim()
// stmtTrimmed = 'SELECT id, name FROM users WHERE id = $1'

Classes

| Class | Description | |-------|-------------| | SqlQuery | Holds .stmt: string and .params: unknown[]. Returned by sql. | | BatchSqlBuilder | Holds .stmt: string (the resolved template). Has .withParams(rows) method. Returned by batchSql. | | BatchSqlQuery | Holds .stmt: string and .params: unknown[][]. Returned by BatchSqlBuilder.withParams(). | | EasySqlError | Error subclass (.name === 'EasySqlError') thrown on invalid interpolation or file load failure. Has .cause set on loadSQL errors. |

TypeScript Types

These are compile-time type exports only — they do not exist at runtime and cannot be used with instanceof.

| Type | Description | |------|-------------| | ValWrapper | Return type of val(). | | IdentWrapper | Return type of ident(). | | Interpolation | Union ValWrapper \| IdentWrapper \| SqlQuery — valid interpolation values for the sql tag. |

Composing Queries

A SqlQuery can be interpolated directly into another sql tag. Parameter placeholders are renumbered automatically so the final .params array is flat and correctly ordered.

import { sql, val, ident } from 'easy-postgres-sql'

const activeFilter = sql`active = ${val(true)}`
const ageFilter    = sql`age > ${val(21)}`

const query = sql`SELECT * FROM ${ident('users')} WHERE ${activeFilter} AND ${ageFilter}`

// query.stmt   = 'SELECT * FROM "users" WHERE active = $1 AND age > $2'
// query.params = [true, 21]

Working with easy-postgres

easy-postgres-sql has no runtime dependency on easy-postgres. It produces SqlQuery and BatchSqlQuery values that you pass directly to easy-postgres methods.

Single query

import EasyPostgres from 'easy-postgres'
import { sql, val, ident } from 'easy-postgres-sql'

const db = new EasyPostgres(connectionConfig)

const query = sql`SELECT id, email FROM ${ident('users')} WHERE active = ${val(true)} AND age > ${val(18)}`

const rows = await db.execSQL(query.stmt, query.params)

Batch insert

import EasyPostgres from 'easy-postgres'
import { batchSql, ident } from 'easy-postgres-sql'

const db = new EasyPostgres(connectionConfig)

const builder = batchSql`INSERT INTO ${ident('events')} (name, ts) VALUES ($1, $2)`

const query = builder.withParams([
  ['page_view', new Date('2026-01-01')],
  ['signup',    new Date('2026-01-02')],
])

await db.execTransactionSQL(query.stmt, query.params)

File-based query

import EasyPostgres from 'easy-postgres'
import { loadSQL } from 'easy-postgres-sql'
import { resolve } from 'node:path'

const db = new EasyPostgres(connectionConfig)

const stmt = await loadSQL(resolve('queries', 'find-user.sql'))
const rows = await db.execSQL(stmt.trim(), [userId])

Error Handling

All errors thrown by this module are instances of EasySqlError, which extends the built-in Error class with .name === 'EasySqlError'.

Invalid interpolation in sql:

import { sql, EasySqlError } from 'easy-postgres-sql'

try {
  const q = sql`SELECT ${42 as any}` // bare number — not wrapped in val()
} catch (err) {
  if (err instanceof EasySqlError) {
    console.error(err.message) // Invalid interpolation in sql tag: ...
  }
}

Invalid interpolation in batchSql (anything other than ident()):

import { batchSql, val, EasySqlError } from 'easy-postgres-sql'

try {
  batchSql`INSERT INTO t VALUES (${val(1) as any})`
} catch (err) {
  if (err instanceof EasySqlError) {
    console.error(err.message) // Invalid interpolation in batchSql tag: ...
  }
}

File not found in loadSQL:

import { loadSQL, EasySqlError } from 'easy-postgres-sql'

try {
  await loadSQL('/queries/missing.sql')
} catch (err) {
  if (err instanceof EasySqlError) {
    console.error(err.message) // loadSQL failed: ...
    console.error(err.cause)   // original fs error
  }
}

License

GPL-3.0-or-later. See LICENSE.