# SQL query builder reference (/docs/orm/reference/sql-query-builder)

> For the complete Prisma documentation index, see [llms.txt](https://www.prisma.io/docs/llms.txt). A markdown version of any docs page is available by appending `.md` to its URL.

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

Location: ORM > Reference > SQL query builder reference

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](https://www.prisma.io/docs/orm/reference/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](https://www.prisma.io/docs/orm/fundamentals/advanced-queries).

> [!NOTE]
> 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](https://www.prisma.io/docs/orm/reference/orm-client) for MongoDB queries, and the [pipeline builder](https://www.prisma.io/docs/orm/reference/pipeline-builder) for aggregation pipelines.

## Example schema [#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](#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](https://www.prisma.io/docs/orm/fundamentals/relations-and-joins#many-to-many) explains. For the schema syntax itself, see [Contract authoring](https://www.prisma.io/docs/orm/contract-authoring/psl-syntax).

**Expand for the example schema**

```prisma
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 [#entry-points]

In Prisma ORM 8 your `schema.prisma` is called `contract.prisma`. [Coming from Prisma ORM 7](https://www.prisma.io/docs/orm/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, and 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`.

### The `db.sql` builder [#the-dbsql-facet]

`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()`](https://www.prisma.io/docs/orm/reference/transactions-and-runtime#close).

A `select()`, `insert()`, `update()`, or `delete()` call starts a query. [`build()`](#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.

```ts
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, but every other one is. The rest of this page shows PostgreSQL and writes the root as `db.sql.public`.

### Table access [#table-access]

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

```ts
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()` [#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()`](#lateraljoin) callback. For the example, see [Subquery via `.as()`](#subquery-via-as).

## SELECT queries [#select-queries]

These methods build and refine a `SELECT`. They chain, and you resolve the query with [`build()`](#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, and 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](#expressions-and-functions) such as `fns.eq()` and `fns.raw`. For an enum column you pass the value stored in the database, which 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()` [#select]

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

#### Options [#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()`](https://www.prisma.io/docs/orm/reference/raw-queries#binding-a-bare-value-with-param) for which JavaScript type becomes which id. See [`fns.raw` and `.returns()`](#fnsraw-and-returns).

#### Return type [#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. |

#### Examples [#examples]

The examples on this page use these ids, which stand for rows that exist:

```ts
const aliceId = '00000000-0000-4000-8000-000000000001';
const carolId = '00000000-0000-4000-8000-000000000003';
const helloWorldId = '00000000-0000-4000-8000-000000000010';
const untaggedPostId = '00000000-0000-4000-8000-000000000011';
const newestPostId = '00000000-0000-4000-8000-000000000012';
```

##### Project a subset of columns [#project-a-subset-of-columns]

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

##### Add an aliased computed column [#add-an-aliased-computed-column]

```ts
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.query(plan);
```

##### Project multiple computed columns at once [#project-multiple-computed-columns-at-once]

```ts
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.query(plan);
// rows === [{ id: aliceId, upperEmail: 'ALICE@EXAMPLE.COM', emailLength: 17 }]
```

### `where()` [#where]

Restrict a query to rows matching an expression.

#### Options [#options-1]

| Name        | Type                     | Required | Description                                                                                                              |
| ----------- | ------------------------ | -------- | ------------------------------------------------------------------------------------------------------------------------ |
| `predicate` | `(f, fns) => Expression` | Yes      | A boolean expression built from `f` and `fns`. Combine comparisons with `fns.and(...)` and `fns.or(...)`, nested freely. |

#### Return type [#return-type-1]

| Return type   | Example                         | Description                        |
| ------------- | ------------------------------- | ---------------------------------- |
| `SelectQuery` | `db.sql.public.post.where(...)` | A query narrowed by the predicate. |

#### Examples [#examples-1]

##### Combine comparisons with `and()` and `or()` [#combine-comparisons-with-and-and-or]

```ts
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.query(plan);
```

### `innerJoin()` [#innerjoin]

Combine matching rows from two tables. Every column is then 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.

#### Options [#options-2]

| Name    | Type                                                                                            | Required | Description                    |
| ------- | ----------------------------------------------------------------------------------------------- | -------- | ------------------------------ |
| `other` | A table (`db.sql.public.<table>`) or an aliased subquery ([`.as()`](#aliasing-a-query-with-as)) | Yes      | The table or subquery to join. |
| `on`    | `(f, fns) => Expression`                                                                        | Yes      | The join condition.            |

#### Return type [#return-type-2]

| Return type   | Example                                                 | Description                                                                                 |
| ------------- | ------------------------------------------------------- | ------------------------------------------------------------------------------------------- |
| `SelectQuery` | `db.sql.public.post.innerJoin(db.sql.public.user, ...)` | A query over the joined tables, with both tables' columns in scope under their table names. |

#### Examples [#examples-2]

##### Join posts to their authors [#join-posts-to-their-authors]

```ts
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.query(plan);
// rows === [{ postId: helloWorldId, authorEmail: 'alice@example.com' }]
```

### `outerLeftJoin()`, `outerRightJoin()`, `outerFullJoin()` [#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()`](#innerjoin) does. `outerLeftJoin()` is SQL's `LEFT OUTER JOIN` and keeps every left-table row. `outerRightJoin()` is `RIGHT OUTER JOIN` and keeps every right-table row. `outerFullJoin()` is `FULL OUTER JOIN` and keeps unmatched rows from both sides.

#### Examples [#examples-3]

##### Left join keeps rows with no match [#left-join-keeps-rows-with-no-match]

```ts
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.query(plan);
// rows === [{ postId: untaggedPostId, tagId: null }]
```

### `lateralJoin()` [#lateraljoin]

Correlate a per-row subquery against the outer row: for each outer row, the joined subquery can reference that row's columns. Use it for a query such as the newest post for each user.

#### Remarks [#remarks]

* **Availability:** PostgreSQL only. On SQLite TypeScript rejects the call. If you cast, or call it from plain JavaScript, it throws an error whose `code` is `ORM.CAPABILITY_MISSING`.
* The callback receives a `lateral` builder. Start its subquery with `lateral.from(otherTable)`, then chain the usual `SELECT` methods. Inside the callback, the outer table's columns are on `f` under its table name, such as `f.user.id`, so the subquery can filter on the outer row.
* Return the query chain **directly** from the callback. Do **not** call `.as(...)` on it. The first argument of `lateralJoin()` already names the subquery.
* Every column is on `f` under its table name, and a column name that only one table has is also on `f` directly. Outside the callback, the subquery's columns are under the alias you gave it, such as `f.latestPost.id`. Inside the callback, the joined table is still `post`, so the same column is `f.post.id`. Because both tables have `id` and `createdAt`, a bare `select('id')` or `orderBy((f) => f.createdAt, ...)` throws.
* `outerLateralJoin(alias, callback)` is the `LEFT JOIN LATERAL` form: same arguments, and rows with no match keep the outer row.

#### Options [#options-3]

| 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.                                  |

#### Return type [#return-type-3]

| Return type   | Example                                             | Description                                                         |
| ------------- | --------------------------------------------------- | ------------------------------------------------------------------- |
| `SelectQuery` | `db.sql.public.user.lateralJoin('latestPost', ...)` | A query with the lateral subquery's columns in scope under `alias`. |

#### Examples [#examples-4]

##### Each user's most recent post [#each-users-most-recent-post]

```ts
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.query(plan);
// rows === [{ userId: aliceId, latestPostId: newestPostId }]
```

### `orderBy()` [#orderby]

Sort the result set by a column or a computed expression, in a direction you choose.

#### Options [#options-4]

| 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.                          |

TypeScript also accepts a `nulls` option, but it does nothing, and no `NULLS FIRST` or `NULLS LAST` reaches the SQL. To put nulls last, sort by a computed value first, then by the column:

```ts
db.sql.public.user
  .orderBy((f, fns) => fns.raw`CASE WHEN ${f.zip} IS NULL THEN 1 ELSE 0 END`.returns('pg/int4@1'))
  .orderBy('zip', { direction: 'asc' });
```

#### Return type [#return-type-4]

| Return type   | Example                                    | Description                                                                      |
| ------------- | ------------------------------------------ | -------------------------------------------------------------------------------- |
| `SelectQuery` | `db.sql.public.user.orderBy('email', ...)` | A query with the sort key applied. Call `orderBy()` again to add secondary keys. |

#### Examples [#examples-5]

##### Sort by a column [#sort-by-a-column]

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

##### Sort by a computed value [#sort-by-a-computed-value]

```ts
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.query(plan);
```

### `distinct()` [#distinct]

De-duplicate identical projected rows (`SELECT DISTINCT`).

#### Return type [#return-type-5]

| Return type   | Example                                            | Description                                     |
| ------------- | -------------------------------------------------- | ----------------------------------------------- |
| `SelectQuery` | `db.sql.public.post.select('priority').distinct()` | A query returning only distinct projected rows. |

#### Examples [#examples-6]

##### De-duplicate projected rows [#de-duplicate-projected-rows]

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

### `distinctOn()` [#distincton]

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

#### Remarks [#remarks-1]

* **Availability:** PostgreSQL only. On SQLite TypeScript rejects the call. If you cast, or call it from plain JavaScript, it throws an error whose `code` is `ORM.CAPABILITY_MISSING`.
* Sort keys that come after the `distinctOn()` columns in `orderBy()` decide which row is kept.

#### Options [#options-5]

| Name      | Type                    | Required | Description                                       |
| --------- | ----------------------- | -------- | ------------------------------------------------- |
| `...keys` | Column names (`string`) | Yes      | The columns whose distinct combinations are kept. |

#### Return type [#return-type-6]

| Return type   | Example                                   | Description                                     |
| ------------- | ----------------------------------------- | ----------------------------------------------- |
| `SelectQuery` | `db.sql.public.post.distinctOn('userId')` | A query keeping the first row per distinct key. |

#### Examples [#examples-7]

##### First post per user by date [#first-post-per-user-by-date]

```ts
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.query(plan);
// one row per user, each the earliest post by createdAt
```

### `limit()` and `offset()` [#limit-and-offset]

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

#### Options [#options-6]

| 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. |

#### Return type [#return-type-7]

| Return type   | Example                       | Description                                 |
| ------------- | ----------------------------- | ------------------------------------------- |
| `SelectQuery` | `db.sql.public.user.limit(2)` | A query with the row cap or offset applied. |

#### Examples [#examples-8]

##### Page through results [#page-through-results]

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

### Subquery via `.as()` [#subquery-via-as]

Call `.as(alias)` on a `SELECT` chain before `build()` to use it as a subquery. Pass the result to any join method as the `other` argument. `SELECT` queries and [grouped queries](#grouped-queries) have `.as()`, but queries started with `insert()`, `update()`, or `delete()` do not, so you cannot join against the rows they return.

#### Options [#options-7]

| Name    | Type     | Required | Description                                                                  |
| ------- | -------- | -------- | ---------------------------------------------------------------------------- |
| `alias` | `string` | Yes      | Names the subquery. Address its columns as `f.<alias>.<col>` after the join. |

#### Return type [#return-type-8]

| Return type | Example                                   | Description                                                                                                           |
| ----------- | ----------------------------------------- | --------------------------------------------------------------------------------------------------------------------- |
| Subquery    | `db.sql.public.post.select(...).as('hp')` | A subquery you pass as a join's `other` argument. You cannot call `build()` on it. Join it into an outer query first. |

#### Examples [#examples-9]

##### Join against a subquery [#join-against-a-subquery]

```ts
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.query(plan);
```

## Grouped queries [#grouped-queries]

Grouping starts with [`groupBy()`](#groupby), which turns a select query into a `GroupedQuery`. Write your aggregates in `select()` first, then call `groupBy()`. After that you can add `having()`, `orderBy()`, `limit()`, `offset()`, and `distinct()`.

The examples below use an extra table, `order`, whose `amount` column is an integer:

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

  @@map("order")
}
```

### `groupBy()` [#groupby]

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

#### Options [#options-8]

| Form        | Signature                     | Description                 |
| ----------- | ----------------------------- | --------------------------- |
| Field names | `groupBy('col', 'col2', ...)` | Group by the named columns. |
| Expression  | `groupBy((f, fns) => expr)`   | Group by a computed value.  |

#### Return type [#return-type-9]

| Return type    | Example                                     | Description                                                              |
| -------------- | ------------------------------------------- | ------------------------------------------------------------------------ |
| `GroupedQuery` | `db.sql.public.order.groupBy('customerId')` | A grouped query supporting `having()`, aggregates, ordering, and limits. |

#### Examples [#examples-10]

##### Group by a column with a count [#group-by-a-column-with-a-count]

The examples below reuse the `runtime` created here, and name each built query `plan`, the object `build()` returns.

```ts
const runtime = db.runtime();

const plan = db.sql.public.order
  .select('customerId')
  .select('orderCount', (f, fns) => fns.count(f.id))
  .groupBy('customerId')
  .build();
const rows = await runtime.query(plan);
// rows === [{ customerId: '<a customer id>', orderCount: 5 }]
```

> [!NOTE]
> What the aggregates give you back
> 
> * A `count()` or integer `sum()` result larger than 2^53 - 1 throws an error whose `code` is `RUNTIME.DECODE_FAILED`. Use `countBigInt()` or `sumBigInt(field)` when totals can get that large.
> * `avg()` is computed as a floating-point `number`, so a long decimal is rounded. Use `avgDecimal(field)` when you need the exact figure.
> * Every error this page names is an `Error` with a `code` property, so check for one as below.

```ts
try { await runtime.query(plan); } catch (error) {
  if (error instanceof Error && 'code' in error && error.code === 'RUNTIME.DECODE_FAILED') { /* the count is too big for a number */ }
}
```

##### Group by a computed value [#group-by-a-computed-value]

```ts
const plan = db.sql.public.order
  .select('yearPlaced', (f, fns) => fns.raw`EXTRACT(YEAR FROM ${f.placedAt})`.returns('pg/numeric@1'))
  .select('orderCount', (f, fns) => fns.count(f.id))
  .groupBy('yearPlaced')
  .build();
const rows = await runtime.query(plan);
// rows === [{ yearPlaced: '2024', orderCount: 10 }]. PostgreSQL computes EXTRACT as numeric, which arrives as the string '2024'. Write Number(row.yearPlaced) for 2024.
```

Annotate with the type PostgreSQL computes, not the type you want. Here `pg/numeric@1` types `yearPlaced` as `string`, which is what you receive, while `.returns('pg/int4@1')` would type it `number` and still hand you a string. `.groupBy('yearPlaced')` groups by the alias given to the selected value. To group by the expression itself, repeat the fragment inside `groupBy()`, `.returns()` and all:

```ts
  .groupBy((f, fns) => fns.raw`EXTRACT(YEAR FROM ${f.placedAt})`.returns('pg/numeric@1'))
```

A name that is neither a column of the table you started from nor an alias you selected throws an error whose `code` is `ORM.COLUMN_UNKNOWN`, at the moment you call `groupBy()`. If you select a column you did not group or aggregate, the query fails when it runs, not when you build it, and that failure comes back as PostgreSQL's own error, not a Prisma ORM code.

### `having()` [#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 [#remarks-2]

* Compare against a plain JavaScript number, as in `fns.gt(fns.count(), 1)`. Passing a `bigint` literal such as `1n` throws an error whose `code` is `RUNTIME.ENCODE_FAILED` when the query runs. `fns.countBigInt()` and `fns.sumBigInt()` are the other way round: compare those against a `bigint` literal, as in `fns.gt(fns.countBigInt(), 1n)`.
* Do not use a selected alias in `having()`. TypeScript will let you, but PostgreSQL rejects it and the query fails when it runs. Write the aggregate again instead.

#### Options [#options-9]

| Name        | Type                     | Required | Description                                                                                   |
| ----------- | ------------------------ | -------- | --------------------------------------------------------------------------------------------- |
| `predicate` | `(f, fns) => Expression` | Yes      | A boolean expression over aggregate functions, for example `fns.gt(fns.sum(f.amount), 1000)`. |

#### Return type [#return-type-10]

| Return type    | Example                                                 | Description                                                    |
| -------------- | ------------------------------------------------------- | -------------------------------------------------------------- |
| `GroupedQuery` | `db.sql.public.order.groupBy('customerId').having(...)` | A grouped query filtered to the groups matching the predicate. |

#### Examples [#examples-11]

##### Filter groups by a sum threshold [#filter-groups-by-a-sum-threshold]

```ts
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.query(plan); // only the customer whose orders sum over 1000, with totalAmount 1500
```

##### Compare with `count()`, `avg()`, `min()`, `max()` [#compare-with-count-avg-min-max]

```ts
const plan = db.sql.public.order
  .select('customerId')
  .select('avgAmount', (f, fns) => fns.avg(f.amount))
  .select('exactAvg', (f, fns) => fns.avgDecimal(f.amount))
  .select('minAmount', (f, fns) => fns.min(f.amount))
  .select('maxAmount', (f, fns) => fns.max(f.amount))
  .groupBy('customerId')
  .having((f, fns) => fns.and(fns.gt(fns.count(), 1), fns.gt(fns.avg(f.amount), 100)))
  .build();
const rows = await runtime.query(plan); // avgAmount is 300, exactAvg is the exact string '300.0000000000000000', minAmount is 100, maxAmount is 500
```

### Ordering and limiting a grouped query [#ordering-and-limiting-a-grouped-query]

A `GroupedQuery` supports the same `orderBy()`, `limit()`, `offset()`, `distinct()`, and [`distinctOn()`](#distincton) methods as a `SelectQuery`. Sort by an aggregate alias to order groups. A name that is neither a column of the table you started from nor an alias you selected throws an error whose `code` is `ORM.COLUMN_UNKNOWN`, at the moment you call `orderBy()`.

#### Examples [#examples-12]

##### Order groups by total, keep the top one [#order-groups-by-total-keep-the-top-one]

```ts
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.query(plan); // the single highest-spending customer
```

## Mutations [#mutations]

> [!WARNING]
> An update or delete with no
> 
> `where()`
> 
>  hits every row
> 
> `where()` is optional on `update()` and `delete()`: leave it out and every row in the table is changed, with no warning.

Mutations start from a table with `insert()`, `update()`, or `delete()`, and end with [`build()`](#build). Add [`returning()`](#returning) to get the affected rows back. Run a mutation with no `returning()` using `runtime.execute(plan)`, which resolves to `{ affectedRows }`. With `returning()`, run it with `runtime.query(plan)`. The examples below go back to this page's example models, so they use `db.sql.public.tag` and `db.sql.public.user` again.

### `insert()` [#insert]

Insert one or more rows in a single statement.

#### Remarks [#remarks-3]

* `insert()` always takes an **array**. A single-row insert is a one-element array. There is no separate single-row overload. An empty array throws an error whose `code` is `ORM.MUTATION_DATA_MISSING`.
* Leave a column out and the `@default(...)` in `contract.prisma` is applied. Prisma ORM generates `@default(uuid())` and `@default(now())` values when you call `build()`, not when the query runs. The built query then holds fixed values, so running it twice inserts the same id twice, which fails on a unique column. Call `.build()` again for each insert.
* There is no `ON CONFLICT` and no upsert here. For "insert or update", use the ORM client's [`upsert()`](https://www.prisma.io/docs/orm/reference/orm-client#upsert) or write the statement with [`db.raw.sql`](https://www.prisma.io/docs/orm/reference/raw-queries).

#### Options [#options-10]

| Name   | Type                 | Required | Description                                                 |
| ------ | -------------------- | -------- | ----------------------------------------------------------- |
| `rows` | Array of row objects | Yes      | The rows to insert. Every column is optional in TypeScript. |

PostgreSQL still rejects a missing required column that has no default. Never call `.returns()` or [`param()`](#param) on an insert value.

#### Return type [#return-type-11]

| Return type   | Example                           | Description                                                                |
| ------------- | --------------------------------- | -------------------------------------------------------------------------- |
| `InsertQuery` | `db.sql.public.tag.insert([...])` | An insert query. Buildable directly, or chain [`returning()`](#returning). |

#### Examples [#examples-13]

##### Insert a single row [#insert-a-single-row]

```ts
const plan = db.sql.public.tag.insert([{ label: 'single-row-tag' }]).build();
await runtime.execute(plan); // execute runs a write that returns no rows. id is filled in from @default(uuid())
```

##### Insert multiple rows in one statement [#insert-multiple-rows-in-one-statement]

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

### `returning()` [#returning]

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

#### Remarks [#remarks-4]

* **Availability:** PostgreSQL and SQLite.
* `returning()` takes column names only. There is no `returning('*')`, and you cannot return a computed expression.

#### Options [#options-11]

| Name         | Type                    | Required | Description                                   |
| ------------ | ----------------------- | -------- | --------------------------------------------- |
| `...columns` | Column names (`string`) | Yes      | The columns to return from each affected row. |

#### Return type [#return-type-12]

| Return type    | Example                                                    | Description                                                                                                            |
| -------------- | ---------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------- |
| Mutation query | `db.sql.public.tag.insert([...]).returning('id', 'label')` | The mutation query, which now gives you back those columns from each affected row. Run it with `query`, not `execute`. |

#### Examples [#examples-14]

##### Return the inserted row [#return-the-inserted-row]

```ts
const plan = db.sql.public.tag.insert([{ label: 'returned-tag' }]).returning('id', 'label').build();
const rows = await runtime.query(plan); // rows === [{ id: '<the generated uuid>', label: 'returned-tag' }]
```

### `update()` [#update]

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

#### Options [#options-12]

`update()` accepts a values object or an expression callback, but you cannot mix the two forms in one call.

| Form                | Signature                             | Description                                                |
| ------------------- | ------------------------------------- | ---------------------------------------------------------- |
| Values object       | `update({ col: value, ... })`         | Set columns to fixed values.                               |
| Expression callback | `update((f, fns) => ({ col: expr }))` | Set columns to expressions computed from existing columns. |

Every value the callback returns must be an expression. To set a column to a fixed value in the callback form, wrap that value in a `fns.raw` fragment, as the second example below does.

#### Return type [#return-type-13]

| Return type   | Example                              | Description                                                                  |
| ------------- | ------------------------------------ | ---------------------------------------------------------------------------- |
| `UpdateQuery` | `db.sql.public.user.update({ ... })` | An update query. Chain `where()` and optionally [`returning()`](#returning). |

#### Examples [#examples-15]

##### Update with a values object [#update-with-a-values-object]

```ts
const plan = db.sql.public.user
  .update({ displayName: 'Bobby' })
  .where((f, fns) => fns.eq(f.email, 'bob@example.com'))
  .returning('id', 'displayName')
  .build();
const rows = await runtime.query(plan); // one row, with displayName now 'Bobby'
```

##### Derive a value from an existing column [#derive-a-value-from-an-existing-column]

```ts
const plan = db.sql.public.user
  .update((f, fns) => ({
    displayName: fns.raw`UPPER(${f.displayName})`.returns('pg/text@1'),
    email: fns.raw`${'carol@archived.example.com'}`.returns('pg/text@1'),
  }))
  .where((f, fns) => fns.eq(f.email, 'carol@example.com'))
  .returning('id', 'displayName')
  .build();
const rows = await runtime.query(plan); // one row, with displayName now 'CAROL' and a fixed new email
```

### `delete()` [#delete]

Delete matched rows. `delete()` takes no arguments. Choose the rows with `where()`, and use [`returning()`](#returning) to get the deleted rows back.

#### Return type [#return-type-14]

| Return type   | Example                      | Description                                                                 |
| ------------- | ---------------------------- | --------------------------------------------------------------------------- |
| `DeleteQuery` | `db.sql.public.tag.delete()` | A delete query. Chain `where()` and optionally [`returning()`](#returning). |

#### Examples [#examples-16]

##### Delete and return the removed row [#delete-and-return-the-removed-row]

```ts
const plan = db.sql.public.tag
  .delete()
  .where((f, fns) => fns.eq(f.label, 'to-delete'))
  .returning('id', 'label')
  .build();
const rows = await runtime.query(plan); // one row, the tag that was deleted
```

### `param()` [#param]

Give a value its type by hand, for a value with no column to take the type from, such as a literal inside `fns.raw`. `param()` works in any query, not only in mutations.

#### Remarks [#remarks-5]

* Import `param` from `@prisma/orm-postgres/relational-core/expression`.
* Values passed to `insert()`, `update()`, and comparison functions such as `fns.eq(f.col, value)` each take their type from the column they are used with, so you rarely need `param()`. Reach for it when the type the builder would pick is not the one the column needs. Inside `fns.raw` no column lends its type, so a bare string becomes `pg/text@1` and a value compared there with a `uuid` column needs `param(id, { codecId: 'pg/uuid@1' })`. A `Temporal.Instant` cannot be interpolated bare at all.
* The option is called `codecId`, but it takes the same type id used everywhere else on this page. [Binding a bare value with `param()`](https://www.prisma.io/docs/orm/reference/raw-queries#binding-a-bare-value-with-param) lists the type id each kind of JavaScript value gets, which is where to look for a date, a uuid, a boolean, or an array.

#### Options [#options-13]

| Name           | Type     | Required | Description                                                  |
| -------------- | -------- | -------- | ------------------------------------------------------------ |
| `value`        | `T`      | Yes      | The value to bind.                                           |
| `opts.codecId` | `string` | Yes      | The type id to send the value as, for example `'pg/text@1'`. |

#### Return type [#return-type-15]

| Return type | Example                                            | Description                                          |
| ----------- | -------------------------------------------------- | ---------------------------------------------------- |
| `ParamRef`  | `param('%@example.com', { codecId: 'pg/text@1' })` | A bound-parameter reference usable inside `fns.raw`. |

#### Examples [#examples-17]

##### Bind a literal inside a raw fragment [#bind-a-literal-inside-a-raw-fragment]

```ts
import { param } from '@prisma/orm-postgres/relational-core/expression';

const targetId = param('11111111-1111-1111-1111-111111111111', { codecId: 'pg/uuid@1' });
const plan = db.sql.public.user
  .select('id', 'email')
  .where((f, fns) => fns.raw`${f.id} = ${targetId}`.returns('pg/bool@1'))
  .build();
const rows = await runtime.query(plan);
```

## Expressions and functions [#expressions-and-functions]

Callback forms receive `fns` alongside the `f` argument. `fns` is an object of helper functions: comparisons, boolean combinations, aggregates, and raw SQL.

### Built-in functions [#built-in-functions]

These exist on every database:

| Category   | Functions                                                                |
| ---------- | ------------------------------------------------------------------------ |
| Comparison | `eq(a, b)`, `ne(a, b)`, `gt(a, b)`, `gte(a, b)`, `lt(a, b)`, `lte(a, b)` |
| Membership | `in(expr, values)`, `notIn(expr, values)`                                |
| Boolean    | `and(...predicates)`, `or(...predicates)`                                |
| Existence  | `exists(subquery)`, `notExists(subquery)`                                |
| Raw SQL    | ``raw`...`.returns(typeId)``                                             |

For `in()` and `notIn()`, `values` is an array or a subquery. All four of `in()`, `notIn()`, `exists()`, and `notExists()` take a select query that you do not call `build()` on.

```ts
const named = db.sql.public.user
  .select('id', 'email')
  .where((f, fns) => fns.in(f.email, ['bob@example.com', 'carol@example.com']))
  .build();

const authorsById = db.sql.public.user
  .select('id', 'email')
  .where((f, fns) => fns.in(f.id, db.sql.public.post.select('userId')))
  .build();

const authors = db.sql.public.user
  .select('id', 'email')
  .where((f, fns) => fns.exists(db.sql.public.post.select('id').where((inner, innerFns) => innerFns.eq(inner.userId, f.id))))
  .build();
```

Which aggregate functions exist depends on your database, and PostgreSQL gives you these eight. The contract type is the one you write in `contract.prisma`, and the PostgreSQL type is in brackets:

| Function                               | Returns                                                                                                                                           |
| -------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------- |
| `count()` / `count(field)`             | `number`. With no argument it counts rows. With a field it counts that field's non-null values                                                    |
| `countBigInt()` / `countBigInt(field)` | `bigint`. Use it when the count can pass [2^53 - 1](#group-by-a-column-with-a-count)                                                              |
| `sum(field)`                           | `number` over an `Int` (`int4`) or `BigInt` (`int8`) column. A string over `Decimal` or `Numeric` (`numeric`), a `number` over `Float` (`float8`) |
| `sumBigInt(field)`                     | `bigint`. The sum over an integer column that can pass [2^53 - 1](#group-by-a-column-with-a-count)                                                |
| `avg(field)`                           | `number` over an `Int` (`int4`) or `Float` (`float8`) column                                                                                      |
| `avgDecimal(field)`                    | An exact decimal string. The version of `avg` that does not round                                                                                 |
| `min(field)`                           | The input column's own type. A `VarChar` column gives a `String` (`text`)                                                                         |
| `max(field)`                           | The input column's own type. A `VarChar` column gives a `String` (`text`)                                                                         |

On PostgreSQL you also get `ilike(expr, pattern)` for text columns, as in `fns.ilike(f.displayName, '%alice%')`. `cosineDistance(a, b)` compares two vector values and returns a `number`. It comes with pgvector, the PostgreSQL extension for vector columns, so it is there only if your contract composes pgvector. See [Extension types](https://www.prisma.io/docs/orm/contract-authoring/psl-syntax#extension-types).

### `fns.raw` and `.returns()` [#fnsraw-and-returns]

Write a raw SQL fragment as a tagged template, and interpolate columns, typed expressions, and bare JavaScript values with `${...}`. For a bare value, the builder picks the type id from its JavaScript type. [Binding a bare value with `param()`](https://www.prisma.io/docs/orm/reference/raw-queries#binding-a-bare-value-with-param) lists which JavaScript type becomes which type id, and shows how to choose a different one with `param(...)`. Call `.returns(typeId)` to declare the fragment's result type.

#### Options [#options-14]

| Name               | Type                                 | Required    | Description                                                                                                                                      |
| ------------------ | ------------------------------------ | ----------- | ------------------------------------------------------------------------------------------------------------------------------------------------ |
| SQL fragment       | Tagged template                      | Yes         | The raw SQL, with `${...}` interpolations for columns and values.                                                                                |
| `.returns(typeId)` | `string`, or `{ codecId, nullable }` | Yes, always | Declares the result type, for example `'pg/int4@1'`, `'pg/text@1'`. For a fragment used as a `where()` predicate, write `.returns('pg/bool@1')`. |

#### Remarks [#remarks-6]

* **Always call `.returns()`.** Nothing in the builder accepts a fragment without it.
* **`.returns()` only tells TypeScript what type to expect.** It does not convert the value. Convert the value in JavaScript, or run the statement with [`db.raw.sql` and `.returnsRow()`](https://www.prisma.io/docs/orm/reference/raw-queries), which does decode.
* Use `fns.raw` for any SQL feature without a dedicated helper: `COALESCE`, `CAST`, `LENGTH`, `UPPER`, `EXTRACT`, and so on. There is no `fns.coalesce` and no `fns.cast`.

#### Examples [#examples-18]

##### Compute a column with a SQL function [#compute-a-column-with-a-sql-function]

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

## Compiling and executing [#compiling-and-executing]

### `build()` [#build]

Compile a query so you can run it.

#### Remarks [#remarks-7]

* `build()` takes **zero arguments** on every query type: select, insert, update, delete, and grouped. You supply parameter values where you write them, inside `insert([...])`, a `where()` callback, or a [`param()`](#param) call.

#### Return type [#return-type-16]

| Return type | Example                                   | Description                                                                                                             |
| ----------- | ----------------------------------------- | ----------------------------------------------------------------------------------------------------------------------- |
| Built query | `db.sql.public.user.select('id').build()` | A query you run with `runtime.query(...)`. You can get the TypeScript type of one row with [`ResultType`](#resulttype). |

### Running a built query [#executing-a-plan]

Run a built query with `runtime`, which is `db.runtime()`.

| Call                    | What you get                                                                                                                 |
| ----------------------- | ---------------------------------------------------------------------------------------------------------------------------- |
| `runtime.query(plan)`   | Rows. `await` it for an array of rows (`Row[]`), or `for await` over it to take one row at a time.                           |
| `runtime.execute(plan)` | `{ affectedRows }`.                                                                                                          |
| Which one               | `query` for anything that returns rows, which means a `SELECT` or a write with `returning()`. `execute` for everything else. |

### `ResultType` [#resulttype]

Recover a built query's row type at the type level.

#### Remarks [#remarks-8]

* Import `ResultType` from `@prisma/orm-postgres/components/runtime`. It works with the ORM client as well as the SQL builder.
* `ResultType<typeof plan>` is **one row**, not an array. `await runtime.query(plan)` resolves to `Row[]`, but `ResultType<typeof plan>` is `Row`.
* It also works on ORM queries: `ResultType<typeof db.orm.public.User.include('posts')>`. `db.orm` is keyed by model name, so the model is `User` there and the table is `user` under `db.sql`. See [Model and result types](https://www.prisma.io/docs/orm/reference/orm-client#model-and-result-types).

#### Examples [#examples-19]

##### Recover the row type from a built query [#recover-the-row-type-from-a-plan]

```ts
import type { ResultType } from '@prisma/orm-postgres/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.query(plan); // Row[]
```

### Streaming vs. collecting [#streaming-vs-collecting]

`await runtime.query(plan)` collects every row into an array, which is the common case and what every example on this page uses. You can also loop the same result with `for await` to handle one row at a time. On PostgreSQL every row is loaded before the loop starts, so `for await` saves nothing there. Use `await`.

Keep the result in a variable, as in `const result = runtime.query(plan)`, and you can `await result` as many times as you like. You cannot mix `await` and `for await` on the same result, and you cannot `for await` it twice. Either one throws an error whose `code` is `RUNTIME.ITERATOR_CONSUMED`. See [`AsyncIterableResult`](https://www.prisma.io/docs/orm/reference/orm-client#asynciterableresult).

## Related pages

- [`Error reference`](https://www.prisma.io/docs/orm/reference/error-reference): Every structured error code Prisma ORM can emit, by namespace, with the condition that raises it.
- [`ORM client reference`](https://www.prisma.io/docs/orm/reference/orm-client): Reference for the Prisma ORM client's query, mutation, filter, and aggregate methods.
- [`Pipeline builder reference`](https://www.prisma.io/docs/orm/reference/pipeline-builder): Reference for the Prisma ORM MongoDB pipeline builder's stages, accumulators, expression helpers, and write methods.
- [`Raw queries reference`](https://www.prisma.io/docs/orm/reference/raw-queries): Reference for Prisma ORM raw queries: PostgreSQL raw SQL and MongoDB raw commands.
- [`Transactions and runtime reference`](https://www.prisma.io/docs/orm/reference/transactions-and-runtime): Reference for the Prisma ORM client lifecycle, transactions, prepared statements, and execution options.