Hello Backend devs...
Time to connect NestJS application with a database. There are many databases can be connected with NestJS, including Sql or NoSql types. Choosing a right database is another crucial decision in the application's architecture. Today, let's focus on connect with the PostgreSQL database - a powerful popular relational database.
Obviously, we can use native drivers to connect with any databases from NestJS app. But, most of time, it is better to use an ORM, since, it is more time efficient and maintainable.
Today, let's focus on Prisma ORM, one of the most modern and developer-friendly ORMs, to connect PostgreSQL with a NestJS application.
📌 Install dependencies
# adding prisma cli
$ pnpm add -D prisma
# prisma client for development
$ pnpm add @prisma/client
📌 Initialise Prisma
This will create prisma folder and .env file in the root
$ pnpx prisma init
📌 Configurations
Need to update .env file for the database connection.
DATABASE_URL="postgresql://johndoe:randompassword@localhost:5432/mydb?schema=public"
📌 Migrations
Schema file located at prisma/schema.prisma file. There are two ways to run migrations here,
Migrations
Mostly use this approach. We create a schema on code, then push the changes to the database.Introspection
In here, When already have the database, we can update the schema by pulling it. Command in bellow,
$ pnpx prisma db pull
Let's go through the most common approach Migrations. In the first stage, we don't have anything in the database. We need to create it from the sketch. Assume that we need to create a user table. What we have to do is update the schema.prisma file as needed,
generator client {
provider = "prisma-client-js"
}
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
enum UserType {
ADMIN
USER
}
model Role {
id Int @id @default(autoincrement())
name String @unique
created_at DateTime @default(now())
User User[]
}
model User {
id Int @id @default(autoincrement())
name String
email String @unique
password String
role_id Int
type UserType // custom type with enum
created_at DateTime @default(now())
Role Role @relation(fields: [role_id], references: [id])
}
Now, need to create the first migration file,
# pnpx prisma migrate dev --name <description_of_change>
$ pnpx prisma migrate dev --name init
This will do two actions.
Create new migration file inside prisma/migrations folder
Create tables in the connected database provided in the schema file
Anyway, we have a option to only create the migration file without execute it,
$ pnpx prisma migrate dev --name <description_of_change> --create-only
We can execute this any time by,
$ pnpx prisma migrate dev
📌 Update table
What if we want to modify existing table or add a new table to the database?
Modify the prisma.schema file for the new change.
Best practices:
- Single logical change/s per migration
- Always, create a migration file for change using
prisma migrate dev. This will re generate the prisma client and create a migration file. - Review migration without execute it using
npx prisma migrate dev --create-only - Descriptive naming on migration file
- Never edit executed migration files. Create a new one for change
Let's assume that we want to add optional age filed to the user table. Modify the user model.
generator client {
provider = "prisma-client-js"
}
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
model Role {
id Int @id @default(autoincrement())
name String @unique
created_at DateTime @default(now())
User User[]
}
model User {
id Int @id @default(autoincrement())
name String
email String @unique
password String
age Int? // ? mark indicate that optional
role_id Int
created_at DateTime @default(now())
Role Role @relation(fields: [role_id], references: [id])
}
Generate migration,
$ pnpx prisma migrate dev --name add-age-field-to-user --create-only
$ pnpx prisma migrate dev
📌 Deploy in production
When we want to apply migrations on production by manually or using CI/CD, then much safe to use,
$ pnpx prisma migrate deploy
Why it safe for production? This does not generate any migrations. Only apply the changes (diff) to the database and does not re generate the prisma client
📌 General tips
- When delete node_modules folder, prisma client also will be deleted. Prisma client usually located at node_modules/@prisma folder. Need to re generate it before use in the code. Better option would be add the following to the package.json
"scripts": {
"postinstall": "prisma generate",
}
OR run the command manually,
$ pnpx prisma generate
📌 Connect from NestJS
Now, we have done the setup for database infrastructure. Let's focus on how do we connect NestJS with the created database.
First step is create a prisma module. (It is work just with a prisma service without having a prisma module. But, with the NestJS module philosophy, it is recommended to have a prisma module)
Then, prisma service,
$ nest generate module prisma
$ nest generate service prisma
Once module generated, module import automatically add to the imports list in the app.module. (this is not necessary. without import prisma module from here, it works as usual)
Update src/prisma/prisma.service.ts file as bellow,
import { Injectable, OnModuleInit } from '@nestjs/common';
import { PrismaClient } from '@prisma/client';
@Injectable()
export class PrismaService extends PrismaClient implements OnModuleInit {
async onModuleInit() {
await this.$connect();
}
}
Now, Prisma will be globally available for dependency injection from any services. Let's try that as well,
Let's say we have a user module. Then when we want to access prisma inside a user module, first, need to import prisma service in the user module:
import { Module } from '@nestjs/common';
import { UserService } from './user.service';
import { UserController } from './user.controller';
import { PrismaService } from 'src/prisma/prisma.service';
@Module({
controllers: [UserController],
providers: [UserService, PrismaService],
})
export class UserModule {}
Then, can inject prisma service to the user service and use it within the user service,
import { Injectable } from '@nestjs/common';
import { PrismaService } from '../prisma/prisma.service';
@Injectable()
export class UserService {
// injecting prisma service to user service
constructor(private prisma: PrismaService) {}
async findAll(): Promise<User[]> {
return this.prisma.user.findMany();
}
async findOne(id: number): Promise<User | null> {
return this.prisma.user.findUnique({
where: { id },
});
}
async create(data: CreateUserDto): Promise<User> {
return this.prisma.user.create({
data,
});
}
async update(id: number, data: UpdateUserDto): Promise<User> {
return this.prisma.user.update({
where: { id },
data,
});
}
async remove(id: number): Promise<User> {
return this.prisma.user.delete({
where: { id },
});
}
}
That's it 🏆
📌 GUI for Prisma
Prisma has awesome tool to visualise the data.
$ pnpx prisma studio
This will open the GUI on http://localhost:5555
📌 Conclusion
There are many ORM supports with NestJS. Drizzle and TypeORM are the other most popular candidates. I hear that Drizzle is much performant than others. All candidates have tradeoffs each other and I use Prisma for small and medium scale projects. It is easy to use when compare with TypeORM.
May be you can share your thoughts on this?
🤝 Let's meet again with another important topic
Cheers... Happy coding!!!
Top comments (0)