Connecting To PostgreSQL Databases In Prisma
Tags: docker, postgres, prisma, webdev
Published: 2023-10-19 14:29:45

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:

```javascript
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

<u>You can see it here:</u> [Prisma](https://www.prisma.io/)

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](https://dev.to/skipperhoa/database-relationships-one-to-many-many-to-many-in-asp-net-core-2-1-5176)

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](https://dev.to/skipperhoa/install-postgresql-using-docker-compose-44jn)

**CREATE TYPESCRIPT PROJECT AND SET UP PRISMA**

```javascript
mkdir hoadev-prisma-postgresql
cd hoadev-prisma-postgresql
```

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

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

**INSTALL PRISMA CLI TO PROJECT**

```javascript
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.

![Connecting To PostgreSQL Databases In Prisma - hoanguyenit.com](https://hoanguyenit.com/public/upload/images/2-connect-postgresql-to-prisma.JPG)

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

```javascript
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:

```javavscript
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:

![Connecting To PostgreSQL Databases In Prisma - hoanguyenit.com](https://hoanguyenit.com/public/upload/images/3-connect-postgresql-to-prisma.JPG)

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

```javascript
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. 

```javascript
npx prisma migrate dev --name init
```

Please see the command line image below:
![Connecting To PostgreSQL Databases In Prisma - hoanguyenit.com](https://hoanguyenit.com/public/upload/images/4-connect-postgresql-to-prisma.JPG)

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

![Connecting To PostgreSQL Databases In Prisma - hoanguyenit.com](https://hoanguyenit.com/public/upload/images/5-connect-postgresql-to-prisma.JPG)

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 

```javascript
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.

```javascript
npx ts-node script.ts
```

![Connecting To PostgreSQL Databases In Prisma - hoanguyenit.com](https://hoanguyenit.com/public/upload/images/6-connect-postgresql-to-prisma.JPG)

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

![Connecting To PostgreSQL Databases In Prisma - hoanguyenit.com](https://hoanguyenit.com/public/upload/images/7-connect-postgresql-to-prisma.JPG)

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:

```javascript
npx prisma studio
```

![Connecting To PostgreSQL Databases In Prisma - hoanguyenit.com](https://hoanguyenit.com/public/upload/images/8-connect-postgresql-to-prisma.JPG)

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

![Connecting To PostgreSQL Databases In Prisma - hoanguyenit.com](https://hoanguyenit.com/public/upload/images/9-connect-postgresql-to-prisma.JPG)

![Connecting To PostgreSQL Databases In Prisma - hoanguyenit.com](https://hoanguyenit.com/public/upload/images/10-connect-postgresql-to-prisma.JPG)

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](https://hoanguyenit.com/connect-to-postgresql-databases-in-prisma.html)