In my previous post, when I was still new to PRISMA, I shared my experience with the ORM so far and raised a few important issues about the ORM being slower than expected. Over time, I have developed a love-hate relationship with PRISMA, and I have learned some useful strategies that can make queries faster. These strategies have helped me speed up my database queries, such as reducing the time users take to log in to Tech Tales to less than 1 second. 

Here is the list of my previous complaints and how I managed to resolve them:

• Prisma is not suited to run on the edge, and thus, the database sleeps when the Prisma client disconnects. Prisma takes time to connect again.

This issue is still highly relevant when using edge and serverless functions since there is no running server. Prisma documentation notes that this is a tradeoff we have to make if we are hosting our applications on the edge, and therefore, this problem is not unique to Prisma.

A serverless function lies dormant until a request triggers it to life. To make this experience viable, that function should be able to jump into action very quickly.

The size of a bundled serverless function has a direct effect on the speed at which the function can be instantiated. Because of this, serverless functions have limitations on the size of the artifact that is deployed.

Ideally, in a serverless architecture, developers will be deploying small, modular functions rather than entire applications. If the size of those functions grows beyond the limitation of the cloud provider, that may be a sign for the developer to reconsider their design and how they bundle their code.

After a serverless function has done its job, it will remain alive for a limited amount of time waiting for additional requests. Once that time is up, the function will be destroyed.

This is a positive behavior of serverless functions, as it is what allows serverless functions to scale to zero. A side-effect of this, however, is that once the function scales to zero, new invocations require a new function to be instantiated to handle the request. This takes a little bit of time (or a lot in some cases). This time is often referred to as a cold start.

What can we do about it?

