NestJS + PostgreSQL + Prisma: Full-Stack API Tutorial

Build a production-ready REST API with NestJS, PostgreSQL, and Prisma. We'll scaffold a Nest app, configure Postgres with Docker, define a Prisma schema (User + Post), run migrations, and implement clean NestJS modules with a typed Prisma client. By the end, you'll have a working CRUD API, env/migration workflow, and patterns ready for auth and deployment.

Prerequisites

• Node.js 18+ (LTS recommended)

• npm or pnpm/yarn

• Docker (for local Postgres) or a Postgres DB (connection string)

• Basic TypeScript knowledge

• Optional: Nest CLI (npm i -g @nestjs/cli)

Quick project scaffold (commands)

Create a project folder.

mkdir nest-prisma-api && cd nest-prisma-api

Initialize git.

git init

Create a Nest app using npm (or use pnpm/yarn).

npm init -y
npm i -D @nestjs/cli
npx nest new server

When CLI prompts, choose npm and REST (or manual).

cd server

(If you used npx nest new, you already have package.json & Nest scaffolding under server/. For this tutorial, assume the project root is server/.)

Docker Compose: Postgres (local dev)

Create docker-compose.yml at project root:

version: "3.8"
services:
  postgres:
    image: postgres:15
    restart: unless-stopped
    environment:
      POSTGRES_USER: djamware
      POSTGRES_PASSWORD: djamware
      POSTGRES_DB: djamware_dev
    ports:
      - "5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data

volumes:
  pgdata:

Start:

docker-compose up -d

Add Prisma to the Nest app

From server/:

npm install prisma --save-dev
npm install @prisma/client

Initialize Prisma

npx prisma init

This creates prisma/schema.prisma and .env with DATABASE_URL.

Edit .env:

DATABASE_URL="postgresql://djamware:djamware@localhost:5432/djamware_dev?schema=public"

Prisma schema (example: User + Post)

