Skip to main content

Documentation Index

Fetch the complete documentation index at: https://mintlify.com/prisma/prisma-next/llms.txt

Use this file to discover all available pages before exploring further.

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();
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']

Build docs developers (and LLMs) love

Get started for freeTalk to us