DEV Community

Cover image for How to use Prisma with Express and Postgresql as database
Devyank Nagpal
Devyank Nagpal

Posted on • Updated on

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

Three 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
Enter fullscreen mode Exit fullscreen mode
npm install prisma typescript ts-node @types/node --save-dev
Enter fullscreen mode Exit fullscreen mode

Now initialize prisma with the following command

 npx prisma init
Enter fullscreen mode Exit fullscreen mode

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

Project Directory

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
Enter fullscreen mode Exit fullscreen mode

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"
Enter fullscreen mode Exit fullscreen mode

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

schema.prisma file

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

datatype modifiers

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


}
Enter fullscreen mode Exit fullscreen mode

@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

relationship in prisma model

Once you run the following command

npx prisma format
Enter fullscreen mode Exit fullscreen mode

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
}
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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

relations of 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
    }
  }
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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

function prisma offers

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

funtion to manipulate data

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);
})
Enter fullscreen mode Exit fullscreen mode

GET

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

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

Enter fullscreen mode Exit fullscreen mode

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);
})
Enter fullscreen mode Exit fullscreen mode

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);

})
Enter fullscreen mode Exit fullscreen mode

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'})
})
Enter fullscreen mode Exit fullscreen mode

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

npx prisma studio
Enter fullscreen mode Exit fullscreen mode

Top comments (0)