From what I have learned, one potential solution to this problem is to never close your Prisma client when fetching data and letting Prisma manage the pooling for you. As a beginner, I used to call prisma.$disconnect(after every query, as this would minimize database calls. However, I realized that this makes the next request take longer than expected, as Prisma has to connect the client again.

Another solution is to use Prisma Accelerate for the database connection. Prisma Accelerate offers  Data Proxy, which manages connection pooling and allows HTTP-based connections, making it more suitable for serverless and edge deployments. This reduces cold start times and connection issues. Additionally, Prisma Accelerate provides global caching to further reduce latency in edge environments. How Prisma & Serverless Fit Together.

To get started with Prisma Accelerate, you need to replace your client and install their package. Begin by heading to their page and creating an account, and then adding your database connection. You will then receive an accelerated API key to be used with your requests. You will get a connection string that looks something like this:

<!-- .env -->
postgresql://neondb_owner:[YOUR-PASSWORD]@ep-lingering-hat-a2e7tkt3.eu-central-1.aws.neon.tech/neondb?sslmode=require

After getting your database string, you need to install the Prisma Accelerate Package, as this will help you generate a Prisma client without an engine and allow for caching data.

npm install @prisma/extension-accelerate
npx prisma generate --no-engine

Now you will need to extend your Prisma client to use accelerate so you can be able to cache requests. However, if you are using NextJS, it might be able to cache the data for you, and this would be safer since you will be in control of revalidating the data.

import { PrismaClient } from "@prisma/client/edge";
import { withAccelerate } from "@prisma/extension-accelerate";
const client = new PrismaClient().$extends(withAccelerate())
type ExtendedPrismaClient = typeof client;
declare global {
  var prisma: ExtendedPrismaClient | undefined;
}
const prisma = globalThis.prisma ?? client;
if (process.env.NODE_ENV !== "production") {
  globalThis.prisma = prisma;
}

Having a singleton client would offer you more benefits as it ensures you always have one instance of the client running. However, note that Accelerate is a paid service, although there is a free tier; it includes a query limit.

• Every new insert via Prisma opened a database-level transaction (?). An extremely weird design choice by Prisma. This makes you exhaust your connection pool very fast.

At the beginning, it was a little bit frustrating since I did not understand the logic behind Prisma. For SQL databases, Prisma does use transactions for atomicity, but there is no official way to disable this per-insert in the current documentation. The Prisma team is working on rewriting their engine with Typescript, and this behavior does not have any impact on performance. However, I did see that this can cause you to exhaust your connection pool, especially if you are on Accelerate. It is advisable to cache requests.

• There is no concept of SQL-level joins in Prisma. This means for every transaction that requires a join, Prisma fetches both tables, merges the data, and returns it to the user. This makes the findMany query take too long.

This was true in earlier versions, but Prisma has introduced the relationJoins feature (currently in Preview) that enables SQL-level joins for relation queries. This can significantly improve performance by reducing the number of round-trips to the database. Prisma ORM Now Lets You Choose the Best Join Strategy (Preview).

To enable SQL-level joins, you need to modify your schema as follows:

generator client {
    provider = "prisma-client-js"
    previewFeatures = ["relationJoins"]
}

After adding this flag, you need to run prisma generate again to re-generate Prisma Client. The relationJoins feature is currently available on PostgreSQL, CockroachDB, and MySQL.

• On every insert, Prisma opens a transaction where it inserts and then returns the inserted record, even if we do not want it.

You can use the relationLoadStrategy option on the top-level in any query that supports include or select.

Here is an example with include:

const users = await prisma.user.findMany({
    relationLoadStrategy: 'join', // or 'query'
    include: {
        posts: true,
    },
})

However, I have seen a myriad of complaints online, and I do not know if this is worth it, especially when using edge, since Prisma does not have an engine and is generally faster. 

When to use which load strategy?

• The join The strategy (default) will be more effective in most scenarios. PostgreSQL uses a combination of LATERAL JOINs JSON aggregation to reduce redundancy in result sets and delegate the work of transforming the query results into the expected JSON structures on the database server. MySQL uses correlated subqueries to fetch the results with a single query.
• There may be edge cases where query could be more performant depending on the characteristics of the dataset and query. We recommend that you profile your database queries to identify these situations.
• Use query If you want to save resources on the database server and do heavy-lifting of merging and transforming data in the application server, which might be easier to scale.

• For every action (Create, Read, Update, and Delete), Prisma returns the object by default, even if you do not want it. Even for delete!

This still remains true with no option of telling Prisma not to select the mutate value, especially on Delete, where it is not needed. The only fix for this problem is to select as little data as possible, since if you omit the select statement, Prisma will select the whole object. Here is an example:

await prisma.user.create({
  data: {
    email: "test@example.com",
    name: "Alice",
  },
  select: { id: true }, // return only the id
})

• Prisma supports JSONB datatypes in PostgreSQL and MySQL. However, there is no way to update such a data type. The existing way is to fetch the whole object, spread it, and then add or delete data, and then post the same object again. This is utterly ridiculous.

This still remains a problem, and I still find it ridiculous. However, I admit that I might have misunderstood the purpose of JSONB fields in the database. Prisma does support JSON/JSONB fields in PostgreSQL and MySQL. However, as of now, there is no built-in way to perform partial updates (e.g., updating or removing a single key) on JSON/JSONB columns directly in the database. The typical workaround is to:

1. Fetch the entire JSON object from the database.
2. Modify it in your application code (e.g., using object spread or similar)
3. Write the whole object back to the database.

This approach is recognized as inefficient and not ideal, especially for large JSON objects or high-concurrency scenarios. The community has requested native support for partial updates (e.g., using SQL functions like JSONB_SET() in PostgreSQL), but this feature is not yet available in Prisma. The issue is being tracked and is on the team's radar, but there is no official solution or timeline for implementation at this time. Workarounds using raw SQL queries (e.g., $executeRaw) are possible but not type-safe or ergonomic within Prisma's API.

• Prisma has no way of hiding sensitive data, such as not returning password fields when you fetch a user object.

Prisma now supports a built-in way to exclude (omit) fields from query results, addressing the long-standing issue of accidentally exposing sensitive data like passwords. There are two ways of doing this:

• Global Omit: You can configure Prisma Client to always omit certain fields (e.g., password) for a model across all queries by passing an omit option when initializing the client:

const prisma = new PrismaClient({
  omit: {
    user: {
      password: true
    }
  }
});

// The password field will be excluded from all user queries
const user = await prisma.user.findUnique({ where: { id: 1 } });

• Local Omit: You can also omit fields on a per-query basis

const user = await prisma.user.findUnique({
  where: { id: 1 },
  omit: { password: true }
});

The global criteria can be problematic since you might later need access to the sensitive data, such as when you need to log in the user if you have custom auth flows.

Bonus

One bonus tip that helped make my queries super fast is using a read replica of my database that is only responsible for reads. If you are using the Neon database, check their detailed guideline on how to create read replicas: Use Neon read replicas with Prisma - Neon Docs.

Per documentation, a Neon read replica is an independent read-only compute that performs read operations on the same data as your primary read-write compute, which means adding a read replica to a Neon project requires no additional storage.

A key benefit of read replicas is that you can distribute read requests to one or more read replicas, enabling you to easily scale your applications and achieve higher throughput for both read-write and read-only workloads.

To implement a read replica, follow the following steps:

• Create a read replica database in Neon and update your .env File as follows:

DATABASE_URL="postgresql://alex:AbC123dEf@ep-cool-darkness-123456.us-east-2.aws.neon.tech/dbname?sslmode=require&channel_binding=require"
DATABASE_REPLICA_URL="postgresql://alex:AbC123dEf@ep-damp-cell-123456.us-east-2.aws.neon.tech/dbname?sslmode=require&channel_binding=require"

• Install the Prisma read replica extension that allows Prisma to use the replica for read-only requests:

npm install @prisma/extension-read-replicas

Now you need to update your Prisma client as follows:

import { PrismaClient } from "@prisma/client/edge";
import { withAccelerate } from "@prisma/extension-accelerate";
import { readReplicas } from "@prisma/extension-read-replicas";
const client = new PrismaClient().$extends(withAccelerate()).$extends(
  readReplicas({
    url: process.env.DATABASE_REPLICA_URL as string,
  })
);
type ExtendedPrismaClient = typeof client;
declare global {
  var prisma: ExtendedPrismaClient | undefined;
}
const prisma = globalThis.prisma ?? client;
if (process.env.NODE_ENV !== "production") {
  globalThis.prisma = prisma;
}
export default prisma as ExtendedPrismaClient;

When your application runs, read operations are sent to the read replica. If you specify multiple read replicas, a read replica is selected randomly.

All write and $transaction queries are sent to the primary compute defined by DATABASE_URL, which is your read/write compute.

If you want to read from the primary compute and bypass read replicas, you can use the $primary() method in your extended Prisma Client instance:

const posts = await prisma.$primary().post.findMany()