Sorting & Multi-Field Ordering

Part of the Query Patterns & Data Shaping Strategies reference. This page covers the full lifecycle of a sort contract: from the OpenAPI parameter definition, through server-side SQL mapping and offset-vs-cursor pagination stability, to type-safe SDK generation and CI enforcement.

Problem Framing

List endpoints without an explicit sort contract produce non-deterministic row sequences that shift whenever the storage engine reorganises pages, a replica falls behind, or a background vacuum runs. At paginated scale this causes duplicate records on page 2 and missing records on page 3 — bugs that only appear in production under concurrent writes. The solution is treating the sort parameter as a first-class contract element: schema-validated at the gateway, mapped to optimised SQL, always terminated with a unique tie-breaker, and enforced by CI tests on every pull request.

Spec Definition

Define sort as a repeatable query parameter. Using style: form with explode: true lets clients repeat the parameter key (?sort=priority:desc&sort=id:asc) without custom parsing on either side.

# openapi.yaml — valid OpenAPI 3.1.0
parameters:
  - name: sort
    in: query
    description: >
      Ordered list of sort directives. Each entry is field:direction.
      Append a unique-key directive last to guarantee deterministic pages.
    required: false
    style: form
    explode: true
    schema:
      type: array
      items:
        type: string
        pattern: '^[a-zA-Z0-9_]+:(asc|desc|nulls_first|nulls_last)$'
        example: created_at:desc
      maxItems: 5
      default: ["created_at:desc", "id:asc"]

The maxItems: 5 guard prevents clients from constructing arbitrarily complex ORDER BY trees that defeat the query planner. The default ensures that clients omitting sort still receive stable pages.

RFC / Standard Alignment

Implementation Walkthrough — Server Side

Step 1: Parse and validate the sort array

Validate the raw query values against the OpenAPI schema before they reach business logic. This Express middleware rejects unknown fields and unsupported directions with a structured error body following the RFC 7807 problem+json format.

// middleware/sortValidator.ts
import { Request, Response, NextFunction } from 'express';

const ALLOWED_FIELDS = new Set(['created_at', 'priority', 'status', 'name', 'id']);
const ALLOWED_DIRECTIONS = new Set(['asc', 'desc', 'nulls_first', 'nulls_last']);

export interface SortDirective {
  field: string;
  direction: 'asc' | 'desc' | 'nulls_first' | 'nulls_last';
}

export function parseSortParam(raw: string[]): SortDirective[] {
  return raw.map(entry => {
    const [field, direction] = entry.split(':');
    if (!ALLOWED_FIELDS.has(field)) {
      throw Object.assign(new Error(`Unsupported sort field: ${field}`), {
        status: 400, code: 'INVALID_SORT_FIELD',
      });
    }
    if (!ALLOWED_DIRECTIONS.has(direction)) {
      throw Object.assign(new Error(`Invalid direction: ${direction}`), {
        status: 400, code: 'INVALID_SORT_DIRECTION',
      });
    }
    return { field, direction: direction as SortDirective['direction'] };
  });
}

export function sortMiddleware(req: Request, res: Response, next: NextFunction) {
  try {
    const raw = ([] as string[]).concat(req.query.sort ?? []);
    req.sortDirectives = raw.length ? parseSortParam(raw) : [
      { field: 'created_at', direction: 'desc' },
      { field: 'id', direction: 'asc' },
    ];
    next();
  } catch (err: any) {
    res.status(err.status ?? 400).json({
      type: '/errors/invalid-sort-parameter',
      title: 'Invalid sort parameter',
      status: err.status ?? 400,
      detail: err.message,
      code: err.code,
    });
  }
}

Step 2: Map sort directives to SQL with explicit null handling

Never rely on the database engine’s default null ordering — it differs between PostgreSQL (NULLS LAST on ASC, NULLS FIRST on DESC) and MySQL (nulls sort low by default). Make it explicit at every call site.

// db/sortBuilder.ts
import { SortDirective } from '../middleware/sortValidator';

// Append id:asc if no unique tie-breaker is present
export function enforceTieBreaker(directives: SortDirective[]): SortDirective[] {
  const hasId = directives.some(d => d.field === 'id');
  return hasId ? directives : [...directives, { field: 'id', direction: 'asc' }];
}

