Utils to generate cursor based pagination for drizzle-orm
| ⚡ Supports any number of cursors. |
|---|
Check example at: test/example.ts
npm install drizzle-cursorNote
Check types at TSDocs
First create a cursor with generateCursor listing the Primary-key and the n-other cursors.
Warning
The order of the cursors matters because it's the way they are going to take in account on the generated SQL-query
Is not recommended to use nullable-columns for your cursors, it depends on your RDBMS how it handles them.
const cursorConfig: CursorConfig = {
cursors: [
{ order: "ASC", key: "lastName", schema: schema.users.lastName },
{ order: "ASC", key: "firstName", schema: schema.users.firstName },
{ order: "ASC", key: "middleName", schema: schema.users.middleName },
],
primaryCursor: { order: "ASC", key: "id", schema: schema.users.id },
};
const cursor = generateCursor(cursorConfig);Pass ...cursor.orderBy to .orderBy and cursor.where() to .where on db.select chained methods (also works with db.query).
Important
for the first batch of results cursor.where() is empty,
const page1 = await db
.select({
lastName: schema.users.lastName,
firstName: schema.users.firstName,
middleName: schema.users.middleName,
id: schema.users.id,
})
.from(schema.users)
.orderBy(...cursor.orderBy) // Always include the order
.where(cursor.where()) // .where() is called empty the first time, meaning "there's not previous records"
.limit(page_size);For the sub-sequent queries you can send the last previous record on cursor.where
const page2 = await db
.select() // .select() can vary while includes the needed data to create next curso (the same as the tables listed in primaryCursor and cursors)
.from(schema.users)
.orderBy(...cursor.orderBy)
.where(cursor.where(page1.at(-1))) // last record of previous query (or any record "before: the one you want to start with)
.limit(page_size);or the token of the item (useful to send/receive from the FE)
const lastToken = cursor.serialize(page2.at(-1)); // use serialize method/function to send tokens to your FE
// if you want to convert back the token to a object:
const lastItem = cursor.parse(lastToken); // use parse method/function to transform back in an object
const page3 = await db.query.users.findMany({
// It also works with Relational Queries
columns: {
// Be sure to include the data needed to create the cursor if using columns
lastName: true,
firstName: true,
middleName: true,
id: true,
},
orderBy: cursor.orderBy, // no need to destructure here
where: cursor.where(lastToken), // .where() also accepts the string token directly, no need to pre-parse it (at least you want to run extra validations)
limit: page_size,
});Submit an Issue with a minimal reproducible example.
PRs are welcome
MIT / Do whatever you want.
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent