<TopBlock>

Prisma Client supports both offset pagination and cursor-based pagination.

</TopBlock>

## Offset pagination

Offset pagination uses `skip` and `take` to skip a certain number of results and select a limited range. The following query skips the first 3 `Post` records and returns records 4 - 7:

```ts line-number
const results = await prisma.post.findMany({
  skip: 3,
  take: 4,
})
```

![](/img/orm/offset-skip-take.png)

To implement pages of results, you would just `skip` the number of pages multiplied by the number of results you show per page.

### ✔ Pros of offset pagination

- You can jump to any page immediately. For example, you can `skip` 200 records and `take` 10, which simulates jumping straight to page 21 of the result set (the underlying SQL uses `OFFSET`). This is not possible with cursor-based pagination.
- You can paginate the same result set in any sort order. For example, you can jump to page 21 of a list of `User` records sorted by first name. This is not possible with cursor-based pagination, which requires sorting by a unique, sequential column.

### ✘ Cons of offset pagination

- Offset pagination **does not scale** at a database level. For example, if you skip 200,000 records and take the first 10, the database still has to traverse the first 200,000 records before returning the 10 that you asked for - this negatively affects performance.

### Use cases for offset pagination

- Shallow pagination of a small result set. For example, a blog interface that allows you to filter `Post` records by author and paginate the results.

### Example: Filtering and offset pagination

The following query returns all records where the `email` field contains `prisma.io`. The query skips the first 40 records and returns records 41 - 50.

```ts line-number
const results = await prisma.post.findMany({
  skip: 40,
  take: 10,
  where: {
    email: {
      contains: 'prisma.io',
    },
  },
})
```

### Example: Sorting and offset pagination

The following query returns all records where the `email` field contains `Prisma`, and sorts the result by the `title` field. The query skips the first 200 records and returns records 201 - 220.

```ts line-number
const results = await prisma.post.findMany({
  skip: 200,
  take: 20,
  where: {
    email: {
      contains: 'Prisma',
    },
  },
  orderBy: {
    title: 'desc',
  },
})
```

## Cursor-based pagination

Cursor-based pagination uses `cursor` and `take` to return a limited set of results before or after a given **cursor**. A cursor bookmarks your location in a result set and must be a unique, sequential column - such as an ID or a timestamp.

The following example returns the first 4 `Post` records that contain the word `"Prisma"` and saves the ID of the last record as `myCursor`:

> **Note**: Since this is the first query, there is no cursor to pass in.

```ts showLineNumbers
const firstQueryResults = await prisma.post.findMany({
  take: 4,
  where: {
    title: {
      contains: 'Prisma' /* Optional filter */,
    },
  },
  orderBy: {
    id: 'asc',
  },
})

// Bookmark your location in the result set - in this
// case, the ID of the last post in the list of 4.

//highlight-start
const lastPostInResults = firstQueryResults[3] // Remember: zero-based index! :)
const myCursor = lastPostInResults.id // Example: 29
//highlight-end
```

The following diagram shows the IDs of the first 4 results - or page 1. The cursor for the next query is **29**:

![](/img/orm/cursor-1.png)

The second query returns the first 4 `Post` records that contain the word `"Prisma"` **after the supplied cursor** (in other words - IDs that are larger than **29**):

```ts line-number
const secondQueryResults = await prisma.post.findMany({
  take: 4,
  skip: 1, // Skip the cursor
  //highlight-start
  cursor: {
    id: myCursor,
  },
  //highlight-end
  where: {
    title: {
      contains: 'Prisma' /* Optional filter */,
    },
  },
  orderBy: {
    id: 'asc',
  },
})

const lastPostInResults = secondQueryResults[3] // Remember: zero-based index! :)
const myCursor = lastPostInResults.id // Example: 52
```

The following diagram shows the first 4 `Post` records **after** the record with ID **29**. In this example, the new cursor is **52**:

![](/img/orm/cursor-2.png)

### FAQ

#### Do I always have to skip: 1?

If you do not `skip: 1`, your result set will include your previous cursor. The first query returns four results and the cursor is **29**:

![](/img/orm/cursor-1.png)

Without `skip: 1`, the second query returns 4 results after (and _including_) the cursor:

![](/img/orm/cursor-3.png)

If you `skip: 1`, the cursor is not included:

![](/img/orm/cursor-2.png)

You can choose to `skip: 1` or not depending on the pagination behavior that you want.

#### Can I guess the value of the cursor?

If you guess the value of the next cursor, you will page to an unknown location in your result set. Although IDs are sequential, you cannot predict the rate of increment (`2`, `20`, `32` is more likely than `1`, `2`, `3`, particularly in a filtered result set).

#### Does cursor-based pagination use the concept of a cursor in the underlying database?

No, cursor pagination does not use cursors in the underlying database ([e.g. PostgreSQL](https://www.postgresql.org/docs/9.2/plpgsql-cursors.html)).

#### What happens if the cursor value does not exist?

Using a nonexistent cursor returns `null`. Prisma Client does not try to locate adjacent values.

### ✔ Pros of cursor-based pagination

- Cursor-based pagination **scales**. The underlying SQL does not use `OFFSET`, but instead queries all `Post` records with an ID greater than the value of `cursor`.

### ✘ Cons of cursor-based pagination

- You must sort by your cursor, which has to be a unique, sequential column.
- You cannot jump to a specific page using only a cursor. For example, you cannot accurately predict which cursor represents the start of page 400 (page size 20) without first requesting pages 1 - 399.

### Use cases for cursor-based pagination

- Infinite scroll - for example, sort blog posts by date/time descending and request 10 blog posts at a time.
- Paging through an entire result set in batches - for example, as part of a long-running data export.

### Example: Filtering and cursor-based pagination

```ts line-number
const secondQuery = await prisma.post.findMany({
  take: 4,
  skip: 1,
  cursor: {
    id: myCursor,
  },
  //highlight-start
  where: {
    title: {
      contains: 'Prisma' /* Optional filter */,
    },
  },
  //highlight-end
  orderBy: {
    id: 'asc',
  },
})
```

### Sorting and cursor-based pagination

Cursor-based pagination requires you to sort by a sequential, unique column such as an ID or a timestamp. This value - known as a cursor - bookmarks your place in the result set and allows you to request the next set.

### Example: Paging backwards with cursor-based pagination

To page backwards, set `take` to a negative value. The following query returns 4 `Post` records with an `id` of less than 200, excluding the cursor:

```ts line-number
const myOldCursor = 200

const firstQueryResults = await prisma.post.findMany({
  take: -4,
  skip: 1,
  cursor: {
    id: myOldCursor,
  },
  where: {
    title: {
      contains: 'Prisma' /* Optional filter */,
    },
  },
  orderBy: {
    id: 'asc',
  },
})
```

---

# Aggregation, grouping, and summarizing