JSON API Sort Parameters: Multi-Field Sorting, Stability & SQL Safety

Last updated: May 20, 2026

Written and reviewed by the Jsonic editorial team — every guide is verified against the official spec or runtime before publication.

Most tutorials cover Array.prototype.sort() for client-side JSON sorting and stop there. But production JSON APIs face a harder problem: designing the sort query parameter convention, safely translating it into SQL ORDER BY without SQL injection, guaranteeing stable sort order across paginated results, and backing it with the right composite indexes. The JSON:API specification defines ?sort=field for ascending and ?sort=-field for descending, with comma-separated multi-field support like ?sort=lastName,-createdAt. This guide covers the full stack: API parameter design, SQL injection prevention via column allowlists, sort stability for pagination correctness, composite index strategy, client-side locale-aware sorting with Intl.Collator, and cursor pagination with compound sort cursors. Every section includes production-ready TypeScript and SQL code.

JSON:API Sort Parameter Convention

The JSON:API specification defines the sort query parameter as the standard way to request sorted results from a REST API. A plain field name means ascending: ?sort=name. A minus prefix means descending: ?sort=-name. Multiple fields are comma-separated, with direction encoded per field: ?sort=lastName,-createdAt sorts by lastName ascending, then createdAt descending. This convention is compact, URL-safe, and directly translatable to SQL ORDER BY clauses.

// ── JSON:API sort parameter examples ──────────────────────────────────
// Single field, ascending (default):
GET /api/users?sort=lastName
// => ORDER BY lastName ASC

// Single field, descending (minus prefix):
GET /api/users?sort=-createdAt
// => ORDER BY createdAt DESC

// Multi-field: lastName asc, then firstName asc, then createdAt desc:
GET /api/users?sort=lastName,firstName,-createdAt
// => ORDER BY lastName ASC, firstName ASC, createdAt DESC

// Plus prefix is also acceptable (explicit ascending):
GET /api/users?sort=+lastName,-createdAt
// => ORDER BY lastName ASC, createdAt DESC

// ── Parsing the sort parameter in TypeScript ───────────────────────────
type SortDirection = 'ASC' | 'DESC'

interface SortField {
  field: string
  direction: SortDirection
}

function parseSortParam(sortParam: string): SortField[] {
  if (!sortParam) return []

  return sortParam.split(',').map((token) => {
    const trimmed = token.trim()
    if (trimmed.startsWith('-')) {
      return { field: trimmed.slice(1), direction: 'DESC' }
    }
    if (trimmed.startsWith('+')) {
      return { field: trimmed.slice(1), direction: 'ASC' }
    }
    return { field: trimmed, direction: 'ASC' }
  })
}

// Usage:
parseSortParam('lastName,-createdAt')
// => [{ field: 'lastName', direction: 'ASC' }, { field: 'createdAt', direction: 'DESC' }]

// ── API response includes sort metadata ───────────────────────────────
{
  "data": [...],
  "meta": {
    "sort": "lastName,-createdAt",
    "total": 1250,
    "page": 1,
    "perPage": 20
  },
  "links": {
    "next": "/api/users?sort=lastName,-createdAt&page=2",
    "prev": null
  }
}

The minus-prefix convention is widely adopted beyond the strict JSON:API spec — Express, Fastify, Django REST Framework, Rails API, and most OpenAPI tooling all support it. Document in your API reference that: (1) fields are applied left to right (leftmost is highest priority), (2) unknown field names return HTTP 400, and (3) a missing sort parameter means the default ordering defined by the endpoint, which may not be stable across requests.

Stable vs Unstable Sorting for JSON APIs

Sort stability determines whether rows with equal sort key values maintain a consistent relative order across queries. If 500 users share status="active" and you sort only by status, an unstable sort can return different subsets on page 1 and page 2 — some rows are skipped, others appear twice. This is a real pagination correctness bug. The fix is always to append a stable tiebreaker field — a column with a unique value per row — as the final sort key.

// ── Problem: unstable sort causes pagination gaps ─────────────────────
// 500 rows share status='active'. Without a tiebreaker, the database
// can return them in any order — and that order may differ between
// page=1 and page=2, causing rows to be skipped or duplicated.

// WRONG — no tiebreaker, unstable across pages:
SELECT * FROM users
WHERE status = 'active'
ORDER BY status ASC          -- 500 rows tied, order undefined
LIMIT 20 OFFSET 0;           -- page 1: rows A-T
-- ...
LIMIT 20 OFFSET 20;          -- page 2: might include rows from page 1!

