PG Query Builder

A lightweight, type-safe PostgreSQL query builder for Node.js applications written in TypeScript. Build complex SQL queries with a fluent API and automatic parameter binding.

Features

- Type-safe: Leverages TypeScript for strong typing, preventing errors at compile time
- Fluent API: Chainable methods for building queries in a readable manner
- CRUD Operations: Complete support for SELECT, INSERT, UPDATE, DELETE operations
- Raw Queries: Template literal support for custom SQL with parameter binding
- SQL Injection Protection: Automatic parameterization prevents SQL injection vulnerabilities
- Subqueries: Support for complex nested queries and CTEs
- Condition Builder: Rich condition operators including date functions and array operations

Installation

``bash
npm install @rusdidev/pg-query-builder
`

Quick Start

`typescript
import { select, insert, update, remove, raw } from '@rusdidev/pg-query-builder';

// Simple SELECT query
const [query, params] = select({ table: "users" })
.addSelectItems("id", "name", "email")
.addWhereClauseItem({ column: "status", operator: "eq", value: "active" })
.useLimit(10, 0)
.compile();

// Result: ["SELECT id,name,email FROM users WHERE status = $1 LIMIT 10 OFFSET 0", ["active"]]
`

API Reference

$3

`typescript
import { select } from '@rusdidev/pg-query-builder';

// Basic SELECT
const query = select({ table: "users" })
.addSelectItems("id", "name", "email")
.compile();

// SELECT with WHERE conditions
const query = select({ table: "users" })
.addSelectItems("*")
.addWhereClauseItem({ column: "age", operator: "gt", value: 18 })
.addWhereClauseItem({ column: "status", operator: "eq", value: "active" })
.compile();

// SELECT with JOINs
const query = select({ table: "users", alias: "u" })
.addSelectItems("u.name", "p.address")
.addJoinClauseItem({ type: "LEFT", table: "profiles", alias: "p", on: "u.id = p.user_id" })
.compile();

// SELECT with ORDER BY and LIMIT
const query = select({ table: "users" })
.addSelectItems("*")
.addOrderByClauseItem({ column: "created_at", direction: "DESC" })
.useLimit(20, 10)
.compile();
`

$3

`typescript
import { insert } from '@rusdidev/pg-query-builder';

// Single INSERT
const query = insert({ table: "users" })
.addInsertColumns("name", "email", "age")
.addInsertPayload({ name: "John", email: "john@example.com", age: 25 })
.compile();

// Bulk INSERT
const query = insert({ table: "users" })
.addInsertColumns("name", "email")
.addInsertPayload(
{ name: "John", email: "john@example.com" },
{ name: "Jane", email: "jane@example.com" }
)
.compile();

// INSERT with RETURNING
const query = insert({ table: "users" })
.addInsertColumns("name", "email")
.addInsertPayload({ name: "John", email: "john@example.com" })
.addReturnItems("id", "created_at")
.compile();
`

$3

`typescript
import { update } from '@rusdidev/pg-query-builder';

// Basic UPDATE
const query = update({ table: "users" })
.addSetClauseItems({ name: "John Doe", email: "john.doe@example.com" })
.addWhereClauseItem({ column: "id", operator: "eq", value: 1 })
.compile();

// UPDATE with RETURNING
const query = update({ table: "users" })
.addSetClauseItems({ status: "inactive" })
.addWhereClauseItem({ column: "last_login", operator: "lt", value: "2023-01-01" })
.addReturnItems("id", "updated_at")
.compile();
`

$3

`typescript
import { remove } from '@rusdidev/pg-query-builder';

// Basic DELETE
const query = remove({ table: "users" })
.addWhereClauseItem({ column: "status", operator: "eq", value: "inactive" })
.compile();

// DELETE with RETURNING
const query = remove({ table: "users" })
.addWhereClauseItem({ column: "id", operator: "eq", value: 1 })
.addReturnItems("id", "name")
.compile();
`

$3

For complex queries or when you need full SQL control:

`typescript
import { raw } from '@rusdidev/pg-query-builder';

// Simple raw query with parameters
const userId = 123;
const status = 'active';
const query = raw
SELECT u.*, p.address
FROM users u
LEFT JOIN profiles p ON u.id = p.user_id
WHERE u.id = ${userId} AND u.status = ${status}
;
const [sql, params] = query.compile();
// Result: ["SELECT u.*, p.address FROM users u LEFT JOIN profiles p ON u.id = p.user_id WHERE u.id = $1 AND u.status = $2", [123, "active"]]

