Connecting To PostgreSQL Databases In Prisma

How we can use PostgreSQL in Prisma . In applications, you often see many Devs using PostgreSQL to manage databases, I also often use PostgreSQL.

If we talk about Postgresql, I think everyone has learned about it and used it, but to make it easier to use Postgresql anywhere, you can install Postgresql through Docker, making it easier for you to use Postgresql . Easy to use in projects

So what is Prisma? Simply put, prisma helps us build entity models of Tables. Exactly the Models, the Models themselves, we can easily edit properties, data types, and format columns by writing code directly on the Models.

For example, we have the following Models:

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
}

The code above shows us that we need to build a User, Post table. Do you find it convenient? We can easily configure the table's entities. It feels more flexible, so programmers prefer it. like it

You can see it here: Prisma

So when we talk about this, we will recall that other Frameworks do the same thing as above,

Eg:

• In Laravel , we build tables through Models, then run the migrate command to build the tables to the database.

• Asp.net also needs to build Class Model, then also use the migration command to build tables to the database, you can see more here: Create Database Using Code First In ASP.NET CORE 2.1

Okay, let's continue with the main work. Because in this article I will connect Postgresql to Prisma , so please review the previous article where I shared with everyone the Postgresql settings through Docker, you can review it at here: Install PostgreSQL Using Docker Compose

CREATE TYPESCRIPT PROJECT AND SET UP PRISMA

mkdir hoadev-prisma-postgresql
cd hoadev-prisma-postgresql

Continuing in the hoadev-prisma-postgresql directory , we need to install some of the following libraries

npm init -y
npm install typescript ts-node @types/node --save-dev
npx tsc --init

INSTALL PRISMA CLI TO PROJECT

npm install prisma --save-dev
npx prisma init --datasource-provider postgresql

The code to run the command above, Prisma CLI will create a prisma directory for us and configure postgresql , you can see the file structure below.

Okay, let's open the file in the path prisma/schema.prisma , set up the Table Models

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
}

After completing the Model iteration, we need to provide the PostgreSQL database management path to Prisma

Open the .env file in the project folder and add the postgresql database path with the following syntax:

postgresql://USER:PASSWORD@HOST:PORT/DATABASE

User : is the username to connect to database management in Postgresql

Password : is your password when you log in to manage the database

Port : configured when starting postgresql

Database : is the database name

So username, password, port, database. Where do we get it from? That's when we run Postgresql through Docker . Please review the previous article to easily identify the parameter values.

I start postgresql in docker as follows:

You see the command line in the image above. I logged in to the database "hoadev_db", and used the command to see if there were any tables in that database

\dt

Currently there are no tables

So our connection path to postgresql is as below, let's update it in the .env file

DATABASE_URL="postgresql://hoadev:hoadev123@localhost:5432/hoadev_db?schema=public"

After configuring the connection link to postgresql , our job is to run the migrate command to let prisma create a table to the database in postgresql.

npx prisma migrate dev --name init

Please see the command line image below:

Okay, it ran the command successfully, now let's check to see if there are 2 tables (User, Post) in our database.

As shown above, we have 2 tables (User, Post), successfully created! So we have successfully connected Postgresql to Prisma

Now is the time for you to write query code in Prisma to perform adding data, searching for data, etc.

Let's create the script.ts file in the project directory

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

async function main() {
  const user = await prisma.user.create({
    data: {
      name: 'Hoa Dev',
      email: 'nguyen.thanh.hoa.ctec@gmail.com',
    },
  })
  console.log(user)
}

main()
  .then(async () => {
    await prisma.$disconnect()
  })
  .catch(async (e) => {
    console.error(e)
    await prisma.$disconnect()
    process.exit(1)
  })

The code above adds data to the "User" table, continues to open the command line to run the script.ts file.

npx ts-node script.ts

You have added data to the User table, now let's see if there is data in postgresql, by copying

Through the above statement, we see that in the user table there is 1 line of data.

Or you can use the interface provided in Prisma , which is easier to check the data by using the following:

npx prisma studio

It will run a localhost with a certain port. You can see the database management interface as follows:

This makes it easier to manage, easier to manipulate, and easier to check data.

If you find this article interesting, please share it with other Devs. To share knowledge with each other

See you all in the next article

The article : Connecting To PostgreSQL Databases In Prisma