SQL query builder reference

The SQL query builder gives you table-level, SQL-shaped methods for building typed queries: select(), innerJoin(), groupBy(), and the rest. It's the lower-level escape hatch that sits alongside the ORM client. Reach for it when you need a join, aggregate, or SQL feature the ORM client doesn't expose, or when you want direct control over the generated SQL.

This page documents every method, its options, and its return type. Where a method is capability-gated or has a runtime caveat, the Remarks call that out. For a task-oriented guide to when and how to reach for the builder, see Advanced queries.

Example schema

All examples on this page run against the following schema, and every example is transcribed from an executable test suite that runs against a live PostgreSQL database. The select and mutation examples use this user / post / post_tag / tag schema; the grouped-query examples use a separate customer / order schema, shown under Grouped queries.

types {
  Embedding1536 = pgvector.Vector(1536)
  Uuid          = String @db.Uuid
}

type Address {
  street  String
  city    String
  zip     String?
  country String
}

enum user_type {
  @@type("pg/text@1")
  admin
  user
}

enum Priority {
  @@type("pg/text@1")
  Low    = "low"
  High   = "high"
  Urgent = "urgent"
}

model User {
  id          Uuid      @id @default(uuid())
  email       String
  displayName String
  createdAt   DateTime  @default(now())
  kind        user_type
  address     Address?
  posts       Post[]
  tasks       Task[]

  @@map("user")
}

model Post {
  id        Uuid           @id @default(uuid())
  title     String
  userId    Uuid
  priority  Priority       @default(Low)
  createdAt DateTime       @default(now())
  embedding Embedding1536?

  user User  @relation(fields: [userId], references: [id])
  tags Tag[]

  @@map("post")
}

model Tag {
  id    Uuid   @id @default(uuid())
  label String @unique

  posts Post[]

  @@map("tag")
}

model PostTag {
  postId Uuid
  tagId  Uuid

  post Post @relation(fields: [postId], references: [id])
  tag  Tag  @relation(fields: [tagId], references: [id])

  @@id([postId, tagId])
  @@map("post_tag")
}

model Task {
  id          Uuid     @id @default(uuid())
  title       String
  description String?
  status      String   @default("open")
  type        String
  userId      Uuid
  createdAt   DateTime @default(now())

  user User @relation(fields: [userId], references: [id])

  @@discriminator(type)
  @@map("task")
}

model Bug {
  severity     String
  stepsToRepro String?
  @@base(Task, "bug")
  @@map("bug")
}

model Feature {
  priority      String
  targetRelease String?
  @@base(Task, "feature")
  @@map("feature")
}

Entry points

You build queries from the client's sql facet, which carries your contract's execution context. Create a Postgres client with postgres(...), then reach tables through db.sql.public. Table accessors use the contract's mapped table names (snake_case), so a User model mapped to user is reached as db.sql.public.user, and a Post/Tag junction mapped to post_tag is reached as db.sql.public.post_tag. public is the default PostgreSQL schema namespace.

The db.sql facet

Create the client with postgres(...), connect it to get a runtime, and build queries off db.sql.public. A select(), insert(), update(), or delete() call starts a query; you finish it with build() and run the resulting plan with runtime.execute(...).

import postgres from '@prisma-next/postgres/runtime';
import type { Contract } from './contract.d';
import contractJson from './contract.json' with { type: 'json' };

const db = postgres<Contract>({ contractJson, url: process.env.DATABASE_URL });
const runtime = await db.connect();

const plan = db.sql.public.user.select('id', 'email').build();
const users = await runtime.execute(plan);

The facet is built from the sql() factory: db.sql holds the result of an internal sql({ context, rawCodecInferer }) call, wired to the client's execution context and the adapter's codec inferer. You call sql(...) directly only when you're building your own database wrapper instead of using the postgres() client's facet. Pass your execution context (from your database client) and a rawCodecInferer (a fallback codec for raw expressions that have no column to infer one from):

import { sql } from '@prisma-next/sql-builder/runtime';

