[FEATURE]: Cursor-based pagination

[Shhu]

Describe what you want

Support for cursor based pagination in Drizzle ORM like others major ORM

Laravel Eloquent TypeORM Prisma

[Pentiado]

Cursor-based pagination is a simple SQL query where you take X rows greater than the cursor.

SELECT * FROM users
WHERE id > :lastCursor
ORDER BY id
LIMIT :pageSize;

import { gt } from "drizzle-orm";

const lastCursor = 5;
const pageSize = 10;

await db.select()
    .from(users)
    .orderBy(users.id)
    .limit(pageSize)
    .where(gt(users.id, lastCursor));

[Shhu]

its easy when you order by only one unique column but its annoying when you have 1 non-unique column + 1 unique order for example

[miketromba]

I found this pretty complex to do too, so I created a helper function withCursorPagination which has been a huge help in my project. I've published it to npm, feel free to give it a try: https://www.npmjs.com/package/drizzle-pagination

[xantiagoma]

I've been working on a way to do cursor-based pagination with n number of "columns" (https://github.com/xantiagoma/drizzle-cursor), a lot of improvement gap on the typings, etc. but so far in my initial tests it seems to be working. Maybe is util for someone.

For a Database, a table with this shape:

it generates the expected results:

> users@1.0.0 execute
> tsx src/execute.ts

Query: select "last_name", "first_name", "middle_name", "id" from "users" order by "users"."last_name" asc, "users"."first_name" asc, "users"."middle_name" asc, "users"."id" asc limit ? -- params: [10]
[
  {
    lastName: 'Abbott',
    firstName: 'Alana',
    middleName: 'Dillon',
    id: 4918
  },
  {
    lastName: 'Abbott',
    firstName: 'Donovan',
    middleName: 'Kessie',
    id: 3526
  },
  {
    lastName: 'Abbott',
    firstName: 'Nehru',
    middleName: 'Dominic',
    id: 746
  },
  {
    lastName: 'Abbott',
    firstName: 'Ralph',
    middleName: 'Aquila',
    id: 3975
  },
  {
    lastName: 'Abbott',
    firstName: 'Whilemina',
    middleName: 'Noble',
    id: 1618
  },
  {
    lastName: 'Acevedo',
    firstName: 'Ciara',
    middleName: 'Denise',
    id: 951
  },
  {
    lastName: 'Acevedo',
    firstName: 'Ivor',
    middleName: 'Jocelyn',
    id: 2604
  },
  {
    lastName: 'Acevedo',
    firstName: 'Ivor',
    middleName: 'Jocelyn',
    id: 5104
  },
  {
    lastName: 'Acevedo',
    firstName: 'Paula',
    middleName: 'McKenzie',
    id: 2851
  },
  {
    lastName: 'Acevedo',
    firstName: 'Paula',
    middleName: 'McKenzie',
    id: 5351
  }
]
Query: select "last_name", "first_name", "middle_name", "id" from "users" where ("users"."last_name" > ? or ("users"."last_name" = ? and "users"."first_name" > ?) or ("users"."last_name" = ? and "users"."first_name" = ? and "users"."middle_name" > ?) or ("users"."last_name" = ? and "users"."first_name" = ? and "users"."middle_name" = ? and "users"."id" > ?)) order by "users"."last_name" asc, "users"."first_name" asc, "users"."middle_name" asc, "users"."id" asc limit ? -- params: ["Acevedo", "Acevedo", "Paula", "Acevedo", "Paula", "McKenzie", "Acevedo", "Paula", "McKenzie", 5351, 10]
-- 2 --
[
  {
    lastName: 'Acosta',
    firstName: 'Clinton',
    middleName: 'Alfonso',
    id: 4643
  },
  {
    lastName: 'Acosta',
    firstName: 'Colton',
    middleName: 'Rachel',
    id: 1322
  },
  {
    lastName: 'Acosta',
    firstName: 'Lysandra',
    middleName: 'Pearl',
    id: 1039
  },
  {
    lastName: 'Adams',
    firstName: 'Amal',
    middleName: 'Sasha',
    id: 1149
  },
  {
    lastName: 'Adams',
    firstName: 'Dane',
    middleName: 'Zachary',
    id: 4742
  },
  {
    lastName: 'Adams',
    firstName: 'Daryl',
    middleName: 'MacKensie',
    id: 147
  },
  {
    lastName: 'Adams',
    firstName: 'Orlando',
    middleName: 'Mona',
    id: 4219
  },
  {
    lastName: 'Adkins',
    firstName: 'Elizabeth',
    middleName: 'Dillon',
    id: 1449
  },
  {
    lastName: 'Adkins',
    firstName: 'Hunter',
    middleName: 'Reese',
    id: 382
  },
  {
    lastName: 'Adkins',
    firstName: 'Keaton',
    middleName: 'Kylee',
    id: 2460
  }
]
Query: select "last_name", "first_name", "middle_name", "id" from "users" where ("users"."last_name" > ? or ("users"."last_name" = ? and "users"."first_name" > ?) or ("users"."last_name" = ? and "users"."first_name" = ? and "users"."middle_name" > ?) or ("users"."last_name" = ? and "users"."first_name" = ? and "users"."middle_name" = ? and "users"."id" > ?)) order by "users"."last_name" asc, "users"."first_name" asc, "users"."middle_name" asc, "users"."id" asc limit ? -- params: ["Adkins", "Adkins", "Keaton", "Adkins", "Keaton", "Kylee", "Adkins", "Keaton", "Kylee", 2460, 10]
-- 3 --
[
  {
    lastName: 'Adkins',
    firstName: 'Kenyon',
    middleName: 'Leo',
    id: 1538
  },
  {
    lastName: 'Adkins',
    firstName: 'Michelle',
    middleName: 'Inga',
    id: 4706
  },
  {
    lastName: 'Adkins',
    firstName: 'Ria',
    middleName: 'Quemby',
    id: 933
  },
  {
    lastName: 'Aguilar',
    firstName: 'Blossom',
    middleName: 'Teagan',
    id: 1990
  },
  {
    lastName: 'Aguilar',
    firstName: 'Elliott',
    middleName: 'Zia',
    id: 1484
  },
  {
    lastName: 'Aguilar',
    firstName: 'Walter',
    middleName: 'Kaye',
    id: 1795
  },
  {
    lastName: 'Aguilar',
    firstName: 'Winter',
    middleName: 'Hannah',
    id: 1890
  },
  {
    lastName: 'Aguirre',
    firstName: 'Kevin',
    middleName: 'Yuri',
    id: 2327
  },
  {
    lastName: 'Aguirre',
    firstName: 'Walter',
    middleName: 'Oscar',
    id: 2441
  },
  {
    lastName: 'Aguirre',
    firstName: 'Warren',
    middleName: 'Bree',
    id: 3501
  }
]

