Schema

DB Schema

Use typed table and column objects from your DB schema directly in query builder methods such as select, from,join, and where.

Why It Exists

DB Schema turns your database structure into first-class TypeScript values. Instead of typing raw strings repeatedly, you work with typed table/column references.

Less typo risk

account.userId is safer than repeatedly writing"account.user_id" by hand.

Better autocomplete

Query composition is faster because your editor can suggest real columns from generated schema files.

Cleaner refactors

Schema changes are easier to track because typed imports reveal affected queries at compile time.

Install sql-builder-cli

Install CLI once, then generate schema files from your live database.

npm install -D @gntrees/sql-builder-cli
pnpm add -D @gntrees/sql-builder-cli
yarn add -D @gntrees/sql-builder-cli

Generate Schema File

Use the generate command to produce a typed DB schema file, then import tables/columns in your queries.

npx @gntrees/sql-builder-cli generate   --url="postgres://user:password@localhost:5432/app_db"   --output="./src/db/app-db.schema.ts"

Schema Table/Column

Schema literals are accepted in place of string identifiers for safer, typed query composition.

import { account, user, projects } from "./db.schema"
import { sqlBuilder } from "@gntrees/sql-builder/pg"

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

// table and column are strongly typed from schema objects
const accountQuery = q.select(account.id, account.userId).from(account)
const projectQuery = q.select(projects.id, projects.name).from(projects)

accountQuery.getSql()
projectQuery.getSql()

Join with Schema

Join conditions stay explicit while keeping table and column references typed.

const query = q
  .select(account.id, user.email)
  .from(account)
  .join(user)
  .on(q.eq(account.userId, user.id))

query.getSql()
// SELECT ... FROM "account" JOIN "user" ON "account"."user_id" = "user"."id"

Where, Group, Order

Schema-based columns also work for filtering, grouping, sorting, and pagination.

const query = q
  .select(account.userId, q.count("*"))
  .from(account)
  .where(q.op(account.updatedAt, "IS NOT", q.null()))
  .groupBy(account.userId)
  .orderBy(q.desc(account.createdAt))
  .limit(10)

query.getSqlWithParameters()