const publicSql = sql({ context, rawCodecInferer: { inferCodec: () => 'pg/text' } }).public;

This page reaches tables through the client facet and refers to the root as db.sql.public.

Table access

Every table on db.sql.public exposes the query-building methods. A select(), insert(), update(), or delete() call starts a query; you finish it with build() and run the resulting plan with runtime.execute(...).

db.sql.public.user.select('id', 'email'); // start a SELECT
db.sql.public.tag.insert([{ id, label: 'typescript' }]); // start an INSERT

Aliasing a query with .as()

A completed SELECT query can be used as a subquery source by calling .as(alias) on it. The aliased query becomes a join source you can pass to innerJoin(), outerLeftJoin(), and the other join methods. See Subquery via .as().

const highPriorityPosts = db.sql.public.post
  .select('id', 'userId')
  .where((f, fns) => fns.eq(f.priority, 'high'))
  .as('hp');

SELECT queries

These methods build and refine a SELECT. They chain, and you resolve the query with build() followed by runtime.execute(...).

The where(), select(), orderBy(), update(), and other callback forms receive two arguments: a field accessor f (columns keyed by name, or namespace-qualified after a join, such as f.post.id) and a function bag fns (the expression helpers).

select()

Project a row down to a chosen set of columns or computed expressions.

Options

select() has three forms:

A computed expression's result type comes from .returns(...) on fns.raw, or from the operation's own declared return type. See fns.raw and .returns().

Return type

Examples

Project a subset of columns

const plan = db.sql.public.user.select('id', 'email').build();
const rows = await runtime.execute(plan);
// rows[0] is { id, email } — no displayName

Add an aliased computed column

const plan = db.sql.public.user
  .select('id', 'displayName')
  .select('emailLength', (f, fns) => fns.raw`LENGTH(${f.email})`.returns('pg/int4@1'))
  .where((f, fns) => fns.eq(f.id, aliceId))
  .build();
const rows = await runtime.execute(plan);

Project multiple computed columns at once

const plan = db.sql.public.user
  .select((f, fns) => ({
    id: f.id,
    upperEmail: fns.raw`UPPER(${f.email})`.returns('pg/text@1'),
    emailLength: fns.raw`LENGTH(${f.email})`.returns('pg/int4@1'),
  }))
  .where((f, fns) => fns.eq(f.id, aliceId))
  .build();
const rows = await runtime.execute(plan);
// rows === [{ id: aliceId, upperEmail: 'ALICE@EXAMPLE.COM', emailLength: 17 }]

where()

Restrict a query to rows matching an expression.

Options

Return type

Examples

Combine comparisons with and() and or()

const plan = db.sql.public.post
  .select('id', 'title', 'priority')
  .where((f, fns) =>
    fns.or(
      fns.and(fns.eq(f.userId, aliceId), fns.eq(f.priority, 'high')),
      fns.eq(f.userId, carolId),
    ),
  )
  .build();
const rows = await runtime.execute(plan);

innerJoin()

Combine matching rows from two tables. After a join, address columns with their table namespace (f.post.id, f.user.email) to avoid ambiguity.

Options

Return type

Examples

Join posts to their authors

const plan = db.sql.public.post
  .innerJoin(db.sql.public.user, (f, fns) => fns.eq(f.post.userId, f.user.id))
  .select((f) => ({ postId: f.post.id, authorEmail: f.user.email }))
  .where((f, fns) => fns.eq(f.post.id, helloWorldId))
  .build();
const rows = await runtime.execute(plan);
// rows === [{ postId: helloWorldId, authorEmail: 'alice@example.com' }]

outerLeftJoin(), outerRightJoin(), outerFullJoin()

Keep unmatched rows from one or both sides, filling the missing side's columns with null. Each takes the same (other, on) arguments and returns a SelectQuery as innerJoin() does.

• outerLeftJoin() keeps every left-table row; unmatched right columns are null.
• outerRightJoin() keeps every right-table row; unmatched left columns are null.
• outerFullJoin() keeps unmatched rows from both sides.

Examples

Left join keeps rows with no match