// ── Solution: always append id as the tiebreaker ──────────────────────
// CORRECT — id makes the sort total (unique position per row):
SELECT * FROM users
WHERE status = 'active'
ORDER BY status ASC, id ASC  -- id breaks all ties deterministically
LIMIT 20 OFFSET 0;           -- page 1: rows 1-20 by id
LIMIT 20 OFFSET 20;          -- page 2: rows 21-40 by id — no gaps

// ── TypeScript: inject tiebreaker in sort builder ─────────────────────
const ALLOWLIST = new Set(['lastName', 'firstName', 'createdAt', 'status', 'score'])
const TIEBREAKER = 'id'

function buildOrderBy(sortFields: SortField[]): string {
  const validated = sortFields
    .filter(({ field }) => ALLOWLIST.has(field))
    .slice(0, 3) // max 3 user-supplied fields

  // Ensure tiebreaker is appended unless user already requested id
  const hasTiebreaker = validated.some(({ field }) => field === TIEBREAKER)
  const allFields = hasTiebreaker
    ? validated
    : [...validated, { field: TIEBREAKER, direction: 'ASC' as const }]

  return allFields
    .map(({ field, direction }) => `${field} ${direction}`)
    .join(', ')
}

// buildOrderBy([{ field: 'lastName', direction: 'ASC' }])
// => 'lastName ASC, id ASC'

// buildOrderBy([{ field: 'status', direction: 'ASC' }, { field: 'createdAt', direction: 'DESC' }])
// => 'status ASC, createdAt DESC, id ASC'

// ── Why the tiebreaker must be unique ────────────────────────────────
// createdAt is NOT a good tiebreaker — many rows can share the same
// timestamp (especially batch imports). id (auto-increment or UUID)
// is unique by definition.
// For UUID primary keys, id ASC still works as a tiebreaker because
// UUIDs have a consistent lexicographic order within a single query.

Sort stability is especially critical for cursor-based pagination, where the cursor encodes the last-seen row's sort field values and id. Without a unique tiebreaker, the cursor cannot uniquely identify a position in the sort order, and the WHERE (sort_col, id) > (:cursor_val, :cursor_id) pattern cannot be applied. See Section 7 for the full cursor pagination implementation.

Multi-Field Sort Parameter Design

Multi-field sorting accepts ?sort=lastName,+firstName,-createdAt and translates it into a validated ORDER BY clause. Key design decisions: how many fields to allow (max 3 is the practical limit), how to handle URL encoding (commas in query strings should be percent-encoded as %2C but most frameworks parse them correctly), and how to document the precedence (leftmost field is primary sort).

// ── Complete multi-field sort handler (Express + Node.js) ─────────────
import express from 'express'
import { Pool } from 'pg'

const pool = new Pool({ connectionString: process.env.DATABASE_URL })
const app = express()

// Column allowlist — the ONLY columns users can sort by
const SORT_ALLOWLIST = new Set([
  'lastName', 'firstName', 'email', 'createdAt', 'score', 'status'
])
const MAX_SORT_FIELDS = 3

interface SortField {
  col: string       // validated column name from allowlist
  dir: 'ASC' | 'DESC'
}

function parseSortParam(raw: string | undefined): SortField[] {
  if (!raw) return [{ col: 'id', dir: 'ASC' }]

  const tokens = decodeURIComponent(raw).split(',').slice(0, MAX_SORT_FIELDS)
  const fields: SortField[] = []

  for (const token of tokens) {
    const trimmed = token.trim()
    const dir: 'ASC' | 'DESC' = trimmed.startsWith('-') ? 'DESC' : 'ASC'
    const name = trimmed.replace(/^[+-]/, '')

    if (!SORT_ALLOWLIST.has(name)) {
      throw Object.assign(new Error(`Invalid sort field: ${name}`), { status: 400 })
    }
    fields.push({ col: name, dir })
  }

  // Always append id tiebreaker if not already present
  if (!fields.some(f => f.col === 'id')) {
    fields.push({ col: 'id', dir: 'ASC' })
  }

  return fields
}

function buildOrderByClause(fields: SortField[]): string {
  // col is from allowlist (safe literal), dir is 'ASC' or 'DESC' (validated)
  return fields.map(({ col, dir }) => `"${col}" ${dir}`).join(', ')
}

