Drizzle ORM 한글 - SQL Limit/Offset 페이지네이션

Drizzle | SQL Limit/Offset 페이지네이션

PostgreSQL

MySQL

SQLite

This guide assumes familiarity with:

이 가이드는 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) * 페이지 크기)입니다. 일관된 페이지네이션을 위해서는 고유한 컬럼으로 정렬해야 합니다. 그렇지 않으면 결과가 일관성 없게 나올 수 있습니다.

고유하지 않은 컬럼으로 정렬해야 하는 경우, 정렬에 고유 컬럼을 추가해야 합니다.

다음은 2개의 컬럼으로 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은 limit/offset 페이지네이션을 쉽게 구현할 수 있는 유용한 관계형 쿼리 API를 제공합니다:

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 Pagination Guide, Efficient Pagination Guide by Aaron Francis.