Setting Up PostgreSQL the Right Way

The NestJS API from last time can validate a request and return a typed User, but right now that user lives nowhere. It's time to give the app a memory. In this series that memory is PostgreSQL, and the way you set it up in the first hour decides whether your data layer stays boring for years or turns into a source of 2am incidents.

This article covers the foundation: getting Postgres running the same way for everyone on the team, connecting the API to it safely, and thinking about your first schema without painting yourself into a corner. Query tuning and partitioning can wait.

This is part five of the Full Stack SaaS Masterclass, continuing from the NestJS API in apps/api.

Run it in Docker, not on your laptop

The first decision is where Postgres actually runs during development, and the answer is a container. Installing Postgres directly on your machine works right up until a teammate has a different version, or you need a second project on a different major release, and then you're debugging the database instead of building features.

We already committed to Docker for local reproducibility back in the stack article. Here's the postgres service in docker-compose.yml at the repo root:

# docker-compose.yml
services:
  postgres:
    image: postgres:16
    restart: unless-stopped
    environment:
      POSTGRES_USER: saas
      POSTGRES_PASSWORD: local_dev_only
      POSTGRES_DB: saas_dev
    ports:
      - '5432:5432'
    volumes:
      - pgdata:/var/lib/postgresql/data

volumes:
  pgdata:

One docker-compose up and every teammate has Postgres 16 on port 5432 with the same database name. The named pgdata volume means your data survives a container restart, and pinning postgres:16 rather than postgres:latest means an image update never silently changes your major version underneath you.

Connect the API without leaking secrets

The API reaches Postgres through a connection string. Keep it in the environment, never in code, and give it a sensible shape:

# apps/api/.env
DATABASE_URL=postgresql://saas:local_dev_only@localhost:5432/saas_dev

That local_dev_only password is fine to commit to an .env.example because it only ever guards a throwaway local container. Production credentials are a different story, and we'll handle environment variables and secrets properly in their own article shortly. The habit to build now: the code reads DATABASE_URL through @nestjs/config, and the value changes per environment while the code never does.

// reading it the right way, via injected config
const url = this.config.get<string>('DATABASE_URL');

Think in schema, not in tables

Before writing a single CREATE TABLE, it helps to decide how you'll reason about your data. For a multi-tenant SaaS, the question that shapes everything is: how does a row know which organization it belongs to? We'll design multi-tenancy in depth later, but the choice you make here ripples through every table, so it's worth naming early.

For the foundation, keep it simple and correct. A users table with a real primary key, a unique constraint where the data demands one, timestamps, and columns that match the shared User type the frontend and backend both import:

CREATE TABLE users (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email       TEXT NOT NULL UNIQUE,
  name        TEXT NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

A few choices in that small block earn their keep. UUID primary keys instead of auto-incrementing integers mean IDs aren't guessable and don't leak how many users you have, which matters the moment IDs appear in URLs. The UNIQUE constraint on email pushes the rule into the database, so two requests racing to register the same address can't both win. And TIMESTAMPTZ rather than a plain timestamp stores the time zone, saving you a category of bugs that only surface once you have users in a second country.

Migrations from the very first table

Never change a schema by hand in a running database and hope everyone else runs the same commands. From day one, every schema change is a migration: a versioned, checked-in file that moves the database from one known state to the next, and that runs the same way on your laptop, in CI, and in production.

The tool you use for migrations usually comes with your data-access layer, which is the subject of the next article. The principle holds regardless of tool. A schema change that isn't a committed migration is a change that will drift between environments and eventually break a deploy.

apps/api/migrations/
├── 0001_create_users.sql
└── 0002_add_organizations.sql   # each one moves the schema forward

Numbered, ordered, forward-only in spirit. When something goes wrong in production, you want to point at an exact migration, not guess which of five people ran which SQL by hand.

What to defer

Postgres has a deep feature set, and almost none of it belongs in your first week. You don't need read replicas or connection pooling infrastructure at zero users; a single instance handles far more traffic than a new SaaS will see for a long time. You don't need partitioning until a table is genuinely large. You don't need to reach for JSONB everywhere just because it's flexible; model relational data relationally and use JSONB for the parts that are actually schemaless.

The foundation is small on purpose: a containerized Postgres, a connection string in the environment, a clean first schema with real constraints, and migrations from the start. That's enough to build real features on, and everything else is a decision you'll make better later, with real data in front of you.

Next in the series, we put a data-access layer on top of this: choosing between Prisma and TypeORM, and wiring it into the NestJS app so those migrations and queries are typed end to end.