Using Prisma with PostgreSQL in NestJS

Introduction

Prisma is a next-generation ORM (Object-Relational Mapping) for NodeJS and TypeScript. It helps developers interact with databases intuitively and in a type-safe manner. Prisma possesses many outstanding advantages, including the ability to automatically generate optimal queries, automate the data migration process, provide powerful code auto-completion, and minimize runtime errors thanks to TypeScript's strict type-checking system.
Compared to TypeORM, Prisma delivers a superior development experience thanks to a centralized, readable, and maintainable schema structure within a single file (schema.prisma). Instead of having to define complex Entity classes with multiple decorators like TypeORM, Prisma automatically generates Prisma Client based on the schema, ensuring absolute synchronization between the database and code. Additionally, Prisma's Rust-based query engine mechanism helps optimize data query performance with PostgreSQL more effectively, naturally avoiding classic issues like N+1 queries without requiring complex configurations like TypeORM.

Prerequisites

In this article, there is a requirement to connect to PostgreSQL; you can check out this article which has instructions, so I will not discuss that part in detail here.

Detail

Please install the Prisma packages as follows

yarn add @prisma/client @prisma/adapter-pg
yarn add -D prisma

Update the .env file to create the database connection url

DATABASE_URL = postgresql://{USER}:{PASSWORD}@{HOST}:{PORT}/{DATABASE_NAME}?schema={SCHEMA_NAME}

Create the prisma.config.ts file

import {defineConfig, env} from '@prisma/config'

export default defineConfig({
  datasource: {
    url: env('DATABASE_URL'),
  },
})

Create the prisma/schema.prisma file

datasource db {
  provider = "postgresql"
}

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

model Product {
  id    Int    @id @default(autoincrement())
  name  String
  price Float
}

The code block above is the core configuration file of Prisma (schema.prisma). In it, the datasource db block defines the connection to the PostgreSQL database. The generator client block specifies the initialization of Prisma Client for JavaScript/TypeScript. The model Product block defines the structure of the Product data table in the database including 3 fields: id (integer type, which is the primary key and autoincrements), name (string type) and price (float type).

Please execute the following commands

npx prisma migrate dev --name initDB
npx prisma generate

The first command (prisma migrate dev) functions to compare the schema file with the current state of the database, automatically creating a SQL migration file saved in the prisma/migrations directory and executing that file to create/update the Product table in PostgreSQL.
The second command (prisma generate) functions to read the schema file to initialize or update the Prisma Client library, helping to generate precise data types (types) for the Product table within the TypeScript source code.

After running, you can check that the migration file and the corresponding table have been created in the PostgreSQL Database.

Create the service/prisma.service.ts file

import {Injectable, OnModuleInit} from '@nestjs/common'
import {PrismaPg} from '@prisma/adapter-pg'
import {PrismaClient} from '@prisma/client'

@Injectable()
export class PrismaService extends PrismaClient implements OnModuleInit {
  constructor() {
    super({
      adapter: new PrismaPg({connectionString: process.env.DATABASE_URL}),
    })
  }

  async onModuleInit() {
    await this.$connect()
  }
}

This code creates PrismaService in NestJS by inheriting from PrismaClient. This service is decorated with @Injectable() so that it can be injected into other controllers or services. In the constructor initialization function, the PrismaPg object is used as a driver adapter to connect to PostgreSQL via DATABASE_URL. The onModuleInit() function executes NestJS's interface, ensuring that the application will automatically establish a connection to the database as soon as the module containing this service is successfully launched.

Create the controller/product.controller.ts file

import {
  Body,
  Controller,
  Delete,
  Get,
  Param,
  ParseIntPipe,
  Post,
  Put,
} from '@nestjs/common'
import {PrismaService} from 'src/service/prisma.service'

@Controller('product')
export class ProductController {
  constructor(private prisma: PrismaService) {}

  @Get()
  async findAll() {
    return this.prisma.product.findMany({orderBy: {id: 'desc'}})
  }

  @Post()
  async create(@Body() data: {name: string; price: number}) {
    return this.prisma.product.create({data})
  }

  @Put(':id')
  async update(
    @Param('id', ParseIntPipe) id: number,
    @Body() updateData: {name: string; price: number}
  ) {
    return this.prisma.product.update({
      where: {id},
      data: updateData,
    })
  }

  @Delete(':id')
  async delete(@Param('id', ParseIntPipe) id: number) {
    await this.prisma.product.delete({
      where: {id},
    })
    return {success: true}
  }
}

This code defines ProductController to handle HTTP requests (CRUD) related to the product object via the /product endpoint. All data operations are called directly through PrismaService injected into the controller.

Update the app.module.ts file to add the services and controllers

import {Module} from '@nestjs/common'
import {ConfigModule} from '@nestjs/config'
import {ProductController} from './controller/product.controller'
import {PrismaService} from './service/prisma.service'

@Module({
  imports: [
    ConfigModule.forRoot({
      isGlobal: true,
      envFilePath: '.env',
    }),
  ],
  controllers: [ProductController],
  providers: [PrismaService],
})
export class AppModule {}