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

Installation

``bash
npm install pg-query-composer zod
`

Requirements: Node.js 18+, TypeScript 5.4+, PostgreSQL 12+

Quick Start

$3

`typescript
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(),
created_at: z.string().datetime(),
});

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

// Build query
const result = composer
.where('email__contains', 'example.com')
.where('created_at__gte', new Date('2024-01-01'))
.orderBy('created_at', 'DESC')
.paginate({ page: 1, limit: 20 })
.build();

console.log(result.sql);
// SELECT * FROM users WHERE email LIKE $1 AND created_at >= $2 ORDER BY created_at DESC LIMIT 20 OFFSET 0

console.log(result.values);
// ['%example.com%', '2024-01-01T00:00:00Z']
`

$3

`typescript
import { createTypedComposer } from 'pg-query-composer';

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', 'test@example.com'); // ✓
`

$3

`typescript
import { defineModel, createModelQuery } from 'pg-query-composer/relations';

// Define models with relationships
defineModel('User', {
table: 'users',
relations: {
posts: { type: 'hasMany', modelName: 'Post', foreignKey: 'user_id' },
profile: { type: 'hasOne', modelName: 'Profile', foreignKey: 'user_id' },
},
});

defineModel('Post', {
table: 'posts',
relations: {
author: { type: 'belongsTo', modelName: 'User', foreignKey: 'user_id' },
},
});

// Build query with eager loading (N+1 prevention via batch loading)
const userQuery = createModelQuery(User, 'users');
const users = await userQuery
.where('status__exact', 'active')
.include('posts', { limit: 5 })
.include('profile')
.build();

// users[i].posts, users[i].profile auto-populated with batch loading
`

$3

`typescript
import { fragment, dateRange, scope } from 'pg-query-composer/composition';

// Pre-built fragments
const f = dateRange('created_at', startDate, endDate);
composer.where(...f);

// Reusable scopes
const activeScope = scope((q) => q.where('deleted_at__isnull', true));
composer.applyScope(activeScope);
`

$3

#### JSONB Operations

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

composer.where(jsonbContains('metadata', { role: 'admin' }));
const pathValue = jsonbPath('data', 'nested.field');
`

#### Full-Text Search

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

composer.where(fullTextSearch('title || content', 'search query'));
const ranked = fullTextRank('title', 'search query');
`

#### Recursive CTEs

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

const ancestors = ancestorsCTE('categories', 'id', 'parent_id', 5);
// Finds all parents up to 5 levels deep
`

Feature Highlights

$3

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

$3

- 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 |
| PostgreSQL | JSONB, FTS, Recursive CTEs | pg-query-composer/pg |

API Reference

$3

`typescript
createQueryComposer(schema, table, options?)
.where(column, value)
.andWhere(column, value)
.orWhere(column, value)
.notWhere(column, value)
.join(table, alias?, on)
.groupBy(...fields)
.having(condition)
.orderBy(field, direction)
.select(...fields) / .exclude(...fields)
.paginate({ page, limit, maxLimit })
.build() // Returns { sql, values }
`

$3

`typescript
createTypedComposer(schema, table)
// Same methods as QueryComposer, but with compile-time type checking
`

$3

`typescript
createModelQuery(model, table)
// All QueryComposer methods plus:
.include(relationName, options?)
.build() // Returns records with relations populated
`

$3

`typescript
// Fragments (13 pre-built)
dateRange(field, start, end)
inList(field, values)
contains(field, value)
// ... and 10 more

// Scopes
scope(callback)
parameterizedScope(callback)

// Merge
merge(composer1, composer2)
mergeAll([composer1, composer2, ...])
`

$3

`typescript
// JSONB (11 operators)
jsonbContains(field, value)
jsonbPath(field, path)
// ... and 9 more

// Full-Text Search
fullTextSearch(field, query)
fullTextRank(field, query)

// Recursive CTE
ancestorsCTE(table, idCol, parentCol, depth)
descendantsCTE(table, idCol, parentCol, depth)
`

Documentation

Comprehensive documentation available in /docs:

- project-overview-pdr.md - Project goals, features, requirements
- codebase-summary.md - Code structure, modules, metrics
- code-standards.md - Development standards, patterns, conventions
- system-architecture.md - System design, data flow, extension points

Examples

$3

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

if (email) composer.where('email__exact', email);
if (status) composer.where('status__exact', status);
if (minAge) composer.where('age__gte', minAge);

const { sql, values } = composer.build();
`

$3

`typescript
const query = createQueryComposer(schema, 'posts');

query
.where('status__exact', 'published')
.andWhere('created_at__gte', new Date('2024-01-01'))
.orWhere('featured__exact', true)
.orderBy('created_at', 'DESC')
.paginate({ page: 1, limit: 10 });

const { sql, values } = query.build();
`

$3

`typescript
import { createRelationLoader } from 'pg-query-composer/relations';
import DataLoader from 'dataloader';

const postLoader = createRelationLoader({
relation: 'posts',
batchFn: async (userIds) => {
// Fetch all posts for all userIds in one query
const posts = await db.query(
'SELECT * FROM posts WHERE user_id = ANY($1)',
[userIds]
);
return userIds.map(id => posts.filter(p => p.user_id === id));
},
});

const post = await postLoader.load(userId);
`

Performance

- Query Building: O(n) where n = conditions
- SQL Generation: Single pass, no rebuilding
- Batch Loading: O(1) per relation depth (not O(n) with N+1)
- Type Checking: < 5s compile time
- Bundle: ~50KB minified (before gzip)

Testing

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

Coverage: 120+ test cases, 85%+ line coverage, all public APIs tested.

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

$3

- Core QueryComposer with 34 operators
- Type-safe TypedQueryComposer wrapper
- 13 reusable filter fragments
- Full relation support (belongsTo, hasOne, hasMany, hasManyThrough)
- DataLoader-based batch loading
- 11 JSONB operators
- Full-Text Search (5 methods)
- Recursive CTE builder
- 120+ test cases

---

pg-query-composer • GitHub • npm