Drizzle ORM - SQL 基于游标的分页

Drizzle | SQL 基于游标的分页

PostgreSQL

MySQL

SQLite

This guide assumes familiarity with:

本指南演示如何在 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) // 如果提供了游标，则获取其后的行
    .limit(pageSize) // 返回的行数
    .orderBy(asc(users.id)); // 排序
};

// 传递前一页最后一行的游标 (id)
await nextUserPage(3);

select * from users order by id asc limit 3;

// 下一页，返回第 4-6 行
[
  {
    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 |
+----+------------+------------+----------------------------+

如果需要动态排序，可以像下面这样做：

const nextUserPage = async (order: 'asc' | 'desc' = 'asc', cursor?: number, pageSize = 3) => {
  await db
    .select()
    .from(users)
    // 游标比较
    .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);
// 降序
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));
};

// 传递来自前一页的游标 (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;

// 下一页，返回第 4-6 行
[
  {
    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', {
// 列声明
},
(t) => ({
  firstNameIndex: index('first_name_index').on(t.firstName).asc(),
  firstNameAndIdIndex: index('first_name_and_id_index').on(t.firstName, t.id).asc(),
}),
);

-- 目前 drizzle-kit 只支持索引名和 on() 参数，所以您需要手动添加顺序
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(
      // 确保为您用于游标的列添加索引
      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));
};

// 传递来自前一页的游标 (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,
  });
};

// 下一页，前一页最后一行的游标 (id = 3)
await nextUserPage(3);

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

基于游标的 分页的缺点：无法直接导航到特定页面，且实现复杂。由于您将更多列添加到排序顺序中，则需要在 where 子句中添加更多过滤器以进行游标比较，以确保分页的一致性。

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