Database Integration Tests

CI's Verify DB job (db-integration-tests in .github/workflows/verify.yml) runs the backend integration tests against a real Postgres. Those tests do not run in the ordinary unit suite, so a failure that only shows up in CI can be hard to reproduce. This guide gives you a copy-paste recipe that stands up the same database environment locally, so you can reproduce a CI database failure, fix it, and confirm green before you push.

Every value below is taken verbatim from the db-integration-tests job in .github/workflows/verify.yml — that job is the source of truth. A test in apps/infra/ci/workflows.test.ts fails if this guide ever drifts from it.

How the backend picks up the database config

• Locally, the integration test runner reads database connection settings from the root .env file. apps/backend/vitest.integration.config.ts parses the root .env and injects it into the test environment. In CI there is no .env; the job's env: block supplies the same variables instead.
• The integration suites skip themselves when the database is not configured. apps/backend/app/common/repositories/user-orm.repository.integration.test.ts wraps its tests in describe.skipIf(!isDbConfigured()), and isDbConfigured() is true only when DB_HOST, DB_USERNAME, DB_PASSWORD, and DB_NAME are all set. If test:integration reports zero database tests, the environment is not configured — the tests did not pass, they were skipped.
• Migrations run automatically. The test setup initializes the data source and runs all migrations before the suite, so there is no separate migration step to perform.
• The port is fixed at 5432. The backend data source hardcodes port 5432 (apps/backend/app/common/utils/database.util.ts), so the container must publish 5432:5432 and local port 5432 must be free.

Prerequisites

• Docker installed and running.
• Local TCP port 5432 free (stop any local Postgres or leftover container using it).
• Dependencies installed: pnpm install from the repo root.

Step 1 — Start the CI-matching Postgres

Run a throwaway postgres:16 container with the exact credentials CI uses:

docker run --rm -d --name ws-mono-st-testdb \
  -e POSTGRES_USER=test -e POSTGRES_PASSWORD=test -e POSTGRES_DB=testdb \
  -p 5432:5432 postgres:16

Wait until it is ready (this mirrors CI's pg_isready health check):

docker exec ws-mono-st-testdb pg_isready -U test

Step 2 — Point the root .env at the container

Set these four keys in the root .env file so the integration tests connect to your local container:

DB_HOST=127.0.0.1
DB_USERNAME=test
DB_PASSWORD=test
DB_NAME=testdb

This temporarily overrides whatever pnpm script update-env wrote into DB_*. Note the values you are replacing (or back the file up) and restore them in Step 5.

CI uses DB_HOST=localhost; 127.0.0.1 is the reliable local equivalent (it avoids localhost resolving to IPv6 ::1 while the container listens on IPv4) and reaches the same Postgres.

Step 3 — Run the integration tests

Run the exact command CI runs:

pnpm --filter ws-mono-st-backend test:integration

Migrations run automatically before the suite. If the output shows no database tests, DB_* is not set in the root .env (see Step 2) — the tests were skipped, not passed.

Step 4 — Reproduce, fix, confirm

You should now see the same failure CI's Verify DB job reports. Fix the code, re-run the command from Step 3, and confirm it is green locally before pushing.

Step 5 — Tear down and restore

Remove the container and restore your .env:

docker rm -f ws-mono-st-testdb

Then restore the original DB_* values in the root .env — re-run pnpm script update-env --pr <n> (or --env <name>), or restore the backup you took in Step 2.

Troubleshooting

• Port 5432 already in use. Another Postgres (local install or a leftover container) is holding the port. Stop it, or remove the previous container with docker rm -f ws-mono-st-testdb. The port is fixed in the backend data source, so remapping to another host port will not work.
• Connection refused against localhost. Use DB_HOST=127.0.0.1 rather than localhost — localhost can resolve to IPv6 ::1 while the container is published on IPv4.
• Container name already exists. Remove the old one: docker rm -f ws-mono-st-testdb.
• All integration tests skip / "no tests" reported. DB_HOST, DB_USERNAME, DB_PASSWORD, and DB_NAME must all be set in the root .env; otherwise the suites skip themselves.
• "Refusing to run integration tests" error. A safety guard refuses to run against a production-looking database. Keep DB_NAME=testdb (the guard rejects a DB_NAME that begins with p).