Setting Up PostgreSQL with Docker & Docker Compose

Unit test vs integration test. A unit test exercises one piece of code in isolation with nothing running. An integration test exercises how your code talks to a real external system, here a real Postgres, so it's slower and needs the database up.

Healthcheck. A command Postgres runs on a loop so Docker knows whether the container is merely started or actually ready to accept connections. We use pg_isready, and docker compose up -d --wait blocks until it reports healthy.

One thing to keep straight: the app's storage is still the in-memory Map. We stand up a real Postgres and prove the tests can reach it, but no application code reads or writes to it yet. This chapter is pure infrastructure; the storage swap comes later.

A Real Database Catches What a Mock Can't

The instinct in a test-driven course is to mock the database. A mock is fast, has no dependencies, and keeps the test pure. So why drag Docker into it?

Because a mock proves the wrong thing. A mocked database test asserts that your code calls the functions you think it calls: pool.query was invoked with this string. It says nothing about whether that string is valid SQL, whether the driver is wired up correctly, whether the connection string, credentials, and port actually reach a live server, or whether Postgres behaves the way your code assumes. A mock will happily return a green check for SQL that a real Postgres would reject outright. That class of bug then only shows up in production.

A real database closes that gap. SELECT 1 returning 1 is a tiny query, but passing it validates the whole connection stack at once: the env vars resolved, the pg pool opened a socket, Docker mapped the port, the credentials authenticated, and Postgres answered. That is integration confidence, the thing a mock structurally cannot give you.

This isn't an anti-mock chapter. Mocks remain the right tool for pure logic and for the service layer, where you want speed and isolation. The point is narrower: a real database earns its keep on the one thing a mock can't prove, that your code can actually talk to the database. The cost is honest and bounded, since it's slower and needs Docker running, which is exactly why we split the two suites later in this chapter.

Why a Separate Test Database

Integration tests are destructive by nature. They insert rows, truncate tables, and rewrite state so each test starts clean. You do not want that running against the same database you use for local development. One stray TRUNCATE and your hand-seeded dev data is gone.

So the tests get their own database, urlshortener_test, separate from the dev database urlshortener. Tests can wipe it freely, and dev data is never in the blast radius.

The interesting decision is how to provide that second database. We use one Postgres instance hosting two databases, not two separate Postgres services. The reasons:

• It's the simplest clean isolation. A separate database (not just a separate schema) is the cleanest blast-radius boundary, while a single instance keeps the setup tiny: one container, one port, one healthcheck.
• It's cheaper and faster. One container uses roughly half the memory of two and starts and stops faster.
• It mirrors CI. When the GitHub Actions pipeline arrives later in the course, it typically runs a single Postgres service container. Using separate database names here keeps local and CI shaped the same way, so "works on my machine" stops being a thing.

Per-worker parallel databases (one database per Jest worker) are a real technique, but they belong to the advanced isolation chapter. For now, a single instance with two databases is plenty.

Pinning the Image Version

The docker-compose.yml says postgres:16-alpine, not postgres:latest. That 16 is doing real work.

latest is a moving target. Whoever runs docker compose up tomorrow, next month, or in CI might pull a different major version than the one you developed and tested against, and Postgres major versions change behaviour, defaults, and occasionally on-disk format. A test suite that passed on your machine can fail on a teammate's purely because the database underneath shifted. Pinning the version makes the database a reproducible part of the project, the same way a lockfile pins your npm dependencies. Everyone runs the exact same Postgres: you, your teammates, CI. The -alpine variant keeps the image small.

Why Configure the Connection with Environment Variables

The connection details (host, port, user, password, database name) live in environment variables, not hardcoded in the source. There are two reasons.

The obvious one is secrets: credentials don't belong in version-controlled code. We commit a .env.example documenting the shape of the config with safe local-dev defaults, and gitignore the real .env.

The subtler one is the dev/test split. The exact same src/db/pool.ts needs to point at the dev database in normal runs and the test database under Jest. Env vars make that a one-line decision: read TEST_DATABASE_URL when NODE_ENV === "test" (Jest sets that automatically), otherwise DATABASE_URL. No code branches per environment, no separate "test build", just a different value in the environment.

Step 1: The Failing Integration Test (Red)

Start from the red branch and install dependencies:

The start branch ships the integration test, but nothing it needs to run. The test imports a connection pool that doesn't exist yet:

This is the specification: borrow a connection, run SELECT 1 as result, expect the value back as 1, and close the pool in afterAll so Jest exits cleanly instead of hanging on an open socket.

The unit suite already ignores this folder. On the start branch we add one line to jest.config.ts so npm test never looks at the integration tests:

So the fast unit tests are untouched and stay green, since they never touch a database:

The integration suite is the one that fails. Run it with the Postgres container already up, so the red is unmistakably "the module doesn't exist," not "the database is unreachable":

Red. The suite can't even compile: Cannot find module '../../src/db/pool'. There's no connection module, pg isn't installed, and there's no docker-compose.yml. npm run typecheck reports the same gap as a TS2307 type error. That missing module is the whole to-do list for this chapter.