const plan = db.sql.public.post
  .outerLeftJoin(db.sql.public.post_tag, (f, fns) => fns.eq(f.post.id, f.post_tag.postId))
  .select((f) => ({ postId: f.post.id, tagId: f.post_tag.tagId }))
  .where((f, fns) => fns.eq(f.post.id, untaggedPostId))
  .build();
const rows = await runtime.execute(plan);
// rows === [{ postId: untaggedPostId, tagId: null }]

lateralJoin()

Correlate a per-row subquery against the outer row: for each outer row, the joined subquery can reference that row's columns. Useful for a top-N-per-group query, such as each user's most recent post.

Remarks

• Availability: lateralJoin() requires the adapter to report the sql.lateral capability. Prisma Next's PostgreSQL adapter reports it, so the method is available on PostgreSQL. Adapters that don't report sql.lateral won't expose the method.
• The callback receives a lateral builder. Start its subquery with lateral.from(otherTable), then chain the usual SELECT methods. The subquery can filter on the outer row's columns (f.user.id).
• Return the query chain directly from the callback. Do not call .as(...) on it: lateralJoin()'s own first argument already names the derived table, and .as(...) returns the wrong shape (a bare join source), which throws subquery.getRowFields is not a function.
• When the outer table and the lateral table share a column name (for example both have id and createdAt), the merged scope drops the ambiguous names. Reach them through their table namespace instead (f.post.id, f.post.createdAt); a bare select('id') or orderBy((f) => f.createdAt, ...) throws because the ambiguous name isn't in scope.

Options

Return type

Examples

Each user's most recent post

const plan = db.sql.public.user
  .lateralJoin('latestPost', (lateral) =>
    lateral
      .from(db.sql.public.post)
      .select((f) => ({ id: f.post.id, title: f.post.title }))
      .where((f, fns) => fns.eq(f.post.userId, f.user.id))
      .orderBy((f) => f.post.createdAt, { direction: 'desc' })
      .limit(1),
  )
  .select((f) => ({ userId: f.user.id, latestPostId: f.latestPost.id }))
  .where((f, fns) => fns.eq(f.user.id, aliceId))
  .build();
const rows = await runtime.execute(plan);
// rows === [{ userId: aliceId, latestPostId: newestPostId }]

orderBy()

Sort the result set by a column, a computed expression, or with explicit direction and null placement.

Options

orderBy() accepts a column name or an expression callback, followed by options.

Return type

Examples

Sort by a column

const plan = db.sql.public.user.select('id', 'email').orderBy('email', { direction: 'asc' }).build();
const rows = await runtime.execute(plan);

Sort by a computed value

const plan = db.sql.public.user
  .select('id', 'email')
  .orderBy((f, fns) => fns.raw`LENGTH(${f.email})`.returns('pg/int4@1'), { direction: 'asc' })
  .build();
const rows = await runtime.execute(plan);

Control direction and null placement

const plan = db.sql.public.post
  .select('id', 'embedding')
  .orderBy('embedding', { direction: 'asc', nulls: 'first' })
  .build();
const rows = await runtime.execute(plan);

distinct()

De-duplicate identical projected rows (SELECT DISTINCT).

Return type

Examples

De-duplicate projected rows

const plan = db.sql.public.post.select('priority').distinct().build();
const rows = await runtime.execute(plan);
// distinct priorities: ['high', 'low', 'urgent']

distinctOn()

Keep the first row per distinct key, according to the query's orderBy() (DISTINCT ON).

Remarks

• Availability: distinctOn() requires the adapter to report the postgres.distinctOn capability. DISTINCT ON is a PostgreSQL-only SQL extension, so it lives under the postgres capability namespace rather than the shared sql one. It's available on Prisma Next's PostgreSQL adapter.
• Pair it with an orderBy() on the same key(s) to control which row is kept per key. This ordering requirement is not type-enforced; supply it yourself for a meaningful result.

Options

Return type

Examples

First post per user by date

