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

npm install @rusdidev/pg-query-builder

Quick Start

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

SELECT Queries

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();

INSERT Queries

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

// Single INSERT
const query = insert({ table: "users" })
  .addInsertColumns("name", "email", "age")
  .addInsertPayload({ name: "John", email: "[email protected]", age: 25 })
  .compile();

// Bulk INSERT
const query = insert({ table: "users" })
  .addInsertColumns("name", "email")
  .addInsertPayload(
    { name: "John", email: "[email protected]" },
    { name: "Jane", email: "[email protected]" }
  )
  .compile();

// INSERT with RETURNING
const query = insert({ table: "users" })
  .addInsertColumns("name", "email")
  .addInsertPayload({ name: "John", email: "[email protected]" })
  .addReturnItems("id", "created_at")
  .compile();

UPDATE Queries

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

// Basic UPDATE
const query = update({ table: "users" })
  .addSetClauseItems({ name: "John Doe", email: "[email protected]" })
  .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();

DELETE Queries

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();

Raw Queries

For complex queries or when you need full SQL control:

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

Subqueries

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();

Common Table Expressions (CTEs)

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();

Complex Conditions

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

With node-postgres (pg)

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] = raw`SELECT * FROM users WHERE id = ${userId}`.compile();
const rawResult = await client.query(rawSql, rawParams);

With Prisma

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

Query Builder Methods

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

INSERT Specific Methods

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

UPDATE Specific Methods

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

Examples

Complex SELECT with Subquery

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();

Bulk INSERT

const users = [
  { name: "John", email: "[email protected]", age: 25 },
  { name: "Jane", email: "[email protected]", age: 30 },
  { name: "Bob", email: "[email protected]", age: 35 }
];

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

Conditional UPDATE

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

Raw Query with Complex Logic

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:

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.