pg-query-composer

Advanced PostgreSQL query builder for TypeScript with dynamic composition, type safety, and N+1 prevention.

Overview

pg-query-composer eliminates the need for raw SQL strings while preventing SQL injection through automatic parameterization. Build complex PostgreSQL queries programmatically with a fluent API, type-safe operations, and production-ready features like batch loading and eager loading.

Key Strengths:

• Django-style operator syntax for intuitive filtering
• Compile-time type validation via Zod schemas
• Automatic SQL injection prevention (parameterized queries)
• Batch loading eliminates N+1 database queries
• PostgreSQL-native features (JSONB, Full-Text Search, Recursive CTEs)
• Reusable scopes and fragments for DRY queries
• Zero runtime dependencies in the core builder (zod is a peer dependency; the optional /relations module pulls in dataloader)

Installation

npm install pg-query-composer zod

Requirements: Node.js 20+, TypeScript 5.4+, PostgreSQL 12+ (built with TypeScript 7.0)

Quick Start

Basic Query

import { z } from 'zod';
import { createQueryComposer } from 'pg-query-composer';

// Define schema (also used for Zod validation)
const userSchema = z.object({
  id: z.number(),
  email: z.string().email(),
  name: z.string(),
  status: z.string(),
  created_at: z.string().datetime(),
});

// Create composer
const composer = createQueryComposer(userSchema, 'users');

// Build query with multiple conditions — where() takes a filter object,
// so a request query string can be passed straight in
const result = composer
  .where({ email__contains: 'example.com', status__exact: 'active' })
  .orderBy('-created_at') // Negative prefix = DESC
  .paginate({ page: 1, limit: 20 })
  .toParam();

console.log(result.text);
// SELECT * FROM users WHERE email ILIKE $1 AND status = $2 ORDER BY created_at DESC LIMIT $3 OFFSET $4

console.log(result.values);
// ['%example.com%', 'active', 20, 0]

LIMIT / OFFSET are parameterized too, so one SQL text serves every page and PostgreSQL reuses the same plan.

Type-Safe Queries

import { createTypedComposer } from 'pg-query-composer/types';

const typed = createTypedComposer(userSchema, 'users');

// Compile-time error: 'invalid_field' not in schema
typed.where({ invalid_field__exact: 'value' }); // ✗ TypeScript error

// Type-safe: 'email' exists in schema
typed.where({ email__exact: '[email protected]' }); // ✓

const { text, values } = typed.toParam();
// SELECT * FROM users WHERE email = $1   |   ['[email protected]']

Eager Loading with Relations

The library builds queries and groups results — it never opens a connection. You supply an executor, so any pg Pool, Client or transaction works.

import { Pool } from 'pg';
import { z } from 'zod';
import {
  defineModel,
  createModelQuery,
  loadRelation,
  type QueryExecutor,
} from 'pg-query-composer/relations';

const pool = new Pool();
const execute: QueryExecutor = async ({ text, values }) =>
  (await pool.query(text, values)).rows;

const postSchema = z.object({
  id: z.number(),
  user_id: z.number(),
  title: z.string(),
  status: z.string(),
});

// Define the model once — `targetSchema` gives the relation query its own
// column whitelist
const User = defineModel({
  name: 'User',
  table: 'users',
  schema: userSchema,
  primaryKey: 'id',
  relations: {
    posts: {
      type: 'hasMany',
      target: 'posts',
      targetSchema: postSchema,
      foreignKey: 'user_id',
      primaryKey: 'id',
    },
  },
});

// 1. Load the parents
const query = createModelQuery(User).where({ status__exact: 'active' });
const users = await execute(query.toParam());
// SELECT * FROM users WHERE status = $1   |   ['active']

// 2. Load the relation — one query for every parent, no N+1
const withPosts = await loadRelation(users, User, 'posts', execute);
// SELECT * FROM posts WHERE user_id = ANY($1)   |   [[1, 2, 3]]
// → [{ id: 1, name: 'Ann', posts: [{ id: 9, title: 'Hello', ... }] }, ...]

hasMany / hasManyThrough yield an array, belongsTo / hasOne a single record or null. Pass a DataLoader from createRelationLoader() as the fifth argument when several call sites (GraphQL resolvers, say) must coalesce into one query and share a per-request cache.

ModelQueryComposer also tracks .include() declarations, which getIncludeQueries() renders as filterable relation queries:

const q = createModelQuery(User)
  .where({ status__exact: 'active' })
  .include('posts', (qc) => qc.where({ status__exact: 'published' }).orderBy('-id'));

q.getIncludeQueries();
// [{ relation: 'posts', type: 'hasMany', foreignKey: 'user_id', primaryKey: 'id',
//    query: { text: 'SELECT * FROM posts WHERE status = $1 ORDER BY id DESC',
//             values: ['published'] } }]

Note the parent-key predicate is not part of that text — getIncludeQueries() hands you the filter and the key names, and you add the key restriction (or use loadRelation(), which builds WHERE user_id = ANY($1) itself but does not apply the .include() callback).

Reusable Filters

import { dateRange, contains, fragment } from 'pg-query-composer/composition';

const composer = createQueryComposer(userSchema, 'users');

// Fragments are plain filter objects, so they compose with where()
const dateFilter = dateRange('created_at', '2024-01-01', '2024-12-31');
const nameFilter = contains('name', 'John');

const { text, values } = composer.where(dateFilter).where(nameFilter).toParam();
// SELECT * FROM users WHERE created_at BETWEEN $1 AND $2 AND name ILIKE $3
// ['2024-01-01', '2024-12-31', '%John%']

