Building queries with the SQL DSL

The SQL DSL (db.sql) is Prisma Next’s lower-level query builder. It lets you compose type-safe SQL plans from individual operations — select, where, order, limit, join — without writing raw SQL strings. Plans are built offline (no network I/O) and executed separately, which makes them easy to inspect, test, and log.

Importing and using `db.sql`

db.sql is available on both the postgres() (Node) and postgresServerless() facades. It is part of the static authoring surface and is safe to use at module scope:

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

const db = postgres<Contract>({ contractJson });

// db.sql exposes a property per storage table name in your contract
// e.g. db.sql.user, db.sql.post, db.sql.order

Selecting columns

Use .select() with field names from your contract. The return type is inferred from the columns you pick:

const plan = db.sql.user
  .select('id', 'email', 'createdAt')
  .limit(10)
  .build();

// result type: Array<{ id: string; email: string; createdAt: Date }>

Filtering with `.where()`

The .where() callback receives two arguments: f (field references) and fns (built-in predicate functions). Use fns to build expressions:

const plan = db.sql.user
  .select('id', 'email')
  .where((f, fns) => fns.eq(f.id, userId))
  .build();

const plan = db.sql.order
  .select('id', 'total', 'status')
  .where((f, fns) => fns.gte(f.total, 100))
  .build();

const plan = db.sql.post
  .select('id', 'title', 'userId')
  .where((f, fns) => fns.eq(f.userId, userId))
  .limit(100)
  .build();

const plan = db.sql.post
  .select('id', 'title')
  .where((f, fns) => fns.and(
    fns.eq(f.userId, userId),
    fns.ne(f.embedding, null),
  ))
  .build();

Available fns predicates: eq, ne, gt, gte, lt, lte, in, notIn, and, or, exists, notExists. Extension packs may add additional predicates (for example, fns.cosineDistance from pgvector).

Ordering with `.orderBy()`

Pass a callback that selects a field and specifies a direction:

const plan = db.sql.post
  .select('id', 'title', 'createdAt')
  .orderBy((f) => f.createdAt, { direction: 'desc' })
  .build();

Limiting and paginating

Use .limit() for a hard row cap:

const plan = db.sql.post
  .select('id', 'title')
  .orderBy((f) => f.createdAt, { direction: 'desc' })
  .limit(20)
  .build();

Use .innerJoin() or .outerLeftJoin() to bring in columns from a related table. Use .as() to alias tables, then reference fields by alias in the expression callback:

// Self-join: find post pairs by different authors
const plan = db.sql.post
  .as('p1')
  .innerJoin(db.sql.post.as('p2'), (f, fns) => fns.ne(f.p1.userId, f.p2.userId))
  .select((f) => ({
    postAId: f.p1.id,
    postATitle: f.p1.title,
    postBId: f.p2.id,
    postBTitle: f.p2.title,
  }))
  .limit(10)
  .build();

Available join types: innerJoin, outerLeftJoin, outerRightJoin, outerFullJoin, and lateralJoin (requires the lateral capability in your contract).

Mutations: insert, update, delete

The table proxy also exposes insert(), update(), and delete(). Mutation queries support .returning() to get back the affected rows:

// Insert a row and return it
const plan = db.sql.user
  .insert({ email: 'alice@example.com' })
  .returning('id', 'email')
  .build();
const rows = await db.runtime().execute(plan);
const newUser = rows[0]; // { id: string; email: string }

// Update with a where clause and return
const updatePlan = db.sql.user
  .update({ email: 'new@example.com' })
  .where((f, fns) => fns.eq(f.id, userId))
  .returning('id', 'email')
  .build();

// Delete with a where clause
const deletePlan = db.sql.user
  .delete()
  .where((f, fns) => fns.eq(f.id, userId))
  .returning('id', 'email')
  .build();

Building a plan with `.build()`

.build() compiles the current builder state into a SqlQueryPlan. This is a pure in-memory operation — no database connection required:

const plan = db.sql.user
  .select('id', 'email')
  .where((f, fns) => fns.eq(f.id, userId))
  .limit(1)
  .build();

// Inspect the plan before executing
console.log(plan);

Building and executing are separate steps. .build() produces a serializable plan object; runtime.execute(plan) sends it to the database. You can build plans at module scope, cache them, or serialize them for logging — none of that requires a live connection.

Executing a plan with `runtime.execute()`

Call db.runtime() (synchronous — returns the cached runtime) and pass the plan to execute():

const plan = db.sql.user
  .select('id', 'email', 'createdAt')
  .where((f, fns) => fns.eq(f.id, userId))
  .limit(1)
  .build();

const rows = await db.runtime().execute(plan);
const user = rows[0]; // { id: string; email: string; createdAt: Date } | undefined

For streaming large result sets, iterate directly:

const plan = db.sql.post
  .select('id', 'title')
  .orderBy((f) => f.createdAt, { direction: 'asc' })
  .limit(10_000)
  .build();

for await (const row of db.runtime().execute(plan)) {
  process(row);
}

Result types from `contract.d.ts`

The return type of runtime.execute(plan) is fully inferred from the columns you selected. No manual casting is needed:

// rows is automatically typed from the selected columns
const rows = await db.runtime().execute(
  db.sql.user.select('id', 'email').build(),
);
// rows: Array<{ id: string; email: string }>

Parameterized queries

Values you pass to fns predicates are automatically encoded as bind parameters — you never interpolate raw values into query strings:

const userId = 'user-abc-123';

const plan = db.sql.post
  .select('id', 'title')
  .where((f, fns) => fns.eq(f.userId, userId))
  .build();

// The emitted SQL uses $1 bind parameters: WHERE user_id = $1
// with params: ['user-abc-123']

Get Started

Core Concepts

Guides

Extensions

Building queries with the SQL DSL

Importing and using `db.sql`

Selecting columns

Filtering with `.where()`

Ordering with `.orderBy()`

Limiting and paginating

Mutations: insert, update, delete

Building a plan with `.build()`

Executing a plan with `runtime.execute()`

Result types from `contract.d.ts`

Parameterized queries

​Importing and using db.sql

​Selecting columns

​Filtering with .where()

​Ordering with .orderBy()

​Limiting and paginating

​Joining related tables

​Mutations: insert, update, delete

​Building a plan with .build()

​Executing a plan with runtime.execute()

​Result types from contract.d.ts

​Parameterized queries

Importing and using `db.sql`

Selecting columns

Filtering with `.where()`

Ordering with `.orderBy()`

Limiting and paginating

Joining related tables

Mutations: insert, update, delete

Building a plan with `.build()`

Executing a plan with `runtime.execute()`

Result types from `contract.d.ts`

Parameterized queries