db-setup.md

PostgreSQL and Prisma

PostgreSQL runs through Compose for host development, container verification, and local database inspection. Prisma owns the schema, generated client, and committed migration history.

Local database

Start PostgreSQL and wait for it to accept connections:

docker compose up --wait database

The database persists in the database-data Compose volume and is published only on the host loopback interface. The default host connection is:

DATABASE_URL="postgresql://inventory:inventory@127.0.0.1:5432/inventory?schema=public"

Copy .env-sample to .env so the host application and Prisma CLI use this connection:

cp .env-sample .env

Stop the containers while retaining the database volume:

docker compose down

Delete the containers and local database data:

docker compose down --volumes

Prisma configuration

The schema is stored in prisma/schema.prisma; prisma.config.ts loads DATABASE_URL and identifies the schema and migration directory. Prisma Client is generated into the ignored src/generated/prisma directory.

npx prisma validate
npx prisma generate

Migrations

Committed files under prisma/migrations are the source of truth for creating a database.

Use migrate dev while changing the schema. Creating the migration separately keeps the SQL review explicit:

npx prisma migrate dev --name <descriptive-name> --create-only

# Review prisma/migrations/<migration-name>/migration.sql

npx prisma migrate dev
npx prisma generate

Use migrate deploy to apply already committed migrations without creating new ones:

docker compose run --rm api \
  npx --no-install prisma migrate deploy

Database tests, CI, staging, and production-style deployments also use migrate deploy.

Runtime composition

main.ts reads DATABASE_URL, creates one Prisma client, and shares it across the Prisma repository adapters:

DATABASE_URL
    |
PrismaClient
    |
PrismaRepositories
    |
UseCases
    |
Express + GraphQL Yoga

The application and domain layers do not depend on Prisma types.

Database integration tests

Database tests remain separate from the persistent Compose database. Testcontainers starts a fresh postgres:17-alpine container, applies committed migrations, provides its connection URL to Vitest, and removes the container after the suite.

npm run test:database

With local Colima:

npm run test:database:colima

The setup lives in tests/database/setup.ts; database-test selection and timeouts live in vitest.database.config.ts.