Implement cursor pagination with PostgreSQL in Node.js

Approach

Order by (created_at, id) descending, filter with a row comparison against the decoded cursor, fetch limit + 1 rows, and return an opaque base64url cursor for the last item. Always use parameterized queries.

Solution

import { Pool } from "pg";
const pool = new Pool({ connectionString: process.env.DATABASE_URL, max: 10 });

type Cursor = { createdAt: string; id: string };
const encode = (c: Cursor) => Buffer.from(JSON.stringify(c)).toString("base64url");
const decode = (s: string): Cursor => JSON.parse(Buffer.from(s, "base64url").toString("utf8"));

export async function listActivity(userId: string, limit = 20, cursor?: string) {
  const params: unknown[] = [userId, Math.min(Math.max(limit, 1), 100) + 1];
  let where = "user_id = $1";
  if (cursor) {
    const c = decode(cursor);
    params.push(c.createdAt, c.id);
    where += " AND (created_at, id) < ($3::timestamptz, $4::uuid)";
  }
  const { rows } = await pool.query(
    `SELECT id, kind, created_at FROM activity WHERE ${where} ORDER BY created_at DESC, id DESC LIMIT $2`,
    params,
  );
  const pageSize = params[1] as number - 1;
  const hasMore = rows.length > pageSize;
  const items = rows.slice(0, pageSize);
  const last = items.at(-1);
  return {
    items,
    next_cursor: hasMore && last ? encode({ createdAt: last.created_at.toISOString(), id: last.id }) : null,
  };
}
// CREATE INDEX ON activity (user_id, created_at DESC, id DESC);

Pitfalls

• String-interpolating values into SQL.
• Non-unique sort keys.
• Missing composite index.

Variations

• Prisma/Drizzle cursor pagination helpers.
• Sign cursors with HMAC to prevent tampering.

Further reading: node-postgres: Queries