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 페이징을 고려해보세요.