Prisma ORM SQL query builder reference

Reference for the Prisma ORM SQL query builder's select, mutation, and grouped query methods.

The SQL query builder builds typed queries from a table, with one method per SQL clause: select(), innerJoin(), groupBy(), and the rest. It is closer to SQL than the ORM client is, and the same db gives you both. 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.

Some methods work only on some databases, and some fail only when the query runs. For a task-oriented guide to when and how to reach for the builder, see Advanced queries.

The builder for SQL databases

The SQL query builder targets SQL databases; PostgreSQL and SQLite are supported today. MongoDB has no SQL builder: use the ORM client for MongoDB queries, and the pipeline builder for aggregation pipelines.

Example schema

The examples run against the schema below and use the user, post, post_tag, and tag tables. The grouped-query examples use a separate customer and order schema, shown under Grouped queries. PostTag is the join table. The two list fields and the PostTag model together are how Prisma ORM 8 declares a many-to-many, which Relations and joins explains. For the schema syntax itself, see Contract authoring.

Expand for the example schema

types {
  Embedding1536 = pgvector.Vector(1536)
}

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

In Prisma ORM 8 your schema.prisma is called contract.prisma. Coming from Prisma ORM 7 gets you from a Prisma ORM 7 project to that file, and has the rest of the setup steps. Install the client with npm install @prisma/orm-postgres. Run npx prisma contract emit to write contract.json and contract.d.ts beside contract.prisma. The examples below assume your own file is in src/prisma/, next to those two files, and they write the type import path as ./contract.d, exactly like that.

You build queries from the client's sql property. Create a Postgres client with postgres(...), then reach tables through db.sql.public. public is the PostgreSQL schema your tables are in. A model goes in public unless your contract puts it in a namespace block. Table accessors are keyed by table name, not model name. A table name is the model name with a lowercase first letter unless the model sets @@map, so the User model mapped to user is reached as db.sql.public.user, and the model for the Post/Tag join table, mapped to post_tag, is reached as db.sql.public.post_tag. Column names are the field names unless a field sets @map.

db.runtime() returns the connection that runs a built query. On PostgreSQL it is synchronous, so no await is needed. Hold it in a variable and reuse it. When your app shuts down, close the client with db.close().

A select(), insert(), update(), or delete() call starts a query. build() turns the chain into a query object, which the examples name plan. Nothing touches the database until you pass that object to runtime.query(...). You can run the same built query more than once.

import postgres from '@prisma/orm-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 = db.runtime();

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

SQLite works the same way. Create the client with sqlite(...) from @prisma/orm-sqlite/runtime. SQLite has no schemas, so there is no public in the path and the same table is db.sql.user. Of the SELECT methods on this page, lateralJoin(), outerLateralJoin(), and distinctOn() are not available on SQLite. Every other one is. The rest of this page shows PostgreSQL and writes the root as db.sql.public.

Table access

Every table on db.sql.public exposes the query-building methods.

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

Aliasing a query with `.as()`

Call .as(alias) on a SELECT chain before build() to use that query as a subquery. The aliased query is then something you can pass to innerJoin(), outerLeftJoin(), and the other join methods. Do not call .as() inside a lateralJoin() callback. For the example, see Subquery via .as().

SELECT queries

These methods build and refine a SELECT. They chain, and you resolve the query with build() followed by runtime.query(...). Chain the methods in any order, with two exceptions. A join must come before any call that names the joined table's columns. The columns you pass to distinctOn() must be the first sort keys in orderBy(). SelectQuery in the tables below is the type they return.

The set of column names a query can use is its scope. It starts as the columns of the table you began with, and every join adds the joined table's columns to it.

The where(), select(), orderBy(), update(), and other callback forms receive two arguments. The first is f, which has one property per column in scope. After a join, every column is on f under its table name, such as f.user.email or f.post.id. A column name that only one table has is also on f directly. The second is fns, an object of expression helpers such as fns.eq() and fns.raw. For an enum column you pass the value stored in the database. That is the string after =, so High = "high" is matched by fns.eq(f.priority, 'high'). A member with no =, such as admin in user_type, is matched by its own name, fns.eq(f.kind, 'admin').

`select()`

Reduce each row to a chosen set of columns or computed expressions.

Options

select() has three forms:

Form	Signature	Description
Column names	`select('col', 'col2', ...)`	Keep the named columns. A name that is not in the query's scope throws an error whose `code` is `ORM.COLUMN_UNKNOWN`.
Aliased expression	`select(alias, (f, fns) => expr)`	Add one computed column under `alias`.
Object of expressions	`select((f, fns) => ({ alias: expr, ... }))`	Add several computed columns at once. The row type is inferred from the object's shape.

Every form adds to what the query already returns. Calling select() a second time never replaces the first call's columns, whichever form you use. To return fewer columns, start a new query instead.

A computed expression's result type comes from .returns(...) on fns.raw, or from the fns.* function you called. Every fns.raw fragment needs .returns(). A type id is pg/ plus the PostgreSQL type name plus a version, always @1 today, such as pg/int4@1 or pg/uuid@1. A few use a longer name, such as pg/timestamptz-temporal@1 for DateTime. The common ones are pg/text@1, pg/int4@1, pg/int8@1, pg/float8@1, pg/bool@1, pg/uuid@1, and pg/timestamptz-temporal@1. The full list is what your contract uses. Read any column's id from db.sql.public.<table>.columns.<column>.codecId, and see Binding a bare value with param() for which JavaScript type becomes which id. See fns.raw and .returns().

Return type

Return type	Example	Description
`SelectQuery`	`db.sql.public.user.select('id', 'email')`	A query projected to the named columns or expressions, chainable and buildable.

Name	Type	Required	Description
`other`	A table (`db.sql.public.<table>`) or an aliased subquery (`.as()`)	Yes	The table or subquery to join.
`on`	`(f, fns) => Expression`	Yes	The join condition.

Name	Type	Required	Description
`alias`	`string`	Yes	Names the subquery. Address its columns as `f.<alias>.<col>` in later `select()`/`where()` calls.
`build`	`(lateral) => SelectQuery`	Yes	Builds the correlated subquery. Return the query chain directly.

Name	Type	Required	Description
`key`	Column name (`string`) or `(f, fns) => Expression`	Yes	The column or computed value to sort by.
`options.direction`	`'asc' \| 'desc'`	No	Sort direction.

Method	Argument	Description
`limit(n)`	`number`, or a numeric expression	Return at most `n` rows.
`offset(n)`	`number`, or a numeric expression	Skip the first `n` rows.

SQL query builder reference

On this page