app.get('/api/users', async (req, res) => {
  try {
    const sortFields = parseSortParam(req.query.sort as string)
    const orderBy = buildOrderByClause(sortFields)

    // orderBy contains only allowlisted column names — safe to interpolate
    const result = await pool.query(
      `SELECT id, "firstName", "lastName", email, "createdAt"
       FROM users
       ORDER BY ${orderBy}
       LIMIT $1 OFFSET $2`,
      [20, Number(req.query.offset ?? 0)]
    )

    res.json({
      data: result.rows,
      meta: { sort: req.query.sort ?? 'id', total: result.rowCount },
    })
  } catch (err: any) {
    res.status(err.status ?? 500).json({ error: err.message })
  }
})

// ── URL encoding notes ─────────────────────────────────────────────────
// ?sort=lastName,firstName     — comma is technically valid in query strings
// ?sort=lastName%2CfirstName   — percent-encoded comma (safer, always works)
// Both are correctly decoded by decodeURIComponent() above
// Limit: 3 user-specified fields + 1 server-appended tiebreaker = 4 total

URL encoding edge case: some proxies and API gateways strip or transform commas in query strings. If your infrastructure has this issue, offer an alternative syntax like ?sort[0]=lastName&sort[1]=-createdAt as a fallback, but keep the comma-separated form as the primary convention since it is more compact and aligns with JSON:API.

Preventing SQL Injection in Sort Parameters

Sort parameters are a common SQL injection vector because column names cannot be passed as bind parameters in most databases — only values can be parameterized. If you interpolate the sort field name from the query string directly into SQL, an attacker can send ?sort=1;DROP TABLE users;-- or ?sort=(SELECT password FROM admin LIMIT 1). The allowlist pattern completely prevents this.

// ── WRONG: direct interpolation — SQL injection vulnerability ─────────
app.get('/api/users', async (req, res) => {
  const sort = req.query.sort // user-supplied: "1;DROP TABLE users;--"
  // NEVER do this:
  await pool.query(`SELECT * FROM users ORDER BY ${sort}`)
  // SQL: SELECT * FROM users ORDER BY 1;DROP TABLE users;--
  // => Drops the users table! Or extracts data via subquery.
})

// ── CORRECT: allowlist + validated direction ────────────────────────────
const ALLOWED_COLUMNS = new Set(['lastName', 'firstName', 'createdAt', 'score'])

function safeOrderByClause(sortParam: string): string {
  const fields = sortParam.split(',').map(token => {
    const trimmed = token.trim()
    const isDesc = trimmed.startsWith('-')
    const colName = trimmed.replace(/^[+-]/, '')

    // Allowlist check — throws on unknown column name
    if (!ALLOWED_COLUMNS.has(colName)) {
      throw Object.assign(
        new Error(`Sort field not allowed: ${colName}`),
        { status: 400 }
      )
    }

    // colName is a known safe literal — safe to embed in SQL
    // dir is constrained to exactly 'ASC' or 'DESC' — never user-supplied text
    const dir: 'ASC' | 'DESC' = isDesc ? 'DESC' : 'ASC'
    return `"${colName}" ${dir}`
  })

  return fields.join(', ')
}

// ── Why column names cannot be parameterized ──────────────────────────
// In PostgreSQL, $1 in a query is always a VALUE placeholder.
// There is no mechanism to pass a column name as a bind parameter.
// This is a fundamental SQL design choice — table/column identifiers
// are resolved at query parse time, before bind parameters are applied.

// DOES NOT WORK — PostgreSQL will treat $1 as a value, not an identifier:
await pool.query('SELECT * FROM users ORDER BY $1 ASC', ['lastName'])
// => SELECT * FROM users ORDER BY 'lastName' ASC
// => orders by the string constant 'lastName', not the column — all rows equal

// ── Direction is safe to hardcode but not to interpolate from user input
// dir must be exactly 'ASC' or 'DESC' — never pass req.query.order directly
// WRONG:
const dir = req.query.order  // user sends "ASC; DROP TABLE users"
await pool.query(`SELECT * FROM users ORDER BY "lastName" ${dir}`)

// CORRECT:
const dir: 'ASC' | 'DESC' = req.query.order === 'desc' ? 'DESC' : 'ASC'
await pool.query(`SELECT * FROM users ORDER BY "lastName" ${dir}`)

