Pagination, Search, Filtering, and URL State

List screens are where database applications become useful: invoices, customers, tickets, orders, posts, products, and audit records all need to be searched, filtered, sorted, paged, shared, and refreshed without losing context. In a Next.js App Router application, the URL is the best home for that state when the state describes what the page is showing rather than private draft input. A URL such as /dashboard/invoices?query=acme&status=paid&page=2 is reloadable, bookmarkable, shareable, and directly usable by a Server Component that queries the database.

The outcome of this lesson is a precise mental model for URL-driven list views. You will parse searchParams, normalize unsafe user input, build stable links, reset page numbers when filters change, and understand how pagination strategy affects correctness and performance. This belongs in the database application section because list state is not just a UI concern: every query string choice eventually becomes a database predicate, an index requirement, a cache key, or a failure mode.

How URL State Drives Rendering

In the App Router, a page receives URL query parameters through the searchParams prop. Those values are strings, arrays of strings, or absent. They are not typed, trusted, or guaranteed to be meaningful. The page should convert them into a small, explicit query model before touching the database: query for text search, status for a finite filter, page for a positive integer, and often sort for a controlled order.

The important internal sequence is: browser requests a URL, the route segment is matched, the Server Component receives searchParams, the application validates and normalizes those parameters, data fetching uses the normalized model, and the rendered result includes links or client controls that create the next URL. If a user changes the search box from acme to zen, the application should navigate to a different URL rather than keep invisible list state in memory. Because that URL changes, the server can render the correct result on refresh and another user can open the same view.

Server Components are a strong fit for the data portion because credentials and SQL construction stay on the server. Client Components are useful for controls that respond to typing, buttons, and select boxes. The boundary is usually: the client reads the current URL, creates the next URL, and calls router.replace or renders a Link; the server parses the URL and fetches rows.

Parameter Anatomy

A useful list URL uses a small vocabulary. query is a trimmed text term. Empty strings should usually be removed so that ?query= behaves like no search. page is one-based for humans, even if your SQL offset is zero-based. status, category, or role should be allow-listed. sort should map to known database columns rather than accepting raw column names.

The first layer is normalization. This function accepts untrusted values and returns the only shape the rest of the page is allowed to use.

function parseInvoiceParams(searchParams) {
  const statuses = new Set(["all", "draft", "pending", "paid"]);
  const rawQuery = typeof searchParams.query === "string" ? searchParams.query : "";
  const query = rawQuery.trim().slice(0, 80);

  const rawStatus = typeof searchParams.status === "string" ? searchParams.status : "all";
  const status = statuses.has(rawStatus) ? rawStatus : "all";

  const rawPage = Number.parseInt(String(searchParams.page ?? "1"), 10);
  const page = Number.isFinite(rawPage) && rawPage > 0 ? rawPage : 1;

  return { query, status, page, pageSize: 10 };
}

console.log(parseInvoiceParams({ query: "  acme  ", status: "paid", page: "3" }));
console.log(parseInvoiceParams({ query: "  ", status: "deleted", page: "-9" }));

The deterministic output is { query: "acme", status: "paid", page: 3, pageSize: 10 } for the first call and { query: "", status: "all", page: 1, pageSize: 10 } for the second. The database layer never sees deleted as a status, a negative offset, or an unbounded search term.

Worked Example: Query Construction

After normalization, build database predicates from structured values. The exact database library may differ, but the rule is the same: parameterize values and map URL options to known predicates. Do not concatenate untrusted search text into SQL.

function buildInvoiceQuery({ query, status, page, pageSize }) {
  const where = [];
  const values = [];

  if (query) {
    values.push(`%${query.toLowerCase()}%`);
    where.push(`lower(customer_name) like $${values.length}`);
  }

  if (status !== "all") {
    values.push(status);
    where.push(`status = $${values.length}`);
  }

  values.push(pageSize);
  const limitRef = `$${values.length}`;
  values.push((page - 1) * pageSize);
  const offsetRef = `$${values.length}`;

  const whereSql = where.length ? `where ${where.join(" and ")}` : "";
  return {
    text: `select id, customer_name, status, total from invoices ${whereSql} order by created_at desc limit ${limitRef} offset ${offsetRef}`,
    values,
  };
}

console.log(buildInvoiceQuery({ query: "acme", status: "paid", page: 2, pageSize: 10 }));

The output uses placeholders for %acme%, paid, 10, and 10. Page two starts at offset ten because page one owns rows zero through nine. This example demonstrates offset pagination, which is easy to implement and works well for moderate result sets. Its weakness is that deep pages become slower on many databases, and rows can shift between pages if new records are inserted while the user navigates.

Worked Example: Next.js Page

A Server Component page can parse the URL, fetch rows, fetch a count, and render stable navigation links. The fragment below assumes fetchInvoices and countInvoices run on the server.

export default async function InvoicesPage({ searchParams }) {
  const params = parseInvoiceParams(searchParams);
  const [rows, total] = await Promise.all([
    fetchInvoices(params),
    countInvoices(params),
  ]);
  const totalPages = Math.max(1, Math.ceil(total / params.pageSize));

  return (
    <main>
      <InvoiceSearch defaultValue={params.query} status={params.status} />
      <InvoiceTable rows={rows} />
      <Pagination page={params.page} totalPages={totalPages} query={params.query} status={params.status} />
    </main>
  );
}