// Complex raw query
const searchTerm = '%john%';
const minAge = 18;
const query = raw
WITH active_users AS (
SELECT * FROM users WHERE status = 'active'
)
SELECT au.name, au.email, COUNT(o.id) as order_count
FROM active_users au
LEFT JOIN orders o ON au.id = o.user_id
WHERE au.name ILIKE ${searchTerm} AND au.age >= ${minAge}
GROUP BY au.id, au.name, au.email
ORDER BY order_count DESC
;
`

Condition Operators

The query builder supports various condition operators:

| Operator | Description | Example |
|----------|-------------|---------|
| eq | Equal | { column: "status", operator: "eq", value: "active" } |
| ne | Not equal | { column: "status", operator: "ne", value: "inactive" } |
| gt | Greater than | { column: "age", operator: "gt", value: 18 } |
| ge | Greater than or equal | { column: "age", operator: "ge", value: 18 } |
| lt | Less than | { column: "age", operator: "lt", value: 65 } |
| le | Less than or equal | { column: "age", operator: "le", value: 65 } |
| like | SQL LIKE | { column: "name", operator: "like", value: "%john%" } |
| notLike | SQL NOT LIKE | { column: "name", operator: "notLike", value: "%test%" } |
| ilike | Case-insensitive LIKE | { column: "email", operator: "ilike", value: "%@GMAIL.COM" } |
| notILike | Case-insensitive NOT LIKE | { column: "email", operator: "notILike", value: "%spam%" } |
| empty | IS NULL | { column: "deleted_at", operator: "empty" } |
| notEmpty | IS NOT NULL | { column: "email", operator: "notEmpty" } |
| isIn | IN clause | { column: "status", operator: "isIn", value: ["active", "pending"] } |
| notIn | NOT IN clause | { column: "status", operator: "notIn", value: ["banned", "deleted"] } |
| eqAny | = ANY() for arrays | { column: "tag", operator: "eqAny", value: ["tech", "news"], dataType: "string" } |
| today | Same day | { column: "created_at", operator: "today" } |
| thisMonth | Same month | { column: "created_at", operator: "thisMonth" } |
| thisYear | Same year | { column: "created_at", operator: "thisYear" } |
| sameDate | Same date as value | { column: "created_at", operator: "sameDate", value: "2023-12-25" } |

Advanced Features

$3

`typescript
import { select, generateSubquery } from '@rusdidev/pg-query-builder';

const mainQuery = select({ table: "users", alias: "u" });
const subQuery = generateSubquery({
parent: mainQuery,
type: "select",
table: "orders",
alias: "o"
}).addSelectItems("user_id", "COUNT(*) as order_count")
.addWhereClauseItem({ column: "status", operator: "eq", value: "completed" })
.addGroupByClauseItem("user_id");

const [sql, params] = mainQuery
.addSelectItems("u.name", "sub.order_count")
.addJoinClauseItem({ type: "LEFT", table: subQuery, alias: "sub", on: "u.id = sub.user_id" })
.compile();
`

$3

`typescript
const cteQuery = select({ table: "users" })
.addSelectItems("*")
.addWhereClauseItem({ column: "status", operator: "eq", value: "active" });

const mainQuery = select({ table: "active_users", alias: "au" })
.addWithClauseItem(cteQuery)
.addSelectItems("au.name", "au.email")
.compile();
`

$3

`typescript
// AND/OR conditions
const query = select({ table: "users" })
.addSelectItems("*")
.setWhereOperator("OR")
.addWhereClauseItem({ column: "status", operator: "eq", value: "active" })
.addWhereClauseItem({ column: "role", operator: "eq", value: "admin" })
.compile();

// Nested conditions
const query = select({ table: "users" })
.addSelectItems("*");

const subCondition = query.generateSubWhereCondition("OR");
subCondition.addConditionClauseItem({ column: "age", operator: "lt", value: 18 });
subCondition.addConditionClauseItem({ column: "age", operator: "gt", value: 65 });

const [sql, params] = query.compile();
`

Usage with Database Clients

$3

`typescript
import { Client } from 'pg';
import { select, insert, raw } from '@rusdidev/pg-query-builder';

const client = new Client({
connectionString: 'postgresql://username:password@localhost:5432/database'
});

// Using query builder
const [sql, params] = select({ table: "users" })
.addSelectItems("*")
.addWhereClauseItem({ column: "status", operator: "eq", value: "active" })
.compile();

const result = await client.query(sql, params);

// Using raw queries
const userId = 123;
const [rawSql, rawParams] = rawSELECT * FROM users WHERE id = ${userId}.compile();
const rawResult = await client.query(rawSql, rawParams);
`

$3

`typescript
import { PrismaClient } from '@prisma/client';
import { raw } from '@rusdidev/pg-query-builder';

const prisma = new PrismaClient();

const userId = 123;
const [sql, params] = raw
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.id = ${userId}
GROUP BY u.id, u.name
.compile();