// ── Additional defense: quote identifiers ──────────────────────────────
// Even for allowlisted column names, use double-quotes to prevent
// case-sensitivity issues and reserved-word collisions:
// "firstName" not firstName  (handles camelCase in PostgreSQL)
// "order" not order          (order is a reserved keyword)

The allowlist pattern is the only correct defense for sort parameter injection. Input sanitization (stripping special characters) is not sufficient — an attacker can use SQL keywords and identifiers that consist of only alphanumeric characters. Always reject unknown field names with HTTP 400, never silently ignore or substitute a default — silent fallback hides bugs in client code and makes API behavior unpredictable.

Database ORDER BY Performance

Without an index that matches the ORDER BY clause, the database performs a filesort — loading all matching rows into memory (or a temp file on disk), sorting them, then returning the top LIMIT rows. For large tables this is O(n log n) and can take seconds. With a matching index, the database reads rows in index order and stops after LIMIT rows — O(log n + limit), typically milliseconds.

-- ── Single-column sort index ──────────────────────────────────────────
CREATE INDEX idx_users_last_name     ON users (lastName);
CREATE INDEX idx_users_created_at    ON users (createdAt DESC);
CREATE INDEX idx_users_score_desc    ON users (score DESC);

-- ── Composite index for multi-field sort ──────────────────────────────
-- ORDER BY lastName ASC, firstName ASC
CREATE INDEX idx_users_name ON users (lastName, firstName);

-- ORDER BY lastName ASC, createdAt DESC
CREATE INDEX idx_users_name_date ON users (lastName ASC, createdAt DESC);

-- ── Leftmost prefix rule: composite index covers these orderings:
-- ORDER BY lastName                        -- YES (leading column)
-- ORDER BY lastName, firstName             -- YES (exact match)
-- ORDER BY lastName ASC, firstName ASC     -- YES
-- ORDER BY firstName                       -- NO  (firstName is not leftmost)
-- ORDER BY firstName, lastName             -- NO  (wrong order)

-- ── NULLS FIRST / NULLS LAST must match index definition ─────────────
-- PostgreSQL default: ASC sorts NULLS LAST, DESC sorts NULLS FIRST
-- If your ORDER BY uses non-default NULLS handling, match in index:
CREATE INDEX idx_users_score_nulls ON users (score DESC NULLS LAST);

-- Matching query:
SELECT * FROM users ORDER BY score DESC NULLS LAST LIMIT 20;
-- => Uses idx_users_score_nulls — no filesort

-- Non-matching (uses default NULLS FIRST with DESC):
SELECT * FROM users ORDER BY score DESC LIMIT 20;
-- => May not use idx_users_score_nulls — check EXPLAIN

-- ── Filter + sort: composite index with WHERE column first ────────────
-- Query: WHERE status = 'active' ORDER BY lastName
CREATE INDEX idx_users_status_name ON users (status, lastName);
-- Index covers the equality filter on status AND the sort on lastName.
-- An index on (lastName, status) would NOT help here — status is not leftmost.

-- ── Verify index usage with EXPLAIN ──────────────────────────────────
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, lastName, firstName FROM users
ORDER BY lastName ASC, firstName ASC
LIMIT 20;

-- With composite index (idx_users_name):
--   Limit  (cost=0.43..1.94 rows=20 width=40)
--     -> Index Scan using idx_users_name on users
--          (cost=0.43..9810.43 rows=100000 width=40)
-- No "Sort" node = index scan in sorted order, stops at LIMIT

-- Without index:
--   Limit  (cost=12355.43..12355.48 rows=20 width=40)
--     -> Sort  (cost=12355.43..12605.43 rows=100000 width=40)
--          Sort Key: last_name, first_name
--          -> Seq Scan on users  (cost=0.00..1540.00 rows=100000 width=40)
-- "Sort" node = filesort — reads and sorts all 100k rows before returning 20

A composite index on (status, lastName) is far more effective than separate single-column indexes when queries filter on status and sort on lastName — the database can use the index for both operations in one pass. Create indexes for the sort combinations that your allowlist exposes as valid API sort options, not arbitrary combinations.

Client-Side JSON Sorting

Client-side sorting is appropriate when all data is already loaded — for example, sorting a response from an endpoint that returns all items without pagination. Array.prototype.sort is stable in all modern environments (V8 7.0+ / Node.js 11+, Chrome 70+, Firefox 3+, Safari 10.1+). For string sorting, Intl.Collator provides locale-aware comparison — critical for names with accents, umlauts, and non-ASCII characters.

