Drizzle ORM - SQL Limit/Offset 페이징

Drizzle | SQL Limit/Offset 페이징

PostgreSQL

MySQL

SQLite

This guide assumes familiarity with:

PostgreSQL, MySQL, SQLite 시작하기
order by 절과 limit & offset 절이 포함된 select 문
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는 필수
  .limit(4) // 반환할 행의 수
  .offset(4); // 건너뛸 행의 수

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

// 5-8행 반환
[
  {
    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)) // first_name(고유하지 않음), id(기본키)로 정렬
    .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(
  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); // 페이징을 적용할 쿼리

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 페이징의 단점: 오프셋이 증가함에 따라 쿼리 성능이 저하됩니다. 데이터베이스는 오프셋 이전의 모든 행을 스캔해야 하기 때문입니다. 또한 데이터 이동으로 인해 일관성이 깨질 수 있어 동일한 행이 다른 페이지에 반환되거나 행이 건너뛰어질 수 있습니다.

다음은 그 작동 방식입니다.

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

// 사용자가 첫 번째 페이지를 탐색 중
await getUsers();

// 첫 번째 페이지 결과
[
  {
    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
  }
]

// 사용자가 첫 번째 페이지를 탐색하는 동안 id가 2인 행이 삭제됨
await db.delete(users).where(eq(users.id, 2));

// 사용자가 두 번째 페이지로 이동
await getUsers(2);

// 두 번째 페이지, id가 3인 행이 건너뛰어짐
[
  {
    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 Pagination Guide 및 Aaron Francis의 Efficient Pagination Guide.