Guides / Select

Build a SELECT

Choose fields, add a source, and join tables to build a typed SELECT query.

Start with a projection and a source

The first argument to select() is the projection. The remaining arguments are independent clauses. A source-aware column must be provided by from() or a join:

import { eq, from, integer, render, select, table, text, where } from "qubu"

const users = table("users", {
  id: integer(),
  name: text(),
  email: text({ nullable: true }),
})

const query = select({ id: users.id, name: users.name }, from(users), where(eq(users.id, 7)))

render(query).text
// SELECT "users"."id" AS "id", "users"."name" AS "name" FROM "users" WHERE ("users"."id" = ?)

An object projection uses its keys as result names. Name the fields you intend to return in the usual case. Use all(source) when you want every source column. It returns the source's columns as a named projection object, so it can still be spread alongside computed expressions:

import { all, from, select, upper } from "qubu"

const query = select({ ...all(users), normalizedName: upper(users.name) }, from(users))

all(source) expands to explicit named columns rather than emitting source.*. That keeps the SQL output and the inferred row keys aligned.

Add joins and predicates

Join functions add a source and make the join condition part of the same scope check as the projection and WHERE clause:

import { eq, from, innerJoin, isNotNull, leftJoin, select, table, text, integer, where } from "qubu"

const posts = table("posts", {
  id: integer(),
  authorId: integer(),
  title: text(),
})

const query = select(
  { userId: users.id, title: posts.title },
  from(users),
  innerJoin(posts, eq(users.id, posts.authorId)),
  where(isNotNull(users.email)),
)

Use innerJoin, leftJoin, rightJoin, or fullJoin with an ON condition. crossJoin and naturalJoin add a source without a condition; use them only when that SQL behavior is intentional.

Handle missing joined rows

leftJoin() makes columns from the joined source nullable in the selected row:

const summary = select(
  {
    userName: users.name,
    postTitle: posts.title,
  },
  from(users),
  leftJoin(posts, eq(users.id, posts.authorId)),
)

// typeof summary.row:
// { userName: string; postTitle: string | null }

Expression nullability follows its own contract: upper(posts.title) also becomes nullable, while count(posts.id) stays non-null in a grouped query.

rightJoin() makes the preceding relation nullable; fullJoin() makes both sides nullable. This includes expressions that depend on those sources. With several joins, widening follows their SQL order; see result shapes.

Add conditions

Compose boolean expressions explicitly:

import { and, gt, inList, or } from "qubu"

const filter = and(gt(users.id, 0), or(inList(users.id, [7, 8]), eq(users.name, "Ada")))

const filtered = select({ id: users.id, name: users.name }, from(users), where(filter))

Values such as 7, 'Ada', and list members become parameters. They are not interpolated into SQL.