How to use Prisma with Express and Postgresql as database

Prisma is proud of its type-safety and auto-completion feature

It consist of three parts which makes it different from any traditional orm tool

• Prisma Client
• Prisma migration
• Prisma Studio

diagram shown below shows the abstract view of the working of different parts of prisma

Now let's dive deep into the prisma architecture and in order to understand how does prisma client and prisma migration works let's create a project .

I am gonna make use of prisma with express for building REST api and we are going to use postgresql as our database for this project .

Initialise your nodejs project with the following command and install the following dependencies

 npm init -y

npm install prisma typescript ts-node @types/node --save-dev

Now initialize prisma with the following command

 npx prisma init

This command downloads a .env file and a folder named prisma for you which contains a schema.prisma file after doing this your project directory should look like as shown below

If yes ? then we are good to move forward so firstly let's connect prisma with our local database .

Now open the .env file it contains a connection url which is used for connecting with the database , the general format of this url is shown below

postgresql://USER:PASSWORD@HOST:PORT/DATABASE?schema=SCHEMA

Change the values in the connection url according to your database configuration in order to make things work properly.

Here is look of my connection URL

DATABASE_URL="postgresql://devyanknagpal:devyanknagpal@localhost:5433/first"

Once you head over to the the schema.prisma file it contains some precoded piece of code for you which are generator and datasource objects, generator is used to create the prisma client for you which interacts with the database to make manipulation in the data and datasource is used to connect with the database

Now let's create models in our schema.prisma file which is going to represent the relations in our postgresql database .
Creating model in schema.prisma file is just like creating relation in database .

We are going to create two models namely User and Expense
things we need to know about prisma model is that the value passed inside any model represent table columns and it mainly consist of two more things i.e datatype and attribute , datatype represent the type of data being referred by the particular column and attribute is used for special case i.e wether a particular column consist of unique value or it is a primary key .

Other then that there are only three types of datatype modifiers as shown below

the code below down shows the two models are created and
One thing to notice here is that both model have primary key column which is referenced via @id attribute therefore it shows that it is necessary for every model to have one primary key.

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

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User{
  id String @id@default(uuid())
  fisrtname String
  lastname String
  email String
  phoneNo String
}

model Expense{
  expenseid String @id @default(uuid())
  topic String
  price Int


}

@default attribute is used to pass default value in our case we are generating the value of ids using uuid function.

Now let's create relation between the two models and in order to do that we need to add reference of User model in Expense model

Once you run the following command

npx prisma format

then code should look like as shown below it will autogenerate @relation attribute having reference and fields plus it will also add a reference of Expense model in User model.

model User {
  id        String    @id @default(uuid())
  fisrtname String
  lastname  String
  email     String
  phoneNo   String
  Expense   Expense[]
}

model Expense {
  expenseid     String @id @default(uuid())
  topic         String
  price         Int
  ExpenseByUser User   @relation(fields: [userId], references: [id])
  userId        String
}

Now models are created but we need to map these models to databases for that we need prisma migration .

Run the following command

npx prisma migrate dev --name init

Prisma migration will sync the schema with the database before running this command there was an't any relation inside the database but after running this command the below image shows the relations of the database

Now create a tsconfig.json file in your project root directory and add the following piece of code

{
    "compilerOptions": {
      "sourceMap": true,
      "outDir": "dist",
      "strict": true,
      "lib": ["esnext"],
      "esModuleInterop": true
    }
  }

In order to create rest api we need to install some other dependencies

npm install express 
npm install  nodemon @types/express --save-dev
npm install @prisma/client

Now create a script.ts file in the root directory of your project from the beginning of this blog we have been discussing that prisma client is used to interact with the database therefore we need to import PrismaClient in our script file.

finally its the time to make the rest api

therefor in order to manipulate data of User relation we need to access it

the above image shows what type of functions does prisma offer to manipulate data in relations .

Let's use create function in order to add data to User relation it takes data object in which all the values are passed

POST

app.post("/postuser",async(req,res)=>{
    const user=await prisma.user.create({
        data:{
            fisrtname:'devyank',
            lastname:'nagpal',
            email:'devyank@gmail.com',
            phoneNo:'4365879876'
        }
    })
       res.status(200).json(user);
})

GET

app.get("/getuser",async(req,res)=>{
    const user=await prisma.user.findMany()

       res.status(200).json(user);
})

We can also filter the output using Where just like we do in sql

app.get("/getuser",async(req,res)=>{
    const user=await prisma.user.findFirst({
        where:{
           fisrtname:'devyank'
        }
    })

       res.status(200).json(user);
})

UPDATE

while updating the data we need to pass the data that needs to be updated and in Where we need to pass a primary key column value .

app.put("/user",async(req,res)=>{
const user = await prisma.user.update({
    data:{
        email:'devyanknagpal@gmail.com'
    },
    where:{
        id:"c6bd44b2-762a-4a37-8c93-5d355b25c9fa"
    }
})
res.status(201).json(user);

})

DELETE

app.delete("/user",async(req,res)=>{
    const user= await prisma.user.delete({
        where:{
            id:"c6bd44b2-762a-4a37-8c93-5d355b25c9fa"
        }
    })
    res.status(201).json({message:'deleted'})
})

Once done with this you can pass the command given below to take tour of prisma studio

npx prisma studio