const result = await prisma.$queryRawUnsafe(sql, ...params);
`

API Methods

$3

| Method | Description | Example |
|--------|-------------|---------|
| addSelectItems(...items) | Add columns to SELECT | .addSelectItems("id", "name") |
| addWhereClauseItem(condition) | Add WHERE condition | .addWhereClauseItem({ column: "id", operator: "eq", value: 1 }) |
| addJoinClauseItem(join) | Add JOIN clause | .addJoinClauseItem({ type: "LEFT", table: "profiles", alias: "p", on: "u.id = p.user_id" }) |
| addOrderByClauseItem(order) | Add ORDER BY | .addOrderByClauseItem({ column: "created_at", direction: "DESC" }) |
| addGroupByClauseItem(...columns) | Add GROUP BY | .addGroupByClauseItem("status", "role") |
| useLimit(limit, offset?) | Add LIMIT/OFFSET | .useLimit(10, 20) |
| addReturnItems(...items) | Add RETURNING clause | .addReturnItems("id", "updated_at") |
| compile() | Compile to SQL | Returns [sql: string, params: unknown[]] |

$3

| Method | Description | Example |
|--------|-------------|---------|
| addInsertColumns(...columns) | Define insert columns | .addInsertColumns("name", "email") |
| addInsertPayload(...data) | Add data to insert | .addInsertPayload({ name: "John", email: "john@example.com" }) |
| addConflictClause(clause) | Add ON CONFLICT | .addConflictClause("ON CONFLICT (email) DO NOTHING") |

$3

| Method | Description | Example |
|--------|-------------|---------|
| addSetClauseItems(data) | Set update values | .addSetClauseItems({ name: "John Doe", status: "active" }) |

Examples

$3

`typescript
const mainQuery = select({ table: "users", alias: "u" });

// Create subquery for order counts
const orderCountSubquery = generateSubquery({
parent: mainQuery,
type: "select",
table: "orders",
alias: "o"
}).addSelectItems("user_id", "COUNT(*) as total_orders")
.addWhereClauseItem({ column: "status", operator: "eq", value: "completed" })
.addGroupByClauseItem("user_id");

const [sql, params] = mainQuery
.addSelectItems("u.name", "u.email", "oc.total_orders")
.addJoinClauseItem({
type: "LEFT",
table: orderCountSubquery,
alias: "oc",
on: "u.id = oc.user_id"
})
.addWhereClauseItem({ column: "u.status", operator: "eq", value: "active" })
.addOrderByClauseItem({ column: "oc.total_orders", direction: "DESC" })
.useLimit(10)
.compile();
`

$3

`typescript
const users = [
{ name: "John", email: "john@example.com", age: 25 },
{ name: "Jane", email: "jane@example.com", age: 30 },
{ name: "Bob", email: "bob@example.com", age: 35 }
];

const [sql, params] = insert({ table: "users" })
.addInsertColumns("name", "email", "age")
.addInsertPayload(...users)
.addReturnItems("id", "created_at")
.compile();
`

$3

`typescript
const [sql, params] = update({ table: "users" })
.addSetClauseItems({
last_login: new Date(),
login_count: rawlogin_count + 1
})
.addWhereClauseItem({ column: "email", operator: "eq", value: "user@example.com" })
.addReturnItems("id", "last_login", "login_count")
.compile();
`

$3

`typescript
const searchTerm = '%developer%';
const minSalary = 50000;
const departments = ['engineering', 'product'];

const [sql, params] = raw
WITH department_stats AS (
SELECT
department,
AVG(salary) as avg_salary,
COUNT(*) as employee_count
FROM employees
WHERE department = ANY(${departments})
GROUP BY department
)
SELECT
e.name,
e.salary,
e.department,
ds.avg_salary,
ds.employee_count
FROM employees e
JOIN department_stats ds ON e.department = ds.department
WHERE e.title ILIKE ${searchTerm}
AND e.salary >= ${minSalary}
ORDER BY e.salary DESC
.compile();
`

TypeScript Support

The library is built with TypeScript and provides full type safety:

`typescript
import { select, QueryBuilderParams, ConditionClauseItem } from '@rusdidev/pg-query-builder';

// Type-safe condition
const condition: ConditionClauseItem = {
column: "status",
operator: "eq",
value: "active"
};

// Type-safe query builder
const query = select({ table: "users" })
.addWhereClauseItem(condition);
`

Contributing

1. Fork the repository
2. Create your feature branch (git checkout -b feature/amazing-feature)
3. Commit your changes (git commit -m 'Add some amazing feature')
4. Push to the branch (git push origin feature/amazing-feature`)
5. Open a Pull Request

License

This project is licensed under the ISC License - see the LICENSE.md file for details.