// ── Intl.Collator for locale-aware string sorting ─────────────────────
// Create once and reuse — instantiation is expensive
const collator = new Intl.Collator('en', {
  sensitivity: 'base',  // case-insensitive, accent-insensitive
  numeric: true,        // "item10" > "item9" (natural sort order)
  ignorePunctuation: false,
})

const users = [
  { id: 1, lastName: 'Ångström', firstName: 'Erik',  score: 9.2 },
  { id: 2, lastName: 'apple',    firstName: 'Jane',  score: 7.1 },
  { id: 3, lastName: 'Zebra',    firstName: 'Alice', score: 8.5 },
  { id: 4, lastName: 'apple',    firstName: 'Bob',   score: 6.0 },
]

// ── Sort by lastName ascending (locale-aware) ─────────────────────────
const byLastName = [...users].sort((a, b) =>
  collator.compare(a.lastName, b.lastName) || (a.id - b.id) // tiebreaker
)
// Ångström, apple (Bob), apple (Jane), Zebra
// Note: 'base' sensitivity makes Ångström ≈ angstrom, sorts near 'a'

// ── Multi-field sort: lastName asc, firstName asc ─────────────────────
const byNameMulti = [...users].sort((a, b) =>
  collator.compare(a.lastName, b.lastName) ||
  collator.compare(a.firstName, b.firstName) ||
  a.id - b.id  // stable tiebreaker
)
// apple/Bob, apple/Jane, Ångström/Erik, Zebra/Alice

// ── Numeric sort: score descending ────────────────────────────────────
const byScoreDesc = [...users].sort((a, b) =>
  b.score - a.score || a.id - b.id
)
// Ångström (9.2), Zebra (8.5), apple/Jane (7.1), apple/Bob (6.0)

// ── Date sort ─────────────────────────────────────────────────────────
const byDate = [...items].sort((a, b) =>
  new Date(a.createdAt).getTime() - new Date(b.createdAt).getTime()
)

// ── Generic multi-field sort from API sort param ──────────────────────
type JsonRecord = Record<string, unknown>

function sortJsonArray<T extends JsonRecord>(
  arr: T[],
  sortParam: string
): T[] {
  const fields = sortParam.split(',').map(token => {
    const trimmed = token.trim()
    return {
      key: trimmed.replace(/^[+-]/, ''),
      desc: trimmed.startsWith('-'),
    }
  })

  return [...arr].sort((a, b) => {
    for (const { key, desc } of fields) {
      const av = a[key]
      const bv = b[key]
      let cmp = 0

      if (typeof av === 'string' && typeof bv === 'string') {
        cmp = collator.compare(av, bv)
      } else if (typeof av === 'number' && typeof bv === 'number') {
        cmp = av - bv
      } else if (av instanceof Date && bv instanceof Date) {
        cmp = av.getTime() - bv.getTime()
      }
      // Handle null/undefined: nulls last
      if (av == null && bv != null) cmp = 1
      if (av != null && bv == null) cmp = -1

      if (cmp !== 0) return desc ? -cmp : cmp
    }
    return 0
  })
}

// Usage:
const sorted = sortJsonArray(users, 'lastName,-score')

// ── Sort stability in V8 (verification) ──────────────────────────────
// All elements with equal comparator output preserve insertion order.
// V8 uses TimSort (stable) since Node.js 11 / Chrome 70.
// Older environments (Node.js 10, IE11) used unstable QuickSort —
// if you support them, add id as a tiebreaker to guarantee stability.

A common mistake is using a.localeCompare(b) directly without options — this uses the system locale which varies across environments and can produce different sort orders on different servers or user browsers. Always instantiate Intl.Collator with an explicit locale string ('en', 'de', etc.) to guarantee consistent results. For locale selection based on user preference, use the Accept-Language header or a user setting.

Cursor Pagination with Sorted Results

OFFSET pagination has O(n) cost — fetching page 100 with OFFSET 2000 requires the database to read and discard 2,000 rows before returning 20. Cursor pagination uses a WHERE clause to skip to the next position in O(log n) using the index. With sorted results, the cursor must encode all sort field values plus the tiebreaker id to uniquely identify the last-seen row's position.

