How to use Prisma ORM and Prisma Postgres with NestJS

Introduction

This guide shows you how to use Prisma ORM with NestJS, a progressive Node.js framework for building efficient and scalable server-side applications. You'll build a REST API with NestJS that uses Prisma ORM to store and retrieve data from a database.

Prisma ORM is an open-source ORM for Node.js and TypeScript. It is used as an alternative to writing plain SQL, or using another database access tool such as SQL query builders (like knex.js) or ORMs (like TypeORM and Sequelize). Prisma currently supports PostgreSQL, MySQL, SQL Server, SQLite, MongoDB and CockroachDB.

While Prisma can be used with plain JavaScript, it embraces TypeScript and provides a level of type-safety that goes beyond the guarantees other ORMs in the TypeScript ecosystem offer.

You can find a ready-to-run example here

Prerequisites

Node.js 20+

1. Create your NestJS project

Install the NestJS CLI and create a new project:

npm install -g @nestjs/cli

nest new nestjs-prisma

When prompted, select npm as your package manager. Navigate to the project directory:

cd nestjs-prisma

You can run npm start to start your application at http://localhost:3000/. Over the course of this guide, you'll add routes to store and retrieve data about users and posts.

In package.json, add the type field set to "module":

package.json

{
  "type": "module"
}

2. Set up Prisma

2.1. Install Prisma and dependencies

Install the necessary Prisma packages and database drivers:

npm install prisma --save-dev

npm install @prisma/client @prisma/adapter-pg pg

If you are using a different database provider (PostgreSQL, MySQL, SQL Server), install the corresponding driver adapter package instead of @prisma/adapter-pg. For more information, see Database drivers.

2.2. Initialize Prisma

Initialize Prisma in your project:

npx prisma init --db --output ../src/generated/prisma

This creates a new prisma directory with the following contents:

schema.prisma: Specifies your database connection and contains the database schema
prisma.config.ts: A configuration file for your projects
.env: A dotenv file, typically used to store your database credentials in a group of environment variables

2.3. Set the generator output path

Specify your output path for the generated Prisma client by either passing --output ../src/generated/prisma during prisma init or directly in your Prisma schema:

prisma/schema.prisma

generator client {
  provider = "prisma-client"
  output   = "../src/generated/prisma"
}

2.4. Configure your database connection

Your database connection is configured in the datasource block in your schema.prisma file. By default it's set to postgresql which is what you need for this guide.

prisma/schema.prisma

generator client {
  provider = "prisma-client"
  output   = "../src/generated/prisma"
}

datasource db {
  provider = "postgresql"
}

Now, open up .env and you should see a DATABASE_URL already specified:

.env

DATABASE_URL=""

Make sure you have a ConfigModule configured, otherwise the DATABASE_URL variable will not be picked up from .env.

2.5. Define your data model

Add the following two models to your schema.prisma file:

prisma/schema.prisma

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

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

2.6. Create and run your migration

With your Prisma models in place, you can generate your SQL migration files and run them against the database. Run the following commands in your terminal:

npx prisma migrate dev --name init

This prisma migrate dev command generates SQL files and directly runs them against the database. In this case, the following migration files was created in the existing prisma directory:

$ tree prisma
prisma
├── migrations
│   └── 20201207100915_init
│       └── migration.sql
└── schema.prisma

2.7. Generate Prisma Client

Once installed, you can run the generate command to generate the types and Client needed for your project. If any changes are made to your schema, you will need to rerun the generate command to keep those types in sync.

npx prisma generate

3. Create a Prisma service

You're now able to send database queries with Prisma Client. When setting up your NestJS application, you'll want to abstract away the Prisma Client API for database queries within a service. To get started, you can create a new PrismaService that takes care of instantiating PrismaClient and connecting to your database.

Inside the src directory, create a new file called prisma.service.ts and add the following code to it:

src/prisma.service.ts

import { Injectable } from "@nestjs/common";
import { PrismaClient } from "./generated/prisma/client.js";
import { PrismaPg } from "@prisma/adapter-pg";

