Drizzle ORM - 基于 SQL 游标的分页

Drizzle | 基于 SQL 游标的分页

PostgreSQL

MySQL

SQLite

本指南假定您熟悉

本指南演示了如何在 Drizzle 中实现基于游标的分页

index.ts

schema.ts

import { asc, gt } from 'drizzle-orm';
import { users } from './schema';

const db = drizzle(...);

const nextUserPage = async (cursor?: number, pageSize = 3) => {
  await db
    .select()
    .from(users)
    .where(cursor ? gt(users.id, cursor) : undefined) // if cursor is provided, get rows after it
    .limit(pageSize) // the number of rows to return
    .orderBy(asc(users.id)); // ordering
};

// pass the cursor of the last row of the previous page (id)
await nextUserPage(3);

select * from users order by id asc limit 3;

// next page, 4-6 rows returned
[
  {
    id: 4,
    firstName: 'Brian',
    lastName: 'Brown',
    createdAt: 2024-03-08T12:34:55.182Z
  },
  {
    id: 5,
    firstName: 'Beth',
    lastName: 'Davis',
    createdAt: 2024-03-08T12:40:55.182Z
  },
  {
    id: 6,
    firstName: 'Charlie',
    lastName: 'Miller',
    createdAt: 2024-03-08T13:04:55.182Z
  }
]

import { pgTable, serial, text, timestamp } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  firstName: text('first_name').notNull(),
  lastName: text('last_name').notNull(),
  createdAt: timestamp('created_at').notNull().defaultNow(),
});

+----+------------+------------+----------------------------+
| id | first_name | last_name  |         created_at         |
+----+------------+------------+----------------------------+
|  1 | Alice      | Johnson    | 2024-03-08 12:23:55.251797 |
+----+------------+------------+----------------------------+
|  2 | Alex       | Smith      | 2024-03-08 12:25:55.182    |
+----+------------+------------+----------------------------+
|  3 | Aaron      | Williams   | 2024-03-08 12:28:55.182    |
+----+------------+------------+----------------------------+
|  4 | Brian      | Brown      | 2024-03-08 12:34:55.182    |
+----+------------+------------+----------------------------+
|  5 | Beth       | Davis      | 2024-03-08 12:40:55.182    |
+----+------------+------------+----------------------------+
|  6 | Charlie    | Miller     | 2024-03-08 13:04:55.182    |
+----+------------+------------+----------------------------+
|  7 | Clara      | Wilson     | 2024-03-08 13:22:55.182    |
+----+------------+------------+----------------------------+
|  8 | David      | Moore      | 2024-03-08 13:34:55.182    |
+----+------------+------------+----------------------------+
|  9 | Aaron      | Anderson   | 2024-03-08 12:40:33.677235 |
+----+------------+------------+----------------------------+

如果您需要动态的 order by，可以按如下方式操作

const nextUserPage = async (order: 'asc' | 'desc' = 'asc', cursor?: number, pageSize = 3) => {
  await db
    .select()
    .from(users)
    // cursor comparison
    .where(cursor ? (order === 'asc' ? gt(users.id, cursor) : lt(users.id, cursor)) : undefined)
    .limit(pageSize)
    .orderBy(order === 'asc' ? asc(users.id) : desc(users.id));
};

await nextUserPage();
await nextUserPage('asc', 3);
// descending order
await nextUserPage('desc');
await nextUserPage('desc', 7);

这种分页的主要思想是使用游标作为数据集中特定行的指针，指示前一页的结束。为了正确的排序和游标比较，游标应该是唯一的且连续的。

如果您需要按非唯一且非连续的列排序，可以使用多个列作为游标。您可以按如下方式操作

import { and, asc, eq, gt, or } from 'drizzle-orm';

const nextUserPage = async (
  cursor?: {
    id: number;
    firstName: string;
  },
  pageSize = 3,
) => {
  await db
    .select()
    .from(users)
    .where(
      cursor
        ? or(
            gt(users.firstName, cursor.firstName),
            and(eq(users.firstName, cursor.firstName), gt(users.id, cursor.id)),
          )
        : undefined,
    )
    .limit(pageSize)
    .orderBy(asc(users.firstName), asc(users.id));
};

// pass the cursor from previous page (id & firstName)
await nextUserPage({
  id: 2,
  firstName: 'Alex',
});

select * from users
  where (first_name > 'Alex' or (first_name = 'Alex' and id > 2))
  order by first_name asc, id asc limit 3;

// next page, 4-6 rows returned
[
  {
    id: 1,
    firstName: 'Alice',
    lastName: 'Johnson',
    createdAt: 2024-03-08T12:23:55.251Z
  },
  {
    id: 5,
    firstName: 'Beth',
    lastName: 'Davis',
    createdAt: 2024-03-08T12:40:55.182Z
  },
  {
    id: 4,
    firstName: 'Brian',
    lastName: 'Brown',
    createdAt: 2024-03-08T12:34:55.182Z
  }
]

请确保为您用作游标的列创建索引，以提高查询效率。

import { index, ...imports } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  // columns declaration
},
(t) => [
  index('first_name_index').on(t.firstName).asc(),
  index('first_name_and_id_index').on(t.firstName, t.id).asc(),
]);

-- As of now drizzle-kit only supports index name and on() param, so you have to add order manually
CREATE INDEX IF NOT EXISTS "first_name_index" ON "users" ("first_name" ASC);
CREATE INDEX IF NOT EXISTS "first_name_and_id_index" ON "users" ("first_name" ASC,"id" ASC);

如果您使用的主键不是连续的（例如 UUIDv4），您应该添加连续列（例如 created_at 列）并使用多个游标。您可以按如下方式操作


const nextUserPage = async (
  cursor?: {
    id: string;
    createdAt: Date;
  },
  pageSize = 3,
) => {
  await db
    .select()
    .from(users)
    .where(
      // make sure to add indices for the columns that you use for cursor
      cursor
        ? or(
            gt(users.createdAt, cursor.createdAt),
            and(eq(users.createdAt, cursor.createdAt), gt(users.id, cursor.id)),
          )
        : undefined,
    )
    .limit(pageSize)
    .orderBy(asc(users.createdAt), asc(users.id));
};

// pass the cursor from previous page (id & createdAt)
await nextUserPage({
  id: '66ed00a4-c020-4dfd-a1ca-5d2e4e54d174',
  createdAt: new Date('2024-03-09T17:59:36.406Z'),
});

Drizzle 提供了实用的关系查询 API，让您可以轻松实现基于游标的分页

import * as schema from './db/schema';

const db = drizzle(..., { schema });

const nextUserPage = async (cursor?: number, pageSize = 3) => {
  await db.query.users.findMany({
    where: (users, { gt }) => (cursor ? gt(users.id, cursor) : undefined),
    orderBy: (users, { asc }) => asc(users.id),
    limit: pageSize,
  });
};

// next page, cursor of last row of the first page (id = 3)
await nextUserPage(3);

基于游标的分页的优点：查询结果一致，不会因插入或删除操作而跳过或重复行，并且与 limit/offset 分页相比效率更高，因为它不需要扫描和跳过之前的行来访问下一页。

基于游标的分页的缺点：无法直接导航到特定页面，并且实现复杂。由于您在排序顺序中添加了更多列，因此需要在 where 子句中添加更多筛选条件来进行游标比较，以确保分页一致。

因此，如果您需要直接导航到特定页面或需要更简单的分页实现，您应该考虑使用offset/limit分页。