Schema

SQL Schema

sqlSchema lets you register named queries and control optional SQL blocks with schemaCase and typed inputs viaschemaParam.

Why It Exists

Think of SQL Schema as a small query DSL for feature flags and typed runtime input. You define one canonical query shape, then turn pieces on or off safely.

Reuse

Register named queries once with setQuery and call them anywhere via schema.query("name").

Safety

Constrain input types with schemaParam and fail fast when runtime values do not match.

Flexibility

Toggle optional SQL blocks with schemaCase instead of duplicating near-identical query builders.

Define Query Schema

Register reusable queries with setQuery, then gate optional clauses using schemaCase.

import { sqlBuilder, sqlSchema } from "@gntrees/sql-builder/pg"

const q = sqlBuilder().setFormatParamHandler("pg")
const sch = sqlSchema()

const schema = sch.setQuery(
  "getUsers",
  sch.set.query(
    q
      .select(q.c("users.id"), q.c("users.name"))
      .from(q.t("users"))
      .schemaCase(
        "filter",
        q.where(
          q.ilike(
            q.c("users.name"),
            q.schemaParam("name").string().default("%john%"),
          ),
        ),
      )
      .limit(q.schemaParam("limit").number().default(10)),
  ),
)

schemaParam Type Rules

If schemaParam("key") is used without.number(), .string(),.boolean(), or .nullable(), it acceptsstring | number | boolean | null. No coercion is applied.

Chaining type methods creates a union. Example:.number().string() means valid runtime values arenumber | string.

If runtime data does not match declared types, the builder throwsInvalid value for SqlSchemaParam.

default(...) must match the current union. For.number().string(), default can only benumber or string.

DefinitionAccepted Runtime Type
schemaParam("x")string | number | boolean | null
schemaParam("x").number()number
schemaParam("x").number().string()number | string
schemaParam("x").string().nullable()string | null
const q = sqlBuilder().setFormatParamHandler("pg")

// 1) No explicit type
const flexible = q.schemaParam("keyword")
// accepted runtime value: string | number | boolean | null

// 2) Single type
const onlyNumber = q.schemaParam("limit").number()
// accepted runtime value: number

// 3) Multiple type methods produce UNION
const numberOrString = q.schemaParam("page").number().string()
// accepted runtime value: number | string

// 4) Add null explicitly
const nullableText = q.schemaParam("search").string().nullable()
// accepted runtime value: string | null

// 5) default(...) follows the currently declared type union
q.schemaParam("limit").number().string().default("10") // valid
q.schemaParam("limit").number().string().default(10) // valid
// q.schemaParam("limit").number().string().default(true) // type error

Set Runtime Params

Use setParams to override defaults. Missing values keep the default value; schemaCase can be skipped by passingundefined or false.

const builder = schema.query("getUsers").setParams({
  filter: { name: "%anna%" },
  limit: 5,
})

builder.getSql()
// SELECT "users"."id", "users"."name" FROM "users" WHERE "users"."name" ILIKE $1 LIMIT $2

builder.getSqlParameters()
// ["%anna%", 5]

// if filter is undefined/false, schemaCase block is skipped
schema.query("getUsers").setParams({ limit: 5 })

schemaCase Behavior

Runtime value controls whether the case block is injected into final SQL and how nested params are resolved.

setParams valueWhat happens
trueCase block is included; nested schema params use defaults when provided.
falseCase block is skipped.
undefined (missing key)Case block is skipped.
{}Case block is included; behaves like true with empty nested params.
{ name: "%john%" }Case block is included; provided object overrides matching nested schemaParam values.
const query = q
  .select("*")
  .from(q.t("users"))
  .schemaCase(
    "filter",
    q.where(
      q.ilike(q.c("users.name"), q.schemaParam("name").string().default("%test%")),
    ),
  )

query.setParams({ filter: false })
// schemaCase skipped

query.setParams({ filter: true })
// schemaCase included, nested params use their default values

query.setParams({ filter: {} })
// schemaCase included, same as true (nested params fall back to default)

query.setParams({ filter: { name: "%john%" } })
// schemaCase included, nested object overrides matching schemaParam values

Nested schemaCase

You can nest schemaCase blocks for deeper conditional filters, while keeping typed param checks for nested objects.

const schema = sch.setQuery(
  "getUsers",
  sch.set.query(
    q
      .select("*")
      .from(q.t("users"))
      .schemaCase(
        "filter",
        q.where(
          q.and(
            q.ilike(q.c("users.name"), q.schemaParam("name").string().default("%test%"))
              .schemaCase(
                "ageFilter",
                q.sub(
                  q.and(
                    q.gt(q.c("users.age"), q.schemaParam("age").number().default(18)),
                  ),
                ),
              ),
          ),
        ),
      )
      .limit(q.schemaParam("limit").number().default(10)),
  ),
)

schema.query("getUsers").setParams({
  filter: {
    name: "%magma%",
    ageFilter: {
      age: 21,
    },
  },
  limit: 20,
})