@Injectable()
export class PrismaService extends PrismaClient {
  constructor() {
    const adapter = new PrismaPg({
      connectionString: process.env.DATABASE_URL as string,
    });
    super({ adapter });
  }
}

4. Create User and Post services

Next, you can write services that you can use to make database calls for the User and Post models from your Prisma schema.

4.1. Create the User service

Still inside the src directory, create a new file called user.service.ts and add the following code to it:

src/user.service.ts

import { Injectable } from "@nestjs/common";
import { PrismaService } from "./prisma.service.js";
import { User, Prisma } from "./generated/prisma/client.js";

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

  async user(userWhereUniqueInput: Prisma.UserWhereUniqueInput): Promise<User | null> {
    return this.prisma.user.findUnique({
      where: userWhereUniqueInput,
    });
  }

  async users(params: {
    skip?: number;
    take?: number;
    cursor?: Prisma.UserWhereUniqueInput;
    where?: Prisma.UserWhereInput;
    orderBy?: Prisma.UserOrderByWithRelationInput;
  }): Promise<User[]> {
    const { skip, take, cursor, where, orderBy } = params;
    return this.prisma.user.findMany({
      skip,
      take,
      cursor,
      where,
      orderBy,
    });
  }

  async createUser(data: Prisma.UserCreateInput): Promise<User> {
    return this.prisma.user.create({
      data,
    });
  }

  async updateUser(params: {
    where: Prisma.UserWhereUniqueInput;
    data: Prisma.UserUpdateInput;
  }): Promise<User> {
    const { where, data } = params;
    return this.prisma.user.update({
      data,
      where,
    });
  }

  async deleteUser(where: Prisma.UserWhereUniqueInput): Promise<User> {
    return this.prisma.user.delete({
      where,
    });
  }
}

Notice how you're using Prisma Client's generated types to ensure that the methods that are exposed by your service are properly typed. You therefore save the boilerplate of typing your models and creating additional interface or DTO files.

4.2. Create the Post service

Now do the same for the Post model.

Still inside the src directory, create a new file called post.service.ts and add the following code to it:

src/post.service.ts

import { Injectable } from "@nestjs/common";
import { PrismaService } from "./prisma.service.js";
import { Post, Prisma } from "./generated/prisma/client.js";

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

  async post(postWhereUniqueInput: Prisma.PostWhereUniqueInput): Promise<Post | null> {
    return this.prisma.post.findUnique({
      where: postWhereUniqueInput,
    });
  }

  async posts(params: {
    skip?: number;
    take?: number;
    cursor?: Prisma.PostWhereUniqueInput;
    where?: Prisma.PostWhereInput;
    orderBy?: Prisma.PostOrderByWithRelationInput;
  }): Promise<Post[]> {
    const { skip, take, cursor, where, orderBy } = params;
    return this.prisma.post.findMany({
      skip,
      take,
      cursor,
      where,
      orderBy,
    });
  }

  async createPost(data: Prisma.PostCreateInput): Promise<Post> {
    return this.prisma.post.create({
      data,
    });
  }

  async updatePost(params: {
    where: Prisma.PostWhereUniqueInput;
    data: Prisma.PostUpdateInput;
  }): Promise<Post> {
    const { data, where } = params;
    return this.prisma.post.update({
      data,
      where,
    });
  }

  async deletePost(where: Prisma.PostWhereUniqueInput): Promise<Post> {
    return this.prisma.post.delete({
      where,
    });
  }
}

Your UserService and PostService currently wrap the CRUD queries that are available in Prisma Client. In a real world application, the service would also be the place to add business logic to your application. For example, you could have a method called updatePassword inside the UserService that would be responsible for updating the password of a user.

5. Implement REST API routes

5.1. Create the controller

Finally, you'll use the services you created in the previous sections to implement the different routes of your app. For the purpose of this guide, you'll put all your routes into the already existing AppController class.

Replace the contents of the app.controller.ts file with the following code:

src/app.controller.ts

