# ORM client reference (/docs/orm/reference/orm-client)

> 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 client's query, mutation, filter, and aggregate methods.

Location: ORM > Reference > ORM client reference

The ORM client gives you model-level methods for reading and writing data across PostgreSQL and MongoDB. This page documents every method, its availability on each database, and the behavior that differs between the two. Each method's Remarks say which databases it works on, how it behaves differently on one of them, and where TypeScript rejects something the database itself would accept.

This page assumes your tables already exist. [Coming from Prisma ORM 7](https://www.prisma.io/docs/orm/coming-from-prisma-orm-7) has the packages to install and the `prisma orm init` command. It also covers `prisma db update`, which replaces `prisma migrate dev`, and `prisma contract emit`, which replaces `prisma generate`. [How migrations work](https://www.prisma.io/docs/orm/migrations/how-migrations-work) shows how the tables get created. For task-oriented walkthroughs, see the Fundamentals guides: [Reading data](https://www.prisma.io/docs/orm/fundamentals/reading-data), [Writing data](https://www.prisma.io/docs/orm/fundamentals/writing-data), [Relations and joins](https://www.prisma.io/docs/orm/fundamentals/relations-and-joins), and [Transactions](https://www.prisma.io/docs/orm/fundamentals/transactions).

## Example schema [#example-schema]

All examples on this page run against the schema below, except the grouped-aggregate ones, which use a separate `Customer` / `Order` schema shown under [Grouped aggregates](#grouped-aggregates). `contract.prisma` replaces `schema.prisma`, and this page calls it the contract. Running `npx prisma contract emit` compiles it into two generated files: `contract.json`, read at run time, and `contract.d.ts`, read by TypeScript. You import both, as the examples below do. Run `npx prisma contract emit` again after every change to `contract.prisma`. A new project keeps all three files in `src/prisma/`.

**Expand for the example schema**

#### PostgreSQL

```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")
}
```

#### MongoDB

```prisma
enum UserRole {
  @@type("mongo/string@1")
  Admin  = "admin"
  Author = "author"
  Reader = "reader"
}

type Address {
  street  String
  city    String
  zip     String?
  country String
}

model User {
  id      ObjectId @id @map("_id")
  name    String
  email   String
  bio     String?
  role    UserRole
  address Address?
  posts   Post[]
  @@map("users")
}

model Post {
  id        ObjectId @id @map("_id")
  title     String
  content   String
  kind      String
  authorId  ObjectId
  createdAt DateTime
  author    User @relation(fields: [authorId], references: [id])
  @@discriminator(kind)
  @@index([authorId])
  @@index([createdAt(sort: Desc), authorId])
  @@map("posts")
}

model Article {
  summary   String
  @@base(Post, "article")
  @@unique([summary])
}

model Tutorial {
  difficulty String
  duration   Int
  @@base(Post, "tutorial")
}
```

Some parts of the schema the examples rely on:

* The `types` block at the top gives a name to a type from an extension package, so your models can use that name. `Embedding1536` comes from the pgvector extension, and `pgvector.` is that package's prefix. See [PostgreSQL](#postgresql) for the extra line it needs when you create the client.

* `@@map("user")` sets the table or collection name. Without it, the table name is the model name with a lowercase first letter.

* On MongoDB, `id ObjectId @id @map("_id")` renames the field to `_id` everywhere: you filter on `_id`, and the returned document has an `_id` key. That is why the MongoDB examples never say `id`.

* `Uuid` is a built-in type for a PostgreSQL `uuid` column. You do not import it. `@default(uuid())` fills the id in when you insert a row.

* `@@type("pg/text@1")` on an `enum` block stores the enum as a text column. The `@1` is the version of that storage format, and only version 1 exists today, so write it exactly like that. The MongoDB form of the same attribute is `@@type("mongo/string@1")`. This page calls an enum written this way a text-backed enum.

* A `native_enum` block stores the same members as a real PostgreSQL enum type instead. Give every member a value. A member with no value is rejected. This page calls an enum written this way a native enum. The two sort differently; see [`orderBy()`](#orderby).

  ```prisma
  native_enum Priority {
    Low    = "low"
    High   = "high"
    Urgent = "urgent"
  }
  ```

* An enum member's stored value is the string after `=`, or the member name when there is no `=`. That string is what you pass to `.eq()`. So `Urgent = "urgent"` is matched by `.eq('urgent')`, and a bare `admin` member is matched by `.eq('admin')`.

* A variant is a model that reuses another model's fields and rows. `@@base(Task, "bug")` makes `Bug` a variant of `Task`. Its second argument, `"bug"`, is the value stored in the discriminator column for a `Bug` row. `@@discriminator(type)` names that column. `db.orm.public.Task.variant('Bug')` returns only the `Bug` rows. Pass the model name, `'Bug'`, not the string in `@@base`.

* `PostTag` is required. To join `Post.tags Tag[]` and `Tag.posts Post[]`, write a third model with one foreign key to each side and an `@@id` of exactly those two columns. Prisma ORM finds it for you, and neither `Post` nor `Tag` names it. A pair of list fields with no such model is rejected. See [Relations and joins](https://www.prisma.io/docs/orm/fundamentals/relations-and-joins#many-to-many).

* On PostgreSQL, a `DateTime` field comes back as a `Temporal.Instant`, the standard JavaScript object for a point in time. A filter on that field takes the same type, so you can pass a value straight back in.

## Setting up the client [#setting-up-the-client]

Create a client with `postgres(...)` or `mongo(...)`, then read and write your models through `db.orm`. The same `db` also has a query builder, `db.sql` on PostgreSQL and `db.query` on MongoDB, plus `db.raw` for raw queries. This page covers none of those three: see [Advanced queries](https://www.prisma.io/docs/orm/fundamentals/advanced-queries). Create `db` once per process, and close it with `await db.close()` when the process shuts down. The two databases have different entry points, and they name the models differently.

### PostgreSQL [#postgresql]

Create a PostgreSQL client with `postgres(...)`. `db.orm` holds your models by model name, grouped by database schema: `db.orm.public.User`, `db.orm.public.Post`. `public` is the PostgreSQL schema your tables are in unless you put them in another one. Import types from `./contract.d`, which resolves to `contract.d.ts`.

```typescript
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 users = await db.orm.public.User.all();
```

If your contract uses a type from an extension package, pass that package to `extensions` when you create the client. The example schema's `Embedding1536` type comes from pgvector, so it needs this:

```typescript
import pgvector from '@prisma/orm-extension-pgvector/runtime';

const db = postgres<Contract>({
  contractJson,
  url: process.env.DATABASE_URL,
  extensions: [pgvector],
});
```

To give a model your own methods on top of the built-in ones, import `Collection` from `@prisma/orm-postgres/orm-client`, subclass it, and register the subclass with `orm(...)` from that same package. You still create the client with `postgres(...)`. `orm(...)` takes two things from it: the `runtime` argument, which is what `await client.connect()` gives you, and the `context` argument, which is `client.context`. The code is under [Custom `Collection` subclass](#custom-collection-subclass).

### MongoDB [#mongodb]

Create a MongoDB client with `mongo(...)`. `db.orm` holds your models by collection name, with no schema in between: `db.orm.users`, `db.orm.posts`, not `db.orm.User`. The collection name is the one you set with `@@map`, or the model name with a lowercase first letter when you did not set one. `dbName` is the MongoDB database name.

```typescript
import mongo from '@prisma/orm-mongo/runtime';
import type { Contract } from './contract.d';
import contractJson from './contract.json' with { type: 'json' };

const db = mongo<Contract>({ contractJson, url: process.env.MONGODB_URL, dbName: 'app' });

const users = await db.orm.users.all();
```

## Query-building methods [#query-building-methods]

These methods narrow a query, and each returns a collection. On this page, a collection is the query object you chain more methods on, such as `db.orm.public.User` or `db.orm.public.User.where(...)`. When this page says "a MongoDB collection", it means that query object. MongoDB itself calls its store of documents a collection too, and the name is the same one. You run the query with a [read method](#read-terminals) such as `all()` or `first()`. MongoDB supports fewer of these methods than PostgreSQL, and each method's Remarks say which databases it works on.

Some examples import a filter helper you can use on its own. On PostgreSQL those come from `@prisma/orm-postgres/orm-client`, on MongoDB from `@prisma/orm-mongo/query-ast/execution`. See [Filter conditions and operators](#filter-conditions-and-operators).

The examples below use these ids, which stand for rows inserted before the query runs:

```typescript
const aliceId = '00000000-0000-4000-8000-000000000001';
const carolId = '00000000-0000-4000-8000-000000000003';
const postId = '00000000-0000-4000-8000-000000000010';
const acmeId = '00000000-0000-4000-8000-000000000020';
```

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

Restrict a query to rows matching a filter.

#### Remarks [#remarks]

* Available for PostgreSQL and MongoDB, but the accepted filter shapes differ.
* On PostgreSQL, `where()` takes either a callback that calls an operator on a column (`u.email.eq(...)`) or an object of field-and-value pairs that must match exactly.
* On MongoDB, `where()` takes either an object of field-and-value pairs that must match exactly or a `MongoFieldFilter`. The object form can only test for equality. For anything else, such as greater-than, use `MongoFieldFilter` (see [MongoFieldFilter](#mongofieldfilter)).
* Calling `where()` more than once on the same query requires a row to match all of the filters.
* `.eq()` is one operator of many. [Filter conditions and operators](#filter-conditions-and-operators), further down this page, lists them all for both databases.

#### Options [#options]

| Argument | Type                                                                                                 | Required | Description                      |
| -------- | ---------------------------------------------------------------------------------------------------- | -------- | -------------------------------- |
| `filter` | A callback that calls an operator on a column, a shorthand object, or (MongoDB) a `MongoFieldFilter` | Yes      | The condition rows must satisfy. |

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

| Return type  | Example                         | Description                                                                                             |
| ------------ | ------------------------------- | ------------------------------------------------------------------------------------------------------- |
| `Collection` | `db.orm.public.User.where(...)` | A collection narrowed by the filter. Chain more methods on it, then run it with a read or write method. |

#### Examples [#examples]

##### Callback form with a column operator (PostgreSQL) [#callback-form-with-a-column-operator-postgresql]

```typescript
const admins = await db.orm.public.User.where((u) => u.kind.eq('admin')).all();
```

##### Shorthand object form [#shorthand-object-form]

  

#### PostgreSQL

```typescript
const bob = await db.orm.public.User.where({ email: 'bob@example.com' }).first();
```

#### MongoDB

```typescript
const authors = await db.orm.users.where({ role: 'author' }).all();
```

##### Chaining `where()` calls (ANDed) [#chaining-where-calls-anded]

```typescript
const carolUrgentPosts = await db.orm.public.Post.where({ userId: carolId })
  .where((p) => p.priority.eq('urgent'))
  .all();
```

##### `MongoFieldFilter` expression (MongoDB) [#mongofieldfilter-expression-mongodb]

```typescript
import { MongoFieldFilter } from '@prisma/orm-mongo/query-ast/execution';

const alice = await db.orm.users.where(MongoFieldFilter.eq('email', 'alice@example.com')).first();

const recentPosts = await db.orm.posts
  .where(MongoFieldFilter.gte('createdAt', new Date('2024-01-02T00:00:00.000Z')))
  .all();
```

For a task-oriented guide to filtering, see [Reading data](https://www.prisma.io/docs/orm/fundamentals/reading-data#filter-records).

### `select()` [#select]

Return only the scalar fields you name.

#### Remarks [#remarks-1]

* Available for PostgreSQL and MongoDB.
* On PostgreSQL, `select()` narrows the returned row shape at the type level: fields you didn't select are absent from the result type.
* On MongoDB, `select()` changes what the database returns but not the TypeScript type. Only the fields you named come back. The type still lists every field on the model. Read a field you did not name and you get `undefined`, because the returned document does not carry it.

#### Options [#options-1]

| Argument    | Type                   | Required | Description                             |
| ----------- | ---------------------- | -------- | --------------------------------------- |
| `...fields` | Field names (`string`) | Yes      | One or more scalar field names to keep. |

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

| Return type  | Example                                    | Description                                 |
| ------------ | ------------------------------------------ | ------------------------------------------- |
| `Collection` | `db.orm.public.User.select('id', 'email')` | A collection projected to the named fields. |

#### Examples [#examples-1]

##### Project to a subset of fields [#project-to-a-subset-of-fields]

  

#### PostgreSQL

```typescript
const summaries = await db.orm.public.User.select('id', 'email').orderBy((u) => u.email.asc()).all();
// summaries[0] is { id, email }, with no displayName
```

#### MongoDB

```typescript
const summaries = await db.orm.users.select('name', 'email').all();
```

### `include()` [#include]

Eagerly load a relation onto the returned rows.

#### Remarks [#remarks-2]

* Available for PostgreSQL and MongoDB, with differences noted below.
* On PostgreSQL, `include(relationName, refineFn?)` loads both to-one and to-many relations. The optional callback receives the related rows as a collection, so you can filter, order, limit, and aggregate them. See [Refinements, aggregates, and combine](#refinements-aggregates-and-combine).
* On MongoDB, wrap the `_id` of a related document loaded by `include()` in `String(...)` before you compare it. It comes back as an `ObjectId` object, while a document's own `_id` comes back as a hex string.
* On MongoDB, `include()` loads a relation that is stored as a reference. It takes the relation name and nothing else, and TypeScript rejects a second argument.

#### Options [#options-2]

| Argument       | Type                                                                | Required | Description                                                               |
| -------------- | ------------------------------------------------------------------- | -------- | ------------------------------------------------------------------------- |
| `relationName` | `string`                                                            | Yes      | The relation to load.                                                     |
| `refineFn`     | A callback that returns the narrowed relation or an aggregate of it | No       | PostgreSQL only. Narrows the loaded relation, or reduces it to one value. |

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

| Return type  | Example                               | Description                                        |
| ------------ | ------------------------------------- | -------------------------------------------------- |
| `Collection` | `db.orm.public.User.include('posts')` | A collection whose rows carry the loaded relation. |

#### Examples [#examples-2]

##### To-one relation (PostgreSQL) [#to-one-relation-postgresql]

```typescript
const posts = await db.orm.public.Post.include('user').where({ id: postId }).all();
// posts[0].user is the related User
```

##### To-many relation (PostgreSQL) [#to-many-relation-postgresql]

```typescript
const users = await db.orm.public.User.include('posts').where({ id: aliceId }).all();
// users[0].posts is an array of the user's posts
```

##### Reference relation (MongoDB) [#reference-relation-mongodb]

```typescript
const posts = await db.orm.posts.include('author').where({ title: 'Hello world' }).all();
// posts[0].author._id is an ObjectId; compare it as String(posts[0].author._id)
```

For a task-oriented guide to loading related records, see [Relations and joins](https://www.prisma.io/docs/orm/fundamentals/relations-and-joins).

### Refinements, aggregates, and combine [#refinements-aggregates-and-combine]

On PostgreSQL, the callback you pass to `include()` receives the related rows as a collection. This page calls that callback a refinement, which is the word in the heading above. You can filter, order, and paginate the related rows in it, and you can also reduce them to a single value, or use `combine()` to return several results at once. To count or sum a whole model instead of a relation, see [Grouped aggregates](#grouped-aggregates), where `aggregate((agg) => ({ n: agg.count() }))` counts every row.

#### Remarks [#remarks-3]

* PostgreSQL only. On MongoDB, `include()` takes no callback, and `count`, `sum`, `avg`, `min`, `max`, and `combine` do not exist on a collection.
* On PostgreSQL, those six are only callable **inside** an `include()` callback. Called anywhere else, they throw an error whose `code` is `ORM.INCLUDE_INVALID`.
* `sum()` and `avg()` take a numeric column: an integer, a floating-point number, or a decimal. They also take an interval or a `Time` column, the time-of-day type. They do not take a date or a timestamp, and TypeScript rejects a date or timestamp field name here.
* `min()` and `max()` take more: numeric and text columns, dates, times, timestamps, intervals, IP addresses, and text arrays. They do not take a boolean, a `uuid`, binary data, a bit string, or JSON.
* `count()` returns a `number`. With no argument it counts rows; with a field name it counts rows where that field is not null. `countBigInt()` returns a `bigint` instead, for a count too large for a JavaScript `number`.
* `sum()` over an integer column returns a `number`. A total too large for a `number` throws an error whose `code` is `RUNTIME.DECODE_FAILED`. Use `sumBigInt(field)` for totals that large. It returns a `bigint`.
* `avg()` over an integer column returns a JavaScript floating-point `number`. `avgDecimal(field)` returns the exact average as a decimal string. Pass that string to a decimal library, or call `Number(...)` on it when a floating-point value is fine.
* When a to-many relation has no rows, `sum()`, `avg()`, `min()`, and `max()` come back as `null`, not `0`.
* `combine(shape)` takes an object. You choose the keys. Call `posts.combine({ ... })`, and inside the object write `posts` again for each value. The `posts` inside the object is the same collection the callback received, with nothing chained on it yet. A value that chains more methods on `posts` comes back as an array of rows. A value that is an aggregate, such as `posts.count()`, comes back as that single value.

#### Options [#options-3]

| Argument                                         | Type                                       | Required | Description                                                                      |
| ------------------------------------------------ | ------------------------------------------ | -------- | -------------------------------------------------------------------------------- |
| `where()` / `orderBy()` / `limit()` / `offset()` | Chained on the nested collection           | No       | Refine which related rows load.                                                  |
| `count()`                                        | Aggregate, no arguments                    | No       | Reduces the relation to a row count.                                             |
| `sum(field)` / `avg(field)`                      | Aggregate over a numeric field             | No       | Reduces the relation to one number.                                              |
| `min(field)` / `max(field)`                      | Aggregate over a sortable field            | No       | Reduces the relation to its smallest or largest value, in the column's own type. |
| `combine(shape)`                                 | Object of named collections and aggregates | No       | Returns several results under one relation key.                                  |

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

| Return type                                                 | Example                              | Description                                                                           |
| ----------------------------------------------------------- | ------------------------------------ | ------------------------------------------------------------------------------------- |
| The narrowed relation, a single value, or an object of both | `include('posts', (p) => p.count())` | The relation key on each row holds the callback's result instead of the related rows. |

#### Examples [#examples-3]

##### Filter, order, and limit within a relation (PostgreSQL) [#filter-order-and-limit-within-a-relation-postgresql]

```typescript
const users = await db.orm.public.User.include('posts', (posts) =>
  posts
    .where((p) => p.priority.eq('low'))
    .orderBy((p) => p.createdAt.desc())
    .limit(1),
)
  .where({ id: aliceId })
  .all();
```

##### Reduce a relation to a count (PostgreSQL) [#reduce-a-relation-to-a-count-postgresql]

```typescript
const users = await db.orm.public.User.include('posts', (posts) => posts.count())
  .where({ id: aliceId })
  .all();
// users[0].posts is the number 2
```

##### `sum()` / `avg()` over a numeric relation (PostgreSQL) [#sum--avg-over-a-numeric-relation-postgresql]

This example uses the `Customer` and `Order` models, which are in the separate schema shown under [Grouped aggregates](#grouped-aggregates).

```typescript
const customers = await db.orm.public.Customer.include('orders', (orders) => orders.sum('amount'))
  .where({ id: acmeId })
  .all();
// customers[0].orders is 1500

const avgCustomers = await db.orm.public.Customer.include('orders', (orders) => orders.avg('amount'))
  .where({ id: acmeId })
  .all();
// avgCustomers[0].orders is 300
```

##### `combine()` multiple sub-views (PostgreSQL) [#combine-multiple-sub-views-postgresql]

```typescript
const users = await db.orm.public.User.include('posts', (posts) =>
  posts.combine({
    recent: posts.orderBy((p) => p.createdAt.desc()).limit(1),
    total: posts.count(),
  }),
)
  .where({ id: aliceId })
  .all();
// users[0].posts.total is 2; users[0].posts.recent is a one-element array
```

> [!NOTE]
> `min()`
> 
>  and
> 
> `max()`
> 
>  over a date column
> 
> `min('createdAt')` over a `DateTime` column compiles and returns a `Temporal.Instant`, the standard JavaScript object for a point in time. Not every JavaScript engine has `Temporal` yet. Check with `typeof Temporal === 'undefined'`, and if yours does not, install `temporal-polyfill` and add `import 'temporal-polyfill/full/global'`.
> 
> `sum('createdAt')` and `avg('createdAt')` do not compile.

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

Sort the result set.

#### Remarks [#remarks-4]

* Available for PostgreSQL and MongoDB, with different argument shapes.
* On PostgreSQL, `orderBy()` takes a callback that returns `.asc()` or `.desc()` on one column. Pass an array of such callbacks to sort by more than one column.
* On MongoDB, `orderBy()` takes an object instead: `{ field: 1 }` sorts ascending and `{ field: -1 }` sorts descending.
* The example schema declares `Priority` text-backed, so on PostgreSQL it sorts by its stored text: `'high'`, then `'low'`, then `'urgent'`. A native enum column sorts in the order its members are declared instead, so the same members in a `native_enum Priority` block sort `Low`, `High`, `Urgent`. Use a native enum when the sort order matters.

#### Options [#options-4]

| Argument | Type                                                                                                                                 | Required | Description                       |
| -------- | ------------------------------------------------------------------------------------------------------------------------------------ | -------- | --------------------------------- |
| `sort`   | Callback `(fields) => f.field.asc() \| .desc()`, an array of such callbacks (PostgreSQL), or a `{ field: 1 \| -1 }` object (MongoDB) | Yes      | The sort key(s) and direction(s). |

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

| Return type  | Example                           | Description                            |
| ------------ | --------------------------------- | -------------------------------------- |
| `Collection` | `db.orm.public.Post.orderBy(...)` | A collection with an ordering applied. |

#### Examples [#examples-4]

##### Ascending and descending [#ascending-and-descending]

  

#### PostgreSQL

```typescript
const newestFirst = await db.orm.public.Post.where({ userId: aliceId })
  .orderBy((p) => p.createdAt.desc())
  .all();
```

#### MongoDB

```typescript
const newestFirst = await db.orm.posts.orderBy({ createdAt: -1 }).all();
```

##### Multiple sort keys (PostgreSQL) [#multiple-sort-keys-postgresql]

```typescript
const byPriorityThenDate = await db.orm.public.Post.orderBy([
  (p) => p.priority.asc(),
  (p) => p.createdAt.asc(),
]).all();
// Priority is text-backed here, so it sorts as 'high', 'low', 'urgent'
```

### `limit()` [#limit]

Limit the number of returned rows.

#### Remarks [#remarks-5]

* Available for PostgreSQL and MongoDB.

#### Options [#options-5]

| Argument | Type     | Required | Description                       |
| -------- | -------- | -------- | --------------------------------- |
| `count`  | `number` | Yes      | Maximum number of rows to return. |

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

| Return type  | Example                       | Description                           |
| ------------ | ----------------------------- | ------------------------------------- |
| `Collection` | `db.orm.public.Post.limit(2)` | A collection limited to `count` rows. |

#### Examples [#examples-5]

##### Limit the result set [#limit-the-result-set]

  

#### PostgreSQL

```typescript
const firstTwo = await db.orm.public.Post.orderBy((p) => p.createdAt.asc()).limit(2).all();
```

#### MongoDB

```typescript
const firstOne = await db.orm.posts.orderBy({ createdAt: 1 }).limit(1).all();
```

### `offset()` [#offset]

Offset into the ordered result set.

#### Remarks [#remarks-6]

* Available for PostgreSQL and MongoDB.
* Combine with `orderBy()` and `limit()` for pagination.

#### Options [#options-6]

| Argument | Type     | Required | Description             |
| -------- | -------- | -------- | ----------------------- |
| `count`  | `number` | Yes      | Number of rows to skip. |

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

| Return type  | Example                        | Description                          |
| ------------ | ------------------------------ | ------------------------------------ |
| `Collection` | `db.orm.public.Post.offset(2)` | A collection offset by `count` rows. |

#### Examples [#examples-6]

##### Offset into the result set [#offset-into-the-result-set]

  

#### PostgreSQL

```typescript
const page2 = await db.orm.public.Post.orderBy((p) => p.createdAt.asc()).offset(2).limit(2).all();
```

#### MongoDB

```typescript
const secondPost = await db.orm.posts.orderBy({ createdAt: 1 }).offset(1).limit(1).all();
```

### `cursor()` [#cursor]

Resume pagination from a known position.

#### Remarks [#remarks-7]

* PostgreSQL only. `cursor()` does not exist on a MongoDB collection.
* Always call `orderBy()` before `cursor()`. TypeScript rejects the call if you do not. Nothing checks this while the query runs, so without the `orderBy()` the cursor is ignored and every row comes back.
* The cursor object must name every column the `orderBy()` sorts on. Leave one out and running the query throws an error whose `code` is `ORM.CURSOR_VALUE_MISSING`. With two sort columns, give both keys. The cursor compares the sort columns in order: first by the first column, then by the second for rows that tie on the first.

#### Options [#options-7]

| Argument | Type                                              | Required | Description                   |
| -------- | ------------------------------------------------- | -------- | ----------------------------- |
| `values` | Object of the `orderBy()` key(s) and their values | Yes      | The position to resume after. |

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

| Return type  | Example                                    | Description                                      |
| ------------ | ------------------------------------------ | ------------------------------------------------ |
| `Collection` | `db.orm.public.Post.cursor({ createdAt })` | A collection resuming after the cursor position. |

#### Examples [#examples-7]

##### Resume pagination (PostgreSQL) [#resume-pagination-postgresql]

```typescript
const page1 = await db.orm.public.Post.orderBy((p) => p.createdAt.asc()).limit(2).all();
const last = page1[page1.length - 1];

const page2 = await db.orm.public.Post.orderBy((p) => p.createdAt.asc())
  .cursor({ createdAt: last.createdAt })
  .limit(2)
  .all();
```

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

Remove duplicate rows, comparing only the fields you name.

#### Remarks [#remarks-8]

* PostgreSQL only. `distinct()` does not exist on a MongoDB collection.
* You do not need `select()`. `distinct('priority')` keeps one whole row per distinct `priority` value, whatever the query returns.
* Which of the tied rows it keeps is not defined. Call `orderBy()` first to choose, as [`distinctOn()`](#distincton) does.

#### Options [#options-8]

| Argument    | Type                   | Required | Description                   |
| ----------- | ---------------------- | -------- | ----------------------------- |
| `...fields` | Field names (`string`) | Yes      | The fields to deduplicate on. |

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

| Return type  | Example                                   | Description                                                   |
| ------------ | ----------------------------------------- | ------------------------------------------------------------- |
| `Collection` | `db.orm.public.Post.distinct('priority')` | A collection with duplicate rows removed on the named fields. |

#### Examples [#examples-8]

##### Deduplicate on a field (PostgreSQL) [#deduplicate-on-a-field-postgresql]

```typescript
const priorities = await db.orm.public.Post.distinct('priority').all();
// priorities holds one whole Post row per distinct priority value
```

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

Keep the first row per key according to `orderBy()`.

#### Remarks [#remarks-9]

* PostgreSQL only. `distinctOn()` does not exist on a MongoDB collection.
* Call `orderBy()` first, so that "the first row per key" means something. TypeScript rejects the call if you do not.
* Start the `orderBy()` with the same columns you pass to `distinctOn()`, as the example does. Sort by anything else first and the query fails in the database.

#### Options [#options-9]

| Argument    | Type                   | Required | Description                                |
| ----------- | ---------------------- | -------- | ------------------------------------------ |
| `...fields` | Field names (`string`) | Yes      | The key field(s) to keep the first row of. |

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

| Return type  | Example                                   | Description                           |
| ------------ | ----------------------------------------- | ------------------------------------- |
| `Collection` | `db.orm.public.Post.distinctOn('userId')` | A collection keeping one row per key. |

#### Examples [#examples-9]

##### First row per key (PostgreSQL) [#first-row-per-key-postgresql]

```typescript
const latestPerUser = await db.orm.public.Post.orderBy([(p) => p.userId.asc(), (p) => p.createdAt.desc()])
  .distinctOn('userId')
  .all();
// latestPerUser holds one post per userId: the newest one, because of the orderBy
```

### `variant()` [#variant]

Narrow a model to one of the variants declared with `@@base`.

#### Remarks [#remarks-10]

* Available for PostgreSQL and MongoDB.
* Pass the variant's model name, such as `'Bug'`, not the string in its `@@base` attribute. On MongoDB you pass the model name too, even though the accessor before it is the collection name.
* On PostgreSQL, a variant that sets its own `@@map`, as `Bug` and `Feature` do, is stored in its own table. Read [`createAndCount()`](#createandcount) and [`upsert()`](#upsert) before you write to such a variant.
* On MongoDB, every variant is a document in the one collection, told apart by the field named in `@@discriminator`.

#### Options [#options-10]

| Argument      | Type     | Required | Description                    |
| ------------- | -------- | -------- | ------------------------------ |
| `variantName` | `string` | Yes      | The model name of the variant. |

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

| Return type                           | Example                             | Description                                     |
| ------------------------------------- | ----------------------------------- | ----------------------------------------------- |
| `Collection`, narrowed to the variant | `db.orm.public.Task.variant('Bug')` | A collection holding only rows of that variant. |

#### Examples [#examples-10]

##### Narrow to a variant [#narrow-to-a-variant]

  

#### PostgreSQL

```typescript
const bugs = await db.orm.public.Task.variant('Bug').all();
```

#### MongoDB

```typescript
const tutorials = await db.orm.posts.variant('Tutorial').all();
```

## Read methods [#read-terminals]

A read method runs the query and gives you the rows. `all()` and `first()` are available on both databases, while `aggregate()` and `groupBy()` are PostgreSQL only and are documented under [Grouped aggregates](#grouped-aggregates).

There is no `count()` method on either database, so on PostgreSQL, count with `aggregate((a) => ({ n: a.count() }))`. On MongoDB, counting takes two steps: `db.query`, the [pipeline builder](https://www.prisma.io/docs/orm/fundamentals/advanced-queries#mongodb-pipeline-builder), builds the pipeline, and `runtime.query(...)` runs it.

```typescript
const built = db.query.from('posts').count('total').build();
const runtime = await db.runtime();
const [counted] = await runtime.query(built); // counted.total is the count
```

For a task-oriented walkthrough, see [Reading data](https://www.prisma.io/docs/orm/fundamentals/reading-data).

### `all()` [#all]

Resolve the query to every matching row.

#### Remarks [#remarks-11]

* Available for PostgreSQL and MongoDB.
* `all()` returns an [`AsyncIterableResult`](#asynciterableresult): you can `await` it to collect an array, or use `for await` to stream rows one at a time.
* `await`ing a result you have already `await`ed is safe. You get the same array back, and the query does not run again. Each result can be used one way only, so awaiting it and then looping it with `for await`, or the reverse, throws an error whose `code` is `RUNTIME.ITERATOR_CONSUMED`. See [Single consumption and mode switching](#single-consumption-and-mode-switching).
* `orderBy()` is written differently on each database. See [`orderBy()`](#orderby).

#### Options [#options-11]

`all()` takes no required arguments.

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

| Return type                | Example                          | Description                                                       |
| -------------------------- | -------------------------------- | ----------------------------------------------------------------- |
| `AsyncIterableResult<Row>` | `await db.orm.public.User.all()` | Awaitable to `Row[]`, or iterable with `for await` for streaming. |

`Row` is the model's row type, from the emitted `contract.d.ts`.

#### Examples [#examples-11]

##### Await to collect an array [#await-to-collect-an-array]

  

#### PostgreSQL

```typescript
const users = await db.orm.public.User.all();
```

#### MongoDB

```typescript
const users = await db.orm.users.all();
```

##### Stream rows one at a time [#stream-rows-one-at-a-time]

  

#### PostgreSQL

```typescript
for await (const user of db.orm.public.User.orderBy((u) => u.email.asc()).all()) {
  console.log(user.email);
}
```

#### MongoDB

```typescript
for await (const post of db.orm.posts.orderBy({ createdAt: 1 }).all()) { // 1 ascending, -1 descending
  console.log(post.title);
}
```

For Prisma ORM 7 users, `findMany` maps onto `all()`:

```diff
- const users = await prisma.user.findMany({ where: { kind: 'admin' } });
+ const users = await db.orm.public.User.where({ kind: 'admin' }).all();
```

### `first()` [#first]

Resolve the query to the first matching row, or `null` if none matches.

#### Remarks [#remarks-12]

* Available for PostgreSQL and MongoDB.
* On PostgreSQL, `first()` accepts an inline filter: a shorthand object or a callback (`first((p) => p.priority.eq('urgent'))`). An inline filter is added to any earlier `where()` call, and both have to match.
* On MongoDB, `first()` takes no filter argument. Filter with `where(...)` first, then call `first()`.

#### Options [#options-12]

| Name     | Type                                           | Required | Description                                |
| -------- | ---------------------------------------------- | -------- | ------------------------------------------ |
| `filter` | Shorthand object or callback (PostgreSQL only) | No       | An inline filter applied before resolving. |

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

| Return type   | Example                               | Description                        |
| ------------- | ------------------------------------- | ---------------------------------- |
| `Row \| null` | `await db.orm.public.User.first(...)` | The first matching row, or `null`. |

#### Examples [#examples-12]

##### Match by an inline filter (PostgreSQL) [#match-by-an-inline-filter-postgresql]

```typescript
const alice = await db.orm.public.User.first({ email: 'alice@example.com' });
const urgentPost = await db.orm.public.Post.first((p) => p.priority.eq('urgent'));
```

##### Match with a prior `where()` (MongoDB) [#match-with-a-prior-where-mongodb]

```typescript
const bob = await db.orm.users.where({ name: 'Bob' }).first();
```

For Prisma ORM 7 users, `findUnique` and `findFirst` map onto `first()`:

```diff
- const alice = await prisma.user.findUnique({ where: { email } });
+ const alice = await db.orm.public.User.first({ email });
- const alice = await prisma.user.findUniqueOrThrow({ where: { email } });
+ const alice = await db.orm.public.User.where({ email }).all().firstOrThrow();
```

`first()` returns `null` on no match and does not check that only one row matched. `findUniqueOrThrow` and `findFirstOrThrow` both map onto `all().firstOrThrow()`, as the last line above shows.

`firstOrThrow()` is a method on the result that `all()` gives you, so calling it is not a second use of that result. It reads every matching row into memory and returns the first one, and it throws an error whose `code` is `RUNTIME.NO_ROWS` when nothing matched.

### Custom `Collection` subclass [#custom-collection-subclass]

On PostgreSQL you can subclass `Collection` to add your own methods to a model, and register the subclass when you create the client.

#### Remarks [#remarks-13]

* PostgreSQL only. You cannot subclass a collection on MongoDB.
* `db.orm` cannot use your subclass. Call the `orm(...)` function with a `collections` object to get a second accessor with the same methods as `db.orm.public`. Use that accessor for the models you subclassed, and keep `db.orm.public` for every other model. Your existing `db.orm.public.X` calls keep working.
* `client` in the example below is what `postgres(...)` returns, the same object [Setting up the client](#setting-up-the-client) calls `db`. The example creates one so that it runs on its own.
* `Collection<Contract, 'Task'>` takes two type arguments: your contract type, and the name of the model this collection is for.
* A method that returns `this.variant('Bug')` gives back a collection narrowed to the `Bug` model. It is chainable, so you finish it with `all()`, `first()`, or any other method.

#### Examples [#examples-13]

##### Register a subclass with a domain method (PostgreSQL) [#register-a-subclass-with-a-domain-method-postgresql]

```typescript
// postgres() comes from /runtime; Collection and orm() come from /orm-client.
import postgres from '@prisma/orm-postgres/runtime';
import { Collection, orm } from '@prisma/orm-postgres/orm-client';
import type { Contract } from './contract.d';
import contractJson from './contract.json' with { type: 'json' };

class TaskCollection extends Collection<Contract, 'Task'> {
  bugs() {
    return this.variant('Bug');
  }
  features() {
    return this.variant('Feature');
  }
}

const client = postgres<Contract>({ contractJson, url: process.env.DATABASE_URL });
const runtime = await client.connect();

const tasks = orm({
  runtime,
  context: client.context,
  collections: { Task: TaskCollection },
}).public; // .public is the PostgreSQL schema

const bugs = await tasks.Task.bugs().all();
const features = await tasks.Task.features().all();
```

## Write methods [#mutation-terminals]

A write method changes the database. `create`, `createAll`, `createAndCount`, `update`, `updateAll`, `updateAndCount`, `delete`, `deleteAll`, `deleteAndCount`, and `upsert` are available on both databases, and the differences between the two are listed under each method.

For Prisma ORM 7 users: `createMany` is `createAll()` or `createAndCount()`, `updateMany` is `updateAll()` or `updateAndCount()`, `deleteMany` is `deleteAll()` or `deleteAndCount()`, and `count` is [`aggregate()`](#grouped-aggregates) on PostgreSQL, or the two steps shown under [Read methods](#read-terminals) on MongoDB. There is no `skipDuplicates` option.

To catch one of the errors named below, wrap the call in `try` and `catch`, then compare `error.code` with the string given here. For a task-oriented walkthrough, see [Writing data](https://www.prisma.io/docs/orm/fundamentals/writing-data). On MongoDB, a write method rejects a chain that already has `orderBy()`, `limit()`, or `offset()` on it, and every write except `updateAll()` and `deleteAll()` also rejects `include()`. Both throw an error whose `code` is `ORM.OPERATION_UNSUPPORTED`. For grouping several writes into one unit, see [Transactions](https://www.prisma.io/docs/orm/fundamentals/transactions).

> [!WARNING]
> Filter before you update or delete
> 
> Always call `where()` before an update or a delete. On MongoDB the six update and delete methods and `upsert()` check for a filter and throw an error whose `code` is `ORM.WHERE_MISSING`, with a message naming the method you called, such as `updateAll() requires a .where() filter. Call .where() before .updateAll()`. On PostgreSQL, these methods do not compile without a `where()`, but nothing checks at run time. If a call without a filter does reach the database, `update()` and `delete()` change a single row and you cannot predict which one, and `updateAll()` and `deleteAll()` change every row.

The examples below use `aliceId`, `bobId`, `carolId`, `postId`, `tagId`, `tutorialId`, and `userId` for the ids of rows that already exist. Substitute your own.

### `create()` [#create]

Insert a single row and return it.

#### Remarks [#remarks-14]

* Available for PostgreSQL and MongoDB.
* On PostgreSQL, you may leave out any field your contract gives a default, including the id. Passing an explicit id is accepted. Nullable fields can be left out too. Every other field is required.
* On MongoDB, `_id` is the only field you can leave out. Give every other field a value, and write `null` for a field you want empty.
* On MongoDB, `create()` returns the values you passed plus the `_id` the server assigned, not the stored document. Read the row back if you need a value the database filled in. On PostgreSQL the returned row is read from the database already.
* On PostgreSQL, `create()` can create or link related rows in the same transaction. Nest `create()` on the side that does not hold the foreign key, and `connect()` on the side that does.
* Nested `create()` and `connect()` are not available on MongoDB.

#### Options [#options-13]

| Name             | Type                   | Required | Description                                                                          |
| ---------------- | ---------------------- | -------- | ------------------------------------------------------------------------------------ |
| The row's fields | Object of field values | Yes      | The row to insert. Pass the object as the only argument. There is no `data` wrapper. |

On PostgreSQL, that object can also carry a `create` or `connect` callback for a related row, as shown below. `create()` and `connect()` each take one object or an array of objects, on any relation. `disconnect()` takes an array, and on a many-to-many relation the array is required.

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

| Return type | Example                               | Description       |
| ----------- | ------------------------------------- | ----------------- |
| `Row`       | `await db.orm.public.Tag.create(...)` | The inserted row. |

#### Examples [#examples-14]

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

  

#### PostgreSQL

```typescript
const tag = await db.orm.public.Tag.create({ label: 'typescript-2' });
```

#### MongoDB

```typescript
const user = await db.orm.users.create({
  name: 'Carol',
  email: 'carol@example.com',
  bio: null,
  role: 'reader',
  address: null,
});
// user._id is the server-assigned id
```

##### Nested `create()` on the side without the foreign key (PostgreSQL) [#nested-create-on-a-child-owned-relation-postgresql]

```typescript
const author = await db.orm.public.User.create({
  email: 'dana@example.com',
  displayName: 'Dana',
  kind: 'user',
  posts: (posts) => posts.create([{ title: 'Dana post one' }]),
});
```

##### Nested `connect()` on the side with the foreign key (PostgreSQL) [#nested-connect-on-a-parent-owned-relation-postgresql]

```typescript
const post = await db.orm.public.Post.create({
  title: 'Connected to Bob',
  user: (user) => user.connect({ id: bobId }),
});
```

For Prisma ORM 7 users, `create()` drops the `data` wrapper:

```diff
- const tag = await prisma.tag.create({ data: { label: 'typescript-2' } });
+ const tag = await db.orm.public.Tag.create({ label: 'typescript-2' });
```

### `createAll()` [#createall]

Insert multiple rows and return them.

#### Remarks [#remarks-15]

* Available for PostgreSQL and MongoDB.
* Returns an [`AsyncIterableResult`](#asynciterableresult): `await` for an array, or `for await` to stream inserted rows.
* On PostgreSQL every row is inserted by one statement before the first row reaches you. Stopping the loop early does not undo an insert.

#### Options [#options-14]

| Name     | Type                   | Required | Description                                                                  |
| -------- | ---------------------- | -------- | ---------------------------------------------------------------------------- |
| The rows | Array of field objects | Yes      | The rows to insert, passed as the only argument. There is no `data` wrapper. |

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

| Return type                | Example                                    | Description                          |
| -------------------------- | ------------------------------------------ | ------------------------------------ |
| `AsyncIterableResult<Row>` | `await db.orm.public.Tag.createAll([...])` | Awaitable to `Row[]`, or streamable. |

#### Examples [#examples-15]

##### Insert and collect [#insert-and-collect]

  

#### PostgreSQL

```typescript
const created = await db.orm.public.Tag.createAll([{ label: 'alpha' }, { label: 'beta' }]);
```

#### MongoDB

```typescript
const created = await db.orm.users.createAll([
  { name: 'Dana', email: 'dana@example.com', bio: null, role: 'author', address: null },
  { name: 'Eve', email: 'eve@example.com', bio: null, role: 'reader', address: null },
]);
```

### `createAndCount()` [#createandcount]

Insert rows and return how many were inserted, without reading them back.

#### Remarks [#remarks-16]

* Available for PostgreSQL and MongoDB.
* On PostgreSQL, `createAndCount()` does not work on a variant that is stored in its own table, meaning its `@@map` names a different table than its base model does. `Bug` in the [example schema](#example-schema) is one: it maps to `bug` while `Task` maps to `task`. Use `createAll()` instead.
* Calling it on such a variant throws an error whose `code` is `ORM.OPERATION_UNSUPPORTED`, with the message `createAndCount() is not supported for MTI variant "Bug" on model "Task". Use createAll() instead.` MTI in the message means multi-table inheritance, the variant stored in its own table described above.
* On MongoDB, a variant is one document in one collection, so `createAndCount()` works on a variant as usual.

#### Options [#options-15]

| Name     | Type                   | Required | Description                                                                  |
| -------- | ---------------------- | -------- | ---------------------------------------------------------------------------- |
| The rows | Array of field objects | Yes      | The rows to insert, passed as the only argument. There is no `data` wrapper. |

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

| Return type | Example                                         | Description                 |
| ----------- | ----------------------------------------------- | --------------------------- |
| `number`    | `await db.orm.public.Tag.createAndCount([...])` | The count of inserted rows. |

#### Examples [#examples-16]

##### Insert and count [#insert-and-count]

  

#### PostgreSQL

```typescript
const inserted = await db.orm.public.Tag.createAndCount([{ label: 'epsilon' }, { label: 'zeta' }]);
// 2
```

#### MongoDB

```typescript
const inserted = await db.orm.posts.variant('Tutorial').createAndCount([
  {
    title: 'Variant createAndCount',
    content: 'body',
    authorId: aliceId,
    createdAt: new Date('2024-02-01T00:00:00.000Z'),
    difficulty: 'beginner',
    duration: 10,
  },
]);
// 1
```

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

Update the matched row and return it, or `null` if none matches.

#### Remarks [#remarks-17]

* Available for PostgreSQL and MongoDB. Requires a prior `where()` (see the warning at the top of this section).

* On MongoDB, `update()` takes an object of field values, or a callback that returns field operations, such as `(p) => [p.content.set('Rewritten')]`. See [Field update operations](#field-update-operations).

* On MongoDB, the update input covers the base model's fields only. Putting a field declared on a variant in the update, such as `duration` on `Tutorial`, makes TypeScript complain even though the update runs correctly. Put `// @ts-expect-error` on the line directly above the line TypeScript flags. When the complaint goes away, TypeScript reports the comment itself as unused, so delete it then. The same is true of `updateAll()` and `updateAndCount()`.

  ```typescript
  // @ts-expect-error duration is a Tutorial field, not a Post field
  .update((t) => [t.duration.inc(10)]);
  ```

* Take care on PostgreSQL: `update()` takes an object of field values only, and passing a function instead writes nothing, reports nothing, and returns `null`.

* On PostgreSQL, `update()` can also relink related rows. `connect()` points the foreign key at a different row, and `disconnect()` removes a many-to-many link without deleting either row.

* Returns `null` when no row matches. On PostgreSQL it also returns `null` when the object you pass is empty and has no keys at all. An object whose values already match the row is different: that update runs and returns the row.

* On PostgreSQL, [`select()`](#select) and [`include()`](#include) before `.update()` choose which fields and relations come back on the returned row. They change nothing about what is written.

#### Options [#options-16]

| Name        | Type                                                              | Required | Description                                                                              |
| ----------- | ----------------------------------------------------------------- | -------- | ---------------------------------------------------------------------------------------- |
| The changes | Object of field values, or on MongoDB a field-operations callback | Yes      | What to set on the matched row, passed as the only argument. There is no `data` wrapper. |

On PostgreSQL that object can also carry `connect` or `disconnect` for a related row, as shown below.

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

| Return type   | Example                                           | Description                                 |
| ------------- | ------------------------------------------------- | ------------------------------------------- |
| `Row \| null` | `await db.orm.public.User.where(...).update(...)` | The updated row, or `null` if none matched. |

#### Examples [#examples-17]

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

  

#### PostgreSQL

```typescript
const updated = await db.orm.public.User.where({ id: bobId }).update({ displayName: 'Bob Updated' });
```

#### MongoDB

```typescript
const updated = await db.orm.users.where({ _id: bobId }).update({ bio: 'Now with a bio' });
```

##### Field-operations callback (MongoDB) [#field-operations-callback-mongodb]

```typescript
const updated = await db.orm.posts
  .where({ _id: postId })
  .update((p) => [p.title.set('Updated title'), p.content.set('Rewritten')]);
```

##### Nested `connect()` relinks a foreign key (PostgreSQL) [#nested-connect-relinks-a-foreign-key-postgresql]

```typescript
const relinked = await db.orm.public.Post.where({ id: postId }).update({
  user: (user) => user.connect({ id: carolId }),
});
```

##### Nested `disconnect()` unlinks a many-to-many row (PostgreSQL) [#nested-disconnect-unlinks-a-many-to-many-row-postgresql]

```typescript
const updated = await db.orm.public.Post.where({ id: postId })
  .select('id', 'title')
  .include('tags', (tag) => tag.select('id', 'label').orderBy((t) => t.label.asc()))
  .update({
    tags: (tag) => tag.disconnect([{ id: tagId }]),
  });
// only the join-table row is removed. The Tag row itself still exists
```

For Prisma ORM 7 users, `update()` moves the filter into `where()` and drops the `data` wrapper:

```diff
- const updated = await prisma.user.update({ where: { id: bobId }, data: { displayName: 'Bob Updated' } });
+ const updated = await db.orm.public.User.where({ id: bobId }).update({ displayName: 'Bob Updated' });
```

### `updateAll()` [#updateall]

Update every matching row and collect the results.

#### Remarks [#remarks-18]

* Available for PostgreSQL and MongoDB. Call `where()` first. Without a filter this changes every row. See the warning at the top of this section.
* On PostgreSQL, `updateAll()` runs as one statement, so every matching row changes together.
* On MongoDB, `updateAll()` is not a single operation, and two things follow. If a document begins matching your filter while the call is running, it is updated but not included in the returned rows. If someone else changes a matched document while the call is running, you get their version back, not yours.
* Prisma ORM has no `db.transaction()` on MongoDB, so you cannot avoid this from the client. To control it, share one `MongoClient` with the MongoDB driver and group the writes in one of the driver's own sessions, shown under [Transactions on MongoDB](https://www.prisma.io/docs/orm/fundamentals/transactions#transactions-on-mongodb).

#### Options [#options-17]

| Name        | Type                                                              | Required | Description                                                                                |
| ----------- | ----------------------------------------------------------------- | -------- | ------------------------------------------------------------------------------------------ |
| The changes | Object of field values, or on MongoDB a field-operations callback | Yes      | What to set on every matched row, passed as the only argument. There is no `data` wrapper. |

#### Return type [#return-type-17]

| Return type                | Example                                              | Description       |
| -------------------------- | ---------------------------------------------------- | ----------------- |
| `AsyncIterableResult<Row>` | `await db.orm.public.Post.where(...).updateAll(...)` | The updated rows. |

#### Examples [#examples-18]

##### Update all matching rows [#update-all-matching-rows]

  

#### PostgreSQL

```typescript
const updated = await db.orm.public.Post.where({ userId: aliceId }).updateAll({ priority: 'urgent' });
```

#### MongoDB

```typescript
const updated = await db.orm.users.where({ role: 'author' }).updateAll({ role: 'admin' });
// see the remark above: this is not one operation
```

### `updateAndCount()` [#updateandcount]

Update every matching row and return the count.

#### Remarks [#remarks-19]

* Available for PostgreSQL and MongoDB. Call `where()` first. Without a filter this changes every row. See the warning at the top of this section.
* On MongoDB the count leaves out documents that already held the new values. On PostgreSQL it counts every matched row.

#### Options [#options-18]

| Name        | Type                                                              | Required | Description                                                                                |
| ----------- | ----------------------------------------------------------------- | -------- | ------------------------------------------------------------------------------------------ |
| The changes | Object of field values, or on MongoDB a field-operations callback | Yes      | What to set on every matched row, passed as the only argument. There is no `data` wrapper. |

#### Return type [#return-type-18]

| Return type | Example                                                   | Description                |
| ----------- | --------------------------------------------------------- | -------------------------- |
| `number`    | `await db.orm.public.Post.where(...).updateAndCount(...)` | The count of updated rows. |

#### Examples [#examples-19]

##### Update and count [#update-and-count]

  

#### PostgreSQL

```typescript
const count = await db.orm.public.Post.where({ userId: carolId }).updateAndCount({ priority: 'low' });
```

#### MongoDB

```typescript
const count = await db.orm.users.where({ role: 'author' }).updateAndCount({ role: 'admin' });
```

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

Remove the matched row and return it, or `null` if none matches.

#### Remarks [#remarks-20]

* Available for PostgreSQL and MongoDB. Requires a prior `where()` (see the section warning above). Returns `null` when no row matches.

#### Options [#options-19]

`delete()` takes no required arguments. Filter with `where()` first.

#### Return type [#return-type-19]

| Return type   | Example                                       | Description                                 |
| ------------- | --------------------------------------------- | ------------------------------------------- |
| `Row \| null` | `await db.orm.public.Tag.where(...).delete()` | The deleted row, or `null` if none matched. |

#### Examples [#examples-20]

##### Delete a row [#delete-a-row]

  

#### PostgreSQL

```typescript
const created = await db.orm.public.Tag.create({ label: 'throwaway' });
const deleted = await db.orm.public.Tag.where({ id: created.id }).delete();
```

#### MongoDB

```typescript
const deleted = await db.orm.users.where({ _id: userId }).delete();
```

For Prisma ORM 7 users, `delete()` moves the filter into `where()`:

```diff
- const deleted = await prisma.tag.delete({ where: { id } });
+ const deleted = await db.orm.public.Tag.where({ id }).delete();
```

### `deleteAll()` [#deleteall]

Remove every matching row and collect the results.

#### Remarks [#remarks-21]

* Available for PostgreSQL and MongoDB. Call `where()` first. Without a filter this deletes every row. See the warning at the top of this section.
* Every matching row is deleted before the first row reaches you. Stopping the loop early does not save any of them.

#### Options [#options-20]

`deleteAll()` takes no required arguments. Filter with `where()` first.

#### Return type [#return-type-20]

| Return type                | Example                                           | Description       |
| -------------------------- | ------------------------------------------------- | ----------------- |
| `AsyncIterableResult<Row>` | `await db.orm.public.Post.where(...).deleteAll()` | The deleted rows. |

#### Examples [#examples-21]

##### Delete all matching rows [#delete-all-matching-rows]

  

#### PostgreSQL

```typescript
const deleted = await db.orm.public.Post.where({ userId: carolId }).deleteAll();
```

#### MongoDB

```typescript
const deleted = await db.orm.users.where({ role: 'reader' }).deleteAll();
```

### `deleteAndCount()` [#deleteandcount]

Remove every matching row and return the count.

#### Remarks [#remarks-22]

* Available for PostgreSQL and MongoDB. Call `where()` first. Without a filter this deletes every row. See the warning at the top of this section. Returns `0` when no row matches.

#### Options [#options-21]

`deleteAndCount()` takes no required arguments. Filter with `where()` first.

#### Return type [#return-type-21]

| Return type | Example                                                | Description                |
| ----------- | ------------------------------------------------------ | -------------------------- |
| `number`    | `await db.orm.public.Post.where(...).deleteAndCount()` | The count of deleted rows. |

#### Examples [#examples-22]

##### Delete and count [#delete-and-count]

  

#### PostgreSQL

```typescript
const count = await db.orm.public.Post.where({ userId: carolId }).deleteAndCount();
```

#### MongoDB

```typescript
const count = await db.orm.users.where({ role: 'reader' }).deleteAndCount();
```

### `upsert()` [#upsert]

Insert a row if none matches, otherwise update the existing row.

#### Remarks [#remarks-23]

* On MongoDB, call `where()` first. The filter decides which document is updated.
* On PostgreSQL, `upsert()` never reads the filters. A `where()` before it is ignored, without an error. `conflictOn` decides which row counts as already existing.
* `conflictOn` names the unique column that decides insert or update, such as `conflictOn: { label: 'typescript' }`. Only the column name is used. The value is required by the type and is then ignored, so pass any value of the right type.
* For a unique constraint on several columns together, name every one of those columns, as in `conflictOn: { orgId: '', email: '' }`. The set you name has to match one `@@unique` constraint.
* When a row is inserted on PostgreSQL, the `create` side is used as you wrote it.
* When a row is inserted on MongoDB, a field that appears in both `create` and `update` takes the `update` value.
* On MongoDB, the `update` side takes an object of field values or a field-operations callback.
* On PostgreSQL, `upsert()` does not work on a variant stored in its own table, the same restriction as [`createAndCount()`](#createandcount). It throws an error whose `code` is `ORM.OPERATION_UNSUPPORTED`, with the message `upsert() is not supported for MTI variant "Bug" on model "Task". Use createAll() instead.`
* There is no upsert for such a variant. Read the row with `first()`, then call `create()` or `update()`.
* On MongoDB, the `_id` on the returned row is a hex string, not the driver's `ObjectId`.

#### Options [#options-22]

| Name         | Type                                                              | Required | Description                                                 |
| ------------ | ----------------------------------------------------------------- | -------- | ----------------------------------------------------------- |
| `create`     | Object of field values                                            | Yes      | The row to insert if none matches.                          |
| `update`     | Object of field values, or on MongoDB a field-operations callback | Yes      | The changes to apply if a row matches.                      |
| `conflictOn` | Object naming the unique column or columns, PostgreSQL only       | No       | What decides insert or update. Omit to use the primary key. |

#### Return type [#return-type-22]

| Return type | Example                               | Description                  |
| ----------- | ------------------------------------- | ---------------------------- |
| `Row`       | `await db.orm.public.Tag.upsert(...)` | The inserted or updated row. |

#### Examples [#examples-23]

##### Insert or update (PostgreSQL) [#insert-or-update-postgresql]

```typescript
// Insert: no existing row has label 'brand-new', so the row is inserted
// exactly as the create side describes it.
const inserted = await db.orm.public.Tag.upsert({
  create: { label: 'brand-new' },
  update: { label: 'brand-new-updated' },
  conflictOn: { label: 'brand-new' }, // only the column name `label` is used
});

// Update: a row with label 'typescript' already exists, so the update runs against it.
const updated = await db.orm.public.Tag.upsert({
  create: { label: 'typescript' },
  update: { label: 'typescript-renamed' },
  conflictOn: { label: 'typescript' },
});
```

##### Insert or update (MongoDB) [#insert-or-update-mongodb]

```typescript
const user = await db.orm.users.where({ email: 'newperson@example.com' }).upsert({
  create: {
    name: 'New Person',
    email: 'newperson@example.com',
    bio: null,
    role: 'reader',
    address: null,
  },
  update: { bio: 'set on upsert' },
});
// on insert, bio is 'set on upsert': the `update` value wins on MongoDB
```

##### Field-operations callback on the update side (MongoDB) [#field-operations-callback-on-the-update-side-mongodb]

```typescript
const post = await db.orm.posts
  .variant('Tutorial')
  .where({ _id: tutorialId })
  .upsert({
    create: {
      title: 'Should not be used',
      content: 'unused',
      authorId: bobId,
      createdAt: new Date('2024-01-01T00:00:00.000Z'),
      difficulty: 'beginner',
      duration: 0,
    },
    update: (t) => [t.content.set('Updated content')],
  });
```

For Prisma ORM 7 users, `upsert()` replaces the `where` argument with `conflictOn` (PostgreSQL):

```diff
- const tag = await prisma.tag.upsert({
-   where: { label: 'typescript' },
-   update: { label: 'typescript-renamed' },
-   create: { label: 'typescript' },
- });
+ const tag = await db.orm.public.Tag.upsert({
+   update: { label: 'typescript-renamed' },
+   create: { label: 'typescript' },
+   conflictOn: { label: 'typescript' },
+ });
```

## Grouped aggregates [#grouped-aggregates]

These methods exist only on PostgreSQL collections. `aggregate()` treats every row the query matches as one group, while `groupBy().aggregate()` aggregates per group and `having()` filters those groups. A MongoDB model's collection has none of them, so to aggregate on MongoDB, use the pipeline builder, which is Prisma ORM's API for MongoDB's aggregation pipeline and is described in [MongoDB: Pipeline builder](https://www.prisma.io/docs/orm/fundamentals/advanced-queries#mongodb-pipeline-builder).

The examples below use a separate `Customer` / `Order` schema, where `Order.amount` is a numeric column:

**Expand for the aggregate example schema**

```prisma
model Customer {
  id      Uuid   @id @default(uuid())
  name    String
  segment String

  orders Order[]

  @@map("customer")
}

model Order {
  id         Uuid     @id @default(uuid())
  customerId Uuid
  channel    String
  amount     Int
  quantity   Int?
  placedAt   DateTime @default(now())

  customer Customer @relation(fields: [customerId], references: [id])

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

### `aggregate()` [#aggregate]

Compute aggregates over the rows the query matches. `aggregate()` runs the query, so you `await` it directly instead of calling `.all()`.

#### Remarks [#remarks-24]

* PostgreSQL only. Not available on MongoDB.
* `count()` counts rows and returns a number, `0` when no rows match.
* `count(field)` counts the rows where that field is not null.
* To count or sum the related rows of each parent row, use the `include()` callback instead; see [Refinements, aggregates, and combine](#refinements-aggregates-and-combine).
* `sum()`, `avg()`, `min()`, and `max()` return `null`, not `0`, when no rows match. Handle the `null`.
* A `limit()` or `offset()` earlier in the chain is applied first. The aggregate then covers only the rows they kept.
* What `sum()` and `avg()` give you back depends on the column type, as the table below shows.

| Column type     | `sum()` returns  | `avg()` returns  | Exact variant                 |
| --------------- | ---------------- | ---------------- | ----------------------------- |
| `Int`, `BigInt` | `number \| null` | `number \| null` | `sumBigInt()`, `avgDecimal()` |
| `Decimal`       | `string \| null` | `string \| null` | `avgDecimal()`                |

Use `sumBigInt()` when the total can pass `Number.MAX_SAFE_INTEGER`, because a `sum()` whose total passes it throws an error whose `code` is `RUNTIME.DECODE_FAILED`, thrown when the query result is read. Use `avgDecimal()` when you need the exact mean as a string, such as `'10.3333333333333333'`. Over a `Decimal` column `sum()` and `avg()` are already exact, and `sumBigInt()` is not available.

#### Options [#options-23]

| Argument   | Type                                               | Required | Description                                                                        |
| ---------- | -------------------------------------------------- | -------- | ---------------------------------------------------------------------------------- |
| `selector` | Callback `(agg) => ({ total: agg.sum('amount') })` | Yes      | The aggregates to compute. Each key you return becomes a key of the result object. |

The callback's `agg` argument gives you `count()`, `count(field)`, `sum(field)`, `avg(field)`, `min(field)`, and `max(field)`, plus `countBigInt()`, `sumBigInt(field)`, and `avgDecimal(field)`. `agg.countBigInt()` counts the same rows as `agg.count()` and returns a `bigint`.

#### Return type [#return-type-23]

| Return type | Example         | Description                                                                                   |
| ----------- | --------------- | --------------------------------------------------------------------------------------------- |
| Object      | `{ total: 10 }` | One key per key you returned from the callback. `sum`, `avg`, `min`, and `max` can be `null`. |

#### Examples [#examples-24]

##### Count (PostgreSQL) [#count-postgresql]

```typescript
const stats = await db.orm.public.Order.aggregate((agg) => ({
  total: agg.count(),
  withQuantity: agg.count('quantity'), // rows where quantity is not null
}));
// { total: 10, withQuantity: 8 }
```

##### Sum and average (PostgreSQL) [#sum-and-average-postgresql]

```typescript
const stats = await db.orm.public.Order.where({
  customerId: '20000000-0000-0000-0000-000000000001',
}).aggregate((agg) => ({
  totalAmount: agg.sum('amount'),
  avgAmount: agg.avg('amount'),
}));
// { totalAmount: 900, avgAmount: 300 }
```

##### Min and max (PostgreSQL) [#min-and-max-postgresql]

```typescript
const stats = await db.orm.public.Order.aggregate((agg) => ({
  cheapest: agg.min('amount'),
  priciest: agg.max('amount'),
}));
// { cheapest: 10, priciest: 500 }
```

##### Counts and totals past JavaScript's precision limit (PostgreSQL) [#counts-and-totals-past-javascripts-precision-limit-postgresql]

```typescript
const exact = await db.orm.public.Order.aggregate((agg) => ({
  rows: agg.countBigInt(),
  total: agg.sumBigInt('amount'),
  average: agg.avgDecimal('amount'),
}));
// { rows: 10n, total: 1500n, average: '150.0000000000000000' }
```

##### `null` over an empty set (PostgreSQL) [#null-over-an-empty-set-postgresql]

```typescript
const stats = await db.orm.public.Order.where((o) => o.amount.gt(999_999)).aggregate((agg) => ({
  total: agg.sum('amount'),
  average: agg.avg('amount'),
  count: agg.count(),
}));
// { total: null, average: null, count: 0 }
```

For Prisma ORM 7 users, `aggregate()` takes a selector callback instead of `_sum`/`_avg` keys:

```diff
- const stats = await prisma.order.aggregate({ _sum: { amount: true }, _avg: { amount: true } });
+ const stats = await db.orm.public.Order.aggregate((agg) => ({ total: agg.sum('amount'), average: agg.avg('amount') }));
```

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

Group rows by one or more fields, then aggregate per group.

#### Remarks [#remarks-25]

* PostgreSQL only. Not available on MongoDB.
* Pass field names as separate arguments: `groupBy('customerId', 'channel')`.
* `groupBy(...)` returns a `GroupedCollection`. You cannot `await` it. Call `.aggregate(...)` on it, which runs the query and gives you one row per group. Each row holds the grouping fields, typed as they are on your model, plus the keys you returned from the aggregate callback.
* Chain order: `where(...)` comes before `groupBy(...)` and filters the rows that get grouped. After `groupBy(...)` come `having(...)` and `orderBy(...)`, then `limit(...)` and `offset(...)`. `aggregate(...)` comes last.
* `orderBy(...)` on a grouped collection sorts the groups by a field you grouped on. You cannot sort groups by an aggregate value. To do that, write the query with the SQL query builder instead. See [Advanced queries](https://www.prisma.io/docs/orm/fundamentals/advanced-queries).
* `limit(n)` and `offset(n)` take a slice of the groups, and both require an `orderBy(...)` before them. Without one they are a TypeScript error.

#### Options [#options-24]

| Argument    | Type                               | Required | Description             |
| ----------- | ---------------------------------- | -------- | ----------------------- |
| `...fields` | One or more field names (`string`) | Yes      | The fields to group by. |

#### Return type [#return-type-24]

| Return type         | Example                                     | Description                            |
| ------------------- | ------------------------------------------- | -------------------------------------- |
| `GroupedCollection` | `db.orm.public.Order.groupBy('customerId')` | Not a promise. Call `.aggregate(...)`. |

#### Examples [#examples-25]

##### Group and aggregate (PostgreSQL) [#group-and-aggregate-postgresql]

```typescript
const perCustomer = await db.orm.public.Order.groupBy('customerId').aggregate((agg) => ({
  orderCount: agg.count(),
  totalAmount: agg.sum('amount'),
}));
// one row per customer, each { customerId, orderCount, totalAmount }
```

##### Sort and page the groups (PostgreSQL) [#sort-and-page-the-groups-postgresql]

```typescript
const firstTwoChannels = await db.orm.public.Order.groupBy('channel')
  .orderBy((group) => group.channel.desc()) // desc() sorts descending, asc() ascending
  .limit(2)
  .aggregate((agg) => ({ orderCount: agg.count() }));
```

For Prisma ORM 7 users, `groupBy()` chains into `aggregate()` instead of taking a `by` array with aggregate keys:

```diff
- const perCustomer = await prisma.order.groupBy({ by: ['customerId'], _sum: { amount: true } });
+ const perCustomer = await db.orm.public.Order.groupBy('customerId').aggregate((agg) => ({ totalAmount: agg.sum('amount') }));
```

### `having()` [#having]

Filter groups by an aggregate comparison.

#### Remarks [#remarks-26]

* PostgreSQL only. Not available on MongoDB. Call it on the result of `groupBy()`.
* The `having()` callback gives you `count()`, `count(field)`, `sum(field)`, `avg(field)`, `min(field)`, and `max(field)`. Call a comparison on the result: `h.sum('amount').gt(1000)`. The comparisons are `eq`, `neq`, `gt`, `lt`, `gte`, and `lte`.
* For more than one condition, call `.having(...)` again. The conditions are combined with AND.
* You cannot refer to a key you returned from `aggregate()` here. Write the aggregate out again in `having()`.

#### Options [#options-25]

| Argument    | Type                                       | Required | Description                          |
| ----------- | ------------------------------------------ | -------- | ------------------------------------ |
| `predicate` | Callback `(h) => h.sum('amount').gt(1000)` | Yes      | The condition each group must match. |

#### Return type [#return-type-25]

| Return type         | Example                                                 | Description                            |
| ------------------- | ------------------------------------------------------- | -------------------------------------- |
| `GroupedCollection` | `db.orm.public.Order.groupBy('customerId').having(...)` | Not a promise. Call `.aggregate(...)`. |

#### Examples [#examples-26]

##### Filter groups by a sum (PostgreSQL) [#filter-groups-by-a-sum-postgresql]

```typescript
const bigSpenders = await db.orm.public.Order.groupBy('customerId')
  .having((h) => h.sum('amount').gt(1000))
  .aggregate((agg) => ({ totalAmount: agg.sum('amount') }));
```

## Filter conditions and operators [#filter-conditions-and-operators]

A condition says which rows match. You write one inside `where()`, and inside a relation filter such as `u.posts.some(...)`. If you call `where()` more than once, the conditions are ANDed, and one call can take the object form and the next the callback form. PostgreSQL and MongoDB write conditions differently.

* **PostgreSQL** compares a field with a method on that field, such as `u.email.eq(...)`, inside a callback. You name the callback's argument yourself, so the `u` and `p` below are only names. To combine conditions, import `and`, `or`, `not`, and `all` from `@prisma/orm-postgres/orm-client`.
* **MongoDB** has no callback form. `where()` takes either the shorthand object or a filter such as `MongoFieldFilter.eq('name', 'Alice')`. The full list is under [`MongoFieldFilter`](#mongofieldfilter). Import `MongoFieldFilter`, `MongoAndExpr`, and `MongoOrExpr` from `@prisma/orm-mongo/query-ast/execution`. To combine conditions, use `.and()`, `.not()`, and `MongoOrExpr.of([...])`, described under [Combinators](#combinators).

### Scalar comparisons (PostgreSQL) [#scalar-comparisons-postgresql]

Comparison methods on a field.

#### Remarks [#remarks-27]

* PostgreSQL. Which comparison methods a field has depends on its type. Autocomplete shows which ones apply.
* `isNull()` and `isNotNull()` are on every field.
* `eq`, `neq`, `in`, and `notIn` are on a field whose values can be compared for equality.
* `gt`, `lt`, `gte`, and `lte` are on a field whose values can be ordered.
* `like` and `ilike` are on a text field.
* `String` and enum fields have all of them. `Uuid` and `DateTime` have all but `like` and `ilike`. A `Boolean` field has only the equality methods, `isNull()`, and `isNotNull()`. A `Json` field has only `isNull()` and `isNotNull()`.
* `like()` matches a SQL pattern case-sensitively. `ilike()` matches the same pattern case-insensitively.
* `in([])` matches no rows. `notIn([])` matches every row.

#### Options [#options-26]

| Method                                                  | Type               | Description                                      |
| ------------------------------------------------------- | ------------------ | ------------------------------------------------ |
| `eq(value)` / `neq(value)`                              | Exact value        | Equality / inequality.                           |
| `gt(value)` / `lt(value)` / `gte(value)` / `lte(value)` | Ordered value      | Ordered comparisons.                             |
| `like(pattern)` / `ilike(pattern)`                      | SQL `LIKE` pattern | Case-sensitive / case-insensitive pattern match. |
| `in(values)` / `notIn(values)`                          | Array of values    | Membership / exclusion.                          |
| `isNull()` / `isNotNull()`                              | No argument        | NULL checks.                                     |

#### Examples [#examples-27]

##### Equality and inequality (PostgreSQL) [#equality-and-inequality-postgresql]

```typescript
const alice = await db.orm.public.User.where((u) => u.email.eq('alice@example.com')).first();
const notAlice = await db.orm.public.User.where((u) => u.email.neq('alice@example.com')).all();
```

##### Ordered comparisons (PostgreSQL) [#ordered-comparisons-postgresql]

```typescript
const after = await db.orm.public.Post.where((p) =>
  p.createdAt.gt(Temporal.Instant.from('2024-01-02T10:00:00.000Z')),
).all();
// gte() is the same comparison, including the boundary value.
```

##### Pattern matching (PostgreSQL) [#pattern-matching-postgresql]

```typescript
const matches = await db.orm.public.User.where((u) => u.email.like('%@example.com')).all(); // case-sensitive
const caseInsensitive = await db.orm.public.User.where((u) => u.email.ilike('%@EXAMPLE.COM')).all();
```

##### Membership and NULL checks (PostgreSQL) [#membership-and-null-checks-postgresql]

```typescript
const lowOrHigh = await db.orm.public.Post.where((p) => p.priority.in(['low', 'high'])).all();
const notLowOrHigh = await db.orm.public.Post.where((p) => p.priority.notIn(['low', 'high'])).all();
const withoutDescription = await db.orm.public.Task.where((t) => t.description.isNull()).all(); // Task.description is String? in the example schema
```

### Combinators [#combinators]

Combine or negate conditions.

#### Remarks [#remarks-28]

* On PostgreSQL, import `and`, `or`, `not`, and `all` from `@prisma/orm-postgres/orm-client`. You call them as functions, as in `and(a, b)`. They are not methods on a field.
* On PostgreSQL, `and(...)` and `or(...)` take any number of conditions, so `and(a, b, c)` is fine. `not(...)` takes one.
* On PostgreSQL, `all()` takes no arguments and returns a condition that matches every row. This `all()` is not the `.all()` you call at the end of a chain to run the query.
* On MongoDB, call `.and(other)` and `.not()` on a condition you already built, as in `MongoFieldFilter.eq('name', 'Alice').and(...)`. There is no `.or()` method. To OR conditions, build `MongoOrExpr.of([...])`.
* On MongoDB, `.and(other)` takes exactly one condition. For three, chain it: `a.and(b).and(c)`. You can also write all three at once with `MongoAndExpr.of([a, b, c])`. `MongoAndExpr` and `MongoOrExpr` come from the same module as `MongoFieldFilter`.

#### Examples [#examples-28]

##### `and` / `or` / `not` (PostgreSQL) [#and--or--not-postgresql]

```typescript
import { and, or, not } from '@prisma/orm-postgres/orm-client';
const both = await db.orm.public.Post.where((p) =>
  and(p.priority.eq('low'), p.userId.eq('00000000-0000-0000-0000-000000000003')),
).all();
const either = await db.orm.public.Post.where((p) => or(p.priority.eq('urgent'), p.priority.eq('high'))).all();
const negated = await db.orm.public.Post.where((p) => not(p.priority.eq('low'))).all();
```

##### `all` (PostgreSQL) [#all-postgresql]

```typescript
import { all } from '@prisma/orm-postgres/orm-client';
const everyPost = await db.orm.public.Post.where(() => all()).all();
```

##### `and` / `or` / `not` (MongoDB) [#and--or--not-mongodb]

```typescript
import { MongoAndExpr, MongoFieldFilter, MongoOrExpr } from '@prisma/orm-mongo/query-ast/execution';

const both = await db.orm.users
  .where(MongoFieldFilter.eq('role', 'author').and(MongoFieldFilter.eq('name', 'Alice')))
  .all();
const either = await db.orm.users
  .where(MongoOrExpr.of([MongoFieldFilter.eq('name', 'Alice'), MongoFieldFilter.eq('name', 'Bob')]))
  .all();
const negated = await db.orm.users.where(MongoFieldFilter.eq('name', 'Alice').not()).all();
const bothAtOnce = await db.orm.users
  .where(MongoAndExpr.of([MongoFieldFilter.eq('role', 'author'), MongoFieldFilter.eq('name', 'Alice')]))
  .all();
```

##### AND of two ORs (MongoDB) [#and-of-two-ors-mongodb]

```typescript
const authorsNamedAliceOrBob = await db.orm.users
  .where(
    MongoOrExpr.of([MongoFieldFilter.eq('name', 'Alice'), MongoFieldFilter.eq('name', 'Bob')]).and(
      MongoOrExpr.of([MongoFieldFilter.eq('role', 'author'), MongoFieldFilter.eq('role', 'editor')]),
    ),
  )
  .all();
```

### Relation filters (PostgreSQL) [#relation-filters-postgresql]

Filter parents by their related rows.

#### Remarks [#remarks-29]

* PostgreSQL. `some()`, `every()`, and `none()` are methods on a to-many relation, which is a relation that holds many related rows, such as `User.posts`.
* Each of the three takes either a callback or a plain object of equality matches: `u.posts.some((p) => p.priority.eq('urgent'))` and `u.posts.some({ priority: 'urgent' })` mean the same thing.
* `some()` with no argument matches parents with at least one related row.
* A user with no posts matches `every()`.
* `not(u.posts.some(...))` and `u.posts.none(...)` match the same rows. Prefer `none(...)`.
* All three are also methods on a to-one relation, such as `Post.user`, which holds a single related row. There `some()` means the related row matches, and `none()` means it does not. `every()` matches when the related row matches, and also when there is no related row.

#### Examples [#examples-29]

##### `some` / `every` / `none` (PostgreSQL) [#some--every--none-postgresql]

```typescript
const withUrgentPost = await db.orm.public.User.where((u) => u.posts.some((p) => p.priority.eq('urgent'))).all();
const withAnyTag = await db.orm.public.Tag.where((t) => t.posts.some()).all();
const allLowPriority = await db.orm.public.User.where((u) => u.posts.every((p) => p.priority.eq('low'))).all();
const noUrgentPost = await db.orm.public.User.where((u) => u.posts.none((p) => p.priority.eq('urgent'))).all();
```

##### To-one relation predicate (PostgreSQL) [#to-one-relation-predicate-postgresql]

```typescript
const postsByAlice = await db.orm.public.Post.where((p) => p.user.some({ email: 'alice@example.com' })).all();
const postsByAnyoneElse = await db.orm.public.Post.where((p) => p.user.none({ email: 'alice@example.com' })).all();
```

For Prisma ORM 7 users, `some()` and `none()` on a to-one relation replace `is` and `isNot`:

```diff
- const postsByAlice = await prisma.post.findMany({ where: { user: { is: { email: 'alice@example.com' } } } });
+ const postsByAlice = await db.orm.public.Post.where((p) => p.user.some({ email: 'alice@example.com' })).all();
```

### Shorthand object filter [#shorthand-object-filter]

A plain object of equality matches, ANDed together.

#### Remarks [#remarks-30]

* Available for PostgreSQL and MongoDB.
* Multiple keys are combined with implicit AND. A key set to `undefined` is skipped.
* On PostgreSQL, a key set to `null` becomes an IS NULL check. On MongoDB it matches documents where the field is null and documents where the field is missing.
* On PostgreSQL, the shorthand object cannot filter a field whose type cannot be compared for equality, such as `Json`. Using that field as a key compiles, but fails at run time with an error whose `code` is `ORM.FILTER_UNSUPPORTED`. Use the callback form, which has whatever methods that type does support. On a `Json` field those are `isNull()` and `isNotNull()`.

#### Examples [#examples-30]

##### Shorthand object [#shorthand-object]

  

#### PostgreSQL

```typescript
const row = await db.orm.public.Post.where({
  userId: '00000000-0000-0000-0000-000000000001',
  priority: 'high',
}).first();
```

#### MongoDB

```typescript
const alice = await db.orm.users.where({ name: 'Alice', role: 'author' }).first();
```

### `MongoFieldFilter` [#mongofieldfilter]

Helpers that build one MongoDB filter condition. Use them for anything other than equality on a top-level field, which `.where({ name: 'Alice' })` already covers. Chaining two of them, as in `.where(a).where(b)`, joins them with AND. For OR, see [Combinators](#combinators).

#### Remarks [#remarks-31]

* MongoDB. Import from `@prisma/orm-mongo/query-ast/execution`. `MongoExistsExpr` is in the same module.
* The helpers are `of`, `eq`, `neq`, `gt`, `lt`, `gte`, `lte`, `in`, `nin`, `isNull`, and `isNotNull`. Each returns one filter, which you can pass straight to `.where()` or hold in a variable. There is no `ne`; use `neq`.
* `of(field, operator, value)`: pass the MongoDB operator as a string, including the `$`, for example `'$regex'`. Use it for operators the named helpers do not cover, such as `MongoFieldFilter.of('tags', '$size', 3)` on an array field `tags`. A misspelled operator such as `'$regexp'` is not caught until the query runs.
* `isNull(field)` matches documents where the field is `null` and documents where the field is missing. `isNotNull(field)` matches everything else. To test only whether a field is present, use `MongoExistsExpr.exists(field)` or `MongoExistsExpr.notExists(field)`. Those two are all `MongoExistsExpr` offers.
* There is no `regex`, `elemMatch`, `all`, or `size` helper. Write those operators through `of`.

#### Options [#options-27]

| Method                                                                              | Type                                    | Description                                                                              |
| ----------------------------------------------------------------------------------- | --------------------------------------- | ---------------------------------------------------------------------------------------- |
| `of(field, operator, value)`                                                        | Field + MongoDB operator string + value | Any operator, including the `$`, such as `'$regex'`.                                     |
| `eq(field, value)` / `neq(field, value)`                                            | Field + value                           | Equality / inequality.                                                                   |
| `gt(field, value)` / `lt(field, value)` / `gte(field, value)` / `lte(field, value)` | Field + value                           | Greater than, less than, greater or equal, less or equal.                                |
| `in(field, values)` / `nin(field, values)`                                          | Field + array                           | Membership / exclusion.                                                                  |
| `isNull(field)` / `isNotNull(field)`                                                | Field                                   | `isNull` matches a `null` value or a missing field. `isNotNull` matches everything else. |

#### Examples [#examples-31]

##### Comparison helpers (MongoDB) [#comparison-helpers-mongodb]

```typescript
import { MongoFieldFilter } from '@prisma/orm-mongo/query-ast/execution';

const alice = await db.orm.users.where(MongoFieldFilter.eq('name', 'Alice')).first();
const notAlice = await db.orm.users.where(MongoFieldFilter.neq('name', 'Alice')).all();
const strictlyAfter = await db.orm.posts
  .where(MongoFieldFilter.gt('createdAt', new Date('2024-01-01T10:00:00.000Z')))
  .all();
```

##### Membership and null checks (MongoDB) [#membership-and-null-checks-mongodb]

```typescript
const articlesOrTutorials = await db.orm.posts
  .where(MongoFieldFilter.in('kind', ['article', 'tutorial']))
  .all();
const notArticles = await db.orm.posts.where(MongoFieldFilter.nin('kind', ['article'])).all();
const noBio = await db.orm.users.where(MongoFieldFilter.isNull('bio')).all();
const hasBio = await db.orm.users.where(MongoFieldFilter.isNotNull('bio')).all();
```

##### An operator written as a string, and `$exists` (MongoDB) [#an-operator-written-as-a-string-and-exists-mongodb]

```typescript
import { MongoExistsExpr, MongoFieldFilter } from '@prisma/orm-mongo/query-ast/execution';

const matching = await db.orm.posts.where(MongoFieldFilter.of('title', '$regex', '^Hello')).all();
const withBioField = await db.orm.users.where(MongoExistsExpr.exists('bio')).all();
```

### Dot-notation into an embedded object (MongoDB) [#dot-notation-into-an-embedded-object-mongodb]

Filter into a field of an object stored inside the document, rather than in its own collection, using a dot path.

#### Remarks [#remarks-32]

* MongoDB. Use `MongoFieldFilter.eq('address.city', 'San Francisco')` for a dotted path. It takes the path as a plain `string` and needs no cast.
* You can also write `.where({ 'address.city': 'San Francisco' })`. That object form runs correctly, but it does not type-check. `.where()` only knows your model's top-level field names, so you must cast.

#### Examples [#examples-32]

##### Dot-notation path (MongoDB) [#dot-notation-path-mongodb]

```typescript
import { MongoFieldFilter } from '@prisma/orm-mongo/query-ast/execution';

const usersInSf = await db.orm.users.where(MongoFieldFilter.eq('address.city', 'San Francisco')).all();
```

`MongoFieldFilter.eq` is the recommended form, because the object form needs a cast, and the cast drops type checking for every key in that object:

```typescript
const usersInSf = await db.orm.users
  .where({ 'address.city': 'San Francisco' } as unknown as Record<string, unknown>)
  .all();
```

## Field update operations [#field-update-operations]

On MongoDB, `update()`, `updateAll()`, `updateAndCount()`, and `upsert()` take a callback in place of a data object. The callback gets one argument, the update builder, written `u` in these examples, and returns an array of the operations you want. `updateAndCount()` takes the callback in the same place as `update()`, while `upsert()` takes it as its `update` key, beside `create`. PostgreSQL has no callback form: pass an object to [`update()`](#update) instead.

* For a top-level field, read it off the update builder as a property: `u.bio.set('Hello')`.
* For a field inside an embedded object, call the update builder as a function with the dot path: `u('address.city').set('San Francisco')`. The `upsert()` callback cannot use a dot path. Given one, `upsert()` throws an error whose `code` is `ORM.OPERATION_UNSUPPORTED`. Set the whole embedded object on its top-level field instead, as in `u.address.set({ street: '1 Market St', city: 'San Francisco', zip: '94105', country: 'US' })`. Give every field of the embedded object, including the optional ones.

All four methods require `.where()`, and without one they throw an error whose `code` is `ORM.WHERE_MISSING`. `.where({})` does not satisfy that rule, because an empty object adds no condition. To change every document, pass a filter that matches them all, such as `MongoFieldFilter.isNotNull('_id')`. A `_id` filter takes the hex string that a read gives you back, as the examples below show. On `upsert()`, `.where()` picks the document to update, exactly as on `update()`, and the full example is under [`upsert()`](#upsert). `update()` returns the updated document, or `null` when nothing matched, and `updateAll()` returns an [`AsyncIterableResult`](#asynciterableresult) of the changed documents. [`updateAndCount()`](#updateandcount) exists on both databases, and it returns the number of changed documents.

### Available operations (MongoDB) [#available-operations-mongodb]

#### Remarks [#remarks-33]

* MongoDB only. These are the four operations the update builder gives you for single-value fields: `set()`, `unset()`, `inc()`, and `mul()`. The four array operations follow under [Array operations](#array-operations-mongodb-unverified).
  * `set(value)` assigns a field.
  * `unset()` removes a field.
  * `inc(n)` increments a numeric field. TypeScript offers `inc` and `mul` on numeric fields only. Applied to a missing field, `inc` sets the field to `n`.
  * `mul(n)` multiplies a numeric field. Applied to a missing field, it sets the field to `0` whatever `n` is.
* After `variant(...)`, `u.duration.inc(10)` is a type error even though it runs correctly. Put `// @ts-expect-error` on the line directly above the line TypeScript flags. When the type error goes away, TypeScript reports the comment as unused, so delete the comment then.

#### Examples [#examples-33]

These examples use the MongoDB accessors from [Setting up the client](#setting-up-the-client).

##### `set()` (MongoDB) [#set-mongodb]

Return as many operations as you want, and they are applied together, in one write.

```typescript
const updated = await db.orm.users
  .where({ _id: '6650f1c2a1b2c3d4e5f60002' })
  .update((u) => [u.bio.set('Set via field op'), u.name.set('Bob R.')]);
// A field inside the embedded `address` object, by dot path:
const moved = await db.orm.users.where({ _id: '6650f1c2a1b2c3d4e5f60002' }).update((u) => [u('address.city').set('San Francisco')]);
```

##### `inc()` and `mul()` (MongoDB) [#inc-and-mul-mongodb]

`duration` is declared on `Tutorial`, not on the base `Post` model, so each `.update(...)` line that touches it needs its own `// @ts-expect-error` directly above it.

```typescript
const incremented = await db.orm.posts
  .variant('Tutorial')
  .where({ _id: '6650f1c2a1b2c3d4e5f60003' })
  // @ts-expect-error duration is a Tutorial field, not a Post field
  .update((u) => [u.duration.inc(10)]);

const multiplied = await db.orm.posts
  .variant('Tutorial')
  .where({ _id: '6650f1c2a1b2c3d4e5f60003' })
  // @ts-expect-error duration is a Tutorial field, not a Post field
  .update((u) => [u.duration.mul(3)]);
```

##### `unset()` (MongoDB) [#unset-mongodb]

```typescript
const updated = await db.orm.users.where({ _id: '6650f1c2a1b2c3d4e5f60001' }).update((u) => [u.bio.unset()]);
const changed = await db.orm.users.where({ _id: '6650f1c2a1b2c3d4e5f60002' }).updateAndCount((u) => [u.bio.unset()]);
```

### Operations that do not exist (MongoDB) [#operations-that-do-not-exist-mongodb]

> [!WARNING]
> No
> 
> `min`
> 
> ,
> 
> `max`
> 
> ,
> 
> `rename`
> 
> , or
> 
> `currentDate`
> 
>  field operations
> 
> The update builder implements eight operations, and only these eight: `set`, `unset`, `inc`, `mul`, `push`, `pull`, `addToSet`, and `pop`. `min`, `max`, `rename`, and `currentDate` are absent from the API, and TypeScript rejects them: `rename` is not a property of `u.bio`.

### Array operations (MongoDB) [#array-operations-mongodb-unverified]

#### Remarks [#remarks-34]

* MongoDB. `push()`, `pull()`, `addToSet()`, and `pop()` are available on the update builder. `push(value)` appends one element. `addToSet(value)` appends it only if it is not already there. `pop(1)` removes the last element, and `pop(-1)` removes the first.
* `pull(match)` removes every element equal to the argument. If the array holds objects, pass part of an object to remove every element that matches it, as in `u.tags.pull({ kind: 'draft' })`.
* These operations need an array field, declared with `[]` in your contract, as in `tags String[]`. Applied to any other field, the write fails. There is no `error.code` to match, because the error is MongoDB's own.
* The four lines below are separate calls because one update cannot apply two operations to the same field. One update can still change two different fields, such as pushing to `tags` and setting `bio`. The example schema has no array field, so these four lines assume `tags String[]` on `User` and will not run against the schema on this page.

```typescript
await db.orm.users.where({ _id: '6650f1c2a1b2c3d4e5f60001' }).update((u) => [u.tags.push('admin')]);
await db.orm.users.where({ _id: '6650f1c2a1b2c3d4e5f60001' }).update((u) => [u.tags.addToSet('admin')]);
await db.orm.users.where({ _id: '6650f1c2a1b2c3d4e5f60001' }).update((u) => [u.tags.pull('draft')]);
await db.orm.users.where({ _id: '6650f1c2a1b2c3d4e5f60001' }).update((u) => [u.tags.pop(1)]);
```

## Result types [#result-types]

### Model and result types [#model-and-result-types]

`contract.d.ts` exports a type for every model, under `Models`. On PostgreSQL the schema is part of the name: `Models.public_User`. The same file exports a `models` constant, so `typeof models.public.User` is the same type. A model type lists every scalar field and every relation, and no query returns exactly that, so three types derive what a query does return:

* `Scalars<M>` is the model without its relations. It is what `all()` and `first()` return when you do not call `select()` or `include()`.
* `Shape<M, Spec>` is a row with the fields and relations you choose. In `Spec`, `"+"` names the scalar fields and relations to keep, `"-"` names scalar fields to drop, and any other key is a relation with its own nested spec. If `"+"` names only relations, every scalar field stays. A relation named in `"+"` comes with all of its scalar fields and none of its own relations.
* `ResultType<typeof query>` is one row of a query you have already written.

Import `Scalars` and `Shape` from `@prisma/orm-postgres/family-contract/types`, or `@prisma/orm-mongo/family-contract/types` on MongoDB. Import `ResultType` from `@prisma/orm-postgres/components/runtime`. A contract emitted before `8.0.0-rc.10` has no `Models`; run `npx prisma contract emit` again after upgrading.

```typescript
import type { Models } from './contract.d';
import type { Scalars, Shape } from '@prisma/orm-postgres/family-contract/types';
import type { ResultType } from '@prisma/orm-postgres/components/runtime';

type UserRow = Scalars<Models.public_User>;
// every scalar field of User, no posts and no tasks

type UserResponse = Shape<Models.public_User, { '-': 'email'; posts: { '+': 'id' | 'title' } }>;
// every scalar field of User except email, plus posts as an array of { id, title }

const usersWithPosts = db.orm.public.User.include('posts');
type UserWithPosts = ResultType<typeof usersWithPosts>;
// the same type as Shape<Models.public_User, { '+': 'posts' }>
```

Use `Shape` to declare a function's return type once, and TypeScript checks the body at the `return`. A wrong field name, a relation under `"-"`, or a scalar field in `"+"` next to a `"-"` is a compile error on that key. For Prisma ORM 7 users, these replace `Prisma.User` and `Prisma.UserGetPayload<...>`.

### `AsyncIterableResult` [#asynciterableresult]

The methods `all()` / `createAll()` / `updateAll()` / `deleteAll()` return an `AsyncIterableResult`. You can use it two ways:

* `await` the result (or call `.toArray()`) to get every row in one array.
* Loop over it with `for await ... of` to take rows one at a time.

`createAll()`, `updateAll()`, and `deleteAll()` exist on PostgreSQL as well as MongoDB, and [Write methods](#mutation-terminals) documents what each one takes, with examples. If you need the type by name, import it from `@prisma/orm-postgres/components/runtime` on PostgreSQL, or from `@prisma/orm-mongo/components/runtime` on MongoDB.

#### Single consumption and mode switching [#single-consumption-and-mode-switching]

Each result is read once, one way:

* Re-`await`ing (or calling `.toArray()` on) a result you already awaited is **safe**. It returns the same array.
* Looping a result a second time with `for await` throws an error whose `code` is `RUNTIME.ITERATOR_CONSUMED`. So does switching from one way to the other, such as awaiting a result and then looping it. The message contains `already been consumed`. To recognise the error, import `isRuntimeError` from `@prisma/orm-postgres/components/runtime` on PostgreSQL, or from `@prisma/orm-mongo/components/runtime` on MongoDB, and check `error.code`.

PostgreSQL and MongoDB behave the same here, and the example below uses the PostgreSQL accessor. On MongoDB the same methods are on `db.orm.users`, see [Setting up the client](#setting-up-the-client). `all()` returns the result object straight away, and the `await` is what runs the query, so the example holds the result in a variable.

```typescript
import { isRuntimeError } from '@prisma/orm-postgres/components/runtime';
const result = db.orm.public.User.all();
const first = await result;
const again = await result.toArray(); // safe: same array

try {
  for await (const user of result) {
  }
} catch (error) {
  if (isRuntimeError(error) && error.code === 'RUNTIME.ITERATOR_CONSUMED') {
    // the result was already read as an array
  }
}
```

### Aggregate result shapes [#aggregate-result-shapes]

* `aggregate((agg) => ({ ... }))` resolves to a **single object** keyed by your aliases. `agg` is the callback's argument, and it carries the aggregate functions. `db.orm.public.Order.aggregate((agg) => ({ total: agg.count() }))` resolves to one object, such as `{ total: 10 }`.
* [Grouped aggregates](#grouped-aggregates) documents `aggregate()`, `groupBy()` with more than one field, and every function `agg` gives you. The bullets here cover only the shape of what you get back.
* `groupBy(...).aggregate((agg) => ({ ... }))` resolves to an **array**, one object per group. Each object carries the fields you grouped by plus your aliases. `db.orm.public.Order.groupBy('customerId').aggregate((agg) => ({ orderCount: agg.count(), totalAmount: agg.sum('amount') }))` resolves to `[{ customerId, orderCount, totalAmount }, ...]`.
* `count()` is always a `number` (`0` over an empty set). `sum()`, `avg()`, `min()`, and `max()` resolve to `null`, not `0`, over an empty result set.

## 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.
- [`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.
- [`SQL query builder reference`](https://www.prisma.io/docs/orm/reference/sql-query-builder): Reference for the Prisma ORM SQL query builder's select, mutation, and grouped query methods.
- [`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.