What I learned by using Prisma with PostgreSQL

SelfDevTV — Tue, 12 Jan 2021 14:26:31 +0000

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 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 VercelAnalytic and waited a day and this is what I got:

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 includesyntax. 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:

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

Forem: SelfDevTV

What I learned by using Prisma with PostgreSQL

Welcome everyone to my first Blog post ever.