Query builder for postgres, the result is a string and query parameter
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.
- 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
``bash`
npm install @rusdidev/pg-query-builder
`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"]]
`
`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();
`
`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();
`
`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();
`
`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();
`
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;`
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" } |
`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();
`
`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();
`
`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();
`
`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);
`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);
`
| 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[]] |
| 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") |
| Method | Description | Example |
|--------|-------------|---------|
| addSetClauseItems(data) | Set update values | .addSetClauseItems({ name: "John Doe", status: "active" }) |
`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();
`
`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();
`
`typescriptlogin_count + 1
const [sql, params] = update({ table: "users" })
.addSetClauseItems({
last_login: new Date(),
login_count: raw`
})
.addWhereClauseItem({ column: "email", operator: "eq", value: "user@example.com" })
.addReturnItems("id", "last_login", "login_count")
.compile();
`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();`
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);
`
1. Fork the repository
2. Create your feature branch (git checkout -b feature/amazing-feature)git commit -m 'Add some amazing feature'
3. Commit your changes ()git push origin feature/amazing-feature`)
4. Push to the branch (
5. Open a Pull Request
This project is licensed under the ISC License - see the LICENSE.md file for details.