PostgreSQL Schema and Database Access

PostgreSQL gives a Next.js application a durable model for state: tables describe what exists, constraints describe what must always be true, and queries move data between server code and the database. The outcome of this lesson is a practical pattern for designing a small schema, reaching it only from server-side Next.js code, and verifying that the behavior is correct under normal use and common failures.

In a full-stack Next.js app, the database layer should sit behind Server Components, Server Actions, Route Handlers, or dedicated server-only modules. Browser code should never receive database credentials or construct SQL. The user interface can ask for data or submit a mutation, but the trusted work of validation, authorization, querying, and transaction control belongs on the server.

How PostgreSQL Organizes Application Data

A PostgreSQL database contains schemas, and a schema contains objects such as tables, indexes, views, functions, and types. Many applications use the default public schema, but larger systems often create named schemas to separate application data, analytics, audit records, or extensions. In ordinary conversation, developers also use the word schema to mean the full shape of the data model: tables, columns, relationships, constraints, and indexes.

Tables store rows. Columns define the type and nullability of each attribute. Primary keys identify one row. Foreign keys connect rows across tables and let PostgreSQL reject references to records that do not exist. Unique constraints prevent duplicates. Check constraints enforce local rules such as nonnegative quantities or valid state names. Indexes are separate structures that make selected lookup patterns faster, at the cost of extra storage and write work.

Next.js does not change these PostgreSQL rules. Its important contribution is execution placement. A Server Component can query while rendering a page. A Route Handler can expose an HTTP API. A Server Action can perform a mutation from a form submission. All three run on the server, so they can import a database module that opens a PostgreSQL connection. Client Components cannot safely do that because their JavaScript is shipped to the browser.

Connection and Query Anatomy

A typical Node-based Next.js application uses a PostgreSQL driver or query builder. At the lowest level, a connection pool holds a bounded number of database connections and hands them to requests as needed. Each query should use parameters instead of string interpolation. Parameters let the driver send values separately from the SQL text, which prevents user input from being interpreted as SQL syntax.

A database access module normally has three parts: a server-only marker, pool construction from environment variables, and exported functions that describe application operations rather than generic table access. Naming a function listPublishedCourses is usually better than exporting a raw query helper everywhere, because the narrower function can embed ordering, limits, tenant filters, and authorization checks.

Example 1: Tables, Keys, and Constraints

This first example models authors and lessons. It uses generated UUID primary keys, a unique slug, a foreign key from lessons to authors, a check constraint for publication state, and an index for the lookup pattern used by a public lesson page.

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE authors (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  display_name text NOT NULL,
  email text NOT NULL UNIQUE
);

CREATE TABLE lessons (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  author_id uuid NOT NULL REFERENCES authors(id),
  slug text NOT NULL UNIQUE,
  title text NOT NULL,
  body text NOT NULL,
  status text NOT NULL CHECK (status IN ('draft', 'published')),
  published_at timestamptz
);

CREATE INDEX lessons_status_slug_idx ON lessons(status, slug);

The deterministic behavior is rejection of invalid data. Two authors cannot share the same email. A lesson cannot reference a missing author. A lesson status other than draft or published fails before bad state reaches application code. The index supports queries that filter by status and slug, which is exactly what a public lesson route needs.

Example 2: Server-Only Reads in Next.js

The next example keeps the pool in a server-only module and exposes one application-specific read function. The SQL uses $1 as a placeholder, and the value is supplied separately. The function returns either a lesson object or null, which gives the calling route a clear branch for a 404 page.

import "server-only";
import { Pool } from "pg";

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

export async function getPublishedLesson(slug: string) {
  const result = await pool.query(
    `SELECT slug, title, body, published_at
     FROM lessons
     WHERE slug = $1 AND status = 'published'
     LIMIT 1`,
    [slug]
  );

  return result.rows[0] ?? null;
}

For a row whose slug is postgres-schema and status is published, the function returns the selected columns. For the same slug in draft status, it returns null. That distinction matters in Next.js because a public route should not reveal draft content just because the slug exists.

Example 3: Mutations with a Transaction

Writes often need more than one statement. A transaction groups statements so they either all commit or all roll back. This example creates a lesson inside a Server Action-style function. It validates the shape at the application boundary, starts a transaction, inserts the row, commits, and rolls back if any statement fails.

import "server-only";
import { z } from "zod";
import { pool } from "./pool";

const LessonInput = z.object({
  authorId: z.string().uuid(),
  slug: z.string().trim().min(3).max(120),
  title: z.string().trim().min(3).max(160),
  body: z.string().trim().min(1)
});

export async function createDraftLesson(input: unknown) {
  "use server";
  const lesson = LessonInput.parse(input);
  const client = await pool.connect();

  try {
    await client.query("BEGIN");
    const inserted = await client.query(
      `INSERT INTO lessons (author_id, slug, title, body, status)
       VALUES ($1, $2, $3, $4, 'draft')
       RETURNING id, slug, status`,
      [lesson.authorId, lesson.slug, lesson.title, lesson.body]
    );
    await client.query("COMMIT");
    return inserted.rows[0];
  } catch (error) {
    await client.query("ROLLBACK");
    throw error;
  } finally {
    client.release();
  }
}

