What I learned by using Prisma with PostgreSQL
Tags: prisma, postgres, react, nextjs
Published: 2021-01-12 14:26:31

## Welcome everyone to my first Blog post ever. 

**Let me introduce myself quickly:**

I'm a youtuber that creates videos about re-creating existing applications (for example a `reddit-clone`). In the past I worked a lot with react and node / express + mongodb as backend. 

So SQL and further PostgreSQL is very new to me.

**In my last project I've decided to use this stack:**

* Next.js
* AWS RDB (with PostgreSQL)
* Prisma as ORM

I'm pretty new to hosting something in general on AWS and I'm also using Prisma the first time.

**With that said here is what I've learned:**

* `Vercel` deploys hosted sites per default to SFO1 (San Francisco) region
* I deployed my RDB on AWS to Frankfurt Gateway (since I thought it will be the fastest solution because I'm from Austria)
* I also chose the free tier and created nothing that a naked PostgreSQL database


**So what I'm trying to say here is the following:**

* Having the db in another region then the hosted application is a bad idea - which you will see later
* Having a naked PostgreSQL db without a [PGBouncer](https://www.pgbouncer.org/) is also not a good idea performance wise


So I deployed my db to AWS and my Next.js App to Vercel.

This is my main api route and all I do (the db has 2 posts total with 0 comments):

```
const sub = await prisma.subreddit.findUnique({
      where: { name: String(req.query.name) },
      include: {
        posts: {
          include: { subreddit: true, user: true, votes: true },
        },
        joinedUsers: true,
      },
    });
```


I turned on `Vercel`Analytic and waited a day and this is what I got:


![blog1](https://dev-to-uploads.s3.amazonaws.com/i/3yrk9r35dealib9siqge.PNG)

 
As you can see the performance is horrible for my simple application and loading just 2 posts. 

I further added a /performance route that does just this:

```
var t0 = performance.now();
    const sub = await prisma.subreddit.findUnique({
      where: { name: String(req.query.name) },
      include: {
        posts: {
          include: { subreddit: true, user: true, votes: true },
        },
        joinedUsers: true,
      },
    });
    var t1 = performance.now();

    const timeItTook = t1 - t0;
    console.log("Fetching full Sub took " + timeItTook + " milliseconds.");
```

The result was always between 3 to 5 seconds. 
>Fetching full Sub took 3358.1404000520706 milliseconds.



At first I thought it's a problem with `Prisma` and more specific it's `include`syntax. I somehow thaught that nested Queries are slow. 

So I opened an issue here: https://github.com/prisma/prisma/issues/4884

**The awesome contributors took a closer look and found the problem(s):**

* The datbase was hosted on one side of the world
* And the Application that connects to it on the other side of the world
* It also has no bouncer (for handling many session since it's serverless / lambda functions)


**As I read that, it instantly clicked!**


At first I wanted to create a RDB Proxy, I think it's called on AWS - but it didn't give me the option to chose the right database. I decided to use `Digital Ocean`


So I headed over to Digital Ocean and created an account + db hosted in San Francisco. 
It also gave me the suggestion to add a "connection pool" (that's basically the same as `PGBounce` - as far as my understanding goes. That was also very new to me and didn't even know existed or I needed.

Next I ...

...changed the `.env` database url to Digital Oceans one.

...Then I migrated the Schema with `yarn prisma migrate dev --preview-feature` and deployed the app to Vercel.

The performance route is now between  20 - 30 ms (compared to about 3000ms before). 

This is how it looks on Analytics:

![blog2](https://dev-to-uploads.s3.amazonaws.com/i/z3clwf88q2v30emvnj5g.PNG)


So the performance is now absolutely beautiful and I learned a lot in the process. 

Thank you to all the `Prisma` contributors to helped me out here. These guys are awesome and I can really say: 

**I love Prisma**

**Shameless plug:** Here is my youtube channel if you are interested: https://www.youtube.com/channel/UCOo3r8Do1Xa97UfQdqK2MSQ