Establishing PostgreSQL Connection with Prisma in NestJS: A Comprehensive Guide with Bookmark API Example

In modern web development, setting up a robust and efficient database connection is crucial for building scalable and maintainable applications. NestJS, a popular backend framework for Node.js, offers a comprehensive ecosystem for creating APIs and microservices. When combined with Prisma, an advanced database toolkit, and Docker, a platform for containerizing applications, you can achieve an optimized development environment for your database-driven applications. In this guide, we’ll walk through the process of establishing a PostgreSQL connection using Prisma in a NestJS setup, all within a Docker environment. To showcase the implementation, we’ll also create a Bookmark API as an example.

Table of Contents

1. Introduction
2. Prerequisites
3. Setting Up NestJS Project
4. Configuring Docker for PostgreSQL
5. Installing and Configuring Prisma
6. Creating PostgreSQL Connection with Prisma
7. Building the Bookmark API with Prisma
8. Running the Application

1. Introduction

NestJS is a powerful and extensible framework that enables developers to build efficient, scalable, and modular server-side applications. Prisma, on the other hand, simplifies database access with a type-safe and query-building API. By integrating these technologies within a Docker environment, you can ensure consistent development and deployment across different platforms.

2. Prerequisites

Before we begin, make sure you have the following prerequisites installed on your system:

• Node.js and npm: For running the NestJS application.
• Docker: For containerizing the PostgreSQL database.
• Nest CLI: For generating NestJS project files.
• Visual Studio Code (or any preferred code editor): For writing and editing code.

3. Setting Up NestJS Project

Let’s start by creating a new NestJS project. Open your terminal and execute the following commands:

# Install the Nest CLI globally (if already installed ignore this command)
npm install -g @nestjs/cli

# Create a new NestJS project
nest new nest-prisma-bookmark-api

Navigate to the project directory:

cd nest-prisma-bookmark-api

4. Configuring Docker for PostgreSQL

To ensure a consistent database environment, we’ll use Docker to set up a PostgreSQL container. Create a docker-compose.yml file in the root of your project directory with the following content:

version: '3.8'
services:
  postgres:
    image: postgres:latest
    environment:
      POSTGRES_USER: nestjs
      POSTGRES_PASSWORD: nestjs_password
      POSTGRES_DB: bookmark_db
    ports:
      - '5432:5432'
    networks:
      - postgres_network
networks:
      - postgres_network

This configuration sets up a PostgreSQL container with a user, password, and database name.

5. Installing and Configuring Prisma

Prisma simplifies database interactions by generating a type-safe client from your data model. To install Prisma, run:

npm install @prisma/cli

Initialize Prisma by running:

npx prisma init

Follow the prompts and choose the PostgreSQL database you set up with Docker. This will generate a prisma folder containing your database configuration.

6. Creating PostgreSQL Connection with Prisma

Open the prisma/schema.prisma file and define your data model. For our Bookmark API example, let's create a Bookmark model:

model Bookmark {
  id        Int      @id @default(autoincrement())
  title     String
  url       String
  createdAt DateTime @default(now())
}

After defining the schema, apply the changes to the database by running:

npx prisma migrate dev

This will generate the necessary database tables.

7. Building the Bookmark API with Prisma

Create a new module for the Bookmark API:

nest generate module bookmark

Inside the generated bookmark module, create a bookmark.entity.ts file with the following content:

import { Prisma } from '.prisma/client';

export class Bookmark implements Prisma.BookmarkCreateInput {
  id?: number;
  title: string;
  url: string;
  createdAt?: Date;
}

Now, create a bookmark.service.ts file within the same module:

import { Injectable } from '@nestjs/common';
import { PrismaService } from 'src/prisma/prisma.service';
import { Bookmark } from './bookmark.entity';

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

  async create(data: Bookmark): Promise<Bookmark> {
    return this.prisma.bookmark.create({
      data,
    });
  }

  async findAll(): Promise<Bookmark[]> {
    return this.prisma.bookmark.findMany();
  }
}

Finally, create a bookmark.controller.ts file within the module:

import { Controller, Get, Post, Body } from '@nestjs/common';
import { BookmarkService } from './bookmark.service';
import { Bookmark } from './bookmark.entity';

@Controller('bookmark')
export class BookmarkController {
  constructor(private readonly bookmarkService: BookmarkService) {}

  @Post()
  async create(@Body() bookmarkData: Bookmark): Promise<Bookmark> {
    return this.bookmarkService.create(bookmarkData);
  }

  @Get()
  async findAll(): Promise<Bookmark[]> {
    return this.bookmarkService.findAll();
  }
}

8. Running the Application

With the Bookmark API implemented, it’s time to run the application. Start the PostgreSQL container using Docker Compose:

docker compose up -d

Then, start the NestJS application:

npm run start:dev

Your NestJS application with Prisma integration and the Bookmark API is now up and running. You can access the API at http://localhost:3000/bookmark.

Conclusion

In this article, we’ve explored the process of establishing a PostgreSQL connection using Prisma within a NestJS application. By combining these technologies with Docker, you can achieve a consistent and efficient development environment. The Bookmark API example demonstrates the seamless integration of Prisma and NestJS, enabling you to build powerful and scalable database-driven applications.

Here’s a link to a sample project repository for a more concrete demonstration of the concepts discussed in this article: Github Repo