If the input has an invalid UUID, no SQL runs. If the author ID is well formed but not present in authors, the foreign key rejects the insert and the transaction rolls back. If the slug already exists, the unique constraint rejects it. On success, the returned row contains the generated id, the submitted slug, and the deterministic status draft.

Design Choices and Trade-Offs

Use constraints for invariants that must be true regardless of which code path writes data. Application validation improves error messages and protects user experience, but database constraints are the final guard. Duplicating important rules in both places is normal: the application explains the problem early, and PostgreSQL prevents corruption under concurrency or a missed code path.

Choose indexes from real query shapes. An index on every column slows inserts and updates because PostgreSQL must maintain each index. A composite index such as (status, slug) is useful when queries commonly filter by both columns in that order. For fuzzy search or analytics, different index types or separate read models may be more appropriate.

Keep migrations reviewed and repeatable. A migration should move the database from one known shape to the next. Destructive changes, such as dropping a column, need a deployment plan because old application instances may still read that column during rollout. Safer changes often happen in phases: add the new column, backfill it, read from it, stop writing the old column, then remove the old column later.

Failure Modes and Troubleshooting

Symptom: the page works locally but deployment fails with authentication or connection errors. Cause: DATABASE_URL is missing, points at the wrong database, or requires SSL settings not used by the driver. Diagnose: print the environment variable name presence without printing the secret, verify the database host from deployment settings, and run a minimal server-side health query such as SELECT 1. Correction: set the correct secret in the deployment environment and configure the driver options required by the hosting provider.

Symptom: requests hang or intermittently fail under load. Cause: too many connections are opened, clients are not released, or serverless concurrency exceeds the database connection limit. Diagnose: inspect PostgreSQL active connections, confirm every borrowed client reaches release(), and compare the pool size with database limits. Correction: use one shared pool per server process, release clients in finally, lower pool size, or add an external pooler when the deployment model needs it.

Symptom: an insert fails with a foreign key or unique constraint error. Cause: the application submitted a missing parent ID or duplicate natural key. Diagnose: reproduce with the exact parameter values, query for the referenced author or existing slug, and inspect recent migrations that changed constraints. Correction: validate the parent selection in the UI, handle duplicate slugs with a clear message, and keep the constraint because it is protecting the invariant.

Symptom: a lesson page is slow even though only one row is expected. Cause: PostgreSQL may be scanning the table because the query does not match an index. Diagnose: run EXPLAIN ANALYZE for the exact SQL and parameters on representative data. Correction: add or adjust an index for the filter and ordering pattern, then verify that the plan uses it and that write cost remains acceptable.

Security, Performance, and Reliability

Database credentials belong in server environment variables and should be scoped to the minimum role needed by the application. Public pages usually need read access to published content, while administrative mutations need stronger permissions and authorization checks. Never expose unrestricted SQL endpoints to the browser.

Parameterized queries are the baseline defense against SQL injection. Authorization is a separate concern: a query can be perfectly parameterized and still return another user’s data if it omits an ownership or tenant predicate. Performance depends on query shape, indexes, row counts, and connection behavior. Reliability depends on timeouts, transaction boundaries, migration discipline, backups, and tested restore procedures.

Hands-On Lab

Prerequisites: a running PostgreSQL database, a Next.js project that can run server code, the pg package, and a DATABASE_URL secret available only to the server runtime.

  1. Create the authors and lessons tables from Example 1 in a local database.
  2. Insert one author, then insert one published lesson and one draft lesson for that author.
  3. Add a server-only database module with a shared pool and the getPublishedLesson function from Example 2.
  4. Create a Next.js route that calls getPublishedLesson(params.slug). Render the title and body when a row is returned; otherwise return the framework’s not-found response.
  5. Add the createDraftLesson mutation and call it from a controlled admin-only path or a local script.
  6. Verification: request the published slug and confirm content renders, request the draft slug and confirm it is not shown, submit a duplicate slug and confirm PostgreSQL rejects it, then check that active connections do not grow after repeated requests.
  7. Cleanup: delete lab rows by slug, drop the two lab tables if they are not reused, and remove any temporary credentials or admin test route.

Assessment Exercises

  1. A product manager asks for editable lesson slugs. Design a migration and deployment sequence that avoids broken links while preserving uniqueness.
  2. Given a query that filters by author_id, status, and published_at ordering, propose an index and explain the read/write trade-off.
  3. Explain why input validation in a Server Action does not replace a PostgreSQL foreign key or unique constraint.
  4. A deployed app starts timing out only during traffic spikes. List the database metrics and code paths you would inspect before changing the schema.
  5. Modify the read function so a signed-in author can preview their own draft without exposing drafts to anonymous visitors.

Summary

PostgreSQL schema design and database access in Next.js are one system. Tables, constraints, and indexes define durable truth. Server-only modules, Server Components, Route Handlers, and Server Actions decide where trusted code runs. Parameterized queries, transactions, migrations, and focused diagnostics turn that design into behavior you can operate and repair.