TimoBlog
Back to all posts
Web Development

Building Full-Stack Applications with PostgreSQL, Next.js and NestJS using Prisma ORM

Timothy Benjamin Timothy Benjamin
12 min read
Building Full-Stack Applications with PostgreSQL, Next.js and NestJS using Prisma ORM

Table of Contents

Share this post

Twitter LinkedIn

Building Full-Stack Applications with PostgreSQL, Next.js, NestJS, and Prisma

Modern web development demands robust, type-safe, and maintainable applications. In this post, I’ll walk through building a complete application using PostgreSQL as the database, Prisma as the ORM, NestJS for the backend API, and Next.js for the frontend.

Why This Stack?

This powerful combination offers several advantages:

  • PostgreSQL: A robust, open-source relational database with excellent performance, reliability, and advanced features.
  • Prisma: A next-generation ORM that provides type-safe database access with auto-generated types.
  • NestJS: A progressive Node.js framework with a structured architecture for building scalable server-side applications.
  • Next.js: A React framework enabling server-side rendering, static site generation, and API routes.

Together, these technologies provide end-to-end type safety, excellent developer experience, and powerful tools for building modern web applications.

Setting Up PostgreSQL

First, let’s set up our PostgreSQL database:

Local Installation

# For Ubuntu/Debian
sudo apt update
sudo apt install postgresql postgresql-contrib

# For macOS using Homebrew
brew install postgresql
brew services start postgresql

Creating Your Database

# Connect to PostgreSQL
sudo -u postgres psql

# Create a database
CREATE DATABASE myapp;
CREATE USER myappuser WITH ENCRYPTED PASSWORD 'mypassword';
GRANT ALL PRIVILEGES ON DATABASE myapp TO myappuser;

Building the Backend with NestJS and Prisma

Setting Up the Project

# Install NestJS CLI
npm i -g @nestjs/cli

# Create a new NestJS project
nest new backend
cd backend

# Install Prisma
npm install prisma --save-dev
npm install @prisma/client

# Initialize Prisma
npx prisma init

Configure Database Connection

After initializing Prisma, it creates a .env file and a prisma directory with a schema.prisma file. Update the database connection in the .env file:

DATABASE_URL="postgresql://myappuser:mypassword@localhost:5432/myapp?schema=public"

Define Your Schema with Prisma

Replace the content of prisma/schema.prisma with:

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

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

model User {
  id        Int       @id @default(autoincrement())
  email     String    @unique
  name      String
  password  String
  createdAt DateTime  @default(now()) @map("created_at")
  posts     Post[]
  comments  Comment[]

  @@map("users")
}

model Post {
  id        Int       @id @default(autoincrement())
  title     String
  content   String
  published Boolean   @default(false)
  createdAt DateTime  @default(now()) @map("created_at")
  updatedAt DateTime  @default(now()) @updatedAt @map("updated_at")
  userId    Int       @map("user_id")
  user      User      @relation(fields: [userId], references: [id], onDelete: Cascade)
  comments  Comment[]

  @@map("posts")
}

model Comment {
  id        Int      @id @default(autoincrement())
  content   String
  createdAt DateTime @default(now()) @map("created_at")
  postId    Int      @map("post_id")
  userId    Int      @map("user_id")
  post      Post     @relation(fields: [postId], references: [id], onDelete: Cascade)
  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@map("comments")
}

Create Prisma Service

Create a Prisma service to manage the Prisma client:

nest generate module prisma
nest generate service prisma

Update 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();
  }
}

Update src/prisma/prisma.module.ts:

import { Module, Global } from '@nestjs/common';
import { PrismaService } from './prisma.service';

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

Generate Prisma Client and Migrate Database

# Create migrations
npx prisma migrate dev --name init

# Generate Prisma client
npx prisma generate

Creating API Features

Let’s build a users module:

nest generate resource users

Update src/users/users.service.ts:

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

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

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

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

  findOne(id: number) {
    return this.prisma.user.findUnique({
      where: { id },
      select: {
        id: true,
        email: true,
        name: true,
        createdAt: true,
      },
    });
  }

  update(id: number, updateUserDto: UpdateUserDto) {
    const data: any = {};
    
    if (updateUserDto.name) data.name = updateUserDto.name;
    if (updateUserDto.email) data.email = updateUserDto.email;
    if (updateUserDto.password) {
      data.password = bcrypt.hashSync(updateUserDto.password, 10);
    }
    
    return this.prisma.user.update({
      where: { id },
      data,
    });
  }

  remove(id: number) {
    return this.prisma.user.delete({ where: { id } });
  }
}

