Keep database errors out of Next.js API responses

A customer submits a form, the database rejects the write, and the API sends back relation "submissions" does not exist with a server file path. Support gets a useful clue, but so does every caller. The same mistake can expose SQL text, constraint names, hostnames, driver codes, table structure, or values copied into an exception.

The safe design keeps two records of one failure. The public response is small, stable, and deliberately boring. Restricted server telemetry keeps the original exception under a generated correlation ID. A credible Next.js API database error leakage testing strategy proves both sides without assuming that the framework, database client, or JavaScript serializer will redact the error for you.

Draw the public boundary before catching anything

Start with an allowlist. For an unexpected order-write failure, the client might be allowed to receive status 500, JSON content type, Cache-Control: no-store, a stable code, a generic message, and a correlation ID. Anything else is outside the contract. This is stronger than a list of forbidden words because a new database driver can introduce a field your regex has never seen.

The route boundary is the right place to enforce that contract. Next.js Route Handlers return standard Web Response objects. The Web API’s Response.json documentation confirms that the method creates a JSON response, sets the JSON content type, and accepts status and header options. None of that chooses which error fields are safe. Application code still chooses the data passed to it.

Do not return the caught value. Avoid Response.json(error), { ...error }, { error: error.message }, and serializer helpers that walk arbitrary properties. An Error instance may serialize to an empty object in one path, while a database-client object contains enumerable code, detail, query, or nested cause fields in another. An apparently safe test using only new Error("boom") can therefore miss the production leak.

Do not put the raw exception into a user-facing message either. “Unable to create order: duplicate key value violates unique constraint orders_reference_key” is still a leak even though it sits under a field named message. Public names do not make private values safe.

Catch at the smallest boundary that can make a useful decision. A route can catch errors from insertOrder because it knows the public operation was an order creation. It should not wrap authentication, JSON parsing, validation, lookup, and response serialization in one giant try block that converts everything to “database unavailable.” Broad catches destroy classification and make programmer errors look like expected infrastructure failures.

The other side of the boundary is server telemetry. Record a generated request ID, route or operation name, and the original error object. Do not log the authorization header, full request body, raw email, or complete URL by default. Database errors can themselves include values, so server logs still need access control, retention limits, transport security, and redaction. “Not in the response” is necessary, not sufficient.

A support-safe response should not claim more than the server knows. If the insert dependency threw an unknown value, return a generic 500. MDN describes 500 Internal Server Error as a generic server failure when a more specific 5xx status is not appropriate. Return 503 only when an owned lower layer has reliably classified a temporary unavailable condition. Guessing from substrings such as “timeout” at the route can misclassify unrelated failures.

There is also an outcome problem. A write can commit and the connection can fail before the client sees confirmation. A generic 500 does not prove that nothing was created. Retrying a non-idempotent request may duplicate work. The error boundary protects information, while an idempotency key or lookup contract protects business outcome. Test them separately.

Map known failures without serializing the exception

Use dependency injection so the failure can be reproduced without breaking a shared database. The handler factory below receives an insert function, a server-side reporter, and a request-ID generator. It validates a small request shape, invokes only the insert inside the protected block, and constructs the public object from literals.

The reporter receives the original unknown error, but the JSON response never reads a property from it. That structural rule is easy to review. A later driver upgrade can change exception fields without changing the public response.

type CreateOrder = {
  sku: string;
  quantity: number;
};

type CreatedOrder = {
  id: string;
};

type ErrorContext = {
  operation: "order.create";
  requestId: string;
  error: unknown;
};

type Dependencies = {
  insertOrder(input: CreateOrder): Promise<CreatedOrder>;
  // Application wiring must provide a non-throwing reporter.
  reportError(context: ErrorContext): void;
  newRequestId(): string;
};

function isCreateOrder(value: unknown): value is CreateOrder {
  if (typeof value !== "object" || value === null) {
    return false;
  }
  const candidate = value as Record<string, unknown>;
  return (
    typeof candidate.sku === "string" &&
    candidate.sku.length > 0 &&
    Number.isInteger(candidate.quantity) &&
    (candidate.quantity as number) > 0
  );
}

