pgsmith

pgsmith is a utility for safely building parameterized SQL queries for use with Postgres and pg.

This is not an ORM or DSL. It’s a simple, composable SQL builder that lets you write SQL the way you want — clearly and safely.

const emails = ['[e-mail]', '[e-mail]'];
const query = sql`
  SELECT * 
  FROM users 
  WHERE email IN (${emails}) AND is_active = ${true}
`;

// query.text:
// SELECT * FROM users WHERE email IN ($1, $2) AND is_active <= $3
// query.values:
// ['[e-mail]', '[e-mail]', true]

🔐 Safe & Convenient

• Automatically numbers placeholders ($1, $2, …) to prevent SQL injection.
• Much easier to use prepared statements for better performance 🚀
• Automatically expands arrays into IN ($1, $2, ...).
• Returns {text, values} — drop-in compatible with pg.query().

🧰 Flexible Builder API

• Dynamically build queries with conditionals or loops.
• Easily compose from reusable parts.

🛠️ Object Helpers

• Generate INSERT, UPDATE, WHERE, UNNEST, and other SQL Fragments from objects.

📦 Tested & Stable

• Fully typed, 100% test coverage
• Zero dependencies, no bloat

Table of Contents

• Installation
• Examples
• Using with pg
• API Reference
• Philosophy
• Contributing
• Related Projects
• License

Installation

npm i pgsmith

Examples

💬 Tagged Template Example

import {sql} from 'pgsmith';

const ids = [33, 22, 11];

const query = sql`
  SELECT * FROM logs
  WHERE id IN (${ids})
  AND level <= ${5}
  ORDER BY created_at DESC
`;

// pg.query(query)

// query.text:
// SELECT * FROM logs WHERE id IN ($1, $2, $3) AND level <= $4 ORDER BY created_at DESC
// query.values:
// [33, 22, 11, 5]

🛠️ Builder API Example

import {sql, sqlBuilder, raw} from 'pgsmith';

// example data, could be anything
const data = {
  id: 42,
  status: 'active',
  role: ['admin', 'editor'],
  order: 'created_at DESC',
}

const builder = sqlBuilder(sql`SELECT * FROM users WHERE 1=1`);

data.id && builder.add(sql`AND id = ${data.id}`);
data.status && builder.add(sql`AND status = ${data.status}`);
data.role && builder.add(sql`AND role IN (${data.role})`);
data.order && builder.add(sql`ORDER BY ${raw('data.order')}`);

const query = builder.build();

// query.text:
// SELECT * FROM users WHERE 1=1 AND id = $1 AND status = $2 AND role IN ($3, $4) ORDER BY created_at DESC
// query.values:
// [42, 'active', 'admin', 'editor']

See a more real-world example of dynamic query building here.

📝 Insert From Object Example

import { buildInsert } from 'pgsmith';

const user = {
  firstName: 'Alice',
  lastName: 'Smith',
  email: '[e-mail]',
  isActive: true,
};

const query = buildInsert('users', user, { returning: true });

// query.text:
// INSERT INTO "users" ("firstName", "lastName", "email", "isActive")
// VALUES ($1, $2, $3, $4) RETURNING *
// query.values:
// ['Alice', 'Smith', '[e-mail]', true]

If you're inserting many rows, or want to take advantage of prepared statements, use UNNEST via buildUnnest.

UNNEST can offer massive performance improvements for large inserts, as it allows PostgreSQL to optimize the query execution plan.

🧩 Composition Example

import { sql, sqlBuilder, buildWhere } from 'pgsmith';

const query = sqlBuilder(sql`SELECT * FROM users`)
  .add(buildWhere({id: 1, status: 'active', role: ['admin', 'editor']}))
  .add(sql`ORDER BY created_at ${raw('DESC')}`)
  .build();

// query.text:
// SELECT * FROM users WHERE "id" = $1 AND "status" = $2 AND "role" IN ($3, $4) ORDER BY created_at DESC
// query.values:
// [1, 'active', 'admin', 'editor']

There are more examples in the API Reference.

Using with pg

pgsmith works seamlessly with pg, the most popular PostgreSQL client for Node.js.

Just pass the { text, values } object directly to pg.query():

import { sql } from 'pgsmith';
import { Client } from 'pg';

const client = new Client();
await client.connect();

const query = sql`SELECT * FROM users WHERE id = ${42}`;
const result = await client.query(query);

await client.end();

console.log(result.rows);
// → [{ id: 42, name: 'Alice', ... }]

API Reference

See the API Reference for detailed documentation on all functions and types.

Philosophy

Most SQL libraries either go too far or not far enough.

• Some are too low-level, forcing you to manually manage strings and $1 bindings.
• Others are too high-level, hiding SQL behind complex DSLs or ORMs.

pgsmith doesn’t try to replace SQL. It gives you a tiny, composable toolset that lets you work with SQL — clearly, safely, and without repetition or risk.

Write SQL the way you want — clearly and safely.

Contributing

• ⭐ Star this repo if you like it!
• 🐛 Open an issue for bugs or suggestions.
• 🤝 Submit a PR to main — all tests must pass.

Related Projects

• autorel: Automate semantic releases based on conventional commits. Similar to semantic-release but much simpler.
• hoare: An easy-to-use, fast, and defensive JS/TS test runner designed to help you to write simple, readable, and maintainable tests.
• jsout: A Syslog-compatible, small, and simple logger for Typescript/Javascript projects.
• cjs-mock: NodeJS module mocking for CJS (CommonJS) modules for unit testing purposes.
• brek: powerful yet simple configuration library for Node.js. It’s structured, typed, and designed for dynamic configuration loading, making it perfect for securely managing secrets (e.g., AWS Secrets Manager).