Create DTOs in src/users/dto/create-user.dto.ts:

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

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

  @IsString()
  @IsNotEmpty()
  name: string;

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

Building the Frontend with Next.js

Next, let’s set up our frontend application with Next.js:

Setup Next.js Project

npx create-next-app@latest frontend
cd frontend

Install Dependencies

npm install @prisma/client axios react-hook-form zod @hookform/resolvers
npm install -D prisma

Setting Up Prisma in Next.js

While we primarily use Prisma in the backend with NestJS, for certain operations like server-side rendering, we might want direct database access from Next.js.

Initialize Prisma in the Next.js project:

npx prisma init

Copy the same schema from the backend:

cp ../backend/prisma/schema.prisma ./prisma/

Update the .env file with the same database URL:

DATABASE_URL="postgresql://myappuser:mypassword@localhost:5432/myapp?schema=public"

Create a Prisma Client Instance

Create a file at lib/prisma.ts:

import { PrismaClient } from '@prisma/client';

// Prevent multiple instances of Prisma Client in development
declare global {
  var prisma: PrismaClient | undefined;
}

export const prisma = global.prisma || new PrismaClient();

if (process.env.NODE_ENV !== 'production') {
  global.prisma = prisma;
}

Create API Service

Create a file at services/api.ts:

import axios from 'axios';

const api = axios.create({
  baseURL: process.env.NEXT_PUBLIC_API_URL || 'http://localhost:3000',
});

export const fetchUsers = async () => {
  const response = await api.get('/users');
  return response.data;
};

export const fetchUser = async (id: number) => {
  const response = await api.get(`/users/${id}`);
  return response.data;
};

export const createUser = async (userData: any) => {
  const response = await api.post('/users', userData);
  return response.data;
};

export default api;

Create User Registration Form

Create a component at components/RegisterForm.tsx:

import { useForm } from 'react-hook-form';
import { zodResolver } from '@hookform/resolvers/zod';
import * as z from 'zod';
import { createUser } from '../services/api';
import { useState } from 'react';

const schema = z.object({
  name: z.string().min(2, 'Name must be at least 2 characters'),
  email: z.string().email('Invalid email address'),
  password: z.string().min(6, 'Password must be at least 6 characters'),
});

type FormData = z.infer<typeof schema>;

export default function RegisterForm() {
  const [isLoading, setIsLoading] = useState(false);
  const [error, setError] = useState('');
  const [success, setSuccess] = useState(false);
  
  const {
    register,
    handleSubmit,
    formState: { errors },
    reset,
  } = useForm<FormData>({
    resolver: zodResolver(schema),
  });

  const onSubmit = async (data: FormData) => {
    try {
      setIsLoading(true);
      setError('');
      await createUser(data);
      setSuccess(true);
      reset();
    } catch (err: any) {
      setError(err.response?.data?.message || 'An error occurred');
    } finally {
      setIsLoading(false);
    }
  };

  return (
    <div className="max-w-md mx-auto p-6 bg-white rounded-lg shadow-md">
      <h2 className="text-2xl font-bold mb-6">Register</h2>
      
      {success && (
        <div className="bg-green-100 p-3 mb-4 rounded text-green-700">
          Registration successful!
        </div>
      )}
      
      {error && (
        <div className="bg-red-100 p-3 mb-4 rounded text-red-700">
          {error}
        </div>
      )}
      
      <form onSubmit={handleSubmit(onSubmit)}>
        <div className="mb-4">
          <label className="block mb-1">Name</label>
          <input
            type="text"
            {...register('name')}
            className="w-full p-2 border rounded"
          />
          {errors.name && (
            <p className="text-red-500 text-sm mt-1">{errors.name.message}</p>
          )}
        </div>
        
        <div className="mb-4">
          <label className="block mb-1">Email</label>
          <input
            type="email"
            {...register('email')}
            className="w-full p-2 border rounded"
          />
          {errors.email && (
            <p className="text-red-500 text-sm mt-1">{errors.email.message}</p>
          )}
        </div>
        
        <div className="mb-6">
          <label className="block mb-1">Password</label>
          <input
            type="password"
            {...register('password')}
            className="w-full p-2 border rounded"
          />
          {errors.password && (
            <p className="text-red-500 text-sm mt-1">{errors.password.message}</p>
          )}
        </div>
        
        <button
          type="submit"
          disabled={isLoading}
          className="w-full bg-blue-500 text-white p-2 rounded hover:bg-blue-600 disabled:bg-blue-300"
        >
          {isLoading ? 'Registering...' : 'Register'}
        </button>
      </form>
    </div>
  );
}

