@bernierllc/query-builder

Type-safe SQL query builder for PostgreSQL with automatic parameter binding to prevent SQL injection attacks.

Features

• Type-Safe Query Building - Full TypeScript support with IDE autocomplete
• SQL Injection Prevention - Automatic parameter binding with PostgreSQL-style placeholders ($1, $2, etc.)
• Fluent API - Chainable methods for intuitive query construction
• Zero Dependencies - Pure utility package with no runtime dependencies
• Comprehensive Operators - Support for all common SQL operators (=, !=, >, <, >=, <=, LIKE, ILIKE, IN, IS NULL, etc.)
• JOIN Support - INNER, LEFT, RIGHT, and FULL joins
• Full Query Support - SELECT, WHERE, JOIN, ORDER BY, LIMIT, and OFFSET

Installation

npm install @bernierllc/query-builder

Usage

Basic SELECT Query

import { QueryBuilder } from '@bernierllc/query-builder';

const query = new QueryBuilder()
  .select('id', 'name', 'email')
  .from('users')
  .build();

// Result: { sql: 'SELECT id, name, email FROM users', params: [] }

WHERE Clauses

// Simple WHERE with automatic parameterization
const query = new QueryBuilder()
  .select('*')
  .from('users')
  .where('status', '=', 'active')
  .where('age', '>=', 18)
  .build();

// Result: { sql: 'SELECT * FROM users WHERE status = $1 AND age >= $2', params: ['active', 18] }

OR Conditions

const query = new QueryBuilder()
  .select('*')
  .from('users')
  .where('status', '=', 'active')
  .where('status', '=', 'pending', { logical: 'OR' })
  .build();

// Result: WHERE status = $1 OR status = $2

LIKE and ILIKE Operators

// Case-sensitive LIKE
const query = new QueryBuilder()
  .select('*')
  .from('users')
  .where('email', 'LIKE', '%@example.com')
  .build();

// Case-insensitive ILIKE (PostgreSQL)
const query2 = new QueryBuilder()
  .select('*')
  .from('users')
  .where('name', 'ILIKE', '%john%')
  .build();

IN Operator

const query = new QueryBuilder()
  .select('*')
  .from('users')
  .where('id', 'IN', [1, 2, 3, 4, 5])
  .build();

// Result: { sql: 'SELECT * FROM users WHERE id IN ($1, $2, $3, $4, $5)', params: [1, 2, 3, 4, 5] }

NULL Checks

const query = new QueryBuilder()
  .select('*')
  .from('users')
  .where('deleted_at', 'IS NULL')
  .build();

const query2 = new QueryBuilder()
  .select('*')
  .from('users')
  .where('email', 'IS NOT NULL')
  .build();

Object-based WHERE Conditions

// Convenient for multiple AND conditions
const query = new QueryBuilder()
  .select('*')
  .from('users')
  .whereObject({
    status: 'active',
    role: 'admin',
    verified: true
  })
  .build();

// Result: WHERE status = $1 AND role = $2 AND verified = $3

JOIN Operations

// INNER JOIN
const query = new QueryBuilder()
  .select('users.id', 'users.name', 'posts.title')
  .from('users')
  .innerJoin('posts', 'users.id = posts.user_id')
  .build();

// LEFT JOIN
const query2 = new QueryBuilder()
  .select('*')
  .from('users')
  .leftJoin('posts', 'users.id = posts.user_id')
  .build();

// Multiple JOINs
const query3 = new QueryBuilder()
  .select('*')
  .from('users')
  .innerJoin('posts', 'users.id = posts.user_id')
  .leftJoin('comments', 'posts.id = comments.post_id')
  .build();

ORDER BY

// Single ORDER BY
const query = new QueryBuilder()
  .select('*')
  .from('users')
  .orderBy('created_at', 'DESC')
  .build();

// Multiple ORDER BY clauses
const query2 = new QueryBuilder()
  .select('*')
  .from('users')
  .orderBy('status', 'ASC')
  .orderBy('created_at', 'DESC')
  .build();

// Defaults to ASC if direction not specified
const query3 = new QueryBuilder()
  .select('*')
  .from('users')
  .orderBy('name')
  .build();