The expected behavior is straightforward. Loading ?query=acme&status=paid&page=2 renders paid Acme invoices from the second page of the normalized result. Refreshing the browser shows the same rows. Sending the URL to a teammate sends the same view, subject to that teammate’s authorization.

Worked Example: Client Controls

Search inputs are often Client Components because typing is interactive. The control should update URL parameters without storing the canonical list state in React component state. When a search or filter changes, reset page to 1; otherwise the old page may point beyond the filtered result set.

"use client";

import { usePathname, useRouter, useSearchParams } from "next/navigation";

export function InvoiceSearch({ defaultValue, status }) {
  const router = useRouter();
  const pathname = usePathname();
  const searchParams = useSearchParams();

  function update(nextQuery, nextStatus = status) {
    const params = new URLSearchParams(searchParams);
    nextQuery.trim() ? params.set("query", nextQuery.trim()) : params.delete("query");
    nextStatus === "all" ? params.delete("status") : params.set("status", nextStatus);
    params.set("page", "1");
    router.replace(`${pathname}?${params.toString()}`);
  }

  return (
    <form>
      <input defaultValue={defaultValue} onChange={(event) => update(event.target.value)} />
      <select defaultValue={status} onChange={(event) => update(defaultValue, event.target.value)}>
        <option value="all">All</option>
        <option value="draft">Draft</option>
        <option value="pending">Pending</option>
        <option value="paid">Paid</option>
      </select>
    </form>
  );
}

For a production search box, debounce the replace call or submit on Enter so that fast typing does not create excessive navigations. Use replace rather than push for each keystroke so the Back button does not replay every intermediate character.

Design Choices and Trade-offs

Offset pagination is simple and supports numbered pages. It pairs naturally with count(*), but counting can be expensive on large filtered tables and deep offsets can degrade. Cursor pagination uses a stable marker such as created_at plus id. It is better for infinite scroll and high-volume feeds, but it does not naturally answer “go to page 17” and requires careful ordering.

Server-side filtering keeps secrets, permissions, and database access off the browser. Client-side filtering can be acceptable only when the complete authorized dataset is already safe and small. If you download 10,000 invoices so the browser can filter them, you have changed the security and performance shape of the feature.

URL state is ideal for shareable view state, not for sensitive values. Do not put access tokens, private notes, or personally sensitive search terms in query strings if they may leak through browser history, logs, analytics, or referrers. For ordinary administrative filters, concise URL state is usually the right trade-off.

Failure Modes and Troubleshooting

Symptom: changing the filter shows an empty table even though matching rows exist. Cause: the user was on page seven, and the new filtered result has only one page. Diagnose: inspect the URL and compare page with the total page count returned by the server. Correct: reset page to 1 whenever search text, status, category, or sort changes.

Symptom: search works locally but is slow in staging. Cause: the predicate cannot use an index, or lower(column) like '%term%' scans too much data. Diagnose: run an explain plan with representative rows and compare it with the indexes available. Correct: add an index that matches the query pattern, adopt database full-text search where appropriate, or limit search to prefix matching if that satisfies the product need.

Symptom: users see rows they should not see when opening a shared URL. Cause: the URL identifies filters, but authorization was applied only in the UI or only to the original sharer. Diagnose: test the same URL as a lower-privilege user and inspect the server query predicates. Correct: add tenant, account, or ownership predicates inside the server data query, independent of visible filters.

Symptom: pagination skips or duplicates rows while new invoices arrive. Cause: offset pagination over a changing result set. Diagnose: reproduce by inserting a newer row between page requests. Correct: use a deterministic order with a tie-breaker, such as created_at desc, id desc, and consider cursor pagination for feeds where inserts are frequent.

Hands-on Lab

Prerequisites: a Next.js App Router project, a database table or mocked data source named invoices, and permission to add one route under app/dashboard/invoices. Start with a small seed set containing at least twenty invoices across draft, pending, and paid.

  1. Create a parser like parseInvoiceParams and unit test valid, missing, invalid, and negative page values.
  2. Implement a server data function that accepts only the normalized model and returns rows plus a total count.
  3. Create the page component that reads searchParams, fetches rows and count, and renders the table.
  4. Add search and status controls that update the URL and reset page to 1.
  5. Add previous and next links that preserve query and status while changing only page.

Verify the lab by opening /dashboard/invoices?query=acme&status=paid&page=1, refreshing the browser, and confirming that the same filter remains active. Then change the status and confirm that the URL contains page=1. Test cleanup by removing the lab route and seed data or rolling back the migration used to create the sample table.

Assessment Exercises

  1. Given a URL with ?query=%20%20&status=archived&page=0, write the normalized query model your page should use and explain why.
  2. Modify the query builder so users can sort by newest or largest invoice total without accepting raw column names from the URL.
  3. Explain when you would replace offset pagination with cursor pagination for an invoice list, and what the cursor would need to contain.
  4. Design a test that proves a shared filtered URL does not bypass tenant authorization.
  5. Find the bug in a search control that changes query but leaves page untouched, then describe the user-visible failure.

Summary

Pagination, search, filtering, and URL state work well in Next.js when the URL describes the visible list, the server turns untrusted strings into a normalized query model, and the database receives parameterized predicates with explicit limits. Use Server Components for privileged data fetching, Client Components for interactive controls, and stable URL construction for shareable navigation. The key trade-offs are offset versus cursor pagination, server-side versus client-side filtering, and convenience versus privacy when choosing what belongs in the query string.