-- ── Single-field cursor: simple case ────────────────────────────────
-- Sort: ORDER BY id ASC
-- Cursor encodes: last id seen

-- First page:
SELECT id, lastName, firstName FROM users
ORDER BY id ASC
LIMIT 20;
-- Returns rows with id 1..20. Cursor = 20.

-- Next page:
SELECT id, lastName, firstName FROM users
WHERE id > 20           -- cursor condition
ORDER BY id ASC
LIMIT 20;
-- O(log n) using primary key index — does not read rows 1-20

-- ── Multi-field cursor: row-value comparison ──────────────────────────
-- Sort: ORDER BY lastName ASC, firstName ASC, id ASC
-- Cursor encodes: { lastName: 'Smith', firstName: 'Alice', id: 42 }

-- First page:
SELECT id, "lastName", "firstName" FROM users
ORDER BY "lastName" ASC, "firstName" ASC, id ASC
LIMIT 20;

-- Next page (row-value comparison — PostgreSQL, MySQL 8+, SQLite):
SELECT id, "lastName", "firstName" FROM users
WHERE ("lastName", "firstName", id) > ($1, $2, $3)
ORDER BY "lastName" ASC, "firstName" ASC, id ASC
LIMIT 20;
-- Bind: $1='Smith', $2='Alice', $3=42
-- Uses composite index on (lastName, firstName, id) — O(log n)

-- ── TypeScript: cursor encode/decode ─────────────────────────────────
interface SortCursor {
  lastName: string
  firstName: string
  id: number
}

function encodeCursor(row: SortCursor): string {
  return Buffer.from(JSON.stringify(row)).toString('base64url')
}

function decodeCursor(cursor: string): SortCursor {
  return JSON.parse(Buffer.from(cursor, 'base64url').toString('utf8'))
}

// ── API handler with cursor pagination ────────────────────────────────
app.get('/api/users', async (req, res) => {
  const limit = 20
  const cursorParam = req.query.cursor as string | undefined

  let rows: any[]

  if (!cursorParam) {
    // First page — no cursor condition
    const result = await pool.query(
      `SELECT id, "lastName", "firstName"
       FROM users
       ORDER BY "lastName" ASC, "firstName" ASC, id ASC
       LIMIT $1`,
      [limit + 1]  // fetch one extra to detect if there is a next page
    )
    rows = result.rows
  } else {
    const cursor = decodeCursor(cursorParam)
    const result = await pool.query(
      `SELECT id, "lastName", "firstName"
       FROM users
       WHERE ("lastName", "firstName", id) > ($1, $2, $3)
       ORDER BY "lastName" ASC, "firstName" ASC, id ASC
       LIMIT $4`,
      [cursor.lastName, cursor.firstName, cursor.id, limit + 1]
    )
    rows = result.rows
  }

  const hasNextPage = rows.length > limit
  const data = rows.slice(0, limit)
  const lastRow = data[data.length - 1]

  res.json({
    data,
    meta: {
      hasNextPage,
      nextCursor: hasNextPage ? encodeCursor(lastRow) : null,
    },
  })
})

-- ── Mixed directions: expand row-value to explicit OR tree ────────────
-- Sort: ORDER BY lastName ASC, createdAt DESC, id ASC
-- Row-value comparison does not support mixed ASC/DESC in all databases.
-- Expand manually:

WHERE
  "lastName" > $1                                              -- next lastName
  OR ("lastName" = $1 AND "createdAt" < $2)                   -- same lastName, earlier date
  OR ("lastName" = $1 AND "createdAt" = $2 AND id > $3)       -- same lastName+date, next id
ORDER BY "lastName" ASC, "createdAt" DESC, id ASC
-- Bind: $1=last_lastName, $2=last_createdAt, $3=last_id

Cursor pagination requires the sort to be total (every row has a unique position), which is why the tiebreaker id column is mandatory. The cursor is opaque to clients — encode it as Base64 so clients cannot manipulate sort values directly. Always validate decoded cursor values before using them in SQL: ensure id is an integer and string fields match expected length limits.

Key Terms

Sort Stability

A property of a sorting algorithm: if two elements compare as equal, a stable sort guarantees they appear in the same relative order as in the input. An unstable sort may reorder equal elements arbitrarily. In JavaScript, Array.prototype.sort is stable since V8 7.0 (Node.js 11+, Chrome 70+) — the ECMAScript 2019 spec mandated stability. In databases, ORDER BY without a unique tiebreaker is inherently unstable — equal rows may appear in different orders across executions or pages. For paginated APIs, instability causes rows to be skipped or duplicated across pages. The fix is always to append a unique column (e.g., id) as the final sort key, making the sort total and therefore stable.