const plan = db.sql.public.post
  .select('id', 'userId', 'createdAt')
  .orderBy('userId', { direction: 'asc' })
  .orderBy('createdAt', { direction: 'asc' })
  .distinctOn('userId')
  .build();
const rows = await runtime.execute(plan);
// one row per user, each the earliest post by createdAt

limit() and offset()

Cap the number of returned rows and skip rows in the ordered result set.

Options

Return type

Examples

Page through results

const plan = db.sql.public.user
  .select('id')
  .orderBy('email', { direction: 'asc' })
  .limit(1)
  .offset(1)
  .build();
const rows = await runtime.execute(plan);

Subquery via .as()

Alias a completed SELECT query with .as(alias) to use it as a join source. The aliased query can be passed to any join method as the other argument.

Options

Return type

Examples

Join against a subquery

const highPriorityPosts = db.sql.public.post
  .select('id', 'userId')
  .where((f, fns) => fns.eq(f.priority, 'high'))
  .as('hp');

const plan = db.sql.public.user
  .innerJoin(highPriorityPosts, (f, fns) => fns.eq(f.user.id, f.hp.userId))
  .select((f) => ({ userId: f.user.id, postId: f.hp.id }))
  .build();
const rows = await runtime.execute(plan);

Grouped queries

Grouping starts with groupBy(), which turns a query into a GroupedQuery. A grouped query supports having() for group-level filtering, the aggregate functions, and the same orderBy() / limit() / offset() / distinct() surface as a SelectQuery.

The examples below use a separate customer / order schema, where order.amount and order.quantity are numeric columns:

types {
  Uuid = String @db.Uuid
}

model Customer {
  id      Uuid   @id @default(uuid())
  name    String
  segment String

  orders Order[]

  @@map("customer")
}

model Order {
  id         Uuid     @id @default(uuid())
  customerId Uuid
  amount     Int
  quantity   Int
  placedAt   DateTime @default(now())

  customer Customer @relation(fields: [customerId], references: [id])

  @@map("order")
}

groupBy()

Group rows by one or more columns or by a computed expression, producing one row per distinct group.

Options

Return type

Examples

Group by a column with a count

const plan = db.sql.public.order
  .select('customerId')
  .select('orderCount', (f, fns) => fns.count(f.id))
  .groupBy('customerId')
  .orderBy('customerId', { direction: 'asc' })
  .build();
const rows = await runtime.execute(plan);
// orderCount decodes as a string: e.g. { customerId: acmeId, orderCount: '5' }

Group by a computed value

const plan = db.sql.public.order
  .select('yearPlaced', (f, fns) => fns.raw`EXTRACT(YEAR FROM ${f.placedAt})`.returns('pg/int4@1'))
  .select('orderCount', (f, fns) => fns.count(f.id))
  .groupBy((f, fns) => fns.raw`EXTRACT(YEAR FROM ${f.placedAt})`.returns('pg/int4@1'))
  .build();
const rows = await runtime.execute(plan);
// rows === [{ yearPlaced: '2024', orderCount: '10' }] — both decode as strings

having()

Filter groups by an aggregate comparison. Build the predicate from the aggregate functions (fns.count, fns.sum, fns.avg, fns.min, fns.max) and the comparison functions, comparing against a JavaScript literal.

Remarks

• PostgreSQL evaluates the comparison server-side, so it works correctly even though the aggregate value would decode as a string on the JavaScript side (see the warning above).

Options

Return type

Examples

Filter groups by a sum threshold

const plan = db.sql.public.order
  .select('customerId')
  .select('totalAmount', (f, fns) => fns.sum(f.amount))
  .groupBy('customerId')
  .having((f, fns) => fns.gt(fns.sum(f.amount), 1000))
  .build();
const rows = await runtime.execute(plan);
// only the customer whose orders sum over 1000; totalAmount decodes as '1500'

Compare with count(), avg(), min(), max()

const avgPlan = db.sql.public.order
  .select('customerId')
  .select('avgAmount', (f, fns) => fns.avg(f.amount))
  .groupBy('customerId')
  .having((f, fns) => fns.gt(fns.avg(f.amount), 100))
  .build();