// Build a Prisma-compatible orderBy array
export function toOrderBy(directives: SortDirective[]) {
  const tiebroken = enforceTieBreaker(directives);
  return tiebroken.map(({ field, direction }) => {
    if (direction === 'nulls_first') return { [field]: { sort: 'asc', nulls: 'first' } };
    if (direction === 'nulls_last')  return { [field]: { sort: 'asc', nulls: 'last'  } };
    return { [field]: direction };
  });
}

The equivalent raw SQL, useful when using a query builder that doesn’t abstract null ordering:

-- PostgreSQL: explicit null position on each ORDER BY column
SELECT id, priority, name, created_at
FROM   resources
WHERE  tenant_id = $1
ORDER BY
  priority DESC NULLS LAST,
  id       ASC  NULLS LAST;

Implementation Walkthrough — Client and CI Side

Step 3: Enforce composite index coverage

Multi-field sorts are only fast when the index column order matches the ORDER BY sequence. A composite index on (priority, id) serves ORDER BY priority DESC, id ASC in index-only scan mode; reversing the columns does not.

-- PostgreSQL composite index for the two most common sort patterns
CREATE INDEX CONCURRENTLY idx_resources_priority_id
  ON resources (priority DESC NULLS LAST, id ASC NULLS LAST)
  WHERE tenant_id IS NOT NULL;  -- partial index further reduces size

-- Verify the planner uses it
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT id, priority, created_at
FROM   resources
WHERE  tenant_id = 'acme'
ORDER BY priority DESC NULLS LAST, id ASC NULLS LAST
LIMIT 20;
-- Expected: "Index Scan using idx_resources_priority_id"
-- Reject if plan shows: "Sort" node or "Seq Scan"

Capture this plan output in CI and diff it on every migration:

# .github/workflows/query-plan-check.yml (excerpt)
- name: Capture sort query plan
  run: |
    psql "$DATABASE_URL" \
      -c "EXPLAIN (FORMAT JSON) SELECT id, priority FROM resources \
          ORDER BY priority DESC NULLS LAST, id ASC LIMIT 20;" \
      > plan_current.json
    diff plan_baseline.json plan_current.json || \
      (echo "Query plan changed — review index coverage" && exit 1)

Edge-Case Handling

Non-sequential primary keys (UUIDs): UUIDs are globally unique but their binary sort order differs from insertion order. Either supplement with a created_at timestamp before the id tie-breaker (created_at:desc, id:asc), or use a UUID v7 which encodes a millisecond timestamp in the first 48 bits, preserving insertion order while remaining globally unique.

Concurrent writes during pagination: A row inserted between page 1 and page 2 fetch will appear on page 2 if its sort position falls between the two cursors. This is expected behaviour for cursor pagination — document it in the API contract and advise clients that total-count fields may rise between pages.

Collation mismatches between environments: ORDER BY name ASC sorts differently on a staging database with en_US.UTF-8 collation vs a production database with C collation. Pin collation explicitly:

ORDER BY name COLLATE "en-US-x-icu" ASC

Or use a deterministic collation in PostgreSQL 15+ (DETERMINISTIC = true) to guarantee identical byte-for-byte ordering across environments.

Sort on computed/virtual columns: A sort on LOWER(name) bypasses a standard B-tree index. Create a functional index:

CREATE INDEX idx_resources_name_lower ON resources (LOWER(name) ASC);

Then expose the API field as name_lower or document that name:asc applies case-insensitive collation.

Validation and Testing Patterns

Spectral rule — enforce tie-breaker in default

# .spectral.yaml
rules:
  sort-default-must-include-id-tiebreaker:
    message: "Sort parameter default must end with an id:asc or id:desc directive."
    severity: error
    given: "$.components.parameters[?(@.name == 'sort')].schema.default"
    then:
      function: pattern
      functionOptions:
        match: '.*(id:(asc|desc))$'

Contract test — invalid field and direction combinations

{
  "request": {
    "method": "GET",
    "path": "/v1/resources",
    "query": { "sort": ["nonexistent_field:asc", "priority:sideways"] }
  },
  "response": {
    "status": 400,
    "body": {
      "type": "/errors/invalid-sort-parameter",
      "code": "INVALID_SORT_FIELD"
    }
  }
}

Run this with Pact or Dredd in a pre-merge CI step:

