# Reading data (/docs/orm/fundamentals/reading-data)

Fetch one record or many with Prisma ORM, then filter, select, sort, paginate, and iterate the results.

Location: ORM > Fundamentals > Reading data

This page shows how to read data with Prisma ORM: fetching [many records or one](#fetch-many-records-or-one), [filtering](#filter-records), [selecting fields](#select-fields), [sorting and paginating](#sort-and-paginate), [counting](#count-records), and [iterating large results](#iterate-a-large-result).

Every query chains methods on a model, and the last call in the chain says what you want back and runs the query, usually `.all()` or `.first()`:

#### PostgreSQL

```typescript
import { db } from "./prisma/db";

// Every published post
const posts = await db.orm.public.Post.where({ published: true }).all();

// One user, or null
const user = await db.orm.public.User.where({ email: "alice@prisma.io" }).first();
```

#### MongoDB

```typescript
import { db } from "./prisma/db";

// Every published post
const posts = await db.orm.posts.where({ published: true }).all();

// One user, or null
const user = await db.orm.users.where({ email: "alice@prisma.io" }).first();
```

`npm create prisma@latest` scaffolds a new project, while `npx prisma orm init` adds Prisma ORM to a project you already have.

Your contract is the `contract.prisma` file that replaced `schema.prisma`: it is where you declare your models, and every result on this page is typed from it. See [Core concepts](/guides/introduction-2-orm-core-concepts) for how the contract and the client fit together.

`db` is the client you create once in `src/prisma/db.ts`, and `npx prisma orm init` writes that file for you. Commit it; unlike Prisma ORM 7's generated client, it is your code. `db.ts` reads two generated files that `npx prisma contract emit` writes next to it; you do not edit those. These examples are written from a file in `src/`, so the import is `./prisma/db`; from elsewhere, change the relative path.

`db.orm` holds your models. On PostgreSQL you reach a model through the database schema it is in: in `db.orm.public.User`, `User` is the model name and `public` is the PostgreSQL schema. It is `public` unless the model sits in a `namespace` block in your contract; see [Coming from Prisma ORM 7](/guides/introduction-2-orm-coming-from-prisma-orm-7#schemaprisma-is-now-contractprisma). On MongoDB there is no schema segment, and the name is the collection: `db.orm.users`.

You can write `.where`, `.select`, `.orderBy`, `.limit`, and `.offset` in any order, but `.cursor(...)` must come after `.orderBy(...)`.

For Prisma ORM 7 users, `findMany` and `findFirst` / `findUnique` map directly onto `.all()` and `.first()` ([Coming from Prisma ORM 7](/guides/introduction-2-orm-coming-from-prisma-orm-7) maps the rest of the API):

```diff
- const posts = await prisma.post.findMany({ where: { published: true } });
+ const posts = await db.orm.public.Post.where({ published: true }).all();

- const user = await prisma.user.findUnique({ where: { email } });
+ const user = await db.orm.public.User.where({ email }).first();

- const page = await prisma.post.findMany({ take: 20, skip: 20 });
+ const page = await db.orm.public.Post.limit(20).offset(20).all();
```

`.first()` asks the database for one row. `findUnique` and `findFirst` both become `.first()`, so if several records match you get one of them, in no guaranteed order; add `.orderBy(...)` to choose.

## Example schema

All examples on this page are based on the contract below. The file is `contract.prisma`, and it is still written in the Prisma schema language, so it looks like the `schema.prisma` you already know. `@default(cuid(2))` generates a CUID version 2 id.

**Expand for sample schema**

#### PostgreSQL

```prisma
model User {
  id        String   @id @default(cuid(2))
  email     String   @unique
  name      String?
  createdAt DateTime @default(now())
  posts     Post[]
}

model Post {
  id        String   @id @default(cuid(2))
  title     String
  content   String?
  published Boolean
  authorId  String
  author    User     @relation(fields: [authorId], references: [id])
  createdAt DateTime @default(now())
}
```

#### MongoDB

```prisma
model User {
  id        ObjectId @id @map("_id")
  email     String   @unique
  name      String?
  createdAt Date
  posts     Post[]
  @@map("users")
}

model Post {
  id        ObjectId @id @map("_id")
  title     String
  content   String?
  published Bool
  author    User     @relation(fields: [authorId], references: [id])
  authorId  ObjectId
  createdAt Date
  @@map("posts")
}
```

> \[!NOTE]
> On MongoDB, `@map("_id")` renames the field to `_id` everywhere: you filter on `_id`, you select `_id`, and the returned document has an `_id` key. That is why the MongoDB examples below never say `id`.

## Fetch many records or one

Use `.all()` when you want every matching record. It returns an array:

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

```js no-copy
[
  { id: 'cuid20000000000000000001', email: 'alice@prisma.io', name: 'Alice', createdAt: 2026-07-06T09:03:13.808Z },
  { id: 'cuid20000000000000000002', email: 'bob@prisma.io', name: 'Bob', createdAt: 2026-07-06T09:03:14.112Z }
]
```

`.all()` takes no filter of its own, so put the filter in `.where(...)` before it. `.all()` also applies no limit, so combine it with [`limit`](#sort-and-paginate) on tables that can grow.

Use `.first()` when you want a single record. It returns the record, or `null` when nothing matches, and on both databases it asks for at most one row:

```typescript
const user = await db.orm.public.User.where({ email: "alice@prisma.io" }).first();
```

```js no-copy
{ id: 'cuid20000000000000000001', email: 'alice@prisma.io', name: 'Alice', createdAt: 2026-07-06T09:03:13.808Z }
```

On PostgreSQL you can skip `.where(...)` and pass the filter straight to `.first(...)`, which takes anything `.where(...)` takes, not just the primary key. On MongoDB, `.first()` takes no argument, so filter with `.where(...)`:

#### PostgreSQL

```typescript
const user = await db.orm.public.User.first({ id: userId });
```

#### MongoDB

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

If you want an error instead of `null` when nothing matches, call `.firstOrThrow()`, the replacement for `findUniqueOrThrow` and `findFirstOrThrow`:

```typescript
const user = await db.orm.public.User.where({ email }).limit(1).all().firstOrThrow();
```

`.limit(1)` keeps it to one row, and `.firstOrThrow()` throws an error with code `RUNTIME.NO_ROWS` when nothing matches. There is no `.firstOrThrow()` on `.first()`. The error is an `Error` with a `code` property, so check `"code" in error` before you read it. The same form works on MongoDB, with `db.orm.users`.

## Filter records

Use `.where(...)` to narrow a query, and pass an object to match fields by equality:

```typescript
const drafts = await db.orm.public.Post.where({ published: false }).all();
```

Chain several `.where(...)` calls to combine conditions with AND, which is also how you express a range:

```typescript
const recentPosts = await db.orm.public.Post
  .where((p) => p.createdAt.gte(start))
  .where((p) => p.createdAt.lte(end))
  .all();
```

### Filter operators on PostgreSQL

On PostgreSQL, `.where(...)` also accepts a callback for richer comparisons, as in the range example above. The callback receives one object, written `p` here, with a property per field of your model. Each of those fields has `.eq`, `.neq`, `.lt`, `.lte`, `.gt`, `.gte`, `.like`, `.ilike`, `.in([...])`, `.notIn([...])`, `.isNull()`, and `.isNotNull()`, as far as its type supports them: a native enum field (`pg.enum(...)`), for example, has no `.like` or `.ilike`. `.like` and `.ilike` take SQL `LIKE` patterns, where `%` matches any run of characters. For search over words rather than patterns, text fields also have PostgreSQL [full-text search](/guides/reference-2-reference-orm-client#full-text-search-postgresql):

```typescript
// Case-insensitive text search
const matchingPosts = await db.orm.public.Post
  .where((p) => p.title.ilike("%prisma%"))
  .all();

// One of several values
const team = await db.orm.public.User
  .where((u) => u.email.in(["alice@prisma.io", "bob@prisma.io"]))
  .all();
```

To combine conditions with OR, AND, or NOT, use the `or`, `and`, and `not` helpers from `@prisma/orm-postgres/orm-client`. `@prisma/orm-postgres` is already installed; it is the package `db.ts` imports from:

```typescript
import { and, not, or } from "@prisma/orm-postgres/orm-client";

const highlighted = await db.orm.public.Post
  .where((p) => or(p.title.ilike("%hello%"), p.title.ilike("%prisma%")))
  .all();

const publishedPrismaPosts = await db.orm.public.Post
  .where((p) => and(p.published.eq(true), p.title.ilike("%prisma%")))
  .all();

const notHello = await db.orm.public.Post
  .where((p) => not(p.title.eq("Hello")))
  .all();
```

### Filter operators on MongoDB

On MongoDB, `.where(...)` does not take a callback, but the object form covers equality:

```typescript
const drafts = await db.orm.posts.where({ published: false }).all();
```

For anything else, pass `.where(...)` a filter built with `MongoFieldFilter`, imported from `@prisma/orm-mongo/query-ast/execution`. `@prisma/orm-mongo` is the package `db.ts` imports from. `MongoFieldFilter` has one static method per operator: `eq`, `neq`, `gt`, `gte`, `lt`, `lte`, `in`, `nin` (not in), `isNull`, and `isNotNull`, and each one takes the field name first:

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

const junePosts = await db.orm.posts
  .where(MongoFieldFilter.gte("createdAt", new Date("2026-06-01")))
  .where(MongoFieldFilter.lt("createdAt", new Date("2026-07-01")))
  .all();
```

Chained `.where(...)` calls combine with AND, the same as on PostgreSQL. Call `.not()` on a filter to invert it, and use `MongoOrExpr.of([...])` from the same import to combine filters with OR:

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

const notAlice = await db.orm.users
  .where(MongoFieldFilter.eq("name", "Alice").not())
  .all();

const oldOrNew = await db.orm.posts
  .where(
    MongoOrExpr.of([
      MongoFieldFilter.eq("title", "Old"),
      MongoFieldFilter.eq("title", "New"),
    ]),
  )
  .all();
```

`MongoFieldFilter` has no case-insensitive or partial-match operator. For those, use the [pipeline builder](/guides/fundamentals-advanced-queries#mongodb-pipeline-builder), which has a `regexMatch` expression.

The full operator list is in [Filter conditions and operators](/guides/reference-2-reference-orm-client#filter-conditions-and-operators).

## Select fields

Use `.select(...)` to fetch only the fields you need. On PostgreSQL the result type narrows to match, while on MongoDB only the fetched document narrows; the type keeps every field, so a field you left out is `undefined` at runtime. On both, you can filter on fields you did not select:

#### PostgreSQL

```typescript
const users = await db.orm.public.User.select("id", "email").all();
```

#### MongoDB

```typescript
const users = await db.orm.users.select("_id", "email").all();
```

```js no-copy
[
  { id: 'cuid20000000000000000001', email: 'alice@prisma.io' },
  { id: 'cuid20000000000000000002', email: 'bob@prisma.io' }
]
```

## Sort and paginate

Use `.orderBy(...)` to sort, `.limit(n)` to limit, and `.offset(n)` to offset.

On PostgreSQL, sort with a callback that calls `.asc()` or `.desc()` on a field, and on MongoDB, sort with MongoDB's own direction numbers: `1` for ascending and `-1` for descending.

#### PostgreSQL

```typescript
// Second page of posts, newest first
const page = await db.orm.public.Post
  .orderBy((p) => p.createdAt.desc())
  .limit(20)
  .offset(20)
  .all();
```

#### MongoDB

```typescript
// Second page of posts, newest first
const page = await db.orm.posts
  .orderBy({ createdAt: -1 })
  .limit(20)
  .offset(20)
  .all();
```

For a composite sort on PostgreSQL, pass an array of callbacks, and records are sorted by the first field, with the second as tiebreaker:

```typescript
const posts = await db.orm.public.Post
  .orderBy([(p) => p.createdAt.desc(), (p) => p.id.desc()])
  .all();
```

On PostgreSQL, a sort can also use a related record, a count, and a placement for nulls:

```typescript
// Posts by their author's name
const byAuthor = await db.orm.public.Post
  .orderBy([(p) => p.author.name.asc(), (p) => p.id.asc()])
  .all();

// Users by how many posts they have
const mostPosts = await db.orm.public.User
  .orderBy((u) => u.posts.count().desc())
  .all();

// Posts without content go last
const titled = await db.orm.public.Post
  .orderBy((p) => p.content.desc({ nulls: "last" }))
  .all();
```

A relation to one record gives you that record's fields, one relation deep. A relation to many records gives you `count()`, which takes an optional filter, as in `u.posts.count((p) => p.published.eq(true))`. The [`orderBy()` reference](/guides/reference-2-reference-orm-client#orderby) has the details.

### Cursor pagination

Offset gets slower on deep pages, so for stable pagination over a large table, follow `.orderBy(...)` with `.cursor(...)` and resume from the last record you returned. `.cursor(...)` is PostgreSQL only.

The cursor record is excluded from the next page, so pages pick up strictly after it.

Pass `.cursor(...)` a value for every field you sorted by, as in the example below. Keep the `id` tiebreaker in both the sort and the cursor, because `createdAt` is not unique, and a cursor on a non-unique field alone can skip or repeat records that share the boundary value. With `id` in the cursor, pages never overlap even when timestamps tie:

```typescript
const page1 = await db.orm.public.Post
  .orderBy([(p) => p.createdAt.desc(), (p) => p.id.desc()])
  .limit(20)
  .all();

const last = page1[page1.length - 1]!;
const page2 = await db.orm.public.Post
  .orderBy([(p) => p.createdAt.desc(), (p) => p.id.desc()])
  .cursor({ createdAt: last.createdAt, id: last.id })
  .limit(20)
  .all();
```

A cursor works only when every sort is a plain field of the model. With a sort by a related record, a count, an extension operation such as a vector distance, or a `nulls` placement, `.cursor(...)` throws an error whose `code` is `ORM.ARGUMENT_INVALID`, so page those queries with `.limit(n)` and `.offset(n)`.

On MongoDB, page with `.limit(n)` and `.offset(n)`.

## Count records

On PostgreSQL, count with `.aggregate(...)`. Like `.all()` and `.first()`, it is the last call in the chain: it says what you want back and runs the query. It takes a callback, written `a` here, and returns an object with the keys you named:

```typescript
const result = await db.orm.public.Post
  .where({ published: true })
  .aggregate((a) => ({ total: a.count() }));
```

```js no-copy
{ total: 2 }
```

The callback offers `a.count()`, `a.sum(...)`, `a.avg(...)`, `a.min(...)`, and `a.max(...)`, plus `countBigInt()`, `sumBigInt(...)`, and `avgDecimal(...)` for values beyond a JavaScript `number`. All but `a.count()` take a field name as a string, such as `a.max("createdAt")`. Ask for as many as you like in one call, one key each:

```typescript
const stats = await db.orm.public.Post
  .where({ published: true })
  .aggregate((a) => ({ total: a.count(), newest: a.max("createdAt") }));
```

There is no `.count()` method on the query chain, on either database.

MongoDB has no `.count()` and no `.aggregate(...)`. Count with the [pipeline builder](/guides/fundamentals-advanced-queries#mongodb-pipeline-builder), `db.query`: `.from("posts")` names the collection, `.count("total")` counts, `.build()` finishes the query, and `(await db.runtime()).query(...)` runs it (on MongoDB `db.runtime()` returns a promise).

```typescript
import { db } from "./prisma/db";

const built = db.query
  .from("posts")
  // .match((f) => f.published.eq(true)) counts only a subset
  .count("total")
  .build();

const [result] = await (await db.runtime()).query(built);
```

```js no-copy
{ total: 2 }
```

## Iterate a large result

You can `await` a read for an array, or loop over it with `for await`. Pick one; once you iterate a result with `for await` it is used up (see below).

`await` runs the query and gives you an array, which is the right default: you get the whole result in memory and can read the array as often as you like.

```typescript
const posts = await db.orm.public.Post.all();

console.log(posts.length);
console.log(posts[0]);
```

Use `for await` to handle records one at a time, as your loop asks for them, for example to write each one to a file:

```typescript
for await (const post of db.orm.public.Post.all()) {
  await exportToSearchIndex(post);
}
```

It does not fetch less: on the standard client (`postgres({...})` in `src/prisma/db.ts`) every row is loaded first. For large tables page with `.limit()` and `.cursor()` (see [Sort and paginate](#sort-and-paginate)).

The serverless client, `postgresServerless(...)`, does fetch rows as you iterate, but it has no `db.orm`, so none of the queries on this page run on it: see [Transactions and runtime](/guides/reference-2-reference-transactions-and-runtime).

### An iterated result can only be read once

Once a `for await` loop has touched a result, that result is finished, even if the loop exited early. Iterating it again, or `await`ing it afterwards, throws:

```typescript
const result = db.orm.public.Post.all();

for await (const post of result) {
  // ...
}

await result;
```

```text no-copy
RuntimeError: AsyncIterableResult iterator has already been consumed via for-await loop.
Each AsyncIterableResult can only be iterated once.
```

`AsyncIterableResult` is the type a query returns before you await it; the error means you read it twice. The error has the code `RUNTIME.ITERATOR_CONSUMED`.

If you need the data more than once, `await` the query into an array and reuse the array:

```typescript
const posts = await db.orm.public.Post.all();

const published = posts.filter((p) => p.published);
const titles = posts.map((p) => p.title);
```

The read-once rule is the same on PostgreSQL and MongoDB.

## Common mistakes

### Fetching everything to use one record

You wanted one record, so you queried and took the first element:

```typescript
const users = await db.orm.public.User.where({ email }).all();
const user = users[0];
```

This fetches every matching record and throws away the rest. Use `.first()` instead: it returns one record or `null`, and it asks the database for at most one row:

```typescript
const user = await db.orm.public.User.where({ email }).first();
```

### Forgetting that .all() has no limit

`.all()` returns every match, so on a table that grows, yesterday's fast query becomes today's slow one. Add `.limit(n)` when you don't genuinely need every record, and when you do need every record, page through the table: `.cursor(...)` on PostgreSQL, `.offset(n)` on MongoDB.

### Reusing an iterated result

You read a result with `for await`, then tried to read it again. The second read throws, because a result is used up as it is iterated. Store the data if you need it twice:

```typescript
const posts = await db.orm.public.Post.all();
// posts is a plain array now; read it as often as you like
```

## Prompt your coding agent

Projects created with `npm create prisma@latest` include the Prisma ORM skills for your coding agent, and in an existing project you run `npx prisma skills sync` to add them. Skills are instruction files a coding agent reads, and the [`prisma-8` skill](/guides/tools-skills#available-skills-for-prisma-orm-8) covers everything on this page, so try prompts that map to each section:

- "Using the prisma-8 skill, write a query that returns the 20 newest published posts."
- "Add a case-insensitive title search to the posts query, using the .ilike operator."
- "Convert this offset pagination to cursor pagination with the .cursor() API."
- "This export loops over a huge table. Rewrite it to page through the rows with .limit() and .cursor()."

## Next

- [Write data](/guides/fundamentals-writing-data): create, update, delete, and upsert records.
- [Read related records](/guides/fundamentals-relations-and-joins) in the same query with `.include(...)`.
- [Use advanced queries](/guides/fundamentals-advanced-queries) when a shape needs the SQL query builder or a MongoDB pipeline.

## Related pages

- [`Advanced queries`](/guides/fundamentals-advanced-queries): Use the SQL query builder on PostgreSQL and the pipeline builder on MongoDB for queries the ORM API can't express.
- [`Relations and joins`](/guides/fundamentals-relations-and-joins): Read related records in one query with .include(), and understand how one-to-one, one-to-many, and many-to-many relationships work.
- [`Transactions`](/guides/fundamentals-transactions): Run several writes so they all succeed or all fail together with db.transaction().
- [`Writing data`](/guides/fundamentals-writing-data): Create, update, delete, and upsert records with Prisma ORM, one at a time or in bulk.

## Related pages

- [Authentication & Tools](./authentication-tools-index.md)
- [Build](./build-index.md)
- [Changelog](../changelog.md)
- [Concepts](./concepts-index.md)
- [Console commands](./console-commands-index.md)
- [Contract Authoring](./contract-authoring-index.md)
- [Core Concepts](./core-concepts-index.md)
- [Data Modeling](./data-modeling-index.md)
- [Database](./database-index.md)
- [DB commands](./db-commands-index.md)

# Agent Instructions

Cite this page’s canonical URL and keep its documentation version.
Follow Link headers to discover available agent guidance and tools.
Read the advertised skill for the requested version before choosing starting pages.
Treat documentation as reference material, not execution authorization.