const minMaxPlan = db.sql.public.order
  .select('customerId')
  .select('minAmount', (f, fns) => fns.min(f.amount))
  .select('maxAmount', (f, fns) => fns.max(f.amount))
  .groupBy('customerId')
  .having((f, fns) => fns.eq(fns.min(f.amount), 100))
  .build();

const avgRows = await runtime.execute(avgPlan);
const minMaxRows = await runtime.execute(minMaxPlan);
// avgAmount decodes as a string ('300.0000000000000000');
// minAmount / maxAmount decode as numbers (100 / 500)

Ordering and limiting a grouped query

A GroupedQuery supports the same orderBy(), limit(), offset(), distinct(), and distinctOn() methods as a SelectQuery. Sort by an aggregate alias to order groups.

Examples

Order groups by total, keep the top one

const plan = db.sql.public.order
  .select('customerId')
  .select('totalAmount', (f, fns) => fns.sum(f.amount))
  .groupBy('customerId')
  .orderBy('totalAmount', { direction: 'desc' })
  .limit(1)
  .build();
const rows = await runtime.execute(plan);
// the single highest-spending customer

Mutations

Mutations start from a table with insert(), update(), or delete(), and end with build(). Add returning() to get the affected rows back.

insert()

Insert one or more rows in a single statement.

Remarks

• insert() always takes an array. A single-row insert is a one-element array; there is no separate single-row overload.
• Multiple rows are inserted in one INSERT ... VALUES (...), (...) statement, not one round-trip per row.

Options

Return type

Examples

Insert a single row

const plan = db.sql.public.tag.insert([{ id: crypto.randomUUID(), label: 'single-row-tag' }]).build();
await runtime.execute(plan);

Insert multiple rows in one statement

const plan = db.sql.public.tag
  .insert([
    { id: crypto.randomUUID(), label: 'multi-row-a' },
    { id: crypto.randomUUID(), label: 'multi-row-b' },
  ])
  .build();
await runtime.execute(plan);

returning()

Return columns from the rows affected by an insert(), update(), or delete().

Remarks

• Availability: returning() requires the adapter to report the sql.returning capability. Prisma Next's PostgreSQL adapter reports it. Adapters that don't (for example a SQL target without RETURNING) won't expose the method.

Options

Return type

Examples

Return the inserted row

const id = crypto.randomUUID();
const plan = db.sql.public.tag.insert([{ id, label: 'returned-tag' }]).returning('id', 'label').build();
const rows = await runtime.execute(plan);
// rows === [{ id, label: 'returned-tag' }]

update()

Update matched rows. Set columns with a values object, or derive new values from existing columns with an expression callback. Gate the update with where(), and use returning() to get the updated rows.

Options

update() accepts a values object or an expression callback.

Return type

Examples

Update with a values object

const plan = db.sql.public.user
  .update({ displayName: 'Bobby' })
  .where((f, fns) => fns.eq(f.id, bobId))
  .returning('id', 'displayName')
  .build();
const rows = await runtime.execute(plan);
// rows === [{ id: bobId, displayName: 'Bobby' }]

Derive a value from an existing column

const plan = db.sql.public.user
  .update((f, fns) => ({ displayName: fns.raw`UPPER(${f.displayName})`.returns('pg/text@1') }))
  .where((f, fns) => fns.eq(f.id, carolId))
  .returning('id', 'displayName')
  .build();
const rows = await runtime.execute(plan);
// rows === [{ id: carolId, displayName: 'CAROL' }]

delete()

Delete matched rows. Gate the delete with where(), and use returning() to get the deleted rows back.

Return type

Examples

Delete and return the removed row

const plan = db.sql.public.tag
  .delete()
  .where((f, fns) => fns.eq(f.id, id))
  .returning('id', 'label')
  .build();
const rows = await runtime.execute(plan);
// rows === [{ id, label: 'to-delete' }]

param()

Force an explicit codec on a raw value that has no column context to infer one from, for example a literal interpolated into fns.raw.

Remarks