Create User List Page

Create a page at pages/users/index.tsx:

import { GetServerSideProps } from 'next';
import Link from 'next/link';
import { fetchUsers } from '../../services/api';

interface User {
  id: number;
  name: string;
  email: string;
  createdAt: string;
}

interface UsersPageProps {
  users: User[];
}

export default function UsersPage({ users }: UsersPageProps) {
  return (
    <div className="max-w-4xl mx-auto p-6">
      <h1 className="text-3xl font-bold mb-6">Users</h1>
      
      <div className="bg-white shadow-md rounded-lg overflow-hidden">
        <table className="min-w-full divide-y divide-gray-200">
          <thead className="bg-gray-50">
            <tr>
              <th className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">ID</th>
              <th className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">Name</th>
              <th className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">Email</th>
              <th className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">Created At</th>
              <th className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">Actions</th>
            </tr>
          </thead>
          <tbody className="bg-white divide-y divide-gray-200">
            {users.map((user) => (
              <tr key={user.id}>
                <td className="px-6 py-4 whitespace-nowrap text-sm text-gray-500">{user.id}</td>
                <td className="px-6 py-4 whitespace-nowrap text-sm font-medium text-gray-900">{user.name}</td>
                <td className="px-6 py-4 whitespace-nowrap text-sm text-gray-500">{user.email}</td>
                <td className="px-6 py-4 whitespace-nowrap text-sm text-gray-500">
                  {new Date(user.createdAt).toLocaleDateString()}
                </td>
                <td className="px-6 py-4 whitespace-nowrap text-sm text-gray-500">
                  <Link href={`/users/${user.id}`} className="text-blue-600 hover:text-blue-900">
                    View
                  </Link>
                </td>
              </tr>
            ))}
          </tbody>
        </table>
      </div>
    </div>
  );
}

export const getServerSideProps: GetServerSideProps = async () => {
  try {
    const users = await fetchUsers();
    return {
      props: {
        users,
      },
    };
  } catch (error) {
    console.error('Error fetching users:', error);
    return {
      props: {
        users: [],
      },
    };
  }
};

Integration and Communication Between Services

NestJS CORS Configuration

To allow Next.js frontend to communicate with our NestJS backend, add CORS configuration in src/main.ts:

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

async function bootstrap() {
  const app = await NestFactory.create(AppModule);
  
  app.enableCors({
    origin: process.env.FRONTEND_URL || 'http://localhost:3000',
    credentials: true,
  });
  
  app.useGlobalPipes(new ValidationPipe({
    whitelist: true,
    transform: true,
  }));
  
  await app.listen(3000);
}
bootstrap();

Deployment Considerations

Docker Compose Setup

For development and potentially production, a Docker Compose setup can help manage all services:

version: '3.8'

services:
  postgres:
    image: postgres:14
    environment:
      POSTGRES_USER: myappuser
      POSTGRES_PASSWORD: mypassword
      POSTGRES_DB: myapp
    volumes:
      - postgres-data:/var/lib/postgresql/data
    ports:
      - "5432:5432"

  backend:
    build:
      context: ./backend
    environment:
      DATABASE_URL: postgresql://myappuser:mypassword@postgres:5432/myapp?schema=public
      NODE_ENV: development
      FRONTEND_URL: http://localhost:3000
    depends_on:
      - postgres
    ports:
      - "3000:3000"

  frontend:
    build:
      context: ./frontend
    environment:
      NEXT_PUBLIC_API_URL: http://localhost:3000
    ports:
      - "3000:3000"
    depends_on:
      - backend

volumes:
  postgres-data:

Conclusion

Building a full-stack application with PostgreSQL, Prisma, NestJS, and Next.js provides a powerful and type-safe development experience. This stack combines the best tools in the JavaScript/TypeScript ecosystem:

  1. PostgreSQL provides a robust, reliable database
  2. Prisma offers type-safe database access with excellent developer experience
  3. NestJS provides a structured, modular backend architecture
  4. Next.js delivers a flexible, performant frontend with modern React features

The combination of these technologies enables rapid development while maintaining code quality, type safety, and scalability. As your application grows, this stack provides the foundation needed to build complex features while keeping your codebase maintainable.

Whether you’re building a small project or a large-scale application, this tech stack provides a solid foundation that will serve you well throughout the development lifecycle.

#PostgreSQL #Next.js #NestJS #Prisma #TypeScript #Full-Stack #Web Development
Timothy Benjamin

About Timothy Benjamin

A Freelance Full-Stack Developer who brings company website visions to reality.

Visit my website