Performance: How to make prisma perform a single SQL query with joins instead of multiple queries

[theodiablo]

Hi there,

When I make a query with the prisma client which includes other tables data, prisma seems to be chaining several SQL queries instead of making a single query to the database using joins.
It is making my database timeout, as it performs many select queries for very simple operations.

Prisma Version: 3.8.1

Here is my query:

      const dbTransactions = await prismaClient.transaction.findMany({
        where: { id: 1,
        select: {
          profile: true,
          currency: true,
          industry: {
            select: {
              industryImpacts: {
                select: {
                  currency: true,
                },
              },
            },
          },
        },
      });

My model is defined like this (I deleted many of the fields to keep it simpler):

model Transaction {
  id                           Int                           @id @default(autoincrement())
...
  profileId                    Int
  profile                      Profile                       @relation(fields: [profileId], references: [id])
  currencyId                   Int
  currency                     Currency                      @relation(fields: [currencyId], references: [id])
  industryId                   Int
  industry                     Industry                      @relation(fields: [industryId], references: [id])

  @@index([currencyId])
  @@index([profileId])
}

model Currency {
  id              Int               @id @default(autoincrement())
...
  transactions    Transaction[]
}

model Industry {
  id                               Int                               @id @default(autoincrement())
...
  industryImpacts                  IndustryImpacts[]
}

model IndustryImpacts {
  id         Int      @id @default(autoincrement())
...
  currencyId Int
  currency   Currency @relation(fields: [currencyId], references: [id])
  industryId Int
  industry   Industry @relation(fields: [industryId], references: [id])
}

This is how I instantiate the prismaClient to be able to debug the queries:

export const prismaClient = new PrismaClient({
  log: [
    {
      emit: 'event',
      level: 'query',
    },
  ],
});

prismaClient.$on('query', async e => {
  console.log(`${e.query} ${e.params}`);
});

Here is the list of SQL queries performed by the client:

SELECT `Transaction`... FROM `Transaction` WHERE `Transaction`.`id` = ? [1]
SELECT `Profile`... FROM `Profile` WHERE `Profile`.`id` IN (?) [31]
SELECT `Currency`... FROM `Currency` WHERE `Currency`.`id` IN (?) [1]
SELECT `Industry`.`id` FROM `Industry` WHERE `Industry`.`id` IN (?) [43]
SELECT `IndustryImpacts`... FROM `IndustryImpacts` WHERE `IndustryImpacts`.`industryId` IN (?) [43]

What I would expect is some sort of intelligent joining like:

SELECT `Transaction`..., `Profile`..., `Currency`..., `Industry`..., `IndustryImpacts`...
FROM `Transaction` 
LEFT JOIN `Profile` ON `Profile`.`id` = `Transaction`.`profileId`
LEFT JOIN `Currency` WHERE `Currency`.`id` = `Transaction`.`currencyId`
LEFT JOIN `Industry` WHERE `Industry`.`id` = `Transaction`.`industryId`
LEFT JOIN `IndustryImpacts` WHERE `IndustryImpacts`.`industryId` = `Industry`.`id`
WHERE `Transaction`.`id` = ? [1]

Does someone know if there is a way that prisma makes joins like instead of calling multiple queries sequentially?

Thanks!

[janpio]

It is making my database timeout, as it asks for many connections for very simple queries.

All these queries are executed with one and the same connection.

There is no way currently to influence the types of queries Prisma executes besides using raw queries and send plain SQL.

[suhjohn]

Great to hear, can't wait. Fyi relevant video if you haven't seen: https://www.youtube.com/watch?v=jqhHXe746Ns. Might be good to clear the air with him too

[nastynaz]

@janpio I find it absolutely insane that an ‘ORM’ that’s so widely recommended in the JS ecosystem has what feels like a complete coverup. Joins are crucial to the performance of SQL and offload the processing to the database. This is especially important for enterprises running large SQL databases.

At the very least it should be made clear on the website and in the docs so that others considering switching to Prisma are aware of the consequences of doing so.

I couldn’t find any reference to this issue in the official docs.

[lukejagodzinski]

Yep, it would be best to mention it in the docs. Unfortunatelly, people not knowing SQL created a lot of hype around Prisma which in full fariness is still great. I haven't had better DX experience with any ORM before. Hopefully, they can resolve this issue soon because I don't want to switch.

For now, I'm just considering using Drizzle. I've already done some tests and it's faster but it doesn't have all the features of Prisma yet. They recently introduced very similar API but still is a little bit lacking.

[janpio]

We are working on fixing this, Prisma will be using JOINs in more cases. Follow #5184 for updates.

[iukea1]

Yep, it would be best to mention it in the docs. Unfortunatelly, people not knowing SQL created a lot of hype around Prisma which in full fariness is still great. I haven't had better DX experience with any ORM before. Hopefully, they can resolve this issue soon because I don't want to switch.

For now, I'm just considering using Drizzle. I've already done some tests and it's faster but it doesn't have all the features of Prisma yet. They recently introduced very similar API but still is a little bit lacking.

I just started using drizzle (I have a backend engineering background and am pretty familiar with SQL

Drizzle is a breath of fresh air and my total query to page render time is now 12ms (Versel edge function and did a small table join)

You only downside..... Is it still extremely new and things are missing ...

I am currently helping build integration into Next-Auth (should be out soon)

[kibertoad]

@janpio Are there plans to ever address that? drizzle managed to implement joins via just one sql query, maybe you could take a look at their approach.

[samulisuomi]

@janpio please reconsider the arguments mentioned in the other thread why this would be good for performance and keeping the operations ACID:
#12715 (reply in thread)
#12715 (reply in thread)
#12715 (reply in thread)

I feel like these points haven’t been addressed properly. In the project that I’m starting, I went with Kysely instead of Prisma because of this and other similar limitations. Is there an intentional philosophy in Prisma to not do many things the ”traditional SQL-way” or why this feature is so quickly dismissed as unnecessary? 🤔

[janpio]

@samulisuomi I just wrote that we will work on implementing JOINs. What is there to reconsider?

[samulisuomi]

@janpio apologizes if I misunderstood. Earlier you said

We will switch our query generation from separate queries to using JOINs when this is our most pressing concern.

which to me sounded like this will not be prioritized for quite some time. Do you have any more explicit time frame in mind?

[janpio]

No. We unfortunately never communicate timeframes or ETAs.
That I am responding to you here indicates that this is not at the end of our list though. We have people working on related problems right now.

[HomiG]

@janpio please reconsider the arguments mentioned in the other thread why this would be good for performance and keeping the operations ACID:
#12715 (reply in thread)
#12715 (reply in thread)
#12715 (reply in thread)

I feel like these points haven’t been addressed properly. In the project that I’m starting, I went with Kysely instead of Prisma because of this and other similar limitations. Is there an intentional philosophy in Prisma to not do many things the ”traditional SQL-way” or why this feature is so quickly dismissed as unnecessary? 🤔

For anyone who is wondering why Prisma is working this way:

I think so. To be honest I've reached this issue on GitHub because I was looking at how Prisma is working under the hood and I got surprised. Watching this conference, you can get a glance at how Prisma has a main core and implements different connectors for each implementation. Either way, towards the end of the video it's explained how a Prisma query is translated. Around +-25 mins it's revealed, that there's a vision to interconnect if I'm not wrong, data from different dbs, even different kind of services (as they say stripe in the video). You'll get a better glance if you see the schema.
This is the vision, and in order to interconnect data from different resources, from the architectural perspective it's definitely easier to query layer by layer since you can't really join between a db and a service.
Although from the other side if you just have Prisma to query just a single db, it's shocking that there's no joining. That's my opinion after all, and my understanding of the video.

[chris-braidwell]

While it's irritating that Prisma doesn't use joins, there are two distinct issues that should be addressed separately. (1) efficiencies relating to using joins or not, and (2) data correctness relating to using joins or not. (1) is a performance-only issue, important but doesn't affect data integrity directly -- while (2) is an ACID correctness / data integrity issue that's deadly if it's done wrong.

If (2) is done wrong, we don't need to care about (1). Frankly if (2) is done wrong, we should all stop using Prisma immediately.

The key question is, does Prisma wrap ALL operations in transactions? If the answer is yes, and we as developers configure our DBs with the right isolation level, then issue (2) is OK. As I read the current Prisma docs, they guarantee transactional behavior for only some operations. See https://www.prisma.io/docs/concepts/components/prisma-client/transactions. That lists: nested writes; bulk transactions; and when the $transaction API is used directly. That's not enough. We need to know that absolutely all Prisma operations are transactional. Here are some example scenarios that would be a problem if not:

• Developer calls a findFirst method with an include clause. If the query (pure read operation) isn't transactional and is broken down by Prisma into multiple SELECTs, then there is a race condition between the SELECTs and the returned data could be different (wrong) compared to the same query if it had been implemented as a single SQL statement (one SELECT with joins).

• Developer calls an update method that Prisma breaks down into one UPDATE and one or more SELECTs. Again there is a race condition between the SELECTs and UPDATEs, and a result that's potentially different from what a single UPDATE would provide.

Bottom line: Prisma team, can you assure us that ALL operations, meaning all writes and all reads, are transactional? If it's true that all Prisma operations are wrapped in a transaction, there is no potential race condition between multiple INSERTs, UPDATEs and SELECTs generated by Prisma, and we don't have to worry about data integrity problems as a result of issue (2). Then we can get back to griping about the performance aspects in issue (1) :). (And you should update the docs to say ALL is transactional).

However if there are any Prisma operations that aren't transactional (not just writes -- even reads, if Prisma chooses to break the read operation into multiple SELECTs) -- then there is a Severity 1+++++++ data integrity issue. Which seems unlikely but I'd really like to hear the Prisma team say all is transactional, not just the special cases listed on the doco page I referenced above.

[nikooiko]

In order for the nested case to be successful, the nested queries should be run as subqueries (or joins instead).

[janpio]

Good that we are already working on that 😆 #5184

[nikooiko]

Although using joins will resolve this, IMO it's not 100% related. ie if Prisma never ends up utilizing joins for this (or simply doesn't on all cases), it means that this won't be resolved. I think product-wise it makes sense to tackle this independently to make sure that no matter what this will be resolved at some point. This (at least for me) is very critical as it can easily cause lots of inconsistencies.

[janpio]

Usage of a JOIN in your test query would make this problem moot, right? And as we are already working on that (as in: writing code right now), that is the best and most direct solution right now as well I would say.
And for the queries where we still can not use JOINs (and there will be many, as Prisma has a much more flexible API), we want (and have) to use transactions to wrap the individual queries together.

What really confuses me still, and I could not figure out today, is why using $transaction([...]) does not wrap the query in a transaction. I'll keep investigating.

[seifsg]

@janpio any updates regarding this please? Thanks.