Build the Database Pool, Types, and Contacts Query

Create the connection pool

Create lib/db.ts. This is the one place in the app that knows how to
connect to Postgres — every query function will import pool from here.

import { Pool } from "pg";

declare global {
  // eslint-disable-next-line no-var
  var _pgPool: Pool | undefined;
}

function createPool() {
  const connectionString = process.env.DATABASE_URL;
  if (!connectionString) {
    throw new Error(
      "DATABASE_URL is not set. Copy .env.example to .env.local and set your Postgres connection string."
    );
  }
  return new Pool({
    connectionString,
    ssl: connectionString.includes("sslmode=require")
      ? { rejectUnauthorized: false }
      : undefined,
  });
}

export const pool = global._pgPool || createPool();
if (process.env.NODE_ENV !== "production") global._pgPool = pool;

The global._pgPool trick matters in development: Next.js hot-reloads your
server code on every save, and without this, each reload would open a new
pool of database connections without closing the old one. Reusing a global
keeps you on one pool across reloads.

The ssl line is why we mentioned ?sslmode=require in the last lesson —
if your connection string includes it, the pool automatically enables SSL.

Define the Contact type

Create lib/types.ts:

export type Contact = {
  id: string;
  name: string;
  phone: string | null;
  email: string | null;
  company: string | null;
  notes: string | null;
  favorite: boolean;
  created_at: string;
  updated_at: string;
};

This matches the contacts table column-for-column. Nullable columns
(phone, email, company, notes) are typed as string | null, since
that’s exactly what Postgres will hand back for an empty field.

Write the query function

Create lib/contacts.ts:

import { pool } from "./db";
import type { Contact } from "./types";

export async function getContacts(): Promise<Contact[]> {
  const result = await pool.query(
    `SELECT * FROM contacts ORDER BY lower(name) ASC`
  );
  return result.rows;
}

lower(name) sorts case-insensitively, so “ada” and “Ada” land next to each
other instead of being split by capitalization.

This is deliberately the simplest version of this function — no search, no
pagination yet. We’ll extend it in Module 3, once there’s a UI that needs
those features.

Add a couple of test contacts

There’s no seed script yet, so add a few rows directly:

psql "$DATABASE_URL" -c "
INSERT INTO contacts (name, phone, email, company, favorite) VALUES
  ('Ada Lovelace', '555-0101', 'ada@example.com', 'Analytical Engines Ltd', true),
  ('Grace Hopper', '555-0102', 'grace@example.com', 'Navy Research', true),
  ('Alan Turing', '555-0103', 'alan@example.com', NULL, false);
"

Checkpoint

You can’t see anything in the browser yet — getContacts isn’t called from
any page. But if you want to sanity-check it, temporarily add
console.log(await getContacts()) to the top of app/page.tsx’s component
and check your terminal: you should see three contact objects logged when
you load the page. Remove the console.log before moving on.