import { Controller, Get, Param, Post, Body, Put, Delete } from "@nestjs/common";
import { UserService } from "./user.service.js";
import { PostService } from "./post.service.js";
import { User as UserModel } from "./generated/prisma/client.js";
import { Post as PostModel } from "./generated/prisma/client.js";

@Controller()
export class AppController {
  constructor(
    private readonly UserService: UserService,
    private readonly postService: PostService,
  ) {}

  @Get("post/:id")
  async getPostById(@Param("id") id: string): Promise<PostModel | null> {
    return this.postService.post({ id: Number(id) });
  }

  @Get("feed")
  async getPublishedPosts(): Promise<PostModel[]> {
    return this.postService.posts({
      where: { published: true },
    });
  }

  @Get("filtered-posts/:searchString")
  async getFilteredPosts(@Param("searchString") searchString: string): Promise<PostModel[]> {
    return this.postService.posts({
      where: {
        OR: [
          {
            title: { contains: searchString },
          },
          {
            content: { contains: searchString },
          },
        ],
      },
    });
  }

  @Post("post")
  async createDraft(
    @Body() postData: { title: string; content?: string; authorEmail: string },
  ): Promise<PostModel> {
    const { title, content, authorEmail } = postData;
    return this.postService.createPost({
      title,
      content,
      author: {
        connect: { email: authorEmail },
      },
    });
  }

  @Post("user")
  async signupUser(@Body() userData: { name?: string; email: string }): Promise<UserModel> {
    return this.UserService.createUser(userData);
  }

  @Put("publish/:id")
  async publishPost(@Param("id") id: string): Promise<PostModel> {
    return this.postService.updatePost({
      where: { id: Number(id) },
      data: { published: true },
    });
  }

  @Delete("post/:id")
  async deletePost(@Param("id") id: string): Promise<PostModel> {
    return this.postService.deletePost({ id: Number(id) });
  }
}

This controller implements the following routes:

`GET`

/post/:id: Fetch a single post by its id
/feed: Fetch all published posts
/filtered-posts/:searchString: Filter posts by title or content

`POST`

/post: Create a new post
- Body:
  - title: String (required): The title of the post
  - content: String (optional): The content of the post
  - authorEmail: String (required): The email of the user that creates the post
/user: Create a new user
- Body:
  - email: String (required): The email address of the user
  - name: String (optional): The name of the user

`PUT`

/publish/:id: Publish a post by its id

`DELETE`

/post/:id: Delete a post by its id

5.2. Register services in the app module

Remember to register the new services in the app module.

Update src/app.module.ts to register all services:

src/app.module.ts

import { Module } from "@nestjs/common";
import { AppController } from "./app.controller";
import { ConfigModule } from "@nestjs/config";
import { AppService } from "./app.service.js";
import { PrismaService } from "./prisma.service.js"; 
import { UserService } from "./user.service.js"; 
import { PostService } from "./post.service.js"; 

@Module({
  imports: [ConfigModule.forRoot()],
  controllers: [AppController],
  providers: [AppService, PrismaService, UserService, PostService], 
})
export class AppModule {}

6. Test your API

Start your application:

npm start

Test your endpoints with curl, Postman, or HTTPie.

Create a user:

curl -X POST http://localhost:3000/user \
  -H "Content-Type: application/json" \
  -d '{"name": "Alice", "email": "alice@prisma.io"}'

Create a post:

curl -X POST http://localhost:3000/post \
  -H "Content-Type: application/json" \
  -d '{"title": "Hello World", "authorEmail": "alice@prisma.io"}'

Get published posts:

curl http://localhost:3000/feed

Publish a post:

curl -X PUT http://localhost:3000/publish/1

Search posts:

curl http://localhost:3000/filtered-posts/hello

Summary

In this guide, you learned how to use Prisma ORM with NestJS to implement a REST API. The controller that implements the routes of the API is calling a PrismaService which in turn uses Prisma Client to send queries to a database to fulfill the data needs of incoming requests.

If you want to learn more about using NestJS with Prisma, be sure to check out the following resources:

NestJS

On this page