Tiebreaker Field

A column appended to an ORDER BY clause to resolve ties — rows where all other sort fields are equal. The tiebreaker must have a unique value per row (typically the primary key: id, uuid) so the full sort expression assigns every row a unique position. Without a tiebreaker, rows with equal sort key values have undefined relative order, causing pagination to be incorrect: different pages may include the same row or skip rows. In cursor pagination, the tiebreaker is encoded in the cursor value, enabling the WHERE (sort_col, id) > (:cursor_val, :cursor_id) pattern. Never use createdAt as a tiebreaker — many rows can share the same timestamp (bulk inserts). Use id (auto-increment) or a UUID primary key.

Compound Index

A database index on two or more columns, also called a composite index or multi-column index. In B-tree indexes (used by PostgreSQL, MySQL, SQLite), the index entries are sorted first by the leftmost column, then by the second column within equal leftmost values, and so on. A compound index on (lastName, firstName) satisfies ORDER BY lastName and ORDER BY lastName, firstName without a filesort (leftmost prefix rule), but does NOT satisfy ORDER BY firstName alone. For sort queries that combine a WHERE filter with an ORDER BY, put the filter column first: (status, lastName) for WHERE status = 'active' ORDER BY lastName. The column order in the index definition is critical and must match the query's sort and filter pattern.

NULLS FIRST / NULLS LAST

SQL clauses that control where NULL values appear in an ordered result set. PostgreSQL default: ASC sorts NULLs last; DESC sorts NULLs first. MySQL default: NULLs sort as lower than any non-null value (first in ASC, last in DESC). SQLite: NULLs sort as lower than any other value. For consistent cross-database behavior, always specify NULLS FIRST or NULLS LAST explicitly. In PostgreSQL, the index must match: CREATE INDEX ON users (score DESC NULLS LAST) must pair with ORDER BY score DESC NULLS LAST or the index will not be used for the sort. For JSON APIs, define a fixed null-handling policy (e.g., nulls always last) and document it.

Collation

A set of rules for comparing and sorting text strings in a specific language and locale. Collation determines how accented characters (é, ü, ñ), case (A vs a), and Unicode code points are ordered. In databases: COLLATE "en-US-x-icu" in PostgreSQL, COLLATE utf8mb4_unicode_ci in MySQL. In JavaScript: new Intl.Collator('en', { sensitivity: 'base' }).compare(a, b). Without explicit collation, string sorting uses the default locale (which varies by server OS) or raw Unicode code points — producing incorrect results for non-ASCII text. For example, without locale-aware collation, "Zebra" sorts before "apple" (Z is Unicode 90, a is 97). Always specify collation explicitly in both the database schema and client-side sort functions to guarantee identical ordering.

Allowlist (Column Allowlist)

A set of known-safe values that an input must belong to before it is used in a privileged context. In the context of sort parameters, a column allowlist is a hardcoded set of permitted column names (e.g., new Set(['lastName', 'firstName', 'createdAt'])) that the server checks every user-supplied sort field against before constructing an ORDER BY clause. If the field is not in the allowlist, the request is rejected with HTTP 400. This prevents SQL injection because column names cannot be passed as bind parameters in SQL — they must be embedded as literals. The allowlist ensures only known-safe literals reach the SQL string. Allowlisting is strictly superior to denylisting (blacklisting) for this use case: it is impossible to enumerate all dangerous SQL fragments, but trivial to enumerate the 5–10 sortable columns in a table.

Cursor (Sort Cursor)

An opaque token returned by a paginated API that encodes the position of the last-seen row in the sorted result set. For cursor-based pagination, the next page request passes the cursor back, and the server uses it to construct a WHERE clause that starts immediately after that position. A simple cursor for ORDER BY id encodes just the last id. A compound cursor for ORDER BY lastName, id encodes both the last lastName value and the last id: {"lastName":"Smith","id":42}, Base64-encoded. The cursor is always opaque to clients (encoded as Base64 or signed JWT) to prevent manipulation. Cursor pagination is O(log n) — it uses the sort index to jump directly to the cursor position — whereas OFFSET pagination is O(n), reading and discarding all skipped rows.

FAQ