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) => [
  index('first_name_index').on(t.firstName).asc(),
  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 페이지네이션을 사용하는 것을 고려해야 합니다.