export function createPostOrder(deps: Dependencies) {
  return async function POST(request: Request): Promise<Response> {
    let input: unknown;
    try {
      input = await request.json();
    } catch {
      return Response.json(
        { error: { code: "INVALID_JSON", message: "Invalid JSON body" } },
        { status: 400 },
      );
    }

    if (!isCreateOrder(input)) {
      return Response.json(
        { error: { code: "INVALID_ORDER", message: "Invalid order" } },
        { status: 400 },
      );
    }

    const requestId = deps.newRequestId();
    try {
      const created = await deps.insertOrder(input);
      return Response.json({ order: created }, { status: 201 });
    } catch (error: unknown) {
      deps.reportError({
        operation: "order.create",
        requestId,
        error,
      });
      return Response.json(
        {
          error: {
            code: "ORDER_CREATE_FAILED",
            message: "Unable to create order",
            requestId,
          },
        },
        {
          status: 500,
          headers: { "Cache-Control": "no-store" },
        },
      );
    }
  };
}

The production route can wire newRequestId to () => crypto.randomUUID(), the insert dependency to its repository, and reportError to the application's structured logger. The request ID should be generated by the server. A caller-supplied x-request-id may be preserved as a separate upstream identifier after validation, but it should not be allowed to forge the only correlation key.

Expected business conflicts need a different shape before they reach the route. Suppose duplicate client references are a normal user-correctable condition. The repository adapter can catch the database-specific failure it understands, based on the exact installed driver and tested behavior, then return a domain result such as { ok: false, reason: "duplicate_reference" }. The route maps that reason to 409 and a public code. It should not search arbitrary error.message text for “duplicate.”

That mapping costs code and maintenance. Every recognized database condition becomes part of the adapter’s contract and needs a test with a realistic driver error or integration fixture. Map only cases that change client behavior. Leaving an unknown failure as a generic 500 is safer than assigning a confident but wrong status.

Worked example one is a missing table after a bad migration. The raw server error can identify the relation and SQL position for the database owner. The public response remains ORDER_CREATE_FAILED with a request ID. Returning 404 would be wrong because the route exists and the lookup did not establish absence.

Worked example two is a duplicate external reference. If the adapter has verified and translated the relevant constraint into duplicate_reference, the route can return 409 without exposing the constraint name. The client can ask the user to refresh or reuse the existing record. An unexpected constraint on a different column stays 500 because it may represent a defect.

Worked example three is a connection failure with an ambiguous write outcome. The route returns a safe 500 unless the repository has a stronger classification. The client does not automatically retry. Support uses the request ID to inspect whether a row was committed before deciding on recovery.

Serialization itself can fail after the database operation succeeds. A response object containing a bigint, circular value, or other unsupported data may throw while JSON is being created. That is not a database failure even if the data originated in a row. Keep database reads, domain conversion, and response construction as named boundaries. Test the DTO conversion with representative typed values, and do not wrap the serializer inside a catch that labels every exception ORDER_CREATE_FAILED.

Logging can fail too. A synchronous reporter that throws inside the catch block may prevent the safe response from being returned. Define whether the logger is best-effort or response-critical, then test that policy. In most APIs, telemetry transport should not replace the client response. A logger can accept the event synchronously into an owned queue while delivery happens elsewhere, but the exact guarantee depends on the logging system. Do not claim persistence merely because reportError returned.

These examples share redaction, not status. Treating all database-related exceptions as one category would erase the business distinction that callers need.

Write a test that can fail when one field leaks

The primary assertion should compare the entire public object. If a developer later adds detail: error.message, stack, cause, or debug, exact equality fails. Then assert the exact status, content type, cache header, and reporter call. A forbidden-pattern scan is useful after that, but it is not the main oracle.

Build a realistic private error. A plain Error has different enumerable properties from many library errors. Add fields that resemble the categories you want to block: driver code, detail, query, table, and a nested cause. These values are deliberately synthetic test data, not claimed output from a specific database version. Their purpose is to prove that arbitrary private properties cannot cross the boundary.

The Vitest test below calls the handler directly with an injected failing repository. No real database is needed, so the failure is deterministic. The route receives the same Web Request it would handle in the application.

import { describe, expect, it, vi } from "vitest";
import { createPostOrder } from "./post-order";

