Query APIs: SQL DSL and ORM client

Prisma Next ships two query surfaces that sit on the same execution foundation: a type-safe SQL plan builder (db.sql) and a fluent ORM client (db.orm). Both compile your intent into immutable, hashable query plans that are verified against the contract before execution. You can use them side by side in the same project.

How plans work

Neither API executes queries directly. Instead, they compile your query into an immutable plan — a structured object containing the SQL text, bound parameters, and metadata:

{
  sql:    "SELECT id, email FROM \"user\" LIMIT $1",
  params: [10],
  meta: {
    storageHash:  "sha256:e57551e…",
    profileHash:  "sha256:1a8dbe0…",
    lane:         "sql-dsl",
    annotations:  [],
    refs:         [{ table: "user", columns: ["id", "email"] }]
  }
}

Plans are:

Immutable — built once and reusable across requests
Hashable — the storageHash ties the plan to a specific contract version
Auditable — refs lists every table and column the plan touches
Portable — a plan can be serialized, inspected, or passed to any compatible runtime

Before the runtime executes a plan, it reads the database marker and verifies that storageHash and profileHash match. A mismatch surfaces immediately as a structured error — not silent wrong data.

SQL DSL (`db.sql`)

The SQL DSL gives you explicit, composable control over the query structure. db.sql exposes one property per storage table in your contract. You chain .select(), .where(), .orderBy(), .limit(), and .build() to assemble a plan, then pass it to the runtime for execution.

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 });

// Select specific columns with a limit (table name = storage table)
const users = await db.runtime().execute(
  db.sql.user
    .select('id', 'email', 'createdAt')
    .limit(10)
    .build(),
);
// users: Array<{ id: string; email: string; createdAt: Date }>

The .where() callback receives field references and built-in predicate functions (fns):

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

Result types are inferred from the columns you select — you never write explicit type annotations for query results.

ORM client (`db.orm`)

The ORM client exposes model collections with a fluent chainable API for filtering, ordering, including relations, and projecting fields. ORM accessors use the model name from your contract (capitalized, as declared in your schema — e.g. db.orm.User, db.orm.Post). It maps storage-column rows back to model-field shapes automatically.

// Simple filter — use the model name (capitalized)
const users = await db.orm.User
  .select('id', 'email')
  .take(10)
  .all();

The ORM client is particularly useful for queries that traverse relations. The include calls are resolved into separate single-statement SQL plans and stitched together — no hidden JOIN explosion and no N+1 without explicit intent:

const orders = await db.orm.Order
  .where({ userId: currentUserId })
  .where((o) => o.status.in(['shipped', 'delivered']))
  .include('shippingAddress')
  .include('items', (item) =>
    item.include('product', (product) =>
      product
        .include('category')
        .include('images', (img) => img.where({ isPrimary: true }).take(1))
        .include('reviews', (reviews) =>
          reviews
            .where((r) => r.rating.gte(4))
            .orderBy((r) => r.createdAt.desc())
            .take(3)
            .include('author', (a) => a.select('name', 'avatar')),
        ),
    ),
  )
  .all()

Each nested .include() is a separately compiled plan, not a cartesian JOIN. Guardrails fire at authoring time if you reference a relation or field that does not exist in the contract.

Comparing the two APIs for the same query

SQL DSL
ORM client

// Fetch recent posts for a user
const plan = db.sql.post
  .select('id', 'title', 'createdAt')
  .where((f, fns) => fns.eq(f.userId, userId))
  .orderBy((f) => f.createdAt, { direction: 'desc' })
  .limit(20)
  .build();

const posts = await db.runtime().execute(plan);
// posts: Array<{ id: string; title: string; createdAt: Date }>

// Same query via ORM — uses model name (Post), not table name (post)
const posts = await db.orm.Post
  .where({ userId })
  .orderBy((p) => p.createdAt.desc())
  .take(20)
  .all();
// posts: Array<{ id: string; title: string; createdAt: Date; userId: string; ... }>

Use the SQL DSL when you need precise control over which columns are selected, want to express computed projections (aggregates, distances, expressions), or are working with joins. Use the ORM client when you are navigating relations, building CRUD endpoints, or want the fluent model-level API to handle include stitching and field mapping automatically.

Transactions

Both APIs participate in the same transaction surface. withTransaction from @prisma-next/sql-runtime wraps a callback in a database transaction and commits on clean return or rolls back on any thrown error:

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

const result = await withTransaction(db.runtime(), async (tx) => {
  await tx.execute(
    db.sql.post
      .insert({ title: `Post for ${userId}`, userId, createdAt: new Date() })
      .build(),
  );

  await tx.execute(
    db.sql.user
      .update({ displayName: newName })
      .where((f, fns) => fns.eq(f.id, userId))
      .build(),
  );

  return { committed: true };
});

Streaming with AsyncIterable

Both runtime.execute() and ORM .stream() return an AsyncIterable<Row>. For large result sets the runtime uses a server-side cursor (when the adapter supports it) so rows are fetched in batches rather than buffered entirely in memory:

// Stream results — only fetches rows as you consume them
for await (const post of db.orm.Post.where({ userId }).stream()) {
  process(post);
}

// Early break closes the cursor and releases the server-side resource
const iter = db.runtime().execute(db.sql.post.select('id', 'title').limit(10_000).build());
for await (const row of iter) {
  if (shouldStop(row)) break; // cursor closed automatically
}

Type inference from contract.d.ts

Result types come entirely from the contract — not from runtime inspection. The companion contract.d.ts file contains branded TypeScript types for every model, field, and codec declared in your schema. When you write db.orm.User.select('id', 'email').all(), the return type is inferred as Array<{ id: string; email: string }> based on what contract.d.ts says about the User model’s id and email fields. This means type errors appear at compile time, not at runtime, and the types are always in sync with the emitted contract.

Get Started

Core Concepts

Guides

Extensions

Query APIs: SQL DSL and ORM client

How plans work

SQL DSL (`db.sql`)

ORM client (`db.orm`)

Comparing the two APIs for the same query

Transactions

Streaming with AsyncIterable

Type inference from contract.d.ts

​How plans work

​SQL DSL (db.sql)

​ORM client (db.orm)

​Comparing the two APIs for the same query

​Transactions

​Streaming with AsyncIterable

​Type inference from contract.d.ts

How plans work

SQL DSL (`db.sql`)

ORM client (`db.orm`)

Comparing the two APIs for the same query

Transactions

Streaming with AsyncIterable

Type inference from contract.d.ts