npx dredd openapi.yaml http://localhost:3000 \
  --only "GET /v1/resources > 400"

Pagination stability test

# tests/pagination/test_sort_stability.py
import httpx, pytest

BASE = "http://localhost:3000"

def fetch_all_ids(sort: str) -> list[str]:
    ids, cursor = [], None
    while True:
        params = {"sort": sort, "limit": 20}
        if cursor:
            params["cursor"] = cursor
        r = httpx.get(f"{BASE}/v1/resources", params=params)
        r.raise_for_status()
        body = r.json()
        ids.extend(item["id"] for item in body["data"])
        cursor = body.get("next_cursor")
        if not cursor:
            break
    return ids

def test_no_duplicate_ids_across_pages():
    ids = fetch_all_ids("priority:desc,id:asc")
    assert len(ids) == len(set(ids)), "Duplicate IDs detected — tie-breaker missing or cursor is wrong"

def test_stable_across_two_passes():
    assert fetch_all_ids("created_at:desc,id:asc") == fetch_all_ids("created_at:desc,id:asc")

SDK Generation Impact

The pattern constraint and enum alternatives in the OpenAPI spec flow directly into generated client code, surfacing as typed constants rather than free-form strings. This eliminates an entire class of runtime 400 errors.

TypeScript (openapi-generator typescript-axios + Zod)

// Generated enum — clients cannot pass arbitrary strings
export enum SortField {
  CreatedAt = 'created_at',
  Priority  = 'priority',
  Status    = 'status',
  Name      = 'name',
  Id        = 'id',
}

export enum SortDirection {
  Asc         = 'asc',
  Desc        = 'desc',
  NullsFirst  = 'nulls_first',
  NullsLast   = 'nulls_last',
}

// Usage — type error at compile time for unknown fields
const params: ListResourcesParams = {
  sort: [`${SortField.Priority}:${SortDirection.Desc}`, `${SortField.Id}:${SortDirection.Asc}`],
};

Python (openapi-python-client + Pydantic)

from enum import Enum
from pydantic import BaseModel
from typing import List
import httpx

class SortField(str, Enum):
    created_at = "created_at"
    priority   = "priority"
    status     = "status"
    id         = "id"

class SortDirection(str, Enum):
    asc         = "asc"
    desc        = "desc"
    nulls_first = "nulls_first"
    nulls_last  = "nulls_last"

def list_resources(
    sort: List[str] | None = None,
    base_url: str = "https://api.example.com",
) -> dict:
    params = {"sort": sort or ["created_at:desc", "id:asc"]}
    r = httpx.get(f"{base_url}/v1/resources", params=params)
    r.raise_for_status()
    return r.json()

# type-checked call
list_resources(sort=[
    f"{SortField.priority}:{SortDirection.desc}",
    f"{SortField.id}:{SortDirection.asc}",
])

Go (oapi-codegen)

type SortField string
const (
    SortFieldCreatedAt SortField = "created_at"
    SortFieldPriority  SortField = "priority"
    SortFieldID        SortField = "id"
)

type SortDirection string
const (
    SortAsc        SortDirection = "asc"
    SortDesc       SortDirection = "desc"
    SortNullsFirst SortDirection = "nulls_first"
    SortNullsLast  SortDirection = "nulls_last"
)

func SortParam(field SortField, dir SortDirection) string {
    return fmt.Sprintf("%s:%s", field, dir)
}

// Usage
params := url.Values{}
params.Add("sort", SortParam(SortFieldPriority, SortDesc))
params.Add("sort", SortParam(SortFieldID, SortAsc))

When a sort field is renamed or removed, bump the OpenAPI spec’s deprecated: true flag on the old field and run npx changeset version to emit a minor version bump in the generated SDK — downstream consumers receive a compiler warning rather than a silent runtime failure.

Anti-Patterns Quick Reference

FAQ

Related

• Query Patterns & Data Shaping Strategies — parent reference covering the full contract-first query architecture
• Offset vs Cursor Pagination — sort tie-breakers are essential for stable cursor tokens
• Advanced Filtering Operators — combine sort predicates with filter clauses for optimal composite index design
• Sparse Fieldsets & Projection — control which columns are returned alongside sorted results
• Building Efficient Multi-Column Sort Endpoints — production implementation patterns for high-throughput sorted list endpoints