describe("POST order database failure boundary", () => {
  it("returns only the allowed public fields and reports the original error", async () => {
    const privateFailure = Object.assign(
      new Error('relation "orders" does not exist'),
      {
        code: "PRIVATE_TEST_CODE",
        detail: "private customer reference test-ref-914",
        query: "insert into orders values (...)",
        table: "orders",
        cause: { host: "db.internal.test" },
      },
    );
    const reportError = vi.fn();
    const handler = createPostOrder({
      insertOrder: vi.fn().mockRejectedValue(privateFailure),
      reportError,
      newRequestId: () => "req-test-7d2",
    });

    const response = await handler(
      new Request("http://test.local/api/orders", {
        method: "POST",
        headers: { "Content-Type": "application/json" },
        body: JSON.stringify({ sku: "qa-book", quantity: 1 }),
      }),
    );
    const body = await response.json();

    expect(response.status).toBe(500);
    expect(response.headers.get("content-type")).toContain("application/json");
    expect(response.headers.get("cache-control")).toBe("no-store");
    expect(body).toEqual({
      error: {
        code: "ORDER_CREATE_FAILED",
        message: "Unable to create order",
        requestId: "req-test-7d2",
      },
    });
    expect(reportError).toHaveBeenCalledOnce();
    expect(reportError).toHaveBeenCalledWith({
      operation: "order.create",
      requestId: "req-test-7d2",
      error: privateFailure,
    });
  });
});

This test can fail when application code changes. If the catch block returns { ...error }, adds error.message, or includes privateFailure.code, the exact body differs. If the reporter receives a newly constructed generic error instead of the original object, the final assertion differs. If the response status becomes 404, the status assertion fails.

Add a second case where the repository rejects with a string or plain object. JavaScript permits throwing any value, even though throwing Error instances is better practice. The route should still report that value as unknown and return the same safe object. This closes the gap where code uses error instanceof Error ? safe : error and accidentally serializes the non-Error branch.

Mutation testing is especially valuable here even without a mutation tool. Temporarily add detail: String(error) to the response in a local branch and confirm the exact-body test fails. Then add an enumerable field to the synthetic failure and confirm no test snapshot needs manual approval. This exercise proves the oracle observes the dangerous code path rather than a nearby validation response.

Add separate tests for invalid JSON and invalid order data. They should assert that insertOrder and reportError were not called. Those cases prove the broad catch has not swallowed client errors. They also prevent a future refactor from logging a malformed request as a database outage.

Do not assert only expect(bodyText).not.toContain("relation"). A leak could contain a hostname, query, stack, constraint, or value without that word. Conversely, a legitimate public message might mention a relation in a different product domain. An allowed-key assertion has much better coverage.

For a deployed test, create a controlled failure seam below the route. A disposable test database can be unavailable, a repository fake can reject, or a known test-only adapter can return an error. Never expose a public “make the database fail” header in production. Compile or configure the seam so production cannot enable it accidentally, and test that restriction as part of deployment.

Distinguish a leak from an HTML error page or proxy response

A body containing SQL text is not automatically proof that the route serialized the database exception. An upstream development proxy, generic error page, hosting platform, or reverse proxy can generate a response after the process crashes. The leak is still serious, but the owner and fix differ. Capture the final status, content type, response URL, headers, and raw body before deciding which layer produced it.

Playwright’s APIResponse reference exposes status(), headersArray(), text(), and url(). Read the text once and attach it to the test report before parsing. If the content type is HTML, preserve it as text. Do not call json() first and lose the clearest failure signal when parsing throws.

A 200 HTML login page usually means authentication redirected and the client followed the redirect. A 500 HTML page can be framework or proxy handling. A 502 or 504 points toward a gateway boundary. A connection refusal means there is no HTTP response to inspect. None of these should pass a JSON leak test merely because the expected public code is absent.

Correlate server logs through the response request ID when one exists. The safe handler above always creates an ID before the insert call and returns it on that catch path. If the response has no ID and the application log has no matching route event, investigate upstream layers. If the application recorded the failure and response construction under the ID, inspect the serialized object and any middleware that transforms it.

