Chapter 18
Prisma Integration with Nest.js
Setup Prisma
What is Prisma?
Prisma is different than traditional Object-Relational Mapping (ORM). Unlike traditional ORMs, Prisma prioritizes the generation of a strongly-typed query API that closely mirrors the database schema. This approach simplifies database operations, eliminates the need for manual SQL queries, and enforces robust type safety throughout development. Prisma enjoys popularity in the modern JavaScript and TypeScript ecosystem and finds extensive use in Node.js applications and frameworks like Nest.js.
The upcoming endeavor involves constructing REST APIs utilizing Prisma, harnessing its capabilities to streamline database-related tasks within the API development process.
Prisma encompasses three primary components:
Prisma Client: It functions as a potent query builder and database client, enabling type-safe interaction with the database using TypeScript or JavaScript. By automatically generating a query API based on the data model and schema, it delivers typing and autocompletion support during database queries.
Prisma Migrate: This tool empowers you to manage database schema changes in a versioned and controlled manner. Prisma Migrate simplifies the application of migrations, ensuring your database schema remains synchronized with your data model.
Prisma Studio: This tool provides a visual interface for database management, offering an intuitive way to inspect and manipulate database data directly from the browser. It facilitates tasks such as viewing, editing, and exploring data stored in your database.
Prisma supports multiple database connectors, including PostgreSQL, MySQL, SQLite, and SQL Server, offering compatibility with various database systems.
Step 1: Setup Prisma
To begin, install the prisma package. The starter kit code is provided, accessible on the starter branch, where running npm install will handle the installation of all required dependencies. This project serves as a fundamental Nest.js application, necessitating the installation of dependencies through the npm install command.
Step 2: Create Database
You have to create a database for the prisma project. You can choose any name for the database. Please create the database by using PGAdmin
Step 3: Install Prisma
Now you need to install prisma as a dev dependency
npm install -D prisma
Step 4: Initialize Prisma Project
npx prisma init
This will create the prisma folder and schema.prisma file. It will also create the .env file.
# Environment variables declared in this file are automatically made available to Prisma.
# See the documentation for more detail: https://pris.ly/d/prisma-schema#accessing-environment-variables-from-the-schema
# Prisma supports the native connection string format for PostgreSQL, MySQL, SQLite, SQL Server, MongoDB and CockroachDB.
# See the documentation for all the connection string options: https://pris.ly/d/connection-strings
DATABASE_URL="postgresql://postgres:root@localhost:5432/nest-prisma-db?schema=public"
You will see the prisma.schema file.
// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema
generator client {
provider = "prisma-client-js"
}
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
Models and Migrations
What is Prisma Model
In Prisma, a model is a fundamental building block that represents a structured data entity in your application. A model defines the structure of a database table and maps it to a corresponding TypeScript/JavaScript class. Models are used to interact with the database and provide a type-safe API for querying and manipulating data.
When using Prisma, you define models in a declarative manner using the Prisma schema language. The Prisma schema language allows you to define the data model of your application, including entities, fields, data types, relationships, and constraints.
Create Prisma Model
We are going to create our first Model in this lesson
model Song {
id Int @id @default(autoincrement())
title String
}
We have created a song model with id and title. I have set the id to autoincrement automatically
Run Migrations
Migrations in Prisma are a way to manage and apply changes to your database schema in a version-controlled and systematic manner. They allow you to make changes to your Prisma schema and then apply those changes to the actual database without losing data or compromising its integrity.
When you modify your Prisma schema to add, remove, or alter models or fields, you create a new migration to represent those changes. Migrations are recorded as code files and are stored in the prisma/migrations directory of your project. Each migration file contains the steps required to apply or revert a specific change to the database schema.
Key aspects of Prisma migrations include:
Version Control: Migrations are managed by Prisma and stored as code files. These files are stored in your version control system (such as Git), enabling collaboration and tracking changes over time.
Idempotent Operations: Migrations are designed to be idempotent, meaning that applying the same migration multiple times results in the same outcome. This ensures that migrating a database to a specific state is a repeatable process.
Data Preservation: Prisma migrations are designed to be data-safe, meaning that data in the database is preserved during the migration process. When you alter a table or add new fields, the existing data is migrated or transformed accordingly.
Rollback Support: Migrations also support rollback, which allows you to revert a previously applied migration and restore the database to its previous state. This feature is useful when dealing with errors or when undoing specific changes.
To use migrations in Prisma, you need to have Prisma Migrate installed. Prisma Migrate is a separate component of the Prisma toolkit that provides migration functionality
Let's run the migration using npx prisma migrate dev --name=init
I have provided the name of the migration init. You can provide any name for your migrations
This migration command will generate the database tables for you. You can check the tables from your PGAdmin. It will also generate the Typescript typings for you with the help of PrismaClient
Creating Prisma Service
Step 1: Setup Prisma Client
PrismaClient will generate the typescript typings for your models. PrismaClient will also provide the CRUD functionality. Let's install the Prisma client
npm install @prisma/client
Step 2: Create PrismaService
import { Injectable, OnModuleInit } from "@nestjs/common";
import { PrismaClient } from "@prisma/client";
@Injectable()
export class PrismaService extends PrismaClient implements OnModuleInit {
onModuleInit() {
this.$connect();
}
}
You have to create a new Prisma service inside the src folder. This PrismaService will make the connect with PrismaClient and interact with DB
Create, FindOne and Find
Step 1: Create a Resource
We are going to use the nestjs crud generator to generate api endpoints
nest g resource songs
You have to select the REST API and Endpoint to yes. This command will generate the songs module, controller, and service for you
Step 2: Update SongService
export class SongsService {
constructor(private prisma: PrismaService) {} //inject the prismaService as a dependency
create(createSongDto: Prisma.SongCreateInput) {
return this.prisma.song.create({
data: createSongDto,
});
}
findAll() {
return this.prisma.song.findMany(); //find all the records
}
findOne(id: number) {
return this.prisma.song.findUnique({ where: { id } }); // find unique single record based on id
}
}
We are going to use the types from the Prisma Client package. PrismaClient has generated many types of us you can find all of the types in node_modules/.prisma/client/index.d.ts
I have used the SongCreateInput for createSongDTO. You can find this type in index.d.ts file
export type SongCreateInput = {
title: string;
artist?: ArtistCreateNestedOneWithoutSongsInput;
};
Step 3: Update SongController
You can access the song object from the Prisma object. This song object is similar to SongRepository in TypeORM
We also have to update the type in the create method in SongController
@Post()
create(@Body() createSongDto: Prisma.SongCreateInput) {
return this.songsService.create(createSongDto);
}
Step 4: Register PrismaService
providers: [SongsService, PrismaService];
You have to register the PrismaService in SongModule
Step 5: Test the Application
You have to run the Application and test create, find, and findone endpoints
### Create Song
POST http://localhost:3000/songs
Content-Type: application/json
{
"title" : "FOLLOW ME"
}
### FETCH ALL SONGS
GET http://localhost:3000/songs
### FETCH SONG BY ID
GET http://localhost:3000/songs/2
Update and Delete
Step 1: Implement the Update method
//songs.service.ts
update(
where: Prisma.SongWhereUniqueInput,
updateSongDto: Prisma.SongUpdateInput,
) {
return this.prisma.song.update({
where,
data: updateSongDto,
});
}
I have used the SongWhereUniqueInput. You can find this type from PrismaClient. I have also used the SongUpdateInput from PrismaClient
export type SongWhereUniqueInput = Prisma.AtLeast<
{
id?: number;
AND?: SongWhereInput | SongWhereInput[];
OR?: SongWhereInput[];
NOT?: SongWhereInput | SongWhereInput[];
title?: StringFilter<"Song"> | string;
artistId?: IntNullableFilter<"Song"> | number | null;
artist?: XOR<ArtistNullableRelationFilter, ArtistWhereInput> | null;
},
"id"
>;
export type SongUpdateInput = {
title?: StringFieldUpdateOperationsInput | string;
artist?: ArtistUpdateOneWithoutSongsNestedInput;
};
Step 2: Update Controller
update(
@Param('id') id: string,
@Body() updateSongDto: Prisma.SongUpdateInput,
) {
return this.songsService.update({ id: +id }, updateSongDto);
}
Step 3: Delete Song
//songs.service.ts
remove(where: Prisma.SongWhereUniqueInput) {
return this.prisma.song.delete({ where });
}
//songs.controller.ts
@Delete(':id')
remove(@Param('id') id: string) {
return this.songsService.remove({ id: +id });
}
One to Many Relation
Step 1: Define Relation
An Artist can publish many songs
Many Songs can belong to a single Artist
model Song {
id Int @id @default(autoincrement())
title String
artist Artist? @relation(fields: [artistId], references: [id])
artistId Int?
}
model Artist {
id Int @id @default(autoincrement())
title String
name String
songs Song[]
}
It uses the @relation attribute to define the relationship. The @relation attribute has two arguments: fields and references. It specifies that the artist field in the Song model references the id field in the Artist model.
artistId: This field is of type Int and represents the foreign key that links a Song to its corresponding Artist. It is optional (Int?) to allow for songs that may not have an associated artist.
Step 2: Generate Artist Resource
First of all, you have to run the migrations for your one-to-many relation
npx prisma migrate dev --name=one-to-many-relation
nest g resource artists
Step 3: Create Artist
//artists.service.ts
export class ArtistsService {
constructor(private prisma: PrismaService) {}
create(createArtistDto: Prisma.ArtistCreateInput) {
return this.prisma.artist.create({
data: createArtistDto,
});
}
}
//artists.controller.ts
@Post()
create(@Body() createArtistDto: Prisma.ArtistCreateInput) {
return this.artistsService.create(createArtistDto);
}
//artists.module.ts
providers: [ArtistsService, PrismaService],
You have to register the PrismaService into ArtistModule
Now we need to send the HTTP request to create the artist
### Create Artist
POST http://localhost:3000/artists
Content-Type: application/json
{
"name" : "Avicci"
}
Step 4: Add Artist's relation during Song Creation
export type SongUncheckedCreateInput = {
id?: number;
title: string;
artistId?: number | null;
};
We have used that type in the create method which means I can add artistId in the request body while creating a new song
Let's test the application by providing artistId
### Create Song
POST http://localhost:3000/songs
Content-Type: application/json
{
"title" : "Animals",
"artistId":1
}
You can populate the record of the artist while fetching all the songs. You can include the artist in the query like this
findAll() {
return this.prisma.song.findMany({ include: { artist: true } });
}
### FETCH ALL SONGS
GET http://localhost:3000/songs
Now you will send the request to fetch all songs and you will get the artist record with a song
One to One Relation
Step 1: Add Relation
A user will have a unique profile. Each unique profile belongs to a single user. We have added the relationship between the user and the profile
model User {
id Int @id @default(autoincrement())
name String
profile Profile?
}
model Profile {
id Int @id @default(autoincrement())
user User @relation(fields: [userId], references: [id])
userId Int @unique
photo String
phone String
}
profile: This field represents the relationship between the User model and the Profile model. It uses the @relation attribute to define the relationship.
The @relation attribute has two arguments: fields and references. It specifies that the profile field in the User model references the id field in the Profile model.
The ? indicates that the profile field is optional, meaning a user may or may not have an associated profile
It uses the @unique attribute to ensure that each Profile is associated with a unique User
Step 2: Run Prisma Migration
npx prisma migrate dev --name=one-to-one
Step 3: Generate User Resource
nest g resource users
Step 4: Save User with Profile
//users.service.ts
create(createUserDto: CreateUserDto) {
return this.prisma.user.create({
data: {
name: createUserDto.name,
profile: {
create: {
phone: createUserDto.phone,
photo: createUserDto.photo,
},
},
},
});
}
Step 5: Find a user with Profile
findAll() {
return this.prisma.user.findMany({ include: { profile: true } });
}
Many to Many Relation
Use Case
One blog post can belong to many categories
Many categories can belong to a single blog post
Step 1: Add Many to Many Relation
model Post {
id Int @id @default(autoincrement())
title String
categories CategoriesOnPosts[]
}
categories: This field represents the relationship between the Post model and the CategoriesOnPosts model. It is an array of CategoriesOnPosts, indicating that a post can have multiple categories associated with it.
model Category {
id Int @id @default(autoincrement())
name String
posts CategoriesOnPosts[]
}
posts: This field represents the relationship between the Category model and the CategoriesOnPosts model. It is an array of CategoriesOnPosts, indicating that a category can be associated with multiple posts.
model CategoriesOnPosts {
post Post @relation(fields: [postId], references: [id])
postId Int
category Category @relation(fields: [categoryId], references: [id])
categoryId Int
asignedAt DateTime @default(now())
assignedBy String
@@id([postId, categoryId])
}
post: This field represents the relationship between the CategoriesOnPosts model and the Post model. It uses the @relation attribute to define the relationship. The @relation attribute has two arguments: fields and references. It specifies that the post field in the CategoriesOnPosts model references the id field in the Post model.
postId: This field is of type Int and represents the foreign key that links a CategoriesOnPosts to its corresponding Post.
category: This field represents the relationship between the CategoriesOnPosts model and the Category model. It uses the @relation attribute to define the relationship. The @relation attribute has two arguments: fields and references. It specifies that the category field in the CategoriesOnPosts model references the id field in the Category model.
categoryId: This field is of type Int and represents the foreign key that links a CategoriesOnPosts to its corresponding Category.
assignedAt: This field represents the timestamp when the category was assigned to the post. It uses the DateTime type and has a default value of now(), meaning it will be set to the current date and time when a new record is created.
assignedBy: This field represents the name of the user who assigned the category to the post and is of type String.
@@id([postId, categoryId]): This attribute defines a composite primary key using both postId and categoryId as the primary key fields. This ensures that a combination of post and category uniquely identifies each record in the CategoriesOnPosts table, allowing for a many-to-many relationship between posts and categories.
Step 2: Run Migrations
npx prisma migrate dev --name=many-to-many
Step 3: Create Posts Resource
nest g resource posts
Step 4: Create Post
//posts.service.ts
create(createPostDto: Prisma.PostCreateInput) {
return this.prisma.post.create({ data: createPostDto })
}
//posts.service.ts
create(createPostDto: Prisma.PostCreateInput) {
return this.prisma.post.create({ data: createPostDto })
}
Now we need to send the request to create a post with 2 new categories in the DB
### Create POST
POST http://localhost:3000/posts
Content-Type: application/json
{
"title": "One to Many Relation",
"categories": {
"create": [
{
"assignedBy": "Jane",
"asignedAt": "2023-08-01T10:03:38.016Z",
"category": {
"create": {
"name": "Prisma"
}
}
},
{
"assignedBy": "Jane",
"asignedAt": "2023-08-01T10:03:38.016Z",
"category": {
"create": {
"name": "Nest.js"
}
}
}
]
}
}
Step 5: Create a Post by making a relationship with Existing Categories
If you want to create a new post but don't want to create new categories you can make a relation with categories
POST http://localhost:3000/posts
Content-Type: application/json
{
"title": "Transactions in Prisma",
"categories": {
"create": [
{
"assignedBy": "Bob",
"asignedAt": "2023-08-01T10:07:00.918Z",
"category": {
"connect": {
"id": 1
}
}
},
{
"assignedBy": "Bob",
"asignedAt": "2023-08-01T10:07:00.918Z",
"category": {
"connect": {
"id": 2
}
}
}
]
}
}
Step 6: Relation Queries
//posts.service.ts
findAll(where: Prisma.PostWhereUniqueInput) {
return this.prisma.post.findMany({
where,
});
}
//posts.controller.ts
@Get()
findAll(
@Body()
where: Prisma.PostWhereUniqueInput,
){
return this.postsService.findAll(where);
}
Let's run the query to fetch all the posts with the Nest.js category
### FETCH ALL THE POSTS WITH NEST.JS CATEGORY
GET http://localhost:3000/posts
Content-Type: application/json
{
"categories": {
"some": {
"category": {
"name": "Nest.js"
}
}
}
}
Nested Queries
What is Transaction
In Prisma, a transaction is a way to group multiple database operations into a single logical unit of work that must either be fully completed or fully rolled back. Transactions ensure data consistency and integrity by making sure that if any part of the transaction fails, all changes made within that transaction are reverted, and the database remains in a consistent state.
When to use nested writes
Consider using nested writes if:
You want to create two or more records related by ID at the same time (for example, create a blog post and a user)
You want to update and create records related by ID at the same time (for example, change a user's name and create a new blog post)
Use Case
There is a One to one relationship between Customer and Address
There is a One to many relationship between Customer and Application
model Customer {
id Int @id @default(autoincrement())
name String
email String @unique
address Address? @relation(fields: [addressId], references: [id])
applications Application[]
addressId Int? @unique
}
model Address {
id Int @id @default(autoincrement())
zip String?
city String
country String
Customer Customer?
}
enum APPLICATION_TYPE {
LOAN
CAR_FINANCING
BUSINESS_FINANCING
}
model Application {
id Int @id @default(autoincrement())
type APPLICATION_TYPE @default(LOAN)
tenure String
amount Int
Customer Customer? @relation(fields: [customerId], references: [id])
customerId Int?
}
Step 2: Run migrations
npx prisma migrate dev --name=nested-queries
Step 3: Generate Application resource
nest generate resource applications
Step 4: Inject Prisma dependency
providers: [ApplicationsService, PrismaService],
You have to register the PrismaService as a provider in the Application Module
constructor(private prisma: PrismaService) {}
Step 5: Create the Application
create(createApplicationDto: Prisma.CustomerCreateInput) {
return this.prisma.customer.create({ data: createApplicationDto });
}
@Post()
create(@Body() createApplicationDto: Prisma.CustomerCreateInput) {
return this.applicationsService.create(createApplicationDto);
}
Let's create a new application:
### CREATE NEW APPLICATION
POST http://localhost:3000/applications
Content-Type: application/json
{
"email": "jane1@gmail.com",
"name": "Jone Doe",
"address": {
"create": {
"city": "New York",
"country": "USA",
"zip": "34443"
}
},
"applications": {
"create": [
{
"amount": 32224,
"tenure": "5 Years",
"type": "BUSINESS_FINANCING"
}
]
}
}
Application is dependent on the customerId and addressId If any of the records fails to be created then the application will not be created or if any error occurs while creating the application it will roll back the transaction for the customer and address. The records will be deleted in the customer and address tables
Batch Bulk Operations
If you have to perform batch and bulk operations like deleteMany, createMany
and updateMany then you can use the $transaction API
We are going to run multiple queries at the same time, if any of query fails you will not get any results back. It helps when you are creating multiple records at the same time. If one record fails then the complete operation will be discarded
return this.prisma.$transaction([
this.prisma.post.findMany(),
this.prisma.artist.findMany(),
this.prisma.song.findMany(),
this.prisma.application.findMany(),
]);
Interactive Transactions
Use Case:
You are going to build the account transfer feature in the banking application
John wants to transfer the amount of $100 to Sam Account
Process / Flow
Update the John Account or deduct the $100 from John
Check the balance of John account
If balance is less than $0 you have to rollback the transaction. The operation should not be continue
If any of the step fails the complete operation will be discarded
Step 1: Create Model
model Account {
id Int @id @default(autoincrement())
balance Float
title String
}
Step 2: Run Migrations and generate resource
npx prisma migrate dev --name=add-account
nest g resource accounts
Step 3: Create two new accounts
Inject PrismaService in AccountsModule
providers: [AccountsService, PrismaService],
Inject PrismaService as a dependency
constructor(private prisma: PrismaService) {}
create(createAccountDto: Prisma.AccountCreateInput) {
return this.prisma.account.create({ data: createAccountDto });
}
create(@Body() createAccountDto: Prisma.AccountCreateInput) {
return this.accountsService.create(createAccountDto);
}
### Create Account
POST http://localhost:3000/accounts
Content-Type: application/json
{
"title" : "John",
"balance" : 100
}
### Create Account
POST http://localhost:3000/accounts
Content-Type: application/json
{
"title" : "Sam",
"balance" : 100
}
You have to create two new accounts to do the testing
Step 4: Create route for transfer account
@Post('transfer')
transfer(@Body() transferAccountDTO: TransferAccountDTO) {
return this.accountsService.transfer(transferAccountDTO);
}
export class TransferAccountDTO {
sender: number;
receiver: number;
amount: number;
}
transfer(transferAccountDTO: TransferAccountDTO) {
throw new Error('Method not implemented.');
}
Step 5: Implement Account Transfer Logic
transfer(transferAccountDTO: TransferAccountDTO) {
const { sender: from, receiver: to, amount } = transferAccountDTO;
return this.prisma.$transaction(async (tx) => {
// John Account
// 1. Decrement amount from the sender.
const sender = await tx.account.update({
data: {
balance: {
decrement: amount,
},
},
where: {
id: from,
},
});
// 2. Verify that the sender's balance didn't go below zero.
if (sender.balance < 0) {
throw new Error(`${from} doesn't have enough to send ${amount}`);
}
// 3. Increment the recipient's balance by amount
// SAM Account
const recipient = await tx.account.update({
data: {
balance: {
increment: amount,
},
},
where: {
id: to,
},
});
return recipient;
});
}
Test Transfer Process
### Trasnfer Amount
POST http://localhost:3000/accounts/transfer
Content-Type: application/json
{
"sender" :1,
"receiver" :2,
"amount": 50
}
### Trasnfer Amount
POST http://localhost:3000/accounts/transfer
Content-Type: application/json
{
"sender" :1,
"receiver" :2,
"amount": 40
}
### Trasnfer Amount
POST http://localhost:3000/accounts/transfer
Content-Type: application/json
{
"sender" :1,
"receiver" :2,
"amount": 20
}
You will get an error at the last HTTP operation because John will not have a balance more than $20 so the transaction will be rollback
$7 bundle of my best NestJS + backend developer Courses.
More details coming soon.
Get the latest insights from the marketing world.
