  
npm install pg-ination!NPM
!NPM
!GitHub Workflow Status
A utility to have arbitrary ordering with cursor based pagination, as well as next and previous page checks
- Options
- Adapter result
- Usage
- node-postgres
- bun.sh
- slonik
- postgres
- drizzle
- Subquery pattern
- sqlite
Limitations:
- You must have a unique, always-increasing id field (eg. UUIDv7)
- You can't use sort by fields that do not come directly from the table (that would be a limitation for the next/previous page). Example below using a subquery to fix it
``ts`
interface PaginateOptions
/**
* the table where your data lives
*/
tableName: string;
/**
* the user cursor input, can be either after, before or undefined
*/
pagination?: { after: string } | { before: string } | undefined;
/**
* the user ordering input, can be either column and order or undefined
*/
orderBy?:
| {
column: string;
order: "asc" | "desc";
/**
* use this parameter if you sort by a numeric or a timestamp column as it would sort amounts and dates lexicographically otherwise
*/
type?: "numeric" | "timestamp" | "text" | undefined;
}
| undefined;
}
`tsselect ${cursor} as cursor
interface AdapterResult
/**
* the cursor fragment (eg. )where ${filter}
*/
cursor: Fragment;
/**
* the filter fragment (eg. )order by ${order}
*/
filter: Fragment;
/**
* the order fragment (eg. )select ${hasNextPage} as "hasNextPage"
*/
order: Fragment;
/**
* the hasNextPage fragment (eg. )select ${hasPreviousPage} as "hasPreviousPage"
*/
hasNextPage: Fragment;
/**
* the hasPreviousPage fragment (eg. )`
*/
hasPreviousPage: Fragment;
}
`ts
// create search params
function paginate(options: PaginateOptions): PaginateResult;
// convert search params into SQL fragments with pgAdapter, bunAdapter, etc.
function adapter(
options: PaginateOptions,
result: PaginateResult
): AdapterResult;
// ensures sorting given the order by initial options
function toSorted
`
`ts
import { Client } from "pg";
import { paginate, toSorted } from "pg-ination";
import { pgAdapter } from "pg-ination/adapters/pg";
const options = {
tableName: "foo",
orderBy: { column: "name", order: "desc", type: "text" },
};
const paginateResult = paginate(options);
const fragments = pgAdapter(options, paginateResult);
const client = new Client(process.env["DB_URI"]);
const settings = paginate({
tableName: "users",
pagination: undefined,
orderBy: undefined,
});
// Fragments are escaped already
const unsortedUsers = await client.query(
select
"id",
${fragments.cursor},
${fragments.hasNextPage} as "hasNextPage",
${fragments.hasPreviousPage} as "hasPreviousPage"
from "users"
where ${fragments.filter}
order by ${fragments.order}
limit 3);
// the applied order by might be different than the provided one to be used with before cursortoSorted()
// hence you should always call with the same settings as the orderBy of paginate
const users = toSorted(unsortedUsers, options.orderBy);
// use with { after: nextPageCursor }
const nextPageCursor = users.at(-1)?.cursor ?? undefined;
// use with { before: previousPageCursor }
const previousPageCursor = users.at(0)?.cursor ?? undefined;
`
`ts
import { SQL } from "bun";
import { paginate, toSorted } from "pg-ination";
import { bunAdapter } from "pg-ination/adapters/bun";
const options = {
tableName: "foo",
orderBy: { column: "name", order: "desc", type: "text" },
};
const paginateResult = paginate(options);
const fragments = bunAdapter(options, paginateResult);
const sql = new SQL(process.env["DB_URI"]);
// Fragments are escaped already
const unsortedUsers = await sql
select
"id",
${fragments.cursor},
${fragments.hasNextPage} as "hasNextPage",
${fragments.hasPreviousPage} as "hasPreviousPage"
from "users"
where ${fragments.filter}
order by ${fragments.order}
limit 3;
// the applied order by might be different than the provided one to be used with before cursortoSorted()
// hence you should always call with the same settings as the orderBy of paginate
const users = toSorted(unsortedUsers, options.orderBy);
// use with { after: nextPageCursor }
const nextPageCursor = users.at(-1)?.cursor ?? undefined;
// use with { before: previousPageCursor }
const previousPageCursor = users.at(0)?.cursor ?? undefined;
`
`ts
import { createPool, sql } from "bun";
import { paginate, toSorted } from "pg-ination";
import { slonikAdapter } from "pg-ination/adapters/slonik";
const options = {
tableName: "foo",
orderBy: { column: "name", order: "desc", type: "text" },
};
const paginateResult = paginate(options);
const fragments = slonikAdapter(options, paginateResult);
const sql = await createPool(process.env["DB_URI"]);
// Fragments are escaped already
const unsortedUsers = await sql
select
"id",
${fragments.cursor},
${fragments.hasNextPage} as "hasNextPage",
${fragments.hasPreviousPage} as "hasPreviousPage"
from "users"
where ${fragments.filter}
order by ${fragments.order}
limit 3;
// the applied order by might be different than the provided one to be used with before cursortoSorted()
// hence you should always call with the same settings as the orderBy of paginate
const users = toSorted(unsortedUsers, options.orderBy);
// use with { after: nextPageCursor }
const nextPageCursor = users.at(-1)?.cursor ?? undefined;
// use with { before: previousPageCursor }
const previousPageCursor = users.at(0)?.cursor ?? undefined;
`
`ts
import postgres from "postgres";
import { paginate, toSorted } from "pg-ination";
import { postgresAdapter } from "pg-ination/adapters/postgres";
const options = {
tableName: "foo",
orderBy: { column: "name", order: "desc", type: "text" },
};
const paginateResult = paginate(options);
const fragments = postgresAdapter(options, paginateResult);
const sql = postgres(process.env["DB_URI"]);
// Fragments are escaped already
const unsortedUsers = await sql
select
"id",
${fragments.cursor},
${fragments.hasNextPage} as "hasNextPage",
${fragments.hasPreviousPage} as "hasPreviousPage"
from "users"
where ${fragments.filter}
order by ${fragments.order}
limit 3;
// the applied order by might be different than the provided one to be used with before cursortoSorted()
// hence you should always call with the same settings as the orderBy of paginate
const users = toSorted(unsortedUsers, options.orderBy);
// use with { after: nextPageCursor }
const nextPageCursor = users.at(-1)?.cursor ?? undefined;
// use with { before: previousPageCursor }
const previousPageCursor = users.at(0)?.cursor ?? undefined;
`
`ts
import { sql } from "drizzle-orm";
import { drizzle } from "drizzle-orm/node-postgres";
import { Pool } from "pg";
import { paginate, toSorted } from "pg-ination";
import { drizzleAdapter } from "pg-ination/adapters/drizzle";
const options = {
tableName: "foo",
orderBy: { column: "name", order: "desc", type: "text" },
};
const paginateResult = paginate(options);
const fragments = drizzleAdapter(options, paginateResult);
const pool = new Pool({ connectionString: process.env["DB_URI"] });
const db = drizzle(pool);
// Fragments are escaped already
const unsortedUsers = await db.execute(
sql
select
"id",
${fragments.cursor} as "cursor",
${fragments.hasNextPage} as "hasNextPage",
${fragments.hasPreviousPage} as "hasPreviousPage"
from "users"
where ${fragments.filter}
order by ${fragments.order}
limit 3
);
// the applied order by might be different than the provided one to be used with before cursortoSorted()
// hence you should always call with the same settings as the orderBy of paginate
const users = toSorted(unsortedUsers.rows, options.orderBy);
// use with { after: nextPageCursor }
const nextPageCursor = users.at(-1)?.cursor ?? undefined;
// use with { before: previousPageCursor }
const previousPageCursor = users.at(0)?.cursor ?? undefined;
`
`ts
import DatabaseSync from "node:sqlite";
import { paginate, toSorted } from "pg-ination";
import { sqliteAdapter } from "pg-ination/adapters/sqlite";
const options = {
tableName: "foo",
orderBy: { column: "name", order: "desc", type: "text" },
};
const paginateResult = paginate(options);
const fragments = sqliteAdapter(options, paginateResult);
const sql = new DatabaseSync(":memory:");
// Fragments are escaped already
const unsortedUsers = sql
.prepare(
select
"id",
${fragments.cursor},
${fragments.hasNextPage} as "hasNextPage",
${fragments.hasPreviousPage} as "hasPreviousPage"
from "users"
where ${fragments.filter}
order by ${fragments.order}
limit 3
)
.all();
// the applied order by might be different than the provided one to be used with before cursortoSorted()
// hence you should always call with the same settings as the orderBy of paginate
const users = toSorted(unsortedUsers, options.orderBy);
// use with { after: nextPageCursor }
const nextPageCursor = users.at(-1)?.cursor ?? undefined;
// use with { before: previousPageCursor }
const previousPageCursor = users.at(0)?.cursor ?? undefined;
`
`ts
const paginateOptions: PaginateOptions = {
tableName: "table_sq",
pagination,
orderBy: { column: "foo", order: "asc", type: "numeric" },
};
const paginateResult = paginate(paginateOptions);
const adapterResult = xAdapter(paginateOptions, paginateResult);
const result = await query(sql
with "table_sq" as (
select
"table"."id",
"table"."name",
"table"."created_at",
"joined_table"."foo"
from "table"
left join "joined_table"
on "joined_table"."table_id" = "table"."id"
)
select
*,
${adapterResult.cursor} as "cursor",
${adapterResult.hasNextPage} as "hasNextPage",
${adapterResult.hasPreviousPage} as "hasPreviousPage"
from "table_sq"
where ${adapterResult.filter}
order by ${adapterResult.order}
limit 5);`
needed?When going backwards, keeping the initial order would mean selecting last rows.
Example:
`sql
-- first page: F, E
select name
from unnest(array['A', 'B', 'C', 'D', 'E', 'F']) as name
order by name DESC
limit 2;
-- second page: D, C
select name
from unnest(array['A', 'B', 'C', 'D', 'E', 'F']) as name
where name < 'E'
order by name DESC
limit 2;
-- third page: B, A
select name
from unnest(array['A', 'B', 'C', 'D', 'E', 'F']) as name
where name < 'C'
order by name DESC
limit 2;
-- now pressing back to second page, keeping the same order
-- this incorrectly returns F, E
select name
from unnest(array['A', 'B', 'C', 'D', 'E', 'F']) as name
where name > 'B'
order by name DESC
limit 2;
-- so instead we sort ascending since we're going backwards
-- this returns C, D which we can reverse to get D, C
select name
from unnest(array['A', 'B', 'C', 'D', 'E', 'F']) as name
where name < 'B'
order by name ASC
limit 2;
``