The following shell diagnostic checks a response after the test environment has already arranged a controlled repository failure. It does not create the failure. API_PATH and REQUEST_BODY_FILE must point to the owned test case. The script saves headers and body, checks the exact allowed JSON keys with jq, then runs a secondary pattern scan.

#!/usr/bin/env bash
set -euo pipefail

: "${API_BASE_URL:?API_BASE_URL is required}"
: "${API_PATH:?API_PATH is required}"
: "${REQUEST_BODY_FILE:?REQUEST_BODY_FILE is required}"
: "${TEST_API_TOKEN:?TEST_API_TOKEN is required}"

artifact_dir="${ARTIFACT_DIR:-artifacts/database-boundary}"
mkdir -p "$artifact_dir"
headers_file="$artifact_dir/response-headers.txt"
body_file="$artifact_dir/response-body.json"

status="$(
  curl --silent --show-error \
    --dump-header "$headers_file" \
    --output "$body_file" \
    --write-out '%{http_code}' \
    --request POST \
    --header 'Content-Type: application/json' \
    --header "Authorization: Bearer $TEST_API_TOKEN" \
    --data-binary "@$REQUEST_BODY_FILE" \
    "$API_BASE_URL$API_PATH"
)"

[[ "$status" == "500" ]]
grep -i '^content-type:.*application/json' "$headers_file" > /dev/null

jq -e '
  (keys == ["error"]) and
  (.error | keys == ["code", "message", "requestId"]) and
  (.error.code == "ORDER_CREATE_FAILED") and
  (.error.message == "Unable to create order") and
  (.error.requestId | type == "string" and length > 0)
' "$body_file" > /dev/null

if grep -Eiq \
  'sqlstate|postgres|constraint|relation|node_modules|select[[:space:]]|insert[[:space:]]' \
  "$body_file"; then
  echo "Private database detail found in the public response" >&2
  exit 1
fi

The exact-key check is the real boundary assertion. The regex adds recognizable tripwires but cannot establish completeness. Keep the raw response as an artifact only in a restricted test environment, because a failing version of the application may put sensitive data in that file.

The script must fail when curl cannot reach the service. Treating a missing response as a clean body would create an oracle that cannot distinguish safety from absence. It must also fail when jq cannot parse the body. An HTML error page is not a safe JSON response even if none of the database words match. The handler example additionally requires a non-throwing reporter wrapper; test that contract so a telemetry outage cannot replace the safe response.

One near-miss deserves special attention: the server log contains a full database stack, but the HTTP body is safe. That is not an API leakage failure. It may still be a logging-security issue, especially if logs are broadly accessible or shipped to third parties. File it under the correct boundary and do not weaken route diagnostics just to make an API test stop seeing restricted telemetry it should never have accessed.

Another near-miss is a client library printing its own request object beside the response. The terminal then contains a database-looking fixture value that never came from the server. Compare the saved raw body bytes with the reporter output. Only the response artifact proves what crossed the HTTP boundary. Test runners, curl verbose mode, and application logs can add surrounding text that belongs to a different channel.

Compression and content negotiation can also confuse manual inspection. Let the HTTP client decode the response normally, then assert the decoded content type and JSON shape. Do not search a binary artifact and declare it clean because the words are not visible. Conversely, do not treat a proxy-generated diagnostic header as a JSON-body leak; classify that header separately and remove it if it exposes private infrastructure.

Roll the fix through an existing API without blinding support

Inventory every catch block that returns an exception, message, cause, query, or spread object. Search for Response.json, NextResponse.json, error.message, JSON.stringify(error), and response helpers that accept unknown. Review middleware and shared error formatters too. A safe route can still leak if a wrapper replaces its response.

Define one public error schema per API family. Keep stable machine codes, generic messages, and optional request IDs. Decide whether clients need a support message separate from a display message. Avoid a universal details field typed as unknown; it becomes the easiest place to put raw validation and database objects.

Add the injected failure test before changing the implementation. Confirm it fails against the leaking route for the right reason, then make the smallest boundary change. A test that is green before the fix may be exercising a plain Error that serializes differently, a validation branch that never reaches the database, or a mock rejected outside the route’s catch.

