NodeJS

Prisma with Nest.js: The Ultimate Guide to Efficient Database Queries

Introduction

Prisma is a modern ORM (Object-Relational Mapping) tool that simplifies database access in Node.js applications. It provides type safety, auto-generated queries, and a powerful query engine, making it an excellent choice for working with databases in Nest.js.

Why Use Prisma with Nest.js?

Nest.js applications typically use TypeORM or Sequelize for database interactions, but Prisma offers several advantages:

Type Safety – Prevents errors by enforcing strict TypeScript types.

Auto-Generated Queries – No need to write raw SQL manually.

Migrations & Schema Management – Easily modify and sync your database schema.

Supports Multiple Databases – Works with PostgreSQL, MySQL, MongoDB, and more.

Performance Optimizations – Uses a highly efficient query engine.

Installing Prisma in a Nest.js Project

First, install Prisma and the required database connector (e.g., PostgreSQL, MySQL, or SQLite).

npm install @prisma/client

npm install --save-dev prisma

Next, initialize Prisma:

npx prisma init

This creates a prisma directory with a schema file (schema.prisma).

Configuring Prisma for Nest.js

Update schema.prisma

Edit the prisma/schema.prisma file to define your database provider and tables.

For example, using PostgreSQL:

generator client { provider = "prisma-client-js"}datasource db { provider = "postgresql" url = env("DATABASE_URL")}model User { id String @id @default(uuid()) name String email String @unique createdAt DateTime @default(now())}

Now, set up your database URL in .env:

DATABASE_URL="postgresql://user:password@localhost:5432/mydb"

Run the migration to apply changes:

npx prisma migrate dev --name init

Setting Up Prisma in Nest.js

Create a Prisma Service

Nest.js follows a modular architecture, so we create a Prisma service to manage database interactions.

Run:

nest g service prisma

Then, update prisma.service.ts:

import { Injectable, OnModuleDestroy, OnModuleInit } from '@nestjs/common';import { PrismaClient } from '@prisma/client';@Injectable()export class PrismaService extends PrismaClient implements OnModuleInit, OnModuleDestroy { async onModuleInit() { await this.$connect(); // Connect to database on app start } async onModuleDestroy() { await this.$disconnect(); // Disconnect when app stops }}

Provide Prisma in the App Module

Now, register PrismaService in app.module.ts

import { Module } from '@nestjs/common';import { PrismaService } from './prisma/prisma.service';@Module({ providers: [PrismaService], exports: [PrismaService],})export class AppModule {}

This allows Prisma to be used in different modules across the app.

Performing Database Queries with Prisma

Now that Prisma is set up, let's create a UserService to handle database operations.

Run:

nest g service user

Then, modify user.service.ts:

import { Injectable } from '@nestjs/common';import { PrismaService } from '../prisma/prisma.service';@Injectable()export class UserService { constructor(private prisma: PrismaService) {} // Create a new user async createUser(name: string, email: string) { return this.prisma.user.create({ data: { name, email }, }); } // Get all users async getAllUsers() { return this.prisma.user.findMany(); } // Get a user by ID async getUserById(id: string) { return this.prisma.user.findUnique({ where: { id } }); } // Update a user async updateUser(id: string, data: { name?: string; email?: string }) { return this.prisma.user.update({ where: { id }, data, }); } // Delete a user async deleteUser(id: string) { return this.prisma.user.delete({ where: { id } }); }}

Using Prisma in a Controller

Now, let's create a UserController to expose APIs for user management.

Modify user.controller.ts:

import { Controller, Get, Post, Body, Param, Delete, Put } from '@nestjs/common';import { UserService } from './user.service';@Controller('users')export class UserController { constructor(private userService: UserService) {} @Post() createUser(@Body() data: { name: string; email: string }) { return this.userService.createUser(data.name, data.email); } @Get() getAllUsers() { return this.userService.getAllUsers(); } @Get(':id') getUserById(@Param('id') id: string) { return this.userService.getUserById(id); } @Put(':id') updateUser(@Param('id') id: string, @Body() data: { name?: string; email?: string }) { return this.userService.updateUser(id, data); } @Delete(':id') deleteUser(@Param('id') id: string) { return this.userService.deleteUser(id); }}

Now, you can start your Nest.js server and test the API with Postman or cURL.

Advanced Prisma Features

Using Select & Include

To fetch specific fields:

const user = await this.prisma.user.findUnique({ where: { id }, select: { name: true, email: true }, // Fetch only name & email});

To fetch related data:

const userWithPosts = await this.prisma.user.findUnique({ where: { id }, include: { posts: true }, // Include related posts});

Raw SQL Queries with Prisma

const users = await this.prisma.$queryRaw`SELECT * FROM "User" WHERE "email" LIKE '%@gmail.com'`;

Pagination with Prisma

const users = await this.prisma.user.findMany({ take: 10, // Limit results to 10 skip: 20, // Skip first 20 results orderBy: { createdAt: 'desc' }, // Sort by createdAt});

Key Takeaways

Prisma is a powerful ORM that simplifies database access in Nest.js.
Easy setup: Install Prisma, define your schema, and generate migrations.
PrismaService: A central service in Nest.js to handle database interactions.
CRUD Operations: Easily perform create, read, update, and delete operations.
Advanced Features: Supports pagination, filtering, raw SQL, and relational queries.