node-condition-builder

ConditionBuilder

A lightweight TypeScript library for building parameterized SQL WHERE clauses. It generates safe, injection-free condition strings with proper placeholder indexing for PostgreSQL and MySQL.

What it is

A condition builder -- it takes your filter parameters and produces a SQL condition string plus an ordered array of values, ready to be passed to any database driver's parameterized query.

What it is NOT

• Not an ORM
• Not a query builder -- it only handles the WHERE clause
• Not a database driver -- it produces strings, you execute them

Use cases

• Building dynamic filters where conditions are optional
• APIs with search/filter endpoints where any combination of parameters may be present
• Anywhere you need safe, parameterized SQL conditions without string concatenation

Install

npm install node-condition-builder

Quick start

import { ConditionBuilder } from 'node-condition-builder';

const condition = new ConditionBuilder('AND')
  .isEqual('status', 'active')
  .isGreater('age', 18)
  .isNull('deleted_at', true);

condition.build();     // (status = $1 AND age > $2 AND deleted_at IS NULL)
condition.getValues(); // ['active', 18]

Undefined values are ignored

This is the key feature. When a value is undefined, the condition is silently skipped. This makes it trivial to build dynamic filters from optional parameters:

interface UserFilters {
  name?: string;
  email?: string;
  role?: string;
  minAge?: number;
  maxAge?: number;
  isVerified?: boolean;
  excludeRoles?: string[];
}

function filterUsers(filters: UserFilters) {
  const condition = new ConditionBuilder('AND')
    .isLike('name', filters.name ? `%${filters.name}%` : undefined)
    .isEqual('email', filters.email)
    .isEqual('role', filters.role)
    .isGreaterOrEqual('age', filters.minAge)
    .isLessOrEqual('age', filters.maxAge)
    .isNotNull('verified_at', filters.isVerified)
    .isNotIn('role', filters.excludeRoles);

  const sql = `SELECT * FROM users WHERE ${condition.build()}`;
  const values = condition.getValues();

  return db.query(sql, values);
}

// Only name and minAge provided:
filterUsers({ name: 'john', minAge: 18 });
// SELECT * FROM users WHERE (name LIKE $1 AND age >= $2)
// values: ['%john%', 18]

// No filters at all:
filterUsers({});
// SELECT * FROM users WHERE (TRUE)
// values: []

API

Every method is chainable and returns this.

Equality

Method	SQL
isEqual(field, value)	field = $1
isNotEqual(field, value)	field != $1

Comparison

Method	SQL
isGreater(field, value)	field > $1
isGreaterOrEqual(field, value)	field >= $1
isLess(field, value)	field < $1
isLessOrEqual(field, value)	field <= $1
isNotGreater(field, value)	field <= $1
isNotGreaterOrEqual(field, value)	field < $1
isNotLess(field, value)	field >= $1
isNotLessOrEqual(field, value)	field > $1

Range

Method	SQL
isBetween(field, from, to)	(field BETWEEN $1 AND $2)
isNotBetween(field, from, to)	(field NOT BETWEEN $1 AND $2)

isBetween supports partial bounds: if only from is provided it becomes >=, if only to it becomes <=. Use undefined to skip a bound.

Inclusion

Method	SQL
isIn(field, values)	field IN ($1, $2, ...)
isNotIn(field, values)	field NOT IN ($1, $2, ...)

Pattern matching

Method	SQL
isLike(field, value)	field LIKE $1
isNotLike(field, value)	field NOT LIKE $1
isILike(field, value)	field ILIKE $1
isNotILike(field, value)	field NOT ILIKE $1

ILIKE is PostgreSQL-specific (case-insensitive LIKE).

Null checks

Method	SQL
isNull(field, true)	field IS NULL
isNotNull(field, true)	field IS NOT NULL

The boolean parameter controls whether the condition is added. isNull('f', false) is a no-op.

Raw expressions

Use expression() to inject raw SQL where a value is expected. No placeholder is generated and no value is added to the parameter array:

const condition = new ConditionBuilder('AND')
  .isEqual('created_at', condition.expression('NOW()'))
  .isGreater('updated_at', condition.expression("NOW() - INTERVAL '1 day'"));

condition.build();     // (created_at = NOW() AND updated_at > NOW() - INTERVAL '1 day')
condition.getValues(); // []

Expressions with bound values

An expression can carry its own values. Write ? where a value goes -- the builder assigns the placeholder indexes, so you never compute an offset:

import { ConditionBuilder, Expression } from 'node-condition-builder';

const condition = new ConditionBuilder('AND')
  .isEqual('bonus', new Expression('COALESCE(discount, ?)', [0]));

condition.build();     // (bonus = COALESCE(discount, $1))
condition.getValues(); // [0]