Ship the safe response and correlation logging together. Redaction without correlation leaves support blind. Correlation without log controls moves the leak to a different audience. Verify that one request ID appears in the response and the owned server event, and that the event records the original error without the raw request body.

Exercise one real deployed failure before making the new contract a release gate. Confirm which component generates the response, where the restricted event lands, and how an on-call engineer resolves the request ID. A unit test proves construction logic; this rehearsal proves the operational path exists. Record only the observed result from that environment, not a projected detection rate.

Migrate clients that parse raw messages. That dependency is already fragile and unsafe. Introduce stable codes, update clients to branch on those codes, and monitor unknown-code handling. Do not keep the private message for “backward compatibility.” If a client needs to distinguish a duplicate reference, add a verified domain mapping rather than exposing the database phrase it happens to recognize.

Run direct handler tests on every relevant change. Add a smaller deployed-mode test to catch middleware, framework, and proxy transformations. The CI example installs pnpm before asking setup-node to use the pnpm cache, then runs the focused Vitest and Playwright files. The paths are examples for a project that has created those tests.

name: api-error-boundary

on:
  pull_request:
  workflow_dispatch:

jobs:
  test:
    runs-on: ubuntu-latest
    timeout-minutes: 20
    steps:
      - uses: actions/checkout@v4
      - uses: pnpm/action-setup@v4
        with:
          version: 10
          run_install: false
      - uses: actions/setup-node@v4
        with:
          node-version: 22
          cache: pnpm
      - run: pnpm install --frozen-lockfile
      - name: Test the injected repository failure
        run: pnpm exec vitest run tests/api/post-order-error.test.ts
      - name: Test the deployed public response
        run: pnpm exec playwright test tests/api/order-error-boundary.spec.ts
      - name: Preserve public test evidence
        if: failure()
        uses: actions/upload-artifact@v4
        with:
          name: api-error-boundary-report
          path: |
            playwright-report
            test-results

Do not upload unrestricted server logs with the public test report. Send them to the logging system with the request ID, access policy, and retention policy already applied. The Playwright report should contain only data a client was allowed to receive, although a failing leak test may temporarily capture the unsafe body in a restricted CI project.

Roll out by endpoint risk. Start with writes, authentication-adjacent routes, and queries involving customer data. Then update shared helpers and read routes. Track actual failing cases from tests and production telemetry. Do not claim a leakage rate or improvement percentage unless those events were measured from a defined population.

The cost is reduced client detail and more server-side observability work. Support cannot diagnose from a screenshot of the response alone. Engineers need log access or a tool that resolves the request ID. That is intentional. Public debugging convenience is not worth disclosing database structure, and restricted diagnostics must be designed rather than improvised.

Know when redaction is not the same as error handling

Do not convert every exception to 500. Invalid JSON and validation failures should retain their client-error contracts. Authentication and authorization failures should stop before the database operation where possible. Expected conflicts should use verified domain mappings. The redaction rule is that private implementation details never cross the boundary, not that every branch looks identical.

Avoid regex replacement on raw messages as the primary fix. Replacing “postgres” or a table name still leaves query fragments, hostnames, values, driver fields, stack frames, and future formats. Construct the public object from allowed literals and domain data. Redaction is much safer when there is nothing private in the input to the serializer.

Do not swallow the server error. An empty catch that returns a generic response protects the client but destroys diagnosis and alerting. Report the original error once with operation and correlation context. Avoid logging it again at every layer, which creates duplicates and increases exposure.

Skip database-specific mapping when the installed client’s behavior has not been verified. A code copied from another driver or version can turn an internal defect into a user-correctable conflict. Keep the route generic until the adapter has a tested classifier based on official documentation, type definitions, or observed integration behavior.

Do not expose a production fault switch for end-to-end testing. Header-based failure injection is convenient but dangerous if a proxy, user, or attacker can reach it. Prefer dependency injection in direct tests, disposable infrastructure in integration tests, or a build variant that production cannot enable.

Finally, do not mistake a clean HTTP response for a secure system. Server logs, traces, metrics labels, analytics events, and CI artifacts can all capture the same exception. This Next.js API database error leakage testing work covers the public route boundary. Give each internal channel its own allowlist, access policy, and retention rule so the diagnostic record remains useful without becoming a second leak.