Drizzle ORM - SQL Limit/Offset 分页

Drizzle | SQL Limit/Offset 分页

PostgreSQL

MySQL

SQLite

本指南假定您熟悉

开始使用 PostgreSQL、 MySQL 和 SQLite
Select 语句附带 order by 子句以及 limit & offset 子句
关系查询附带 order by 子句以及 limit & offset 子句
动态查询构建

本指南演示了如何在 Drizzle 中实现 limit/offset 分页

index.ts

schema.ts

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

const db = drizzle(...);

await db
  .select()
  .from(users)
  .orderBy(asc(users.id)) // order by is mandatory
  .limit(4) // the number of rows to return
  .offset(4); // the number of rows to skip

select * from users order by id asc limit 4 offset 4;

// 5-8 rows returned
[
  {
    id: 5,
    firstName: 'Beth',
    lastName: 'Davis',
    createdAt: 2024-03-11T20:51:46.787Z
  },
  {
    id: 6,
    firstName: 'Charlie',
    lastName: 'Miller',
    createdAt: 2024-03-11T21:15:46.787Z
  },
  {
    id: 7,
    firstName: 'Clara',
    lastName: 'Wilson',
    createdAt: 2024-03-11T21:33:46.787Z
  },
  {
    id: 8,
    firstName: 'David',
    lastName: 'Moore',
    createdAt: 2024-03-11T21:45:46.787Z
  }
]

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    |
+----+------------+-----------+----------------------------+

Limit 是要返回的行数 （页面大小），而 offset 是要跳过的行数 （（页码 - 1）* 页面大小）。为了一致的分页，请确保按唯一列排序。否则，结果可能会不一致。

如果您需要按非唯一列排序，还应在排序中附加一个唯一列。

这是如何用两列实现 limit/offset 分页的方法

const getUsers = async (page = 1, pageSize = 3) => {
  await db
    .select()
    .from(users)
    .orderBy(asc(users.firstName), asc(users.id)) // order by first_name (non-unique), id (pk)
    .limit(pageSize) 
    .offset((page - 1) * pageSize);
}

await getUsers();

Drizzle 拥有有用的关系查询 API，可让您轻松实现 limit/offset 分页

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

const db = drizzle({ schema });

const getUsers = async (page = 1, pageSize = 3) => {
  await db.query.users.findMany({
    orderBy: (users, { asc }) => asc(users.id),
    limit: pageSize,
    offset: (page - 1) * pageSize,
  });
};

await getUsers();

Drizzle 拥有简单灵活的 API，可让您轻松创建自定义解决方案。以下是您如何使用 .$dynamic() 函数创建自定义分页函数的方法

import { SQL, asc } from 'drizzle-orm';
import { PgColumn, PgSelect } from 'drizzle-orm/pg-core';

function withPagination<T extends PgSelect>(
  qb: T,
  orderByColumn: PgColumn | SQL | SQL.Aliased,
  page = 1,
  pageSize = 3,
) {
  return qb
    .orderBy(orderByColumn)
    .limit(pageSize)
    .offset((page - 1) * pageSize);
}

const query = db.select().from(users); // query that you want to execute with pagination

await withPagination(query.$dynamic(), asc(users.id));

您可以通过使用 deferred join 技术来提高 limit/offset 分页的性能。此方法对数据的子集而不是整个表执行分页。

要实现它，您可以这样做

const getUsers = async (page = 1, pageSize = 10) => {
   const sq = db
    .select({ id: users.id })
    .from(users)
    .orderBy(users.id)
    .limit(pageSize)
    .offset((page - 1) * pageSize)
    .as('subquery');

   await db.select().from(users).innerJoin(sq, eq(users.id, sq.id)).orderBy(users.id);
};

limit/offset 分页的优点：实现简单，页面易于访问，这意味着您可以导航到任何页面，而无需保存前一个页面的状态。

limit/offset 分页的缺点：随着 offset 的增加，查询性能会下降，因为数据库必须扫描 offset 之前的所有行才能跳过它们；以及由于数据位移导致的不一致性，这可能导致同一行在不同页面返回或行被跳过。

其工作原理如下

const getUsers = async (page = 1, pageSize = 3) => {
  await db
    .select()
    .from(users)
    .orderBy(asc(users.id))
    .limit(pageSize)
    .offset((page - 1) * pageSize);
};

// user is browsing the first page
await getUsers();

// results for the first page
[
  {
    id: 1,
    firstName: 'Alice',
    lastName: 'Johnson',
    createdAt: 2024-03-10T17:17:06.148Z
  },
  {
    id: 2,
    firstName: 'Alex',
    lastName: 'Smith',
    createdAt: 2024-03-10T17:19:06.147Z
  },
  {
    id: 3,
    firstName: 'Aaron',
    lastName: 'Williams',
    createdAt: 2024-03-10T17:22:06.147Z
  }
]

// while user is browsing the first page, a row with id 2 is deleted
await db.delete(users).where(eq(users.id, 2));

// user navigates to the second page
await getUsers(2);

// second page, row with id 3 was skipped
[
  {
    id: 5,
    firstName: 'Beth',
    lastName: 'Davis',
    createdAt: 2024-03-10T17:34:06.147Z
  },
  {
    id: 6,
    firstName: 'Charlie',
    lastName: 'Miller',
    createdAt: 2024-03-10T17:58:06.147Z
  },
  {
    id: 7,
    firstName: 'Clara',
    lastName: 'Wilson',
    createdAt: 2024-03-10T18:16:06.147Z
  }
]

因此，如果您的数据库实时频繁进行插入和删除操作，或者您需要高性能来对大型表进行分页，则应考虑使用基于游标的分页。

要了解更多关于 deferred join 技术的信息，请参考以下指南：Planetscale 分页指南和 Aaron Francis 的高效分页指南。