Module 2 — The Data Layer

Your skeleton runs but forgets everything the moment a request ends. This module gives it a memory: a Postgres database, a real schema, and pooled access. We lean on The Data Layer chapter and the Postgres storage deep dive — and we put the indexes in now, because retrofitting them under load is how slow-query incidents start.

Goal

• A users and a snippets table with sensible keys, types, and constraints.
• The schema applied as a migration (a versioned, repeatable change), not hand-typed into a console.
• A pooled Postgres client the app uses for every query.
• A repository module with the first queries (create, get-by-public-id), each backed by an index.

Step 1: Run Postgres

Locally, the easiest real Postgres is a container:

docker run --name snippets-db -e POSTGRES_PASSWORD=dev \
  -e POSTGRES_DB=snippets -p 5432:5432 -d postgres:16

Set DATABASE_URL=postgres://postgres:dev@localhost:5432/snippets in a .env file (and load it in dev). Your Module 1 config validation now has something to validate.

Step 2: Model the schema

Two tables. The decisions here matter more than the syntax:

create table users (
  id          bigint generated always as identity primary key,
  email       text not null unique,
  password_hash text not null,
  created_at  timestamptz not null default now()
);

create table snippets (
  id          bigint generated always as identity primary key,
  public_id   text not null unique,          -- the short URL id, e.g. "aB3x9"
  owner_id    bigint not null references users(id),
  title       text,
  body        text not null,
  language    text not null default 'text',
  is_public   boolean not null default false,
  view_count  bigint not null default 0,
  created_at  timestamptz not null default now()
);

Notice what the database is enforcing for you: email is unique (no duplicate accounts, guaranteed even under a race), owner_id references users(id) (you can't orphan a snippet), not null where a value is mandatory. These are constraints you never have to re-check in application code, because the database refuses the bad write.

Step 3: Make it a migration

A migration is a versioned SQL change that runs the same way in dev, CI, and production. Use a migration tool (node-pg-migrate, Drizzle, Prisma Migrate — pick one) so the schema's history is in version control and reproducible.

1. Write the change as an up migration

   The create table statements above go in a migration file, numbered/timestamped so order is deterministic.

2. Apply it with a command, never by hand

   npm run migrate up. The tool records which migrations have run in a tracking table, so running it again is a no-op — it's idempotent.

3. Commit the migration file

   Now CI and production build the exact same schema you have locally, from the same files.

Step 4: Connect through a pool

Opening a fresh database connection per request is slow (a TCP + auth handshake every time) and dangerous (Postgres has a hard connection limit; a traffic spike opens thousands and the database falls over). A connection pool keeps a small set of connections open and lends them out, exactly as the chapter describes.

// src/db.ts
import { Pool } from "pg";
import { config } from "./config.js";

export const pool = new Pool({
  connectionString: config.databaseUrl,
  max: 10, // tune to your DB's limit ÷ number of app instances
});

export async function shutdownDb() {
  await pool.end();
}

Call shutdownDb() from your Module 1 graceful-shutdown drain, before process.exit — so a deploy closes connections cleanly instead of leaving them dangling on the database.

Step 5: First queries, each on an index

// src/repos/snippets.ts
import { pool } from "../db.js";

export async function insertSnippet(s: {
  publicId: string; ownerId: number; title: string | null;
  body: string; language: string; isPublic: boolean;
}) {
  const { rows } = await pool.query(
    `insert into snippets (public_id, owner_id, title, body, language, is_public)
     values ($1,$2,$3,$4,$5,$6) returning *`,
    [s.publicId, s.ownerId, s.title, s.body, s.language, s.isPublic]
  );
  return rows[0];
}

export async function getByPublicId(publicId: string) {
  const { rows } = await pool.query(
    `select * from snippets where public_id = $1`, [publicId]
  );
  return rows[0] ?? null;
}

getByPublicId filters on public_id, and we declared that unique, which gives it an index for free — so this lookup is an index hit, not a full table scan, even at a million rows. We'll add the index for listing a user's snippets in Module 3 when we build that query, because the right index depends on exactly how you query.

Acceptance check

Write a tiny throwaway script (or a psql session) that inserts a snippet and reads it back by public_id:

npm run migrate up      # schema applies cleanly
# then run a script that calls insertSnippet(...) and getByPublicId(...)
# → you get the same row back, with an auto-assigned id and created_at.

You're done when migrations apply from scratch, an insert returns a row with a generated id, and getByPublicId reads it back through the pool. Commit it.