API & Backend
Cursor Pagination with Node.js and PostgreSQL
Cursor pagination retrieves the next page by comparing an ordered key with the last item previously returned. In PostgreSQL, this is commonly implemented as keyset pagination: order by an indexed, deterministic tuple such as (created_at, id), encode that tuple in an opaque cursor, and query rows strictly after or before it.
This approach avoids the increasing scan cost and shifting-page behavior of large OFFSET queries. It does not automatically create a frozen snapshot, and it requires more API design than page=4. This tutorial builds a forward-paginated Node.js endpoint, then extends the design to validation, backward navigation, filtering, indexing, and tests.
Know when cursor pagination is the right choice
Offset pagination expresses a position as a row count:
SELECT id, title, created_at
FROM articles
ORDER BY created_at DESC, id DESC
LIMIT 25 OFFSET 50000;The database may still need to walk past many rows before returning the requested page. More importantly, inserting a newer row between two client requests shifts later offsets. A client can see a duplicate or skip an item even when both queries succeed.
Cursor pagination expresses a position in the ordering itself. “Continue after the article created at this timestamp with this ID” remains meaningful when newer rows arrive above it. PostgreSQL can seek into a matching B-tree index instead of counting from the beginning.
Choose cursors for activity feeds, audit events, catalog browsing, logs, and APIs with large or changing datasets. Offset pagination remains useful for small administrative tables, direct page-number navigation, and reports where the dataset is fixed. Explain the choice in your API documentation rather than presenting cursor pagination as universally superior.
Our API documentation guide describes the surrounding contract an integration needs. The developer portal checklist can help you evaluate whether pagination examples, errors, and SDK behavior are discoverable.
Choose a deterministic sort key
A cursor must represent a total order. created_at alone is not enough because several rows can share a timestamp. Add a unique tie-breaker such as the primary key:
CREATE TABLE articles (
id uuid PRIMARY KEY,
title text NOT NULL,
status text NOT NULL,
created_at timestamptz(3) NOT NULL DEFAULT now()
);
CREATE INDEX articles_created_id_desc_idx
ON articles (created_at DESC, id DESC);The query must use the same columns, directions, and tie-breaking semantics as the cursor. For a newest-first feed, order both fields descending. If two articles have the same timestamp, their UUIDs still establish a stable order.
This example deliberately stores millisecond precision with timestamptz(3). PostgreSQL can store microseconds, but JavaScript Date retains only milliseconds. With the default node-postgres date parser, encoding a higher-precision database timestamp through Date.toISOString() would move the cursor boundary and could skip rows. For an existing microsecond-precision table, preserve the full timestamp as a string with a custom parser and matching cursor validation; do not round its values at the API boundary. See PostgreSQL date/time precision and node-postgres date parsing.
Prefer immutable sort fields. If a cursor uses updated_at and an item changes between page requests, it can move across the boundary. That may be correct for a “recently updated” feed, but the API must describe the behavior. For stable traversal, use creation time or a monotonically assigned sequence.
Null values complicate tuple comparisons and ordering. Make cursor fields non-null or define explicit null ordering and encode enough state to reproduce it. Avoid locale-dependent text as a cursor key because collation changes can change order.
PostgreSQL supports lexicographic row constructor comparisons, allowing a tuple comparison that matches a multi-column B-tree order.
Write the keyset query
The first page has no cursor:
SELECT id, title, status, created_at
FROM articles
WHERE status = $1
ORDER BY created_at DESC, id DESC
LIMIT $2;For the next page, continue strictly below the previous final tuple:
SELECT id, title, status, created_at
FROM articles
WHERE status = $1
AND (created_at, id) < ($2::timestamptz, $3::uuid)
ORDER BY created_at DESC, id DESC
LIMIT $4;Because both sort directions are descending, “after” in the result sequence corresponds to <. For an ascending order it would use >. Keep this logic next to the order definition; changing one without the other creates missing or repeated items.
Fetch limit + 1 rows. The extra row tells the server whether another page exists without running a separate count query. Remove it before returning the response.
const requestedLimit = input.limit;
const rows = await queryArticles({ ...input, limit: requestedLimit + 1 });
const hasNextPage = rows.length > requestedLimit;
const items = hasNextPage ? rows.slice(0, requestedLimit) : rows;A total count is a separate product decision. Exact counts can be expensive and quickly stale. Do not add COUNT(*) to every request merely because page-number pagination usually exposes it.
Encode an opaque cursor
Clients should treat the cursor as an uninterpreted token. That lets the server add fields or change encoding without making the tuple part of the public API. Opaque does not mean secure: Base64URL is encoding, not encryption or integrity protection.
type ArticleCursor = {
v: 1;
createdAt: string;
id: string;
};
class CursorError extends Error {}
function isArticleCursor(value: unknown): value is ArticleCursor {
if (!value || typeof value !== 'object' || Array.isArray(value)) return false;
const cursor = value as Record<string, unknown>;
if (Object.keys(cursor).sort().join(',') !== 'createdAt,id,v' || cursor.v !== 1) return false;
if (typeof cursor.id !== 'string' ||
!/^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i.test(cursor.id)) return false;
if (typeof cursor.createdAt !== 'string' ||
!/^\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}\.\d{3}Z$/.test(cursor.createdAt)) return false;
const milliseconds = Date.parse(cursor.createdAt);
return Number.isFinite(milliseconds) && new Date(milliseconds).toISOString() === cursor.createdAt;
}
function encodeCursor(row: { created_at: Date; id: string }): string {
const value: ArticleCursor = {
v: 1,
createdAt: row.created_at.toISOString(),
id: row.id
};
return Buffer.from(JSON.stringify(value), 'utf8').toString('base64url');
}
function decodeCursor(value: string): ArticleCursor {
if (value.length > 512) throw new CursorError('cursor is too long');
if (!/^[A-Za-z0-9_-]+$/.test(value) || value.length % 4 === 1) {
throw new CursorError('cursor is malformed');
}
let parsed: unknown;
try {
parsed = JSON.parse(Buffer.from(value, 'base64url').toString('utf8'));
} catch {
throw new CursorError('cursor is malformed');
}
if (!isArticleCursor(parsed)) throw new CursorError('cursor is invalid');
return parsed;
}Node’s Base64URL decoder is intentionally permissive, so the character and length checks run before decoding. isArticleCursor then validates the version, the UTC millisecond timestamp contract used here, UUID syntax, and exact keys. Reject unsupported versions with a documented client error. Never concatenate decoded values into SQL; continue using parameters.
If cursor tampering could expose a different tenant, filter set, or retention boundary, sign the serialized payload with an HMAC and compare signatures safely. Still enforce authorization in the query. A signed cursor is not permission to access the referenced row.
Include filter identity in the cursor when changing filters midstream would be ambiguous. You can either reject a cursor whose embedded filter hash does not match the request or define that the cursor is valid only with the original query parameters.
Build the Node.js endpoint
The endpoint accepts limit, after, and a fixed-status filter. Keep these request validators beside the cursor helpers. The demonstration has public published articles only; exposing drafts requires a separate authorization policy.
function parseLimit(value: unknown): number {
if (value === undefined) return 25;
if (typeof value !== 'string' || !/^[1-9]\d{0,2}$/.test(value) || Number(value) > 100) {
throw new CursorError('limit must be an integer from 1 to 100');
}
return Number(value);
}
function parseStatus(value: unknown): 'published' {
if (value !== undefined && value !== 'published') {
throw new CursorError('only published articles are available');
}
return 'published';
}
function toArticleResponse(row: { id: string; title: string; status: string; created_at: Date }) {
return { id: row.id, title: row.title, status: row.status, createdAt: row.created_at.toISOString() };
}Register the route, then map validation failures to a client error:
import express from 'express';
import { Pool } from 'pg';
const app = express();
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
app.get('/v1/articles', async (request, response, next) => {
try {
const limit = parseLimit(request.query.limit);
const status = parseStatus(request.query.status);
if (request.query.after !== undefined && typeof request.query.after !== 'string') {
throw new CursorError('after must be a single cursor string');
}
const cursor = typeof request.query.after === 'string'
? decodeCursor(request.query.after)
: undefined;
const parameters: unknown[] = [status];
let boundary = '';
if (cursor) {
parameters.push(cursor.createdAt, cursor.id);
boundary = `AND (created_at, id) < ($2::timestamptz, $3::uuid)`;
}
parameters.push(limit + 1);
const limitParameter = parameters.length;
const result = await pool.query(
`SELECT id, title, status, created_at
FROM articles
WHERE status = $1
${boundary}
ORDER BY created_at DESC, id DESC
LIMIT $${limitParameter}`,
parameters
);
const hasNextPage = result.rows.length > limit;
const rows = result.rows.slice(0, limit);
const last = rows.at(-1);
response.json({
data: rows.map(toArticleResponse),
page: {
hasNextPage,
nextCursor: hasNextPage && last ? encodeCursor(last) : null
}
});
} catch (error) {
next(error);
}
});
app.use((error: unknown, _request: express.Request, response: express.Response, _next: express.NextFunction) => {
if (error instanceof CursorError) {
response.status(400).json({ error: { code: 'invalid_pagination', message: error.message } });
return;
}
response.status(500).json({ error: { code: 'internal_error', message: 'Unable to list articles' } });
});The dynamic SQL inserts only a fixed server-controlled fragment. Values remain parameterized. If you add selectable sort fields, map a small public enum to known SQL identifiers. Never insert a request-provided column name directly.
Normalize database names in toArticleResponse, for example converting created_at to createdAt. Keep date formatting consistent and state whether timestamps are UTC RFC 3339 values.
Design the response contract
A compact response separates data from pagination metadata:
{
"data": [
{
"id": "bd7fd675-7dbf-45b6-946a-440f643fadba",
"title": "Designing stable API pagination",
"status": "published",
"createdAt": "2026-09-04T08:30:00.000Z"
}
],
"page": {
"hasNextPage": true,
"nextCursor": "eyJ2IjoxLCJjcmVhdGVkQXQiOiIyMDI2LTA5LTA0VDA4OjMwOjAwLjAwMFoiLCJpZCI6ImJkN2ZkNjc1LTdkYmYtNDViNi05NDZhLTQ0MGY2NDNmYWRiYSJ9"
}
}Use null or omit nextCursor when no next page exists; choose one convention and document it. Do not promise that data.length < limit is the only end signal if permission filtering or post-query filtering can reduce results.
Return 400 Bad Request for a malformed, expired, or incompatible cursor. Use a machine-readable error code such as invalid_cursor plus a safe message. Do not expose JSON parsing errors, SQL values, or HMAC details.
Decide whether cursors expire. Expiry can be useful when the underlying retention window changes or the encoding is expected to evolve. If they expire, include a timestamp or key version, document the lifetime, and tell clients how to restart from the first page.
Add backward pagination carefully
Backward pagination is more than changing < to >. To retrieve the previous page in a descending feed, query the opposite direction from the boundary, limit the result, and reverse it before responding.
SELECT id, title, status, created_at
FROM articles
WHERE status = $1
AND (created_at, id) > ($2::timestamptz, $3::uuid)
ORDER BY created_at ASC, id ASC
LIMIT $4;The database returns the nearest preceding items efficiently in ascending order. Reverse them in application code so the API still presents newest first. Encode both the first and last returned tuples when the response offers previousCursor and nextCursor.
Define parameter combinations. A simple contract can allow either after or before, never both. A richer connection model can expose first/after and last/before. Whichever vocabulary you choose, include examples for first, next, previous, and terminal pages.
Backward traversal over a changing feed does not recreate history. Newly inserted rows may appear when moving toward the beginning. If the user experience requires a stable snapshot, add a snapshot boundary.
Preserve a stable snapshot when required
Keyset pagination prevents offset drift around the cursor, but it does not freeze the dataset. New rows outside the traversed boundary are expected. Updates to filter fields or deletions can still change what later requests see.
For an export or audit report, capture a high-water mark on the first request, such as the maximum sequence or a server timestamp, and include it in every cursor. Adding created_at <= snapshot_at excludes rows with a later creation timestamp. It is not a transaction snapshot: a backdated insert or a transaction that commits later with an earlier timestamp can still appear.
A timestamp boundary does not freeze updates or deletions. For stronger consistency, paginate a materialized export, use a durable snapshot identifier, or query immutable event rows. Holding a database transaction open across client requests is generally a poor fit because requests can be minutes apart and long transactions interfere with maintenance.
State the consistency model plainly: stable ordering, visibility of new records, behavior after deletion, and cursor lifetime. “Cursor-based” alone does not communicate those guarantees.
Build indexes around filters and tenancy
The ideal index starts with equality filters and ends with the ordered cursor keys. For a tenant-scoped published-article feed:
CREATE INDEX articles_tenant_status_created_id_idx
ON articles (tenant_id, status, created_at DESC, id DESC);The query must include tenant_id and status equality predicates before the tuple boundary. Use EXPLAIN (ANALYZE, BUFFERS) against realistic data to verify the plan. A fast query on 200 development rows says little about a production feed with millions of records.
Do not create every possible compound index. Each index consumes storage and adds write cost. Support a deliberate set of sort and filter combinations, publish them in the contract, and reject arbitrary combinations that cannot perform predictably.
For soft-deleted records, a partial index may help if nearly every request excludes them. For row-level security, verify plans with the real policy and role. Authorization predicates must be part of every page, not applied after fetching an unscoped batch.
Test boundaries and concurrent changes
Seed rows where several timestamps are identical. A test dataset with unique timestamps will not expose a missing tie-breaker. Traverse every page and assert that each expected ID appears exactly once.
Test limits of one, the maximum, and invalid values. Test a malformed Base64 string, valid JSON with wrong fields, an unsupported cursor version, an unknown UUID, and a cursor from a different filter. Verify error codes and ensure internal parsing detail stays private.
Insert a newer record after fetching page one, then fetch page two. The new row should not shift the next boundary. Delete the boundary row and confirm the tuple still allows continuation because the cursor carries values rather than a database reference.
Use property-based or randomized tests for larger sequences. Generate records with duplicate timestamps, paginate at several limits, and compare the flattened IDs with one canonical ordered query. This catches direction and reversal mistakes that example tests miss.
Performance tests should inspect query plans at early and late positions. Keyset latency should remain comparatively stable because the index seeks to the boundary. Monitor scanned rows, returned rows, database time, and endpoint latency in production without using cursor values as metric labels.
Document the developer experience
Show the first request, response, second request, and end response. Describe the cursor as opaque and warn clients not to parse or construct it. State maximum and default limits, allowed filters, sort order, cursor lifetime, error shape, and consistency behavior.
SDKs should expose an iterator or paginator so most developers do not manually copy cursor strings. Preserve cancellation and surface rate-limit information. Avoid eagerly loading all pages into memory under a convenient method name.
Include pagination headers only if they add a stable benefit. Body links or tokens are often easier across HTTP clients. If you publish Link headers, construct URLs safely and preserve applicable filters.
Treat a cursor change like an API contract change. Version the payload internally, support an overlap window, and measure use before retiring old versions. The practices in our docs-as-code guide help keep examples and behavior reviewed with implementation changes.
Protect authorization and tenant boundaries
Apply authorization predicates in the database query that selects each page. Fetching a broad page and filtering unauthorized rows in application code can return short pages, leak timing information, and produce cursors that skip data unpredictably.
For a multi-tenant API, include tenant_id = $1 before the tuple comparison and make the matching index tenant-first. Derive the tenant from authenticated server context, never from cursor contents alone. If the cursor embeds tenant or filter identity, treat that value as a consistency check rather than authority.
Limit cursor length before decoding to avoid unnecessary memory and parsing work. Return one generic invalid-cursor error for bad encoding, signature, version, or tenant mismatch. Detailed diagnostics belong in protected logs. The OWASP API Security project is a useful checklist for object-level authorization and resource-consumption risks around collection endpoints.
Rate-limit expensive traversals and cap page size. A perfectly indexed query can still be abused through high concurrency or many distinct filters. Track database time and rejected limits with bounded metrics, without recording raw cursors.
Frequently asked questions
Is cursor pagination the same as keyset pagination?
Keyset pagination is the database technique of seeking from ordered values. Cursor pagination is the API contract that carries continuation state in a token. A cursor can contain a keyset tuple, but other cursor implementations are possible.
Should a cursor be encrypted?
Usually it only needs to be opaque to clients. Sign it when tampering matters, and encrypt it only when the contents themselves are sensitive. Always enforce authorization independently of the cursor.
Can cursor pagination show a total page count?
Not naturally. You can run a separate count, provide an estimate, or omit it. A page count also assumes a stable dataset and fixed page size, which often conflicts with the reason for using cursors.
What happens when the cursor row is deleted?
A value-based cursor still works because it contains the ordering tuple. The next query compares values and does not need to find the original row. A cursor that stores only an ID may fail unless the server can recover the sort values elsewhere.
Ship a cursor as a real contract
Reliable cursor pagination comes from deterministic ordering, matching indexes, strict token validation, explicit consistency semantics, and boundary-heavy tests. The SQL is short; the developer experience depends on everything around it.
If your API needs pagination guidance, reference examples, or SDK documentation that stays aligned with implementation, explore our API documentation service. A precise continuation contract saves every integrator from rediscovering the same edge cases.