What I learned by using Prisma with PostgreSQL

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