Replace prisma/schema.prisma with:

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  password  String
  posts     Post[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  authorId  Int
  author    User     @relation(fields: [authorId], references: [id])
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

Run migrations:

npx prisma migrate dev --name init
# generates client too

(Optional seed) Create prisma/seed.ts and run npx prisma db seed with config if desired.

Integrate Prisma into NestJS

Install types & helper

npm install @nestjs/config
npm install class-validator class-transformer

Create src/prisma/prisma.service.ts

// src/prisma/prisma.service.ts
import { Injectable, OnModuleInit, OnModuleDestroy } from '@nestjs/common';
import { PrismaClient } from '@prisma/client';

@Injectable()
export class PrismaService extends PrismaClient implements OnModuleInit, OnModuleDestroy {
  async onModuleInit() {
    await this.$connect();
  }

  async onModuleDestroy() {
    await this.$disconnect();
  }

  // helper to run transactions with Prisma >=3
  async enableShutdownHooks(app: any) {
    this.$on('beforeExit', async () => {
      await app.close();
    });
  }
}

Create src/prisma/prisma.module.ts

// src/prisma/prisma.module.ts
import { Module } from '@nestjs/common';
import { PrismaService } from './prisma.service';

@Module({
  providers: [PrismaService],
  exports: [PrismaService],
})
export class PrismaModule {}

Register PrismaModule in AppModule:

// src/app.module.ts
import { Module } from '@nestjs/common';
import { PrismaModule } from './prisma/prisma.module';
// import UsersModule later

@Module({
  imports: [PrismaModule /*, UsersModule */],
  controllers: [],
  providers: [],
})
export class AppModule {}

Build a Users module (CRUD example)

DTOs

src/users/dto/create-user.dto.ts

import { IsEmail, IsNotEmpty, IsOptional, MinLength } from 'class-validator';

export class CreateUserDto {
  @IsEmail()
  email: string;

  @IsOptional()
  name?: string;

  @IsNotEmpty()
  @MinLength(6)
  password: string;
}

src/users/dto/update-user.dto.ts

import { PartialType } from '@nestjs/mapped-types';
import { CreateUserDto } from './create-user.dto';

export class UpdateUserDto extends PartialType(CreateUserDto) { }

(Install @nestjs/mapped-types if not present: npm i @nestjs/mapped-types)

Users service

src/users/users.service.ts

import { Injectable, NotFoundException } from '@nestjs/common';
import { CreateUserDto } from './dto/create-user.dto';
import { UpdateUserDto } from './dto/update-user.dto';
import * as bcrypt from 'bcrypt';
import { PrismaService } from 'prisma/prisma.service';

@Injectable()
export class UsersService {
    constructor(private readonly prisma: PrismaService) { }

    async create(createUserDto: CreateUserDto) {
        const hashed = await bcrypt.hash(createUserDto.password, 10);
        const user = await this.prisma.user.create({
            data: {
                email: createUserDto.email,
                name: createUserDto.name,
                password: hashed,
            },
            select: { id: true, email: true, name: true, createdAt: true },
        });
        return user;
    }

    findAll() {
        return this.prisma.user.findMany({
            select: { id: true, email: true, name: true, createdAt: true },
        });
    }

    async findOne(id: number) {
        const user = await this.prisma.user.findUnique({
            where: { id },
            select: { id: true, email: true, name: true, createdAt: true },
        });
        if (!user) throw new NotFoundException(`User ${id} not found`);
        return user;
    }

    async update(id: number, updateUserDto: UpdateUserDto) {
        if (updateUserDto.password) {
            updateUserDto.password = await bcrypt.hash(updateUserDto.password, 10);
        }

        try {
            const user = await this.prisma.user.update({
                where: { id },
                data: updateUserDto as any,
                select: { id: true, email: true, name: true, createdAt: true },
            });
            return user;
        } catch (err) {
            throw new NotFoundException(`User ${id} not found`);
        }
    }

    async remove(id: number) {
        try {
            await this.prisma.user.delete({ where: { id } });
            return { deleted: true };
        } catch (err) {
            throw new NotFoundException(`User ${id} not found`);
        }
    }
}

Install bcrypt types:

npm i bcrypt
npm i -D @types/bcrypt

Users controller

src/users/users.controller.ts

import { Controller, Get, Post, Body, Param, Patch, Delete, ParseIntPipe } from '@nestjs/common';
import { UsersService } from './users.service';
import { CreateUserDto } from './dto/create-user.dto';
import { UpdateUserDto } from './dto/update-user.dto';

@Controller('users')
export class UsersController {
  constructor(private readonly usersService: UsersService) {}

  @Post()
  create(@Body() dto: CreateUserDto) {
    return this.usersService.create(dto);
  }

  @Get()
  findAll() {
    return this.usersService.findAll();
  }

  @Get(':id')
  findOne(@Param('id', ParseIntPipe) id: number) {
    return this.usersService.findOne(id);
  }

  @Patch(':id')
  update(@Param('id', ParseIntPipe) id: number, @Body() dto: UpdateUserDto) {
    return this.usersService.update(id, dto);
  }

  @Delete(':id')
  remove(@Param('id', ParseIntPipe) id: number) {
    return this.usersService.remove(id);
  }
}

Users module

src/users/users.module.ts

import { Module } from '@nestjs/common';
import { UsersService } from './users.service';
import { UsersController } from './users.controller';
import { PrismaModule } from 'prisma/prisma.module';

@Module({
    imports: [PrismaModule],
    controllers: [UsersController],
    providers: [UsersService],
})
export class UsersModule { }

Register UsersModule in AppModule:

imports: [PrismaModule, UsersModule],

Validation and global pipes

In main.ts enable validation:

import { ValidationPipe } from '@nestjs/common';
import { NestFactory } from '@nestjs/core';
import { AppModule } from './app.module';

async function bootstrap() {
  const app = await NestFactory.create(AppModule);
  app.useGlobalPipes(new ValidationPipe({ whitelist: true }));
  await app.listen(process.env.PORT || 3000);
}
bootstrap();

Running the app (dev)

Scripts in package.json (server-level):

"scripts": {
  "start": "nest start",
  "start:dev": "nest start --watch",
  "build": "nest build",
  "prisma:migrate": "prisma migrate dev",
  "prisma:generate": "prisma generate"
}

Start DB:

docker-compose up -d

Migrate & run:

npx prisma migrate dev --name init
npm run start:dev

API available at http://localhost:3000/users.

Useful Prisma queries (examples)

• Get user with posts:

prisma.user.findUnique({
  where: { id: 1 },
  include: { posts: true },
});

• Paginate posts:

prisma.post.findMany({
  skip: (page - 1) * perPage,
  take: perPage,
  orderBy: { createdAt: 'desc' }
});

Seed script (optional)

prisma/seed.ts

import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

async function main() {
  await prisma.user.create({
    data: {
      email: '[email protected]',
      name: 'Alice',
      password: 'secret-hash', // ideally hash
      posts: { create: [{ title: 'Hello World', content: 'First post' }] },
    },
  });
}

main()
  .catch((e) => console.error(e))
  .finally(async () => await prisma.$disconnect());

Add to package.json:

"prisma": {
  "seed": "ts-node prisma/seed.ts"
}

Run:

npx prisma db seed

Next steps/expansion

• Add JWT auth: @nestjs/jwt + passport-jwt — store hashed passwords, issue tokens

• Refresh tokens and cookie best practices

• Rate-limiting and CORS

• Pagination, filtering, and projection endpoints

• E2E tests with supertest + in-memory DB or test Postgres

• Dockerfile & production docker-compose (with migrations)

• Database backups & connection pool (PGBouncer) notes

• Monitoring: Query logging & slow query detection

Common pitfalls & fixes

• PrismaClientUnknownRequestError on serverless — configure disablePrismaClientInstanceCreation or use PrismaClient per-request for serverless.

• ECONNREFUSED — ensure Docker Postgres is up and DATABASE_URL is correct (host mapping, port).

• Type errors — run npx prisma generate after schema changes.

You can get the full source code on our GitHub.

That's just the basics. If you need more deep learning about Nest.js, you can take the following cheap course:

• Learn NestJS from Scratch
• Mastering NestJS
• Angular & NestJS Full Stack Web Development Bootcamp 2023
• React and NestJS: A Practical Guide with Docker
• NestJS Masterclass - NodeJS Framework Backend Development
• Build A TodoList with NestJS and Vue JS
• The Complete Angular & NestJS Course

Thanks!