Step 2: Define the Database (Green)

Switch to the finish branch:

The Docker Compose file

The heart of the chapter is the compose file: one pinned Postgres service with a healthcheck, a persistent volume, and a non-default host port.

A few choices are worth calling out:

• image: postgres:16-alpine is pinned, as discussed. Same Postgres for everyone.
• The port mapping "${POSTGRES_PORT:-5433}:5432" maps host port 5433 to the container's standard 5432. Postgres inside the container still listens on 5432; from your machine you reach it on 5433. We use 5433 because the default 5432 was already taken on the author's machine by another project's Postgres, a real and common clash. It's overridable via POSTGRES_PORT, but the whole repo is consistent on 5433, so we teach 5433 throughout.
• The ./docker/init mount is a small but important trick. Postgres runs every *.sql file in /docker-entrypoint-initdb.d exactly once, on first boot, when the data volume is empty. That's how we create the second database without any manual step.
• The healthcheck runs pg_isready on a loop. It exists so that docker compose up -d --wait can block until Postgres is ready to accept connections, not merely once "the container started." Without it, the test run can race a database that's still initialising and fail intermittently.

The init script is one line:

Postgres creates urlshortener automatically from POSTGRES_DB, and this script adds urlshortener_test alongside it, so a single instance ends up hosting both databases.

The environment config

.env.example documents every variable, with the container settings and the two connection strings:

Both connection strings point at localhost:5433, the host side of the port mapping, and differ only in the database name at the end. The real .env (gitignored) holds the same values; you create it by copying the example. The repo's .gitignore already ignores .env and .env.* while un-ignoring !.env.example, so nothing changed there.

The connection pool

The connection module is small on purpose. It opens one shared pg pool and decides which database to hit based on NODE_ENV:

A connection pool is a set of reusable open connections the driver hands out and reclaims, and it matters because opening a fresh TCP and auth handshake on every query is slow. pg manages that pool for you; we create exactly one and export it, and everything else borrows from it via pool.query(...).

The dev/test selection is the only logic in the file, and it lives here so no other module ever has to think about it. When Jest runs, it sets NODE_ENV=test automatically, the pool reads TEST_DATABASE_URL, and every query lands in urlshortener_test. The guard that throws on a missing connection string is deliberate: a clear "copy .env.example to .env" message beats pg silently trying to reach some default localhost database and failing confusingly. A later chapter replaces this with proper, schema-validated config loaded at startup.

Splitting the test suites

The integration tests need a real Postgres; the unit tests don't. Forcing every npm test to require Docker would tax the fast feedback loop TDD depends on. So the integration suite gets its own Jest config and its own script.

Two additions over the start-branch config make this work. setupFiles runs setup-env.ts before any test module is imported, which is exactly the window where the connection strings must exist, because src/db/pool.ts reads them the moment it's imported. And testMatch restricts "what counts as a test" to files ending in .test.ts, so Jest doesn't try to run the setup file and the helpers (which have no test cases) as if they were test suites.

The setup file is a one-liner that loads .env into process.env via dotenv:

The two npm scripts make the split explicit:

npm test runs the fast unit suite with no Docker required, so you can run it any time, on any machine. npm run test:integration runs the DB suite and requires the container to be up. The finish branch also adds three dependencies for this to work: pg and @types/pg for the driver, and dotenv for loading .env.

Step 3: Start Postgres and Go Green

The integration test can't pass against a database that isn't running, so start the container first. The --wait flag blocks until the healthcheck reports healthy:

The --wait flag is the healthcheck paying off: the command only returns once Postgres is accepting connections, and on this first boot the init script has created both databases (urlshortener and urlshortener_test). Now run the integration suite:

Green. The pool connected to urlshortener_test, SELECT 1 came back as 1, and the second test (the cleanup seam, covered next) ran without error. The fast unit suite is unchanged and still green, and npm run typecheck now passes. The full connection stack is proven end to end.

The Cleanup Seam for Chapter 10

That second passing test exercises a helper that's about to become important. The moment tests share a real database, they also share its state: one test's rows are visible to the next, so order suddenly matters and the suite goes flaky. The fix is to reset the database between tests, and truncateAllTables is the seam where that reset will live:

Right now this is effectively a no-op: there are no application tables yet, since the Url table arrives with Prisma a couple of chapters later, so getPublicTableNames() returns an empty list and the function exits before running any destructive statement. The test only asserts that it runs against the real database without throwing.

The job here is to plant the seam and prove it works against real Postgres. The next chapter is where it gets rigorous: wiring it into a beforeEach, weighing truncate against transaction-rollback, and centralizing it so every DB test starts from a clean slate.

Tearing Down

When you're done, stop the container. The plain down removes the container and network but keeps the data volume; down -v also deletes the volume, which means the init script re-runs (and recreates urlshortener_test) on the next up:

What's Next

The cleanup seam is still inert, so the tests don't reset state between runs yet. Next we lay the test isolation foundations: per-test cleanup that keeps every database test deterministic and order-independent.