SQL Expr

A TypeScript library for building type-safe, serializable PostgreSQL filters with schema validation.

Features

• Parameterized values - All values are parameterized automatically
• Type Safe - Full TypeScript support with discriminated unions
• Schema-Driven - Define your database structure once, use everywhere
• JSON Operations - Complete PostgreSQL JSON operator support with chaining
• Serializable - Filter expressions can be JSON serialized/deserialized
• Framework Agnostic - Works with any PostgreSQL client (pg, Prisma, Slonik, etc.)

Usage

Simple Example

import { factory, buildSql } from 'sql-expr';

// Simple equality filter
const filter = factory.equals(
  factory.column("user.email"),
  factory.string("john@example.com")
);

const result = buildSql(filter, {
  columns: {
    "user.email": {
      table: "users",
      column: "email"
    }
  }
});

if (result.isOk()) {
  console.log(result.value.sql);
  // ("users"."email") = $1

  console.log(result.value.values);
  // ["john@example.com"]
}

JSON Operations Example

import { factory, buildSql } from 'sql-expr';

// Query nested JSON field
const filter = factory.equals(
  factory.column("user.profile.name"),
  factory.string("John")
);

const result = buildSql(filter, {
  columns: {
    "user.profile.name": {
      table: "users",
      column: "profile",
      jsonOperations: [{ type: "->>", key: "name" }]
    }
  }
});

if (result.isOk()) {
  console.log(result.value.sql);
  // ("users"."profile" ->> 'name') = $1

  console.log(result.value.values);
  // ["John"]
}

Available Factory Functions

// Comparison operations
factory.equals(left, right)
factory.notEquals(left, right)
factory.greaterThan(left, right)
factory.greaterThanOrEqual(left, right)
factory.lessThan(left, right)
factory.lessThanOrEqual(left, right)
factory.like(left, right)
factory.in(left, right)

// Logical operations
factory.and(left, right)
factory.or(left, right)

// Arithmetic operations
factory.add(left, right)
factory.subtract(left, right)
factory.multiply(left, right)
factory.divide(left, right)

// Value nodes
factory.column("column.path")
factory.string("text")
factory.number(42)
factory.bool(true)
factory.null()
factory.list([factory.string("a"), factory.string("b")])

License

MIT