// Or merge them into one call
composer.where({ ...dateFilter, ...nameFilter });

PostgreSQL Features

JSONB Operations

import { jsonbContains, jsonbPath } from 'pg-query-composer/pg';

composer.where(jsonbContains('metadata', { role: 'admin' }));
// WHERE metadata @> $1::jsonb   |   ['{"role":"admin"}']

const pathValue = jsonbPath('metadata', ['profile', 'city']);
// metadata->'profile'->'city'   (path is an array, not a dotted string)

Full-Text Search

import { fullTextSearch, fullTextRank } from 'pg-query-composer/pg';

// First argument is a tsvector column (or a plain identifier) — expressions
// such as `title || content` are rejected by identifier validation
composer.where(fullTextSearch('search_vector', 'react hooks'));
// WHERE search_vector @@ plainto_tsquery('english', $1)   |   ['react hooks']

const ranked = fullTextRank('search_vector', 'react hooks');
// ts_rank(search_vector, plainto_tsquery('english', 'react hooks'))

Recursive CTEs

import { ancestorsCTE } from 'pg-query-composer/pg';

const categorySchema = z.object({
  id: z.number(),
  name: z.string(),
  parent_id: z.number().nullable(),
});

// (table, schema, startId, parentColumn?)
const { text, values } = ancestorsCTE('categories', categorySchema, '42')
  .withMaxDepth(5)
  .toParam();

// WITH RECURSIVE ancestors AS (
//   SELECT id, name, parent_id, 0 AS depth FROM categories WHERE id = $1
//   UNION ALL
//   SELECT categories.id, categories.name, categories.parent_id, depth + 1
//   FROM categories JOIN ancestors ON ancestors.parent_id = categories.id
//   WHERE depth < 5
// )
// SELECT id, name, parent_id, depth FROM ancestors      |   ['42']

Feature Highlights

34 Built-In Operators

| Category | Operators | |----------|-----------| | Comparison | exact, notexact, gt, gte, lt, lte | | Text | contains, icontains, startswith, istartswith, endswith, iendswith, regex, iregex | | Range | in, notin, between, notbetween | | Null | isnull, isnotnull | | Date | date, datebetween, year, month, day, week, today, thisweek, thismonth, thisyear | | Array | arraycontains, arrayoverlap, arraycontained |

Advanced Capabilities

• Composition: AND/OR conditions, complex WHERE clauses, JOIN support
• Pagination: Automatic LIMIT/OFFSET with metadata (page, total, hasNext)
• Sorting: Multi-field sorting with custom directions
• Relations: belongsTo, hasOne, hasMany, hasManyThrough eager loading
• Subqueries: IN subqueries, EXISTS checks, LATERAL joins
• JSONB: 11 operators for JSON data manipulation
• Full-Text Search: 5 methods including websearch and ranking
• Recursive CTEs: Hierarchical query builder
• Type Safety: Compile-time column validation with Zod schemas

Module Overview

| Module | Purpose | Import | |--------|---------|--------| | Main | QueryComposer, operators, types | pg-query-composer | | Composition | Fragments, scopes, merge | pg-query-composer/composition | | Subquery | Subqueries, EXISTS, LATERAL | pg-query-composer/subquery | | Relations | Models, eager loading, batch load | pg-query-composer/relations | | Types | Compile-time column validation | pg-query-composer/types | | PostgreSQL | JSONB, FTS, Recursive CTEs | pg-query-composer/pg |

Every subpath is also re-exported from the root entry, so import { jsonbContains } from 'pg-query-composer' works too.

API Reference

See full API docs in /docs:

| API | Method | Returns | |-----|--------|---------| | createQueryComposer(schema, table) | .where() .or() .not() .orderBy() .paginate() .join() .groupBy() .having() .select() .exclude() | .toParam() → { text, values } | | createTypedComposer(schema, table) | Same as above + compile-time type checking | .toParam() → { text, values } | | createModelQuery(model) | All above + .include(relation, queryFn?) | .toParam(), .getIncludeQueries() | | loadRelation(rows, model, name, exec) | Batch-loads one relation for a row set | Promise<rows with relation> | | scope(callback) | Reusable query modifier | .apply(scope) | | merge(qc1, qc2) | Combine composers | QueryComposer |

Documentation

• System Architecture - Design, data flow
• Code Standards - Conventions, patterns
• Changelog - Release history and engineering decisions

Feature Guides

• Core Builder - WHERE, JOINs, pagination, sorting
• Composition - Fragments, scopes, merge
• Subqueries - IN, EXISTS, LATERAL
• Relations - Models, eager loading, batch loading
• PostgreSQL - JSONB, Full-Text Search, CTEs

Performance

• Query Building: O(n) where n = conditions
• SQL Generation: Single pass, no rebuilding
• Batch Loading: one query per relation depth (not O(n) with N+1)
• Stable SQL text: IN lists bind as one array parameter and LIMIT/OFFSET are parameterized, so list length and page number never fragment PostgreSQL's plan cache

Measured numbers and the reasoning behind each decision live in CHANGELOG.md → Engineering decisions.

Testing

npm test              # Run all tests
npm run test:watch    # Watch mode

286 test cases across 23 files, including 59 dedicated SQL injection prevention tests plus guards over the published exports map and the version constant. Verified against TypeScript 7.0 on Node 20 and 22.

Contributing

Contributions welcome! Please:

1. Read code-standards.md for conventions
2. Add tests for new features
3. Ensure TypeScript strict mode passes
4. Update docs if adding new operators or features

License

MIT - See LICENSE file

Changelog

See CHANGELOG.md.

pg-query-composer • GitHub • npm