LIMIT and OFFSET (Pagination)

// LIMIT only
const query = new QueryBuilder()
  .select('*')
  .from('users')
  .limit(10)
  .build();

// LIMIT with OFFSET
const query2 = new QueryBuilder()
  .select('*')
  .from('users')
  .limit(10)
  .offset(20)  // Skip first 20 rows
  .build();

// Note: OFFSET requires LIMIT in PostgreSQL

Complex Queries

const query = new QueryBuilder()
  .select('users.id', 'users.name', 'posts.title', 'posts.created_at')
  .from('users')
  .innerJoin('posts', 'users.id = posts.user_id')
  .where('users.status', '=', 'active')
  .where('posts.published', '=', true)
  .where('posts.category', 'IN', ['tech', 'science', 'education'])
  .orderBy('posts.created_at', 'DESC')
  .limit(10)
  .offset(20)
  .build();

// Result: Fully parameterized query ready for database execution

Using with Database Clients

Node-Postgres (pg)

import { Pool } from 'pg';
import { QueryBuilder } from '@bernierllc/query-builder';

const pool = new Pool();

const { sql, params } = new QueryBuilder()
  .select('*')
  .from('users')
  .where('status', '=', 'active')
  .limit(10)
  .build();

const result = await pool.query(sql, params);
console.log(result.rows);

Query Builder State Management

// Reuse the same builder instance
const builder = new QueryBuilder();

// Build first query
const query1 = builder
  .select('*')
  .from('users')
  .build();

// Reset and build new query
const query2 = builder
  .reset()
  .select('*')
  .from('posts')
  .build();

// Get current query structure (for debugging)
const structure = builder.getQuery();
console.log(structure);

API Reference

QueryBuilder Class

Methods

• select(...columns: string[]) - Specify columns to select (defaults to '*')
• from(table: string) - Specify the table to query from
• where(column: string, operator: WhereOperator, value?: unknown, options?: WhereOptions) - Add WHERE condition
• whereObject(conditions: Record<string, unknown>) - Add multiple AND conditions from object
• join(type: JoinType, table: string, on: string) - Add JOIN clause
• innerJoin(table: string, on: string) - Add INNER JOIN (convenience method)
• leftJoin(table: string, on: string) - Add LEFT JOIN (convenience method)
• rightJoin(table: string, on: string) - Add RIGHT JOIN (convenience method)
• fullJoin(table: string, on: string) - Add FULL JOIN (convenience method)
• orderBy(column: string, direction?: OrderDirection) - Add ORDER BY clause
• limit(n: number) - Set LIMIT clause
• offset(n: number) - Set OFFSET clause
• build() - Build the final SQL query with parameters
• reset() - Reset the query builder state
• getQuery() - Get copy of current query structure (for debugging)

Types

WhereOperator

type WhereOperator = '=' | '!=' | '>' | '<' | '>=' | '<=' | 'LIKE' | 'ILIKE' | 'IN' | 'NOT IN' | 'IS NULL' | 'IS NOT NULL';

OrderDirection

type OrderDirection = 'ASC' | 'DESC';

JoinType

type JoinType = 'INNER' | 'LEFT' | 'RIGHT' | 'FULL';

LogicalOperator

type LogicalOperator = 'AND' | 'OR';

BuiltQuery

interface BuiltQuery {
  sql: string;      // The SQL query with $1, $2, etc. placeholders
  params: unknown[]; // Array of parameter values
}

SQL Injection Prevention

All values are automatically parameterized to prevent SQL injection. User-provided values are never interpolated directly into the SQL string - they are always passed as parameterized values that the database driver handles safely.

Validation and Error Handling

The query builder validates queries during build:

• FROM clause is required
• OFFSET requires LIMIT in PostgreSQL
• LIMIT must be non-negative
• IN operator requires an array value

License

Copyright (c) 2025 Bernier LLC. All rights reserved.

This file is licensed to the client under a limited-use license. The client may use and modify this code only within the scope of the project it was delivered for. Redistribution or use in other products or commercial offerings is not permitted without written consent from Bernier LLC.