An expression without values is emitted verbatim, exactly as before, so SQL containing a literal ? (such as PostgreSQL's jsonb operator) keeps working untouched. In an expression with values, escape a literal question mark as \?.

Expressions as the left-hand side

Every condition method accepts an expression in place of the field name. The expression's own values are bound before the compared value:

const bonus = new Expression('CASE WHEN role = ? THEN salary ELSE 0 END', ['admin']);

const condition = new ConditionBuilder('AND')
  .isEqual('dept', 'sales')
  .isGreater(bonus, 1000);

condition.build();     // (dept = $1 AND CASE WHEN role = $2 THEN salary ELSE 0 END > $3)
condition.getValues(); // ['sales', 'admin', 1000]

This is what makes computed or derived columns filterable without hand-managing placeholder positions. It works with all methods, including isBetween, isIn, isNull and the negated variants.

Composing a builder into a fragment

toExpression() renders a builder as a reusable fragment -- SQL with ? markers, plus the matching values -- so a sub-condition can be embedded in a larger statement (a subquery, a join clause) while the outer builder assigns every index:

const inner = new ConditionBuilder('AND')
  .isEqual('status', 'pending')
  .isGreater('total', 100);

const fragment = inner.toExpression();

const outer = new ConditionBuilder('AND')
  .isEqual('id', 7)
  .raw(`EXISTS (SELECT 1 FROM o WHERE o.c_id = c.id AND ${fragment.value})`, [...fragment.values]);

outer.build();
// (id = $1 AND EXISTS (SELECT 1 FROM o WHERE o.c_id = c.id AND (status = $2 AND total > $3)))
outer.getValues(); // [7, 'pending', 100]

Raw conditions

Use raw() to inject an arbitrary SQL fragment as a condition. Use ? as placeholder markers -- they will be replaced with the dialect's placeholders and values will be tracked:

const condition = new ConditionBuilder('AND')
  .isEqual('name', 'test')
  .raw('ST_Distance(point, ?) < ?', [somePoint, 100]);

condition.build();     // (name = $1 AND ST_Distance(point, $2) < $3)
condition.getValues(); // ['test', somePoint, 100]

You can also use raw() without values for static fragments:

condition.raw('active IS TRUE');

raw() follows the same undefined philosophy as the rest of the builder. When all values in the array are undefined, the condition is skipped. When some are undefined and some are not, it throws an error (since partial replacement in arbitrary SQL is ambiguous). Static calls without values (or with an empty array) are always added:

condition.raw('? BETWEEN col1 AND col2', [undefined]); // skipped (all undefined)
condition.raw('? BETWEEN col1 AND ?', [42, undefined]); // throws Error
condition.raw('active IS TRUE');                         // always added (no values)

Use \? to include a literal ? without it being treated as a placeholder (useful for PostgreSQL's jsonb ? operator):

condition.raw('data::jsonb \\? ? AND active = ?', ['key', true]);
// → data::jsonb ? $1 AND active = $2

Nesting with append

Use append() to nest a ConditionBuilder inside another, mixing AND/OR logic:

const condition = new ConditionBuilder('AND')
  .isEqual('active', true)
  .append(
    new ConditionBuilder('OR')
      .isEqual('role', 'admin')
      .isEqual('role', 'editor')
  );

condition.build();     // (active = $1 AND (role = $2 OR role = $3))
condition.getValues(); // [true, 'admin', 'editor']

Nesting is recursive -- you can nest as deep as you need.

Value types

The library exports three types that constrain what values each method accepts:

import type { SqlValue, ConditionValue, ConditionValueOrUndefined } from 'node-condition-builder';

type SqlValue = string | number | boolean | bigint | Date;
type ConditionValue = SqlValue | Expression;
type ConditionValueOrUndefined = ConditionValue | undefined;

Type	Used in
ConditionValueOrUndefined	Most methods: isEqual, isGreater, isLike, isBetween, etc.
ConditionValue[]	isIn, isNotIn (the array itself can be undefined to skip)
unknown[]	raw() values (escape hatch: accepts any value the DB driver supports)

undefined means "filter not provided" and silently skips the condition. null is not accepted -- use isNull() / isNotNull() for NULL checks, or expression('NULL') in raw SQL.

Dialects

PostgreSQL ($1, $2, ...) is the default. You can switch to MySQL (?) globally or per instance:

// Global
ConditionBuilder.DIALECT = 'mysql';

// Per instance (overrides global)
const condition = new ConditionBuilder('AND', 'mysql');

Empty conditions

An empty AND evaluates to (TRUE), an empty OR to (FALSE). This is safe to include in any query.

For LLMs / AI agents

This package ships an llm.md file with a concise, token-friendly API reference designed for LLM consumption. If you're using an AI coding agent, instruct it to read the file:

node_modules/node-condition-builder/llm.md