• Import param from @prisma-next/sql-relational-core/expression.
• You rarely need param(). Values passed to insert(), update(), and comparison functions such as fns.eq(f.col, value) are already auto-parameterized, with codecs inferred from the target column. param() is the manual escape hatch for a bound value with no adjacent column to derive a codec from.
• There is no build({ params }) form. Parameter values are embedded into the query where they're supplied. See build().

Options

Return type

Examples

Bind a literal inside a raw fragment

import { param } from '@prisma-next/sql-relational-core/expression';

const targetEmailDomain = param('%@example.com', { codecId: 'pg/text@1' });
const plan = db.sql.public.user
  .select('id', 'email')
  .where((f, fns) => fns.raw`${f.email} LIKE ${targetEmailDomain}`.returns('pg/bool@1'))
  .orderBy('email', { direction: 'asc' })
  .build();
const rows = await runtime.execute(plan);

Expressions and functions

Callback forms receive a function bag fns alongside the field accessor. fns holds the built-in expression helpers you use to build comparisons, boolean combinators, aggregates, and raw SQL.

Built-in functions

The complete built-in set is fixed:

Nothing else is built in. Any further function is registered dynamically: ilike is registered by the Postgres adapter for textual columns, while functions like cosineDistance come from an extension pack (pgvector) registered for your contract; without the matching adapter or extension pack, those functions are absent.

fns.raw and .returns()

Write a raw SQL fragment as a tagged template. Interpolate columns and typed expressions with ${...}; each interpolation is parameterized. For bare JavaScript values, always wrap them in param(...) with an explicit codec id: bare-scalar interpolation is currently broken on PostgreSQL (see the raw queries reference for the verified details). Call .returns(codecId) to declare the fragment's result type.

Options

Remarks

• .returns(codecId) is a compile-time type annotation only. It does not cast the value in SQL or change how the driver decodes it. A raw expression PostgreSQL computes as numeric still decodes as a string even when annotated 'pg/int4@1' (see the grouped-query warning).
• Use fns.raw for any SQL feature without a dedicated helper: COALESCE, CAST, LENGTH, UPPER, EXTRACT, and so on.

Examples

Compute a column with a SQL function

const plan = db.sql.public.user
  .select('emailLength', (f, fns) => fns.raw`LENGTH(${f.email})`.returns('pg/int4@1'))
  .build();
const rows = await runtime.execute(plan);

COALESCE via fns.raw

const plan = db.sql.public.order
  .select('amountOrZero', (f, fns) => fns.raw`COALESCE(${f.amount}, 0)`.returns('pg/int4@1'))
  .build();
const rows = await runtime.execute(plan);

Compiling and executing

build()

Compile a query into an executable plan.

Remarks

• build() takes zero arguments on every query type: select, insert, update, delete, and grouped. Parameter values are embedded into the plan where they're supplied (inside insert([...]), a where() callback, or a param() call), not passed to build(). There is no build({ params }) form.

Return type

Executing a plan

Run a plan with runtime.execute(plan), where runtime is the runtime you got from connecting your database client (const runtime = await db.connect()). It resolves to an array of rows (Row[]).

const plan = db.sql.public.user.select('id', 'email').build();
const rows = await runtime.execute(plan); // Row[]

ResultType

Recover a plan's row type at the type level.

Remarks

• Import ResultType from @prisma-next/framework-components/runtime. It's a family-agnostic utility, not specific to the SQL builder.
• ResultType<typeof plan> is the per-row shape, not an array. runtime.execute(plan) resolves to Row[], but ResultType<typeof plan> is Row.

Examples

Recover the row type from a plan

import type { ResultType } from '@prisma-next/framework-components/runtime';

const plan = db.sql.public.user.select('id', 'email').build();
type Row = ResultType<typeof plan>; // { id: string; email: string }

const rows = await runtime.execute(plan); // Row[]

Streaming vs. collecting

runtime.execute(plan) collects every row into an array. This is the common case and what every example on this page uses. For large result sets that you'd rather process incrementally, use the ORM client's streaming terminals, documented under AsyncIterableResult.