And follows the same order as when browsing in the GUI:

[Victor-Timi]

Please I haven't gotten the solution on how to solve the Cursor issue on Drizzle ORM. I was able to use a custom limit by using the Slice method on Javascript, however for the cursor scroll I still dont know how to add a cursor here:

      const selectedMessage = limitUserMessage.map((_message) => ({
        isUserMessage: _message.isUserMessage!,
        createdAt: _message.createdAt!,
        text: _message.text!,
        id: _message.id === cursor ? _message.id : undefined,
      }));

If it were to be Prisma I'd have done it this way:

      const selectedMessages = await db.message.findMany({
        take: limit + 1,
        where: {
          fileId,
        },
        orderBy: {
          createdAt: 'desc',
        },
        cursor: cursor ? { id: cursor } : undefined,
        select: {
          id: true,
          isUserMessage: true,
          createdAt: true,
          text: true,
        },
      })

However, cursor and Pagination feel's strange on Drizzle ORM, please help me with where I might probably be having errors, thank you, here is full code below:

getFileMessages: privateProcedure
    .input(
      z.object({
        limit: z.number().min(1).max(100).nullish(),
        cursor: z.string().nullish(),
        fileId: z.string(),
      })
    )
    .query(async ({ ctx, input }) => {
      const { userId } = ctx;
      const { fileId, cursor } = input;
      const limit = input.limit ?? INFINITE_QUERY_LIMIT;

      const userFile = await db
        .select()
        .from(_file)
        .where(eq(_file.userId, userId));

      const selectedFile = userFile.find((_file) => _file.id === fileId);

      if (!selectedFile) throw new TRPCError({ code: "NOT_FOUND" });

      const userMessage = await db
        .select()
        .from(_message)
        .where(eq(_message.fileId, fileId))
        .orderBy(desc(_message.createdAt));

      console.log("USER_MESSAGE", userMessage);

      function limitMessage(messages: DBMessage[]) {
        const messagesToLimit = limit + 1;
        const lastIndex = Math.max(messagesToLimit - messages.length, 0);
        return messages.slice(lastIndex);
      }

      const limitUserMessage = limitMessage(userMessage);

      const selectedMessage = limitUserMessage.map((_message) => ({
        isUserMessage: _message.isUserMessage!,
        createdAt: _message.createdAt!,
        text: _message.text!,
        id: _message.id === cursor ? _message.id : undefined,
      }));

      console.log("SELECTED_MESSAGES", selectedMessage);

[cryptrr]

Cursor-based pagination is a simple SQL query where you take X rows greater than the cursor.

But @Pentiado, will this strategy work if the id is not an autoincremnting integer?

Will this work for uuids and cuids? Both are K-Sortable, but idk about the specifics.

[nathanchapman]

One thing not mentioned here yet: for cursor-based pagination, you typically want to know whether or not there's an additional page after the endCursor, e.g. in a field called hasNextPage. This means you need to add one to the limit (pageSize + 1) and set hasNextPage to results > pageSize but slice off the last result (only if hasNextPage is true) before returning the page

[gnllucena]

@cryptrr were you able to make pagination work for a non-auto incrementing integer?

[cryptrr]

@gnllucena No. It's better to use the drizzle-pagination plugin.