Replies: 17 comments 33 replies
-
|
Hey Jan... Just wanted to post some initial feedback. Given the 1-1 example query yall showed off I ran that example against our data and it looks like below... This compared to a simple left join like below... The simple left join on this 1-1 relation is about 2-10x (depending on the limit) faster compared to what prisma would generate for this query. It's also worth mentioning that the lower query is also pulling back significantly more data as it's doing a select star compared to a subset of the information in the first query. |
Beta Was this translation helpful? Give feedback.
-
|
The correct way to do a SQL query to retrieve a user and their profile (from the release page example) is simply: SELECT *
FROM user U
INNER JOIN profile P ON P.user_id = U.id
WHERE U.id = 1Why do we end up with this instead? SELECT
"t1"."id",
"t1"."name",
"User_profile"."__prisma_data__" AS "profile"
FROM
"public"."User" AS "t1"
LEFT JOIN LATERAL (
SELECT
COALESCE(JSON_AGG("__prisma_data__"), '[]') AS "__prisma_data__"
FROM
(
SELECT
"t4"."__prisma_data__"
FROM
(
SELECT
JSON_BUILD_OBJECT(
'id',
"t3"."id",
'bio',
"t3"."bio",
'userId',
"t3"."userId"
) AS "__prisma_data__"
FROM
(
SELECT
"t2".*
FROM
"public"."Profile" AS "t2"
WHERE
"t1"."id" = "t2"."userId"
) AS "t3"
) AS "t4"
) AS "t5"
) AS "User_profile" ON TRUE
WHERE "t1"."id" = $1
LIMIT $2I don't understand how anyone in their right mind could think of this as a "good idea". |
Beta Was this translation helpful? Give feedback.
-
|
Any updates on this? Would love to use the new |
Beta Was this translation helpful? Give feedback.
-
|
Checkout / upvote issue #22596 to track this, I think this is probably what you are experiencing (as do we, commented below) |
Beta Was this translation helpful? Give feedback.
-
|
Is your feedback @kimchinosys related to 1-1 relations as @Scalahansolo mentioned in his comment, or more general about all queries when using |
Beta Was this translation helpful? Give feedback.
-
|
That's the same issue for us, thank you for sharing the link. Will keep track of it. |
Beta Was this translation helpful? Give feedback.
-
|
Our server test suite passed with the |
Beta Was this translation helpful? Give feedback.
-
|
What are the benefits of using database-native JOINs? does this increase performance? |
Beta Was this translation helpful? Give feedback.
-
|
Yes, usually it does. If it does not, please let us know which queries are affected. |
Beta Was this translation helpful? Give feedback.
-
|
const volunteer = await prisma.volunteer.findUnique({
where: {
id: volunteerId,
},
select: {
name: true,
volunteerEvents: {
select: { volunteerEventImage: true },
where: { volunteerEventImage: { isNot: null } },
orderBy: [
{ archivedAt: "desc" },
{ endTime: "desc" },
{ startTime: "desc" },
],
take: 1,
},
},
});SELECT
"t1"."id",
"t1"."name",
"Volunteer_volunteerEvents_m2m"."__prisma_data__" AS "volunteerEvents"
FROM
"public"."Volunteer" AS "t1"
LEFT JOIN LATERAL (
SELECT
COALESCE(
JSON_AGG("__prisma_data__"),
'[]'
) AS "__prisma_data__"
FROM
(
SELECT
"Volunteer_volunteerEvents"."__prisma_data__"
FROM
"public"."_VolunteerToVolunteerEvent" AS "t2"
LEFT JOIN LATERAL (
SELECT
JSON_BUILD_OBJECT(
'volunteerEventImage', "VolunteerEvent_volunteerEventImage"."__prisma_data__"
) AS "__prisma_data__",
"t4"."archivedAt",
"t4"."endTime",
"t4"."startTime",
"t4"."id"
FROM
(
SELECT
"t3".*
FROM
"public"."VolunteerEvent" AS "t3"
WHERE
"t2"."B" = "t3"."id"
/* root select */
) AS "t4"
LEFT JOIN LATERAL (
SELECT
COALESCE(
JSON_AGG("__prisma_data__"),
'[]'
) AS "__prisma_data__"
FROM
(
SELECT
"t9"."__prisma_data__"
FROM
(
SELECT
JSON_BUILD_OBJECT(
'id', "t8"."id", 'createdAt', "t8"."createdAt",
'url', "t8"."url", 'volunteerEventId',
"t8"."volunteerEventId"
) AS "__prisma_data__"
FROM
(
SELECT
"t7".*
FROM
"public"."VolunteerEventImage" AS "t7"
WHERE
"t4"."id" = "t7"."volunteerEventId"
/* root select */
) AS "t8"
/* inner select */
) AS "t9"
/* middle select */
) AS "t10"
/* outer select */
) AS "VolunteerEvent_volunteerEventImage" ON true
) AS "Volunteer_volunteerEvents" ON true
LEFT JOIN "public"."VolunteerEventImage" AS "j1" ON ("j1"."volunteerEventId") = ("public"."VolunteerEvent"."id")
WHERE
(
"t2"."A" = "t1"."id"
AND (
NOT ("j1"."volunteerEventId" IS NULL)
)
)
ORDER BY
"Volunteer_volunteerEvents"."archivedAt" DESC,
"Volunteer_volunteerEvents"."endTime" DESC,
"Volunteer_volunteerEvents"."startTime" DESC
LIMIT
$1
) AS "Volunteer_volunteerEvents_m2m_1"
) AS "Volunteer_volunteerEvents_m2m" ON true
WHERE
(
"t1"."id" = $2
AND 1 = 1
)
LIMIT
$3 |
Beta Was this translation helpful? Give feedback.
-
|
sorry, just saw you all would prefer new issues for bug reports: #22311 |
Beta Was this translation helpful? Give feedback.
-
|
All good, we put that comment in our backlog as well - but thanks for putting it into an issue. Now we are tracking that. |
Beta Was this translation helpful? Give feedback.
-
|
This has been fixed, and released with an earlier patch 5.7.1 and again with today's 5.8.0. |
Beta Was this translation helpful? Give feedback.
-
|
If I turn on the preview feature many of my queries fail with this error: "PrismaClientKnownRequestError:
Invalid `.findUniqueOrThrow()` invocation in
/user/db/getUser.ts:20:6
17 include: Prisma.Subset<T, Prisma.UserInclude>,
18 ) {
19 return db.user
→ 20 .findUniqueOrThrow(
Inconsistent column data: Unexpected conversion failure from String(2023-06-09T23:40:31.6+00:00) to DateTime.
Reason: $trailing input at Object.handleRequestError (/web/node_modules/.pnpm/@prisma+client@5.7.0_prisma@5.7.0/node_modules/@prisma/client/runtime/library.js:125:6817)
at Object.handleAndLogRequestError (/web/node_modules/.pnpm/@prisma+client@5.7.0_prisma@5.7.0/node_modules/@prisma/client/runtime/library.js:125:6151)
at Object.request (/web/node_modules/.pnpm/@prisma+client@5.7.0_prisma@5.7.0/node_modules/@prisma/client/runtime/library.js:125:5859)
at l (//web/node_modules/.pnpm/@prisma+client@5.7.0_prisma@5.7.0/node_modules/@prisma/client/runtime/library.js:130:10025)
at <anonymous> (/web/node_modules/.pnpm/@prisma+client@5.7.0_prisma@5.7.0/node_modules/@prisma/client/runtime/library.js:34:550)"if I turn it off it is working all fine again. query looks like this: |
Beta Was this translation helpful? Give feedback.
-
|
Thanks for the report! I think that is known and tracked as #22293, work on fixing it is already in progress. |
Beta Was this translation helpful? Give feedback.
-
|
This should be fixed with an earlier patch release and also today's 5.8.0. Can you confirm @BHesseldieck? |
Beta Was this translation helpful? Give feedback.
-
|
Some feedback on the I turned it on a few days ago and have had to just turn it off again because many of our list APIs that use skip/limit pagination with complex joins and filtering on join tables experienced an awful drop in performance with API calls usually taking ~0.2s suddenly taking 15+s and causing memory pressure on the backend. From the logged queries from Prisma it appears the |
Beta Was this translation helpful? Give feedback.
-
|
That would definitely not be expected. Thanks for reporting. Can you share an example of such a query, so we are sure we are looking at the right thing? |
Beta Was this translation helpful? Give feedback.
-
|
Just to chime in here: We have experiences the same: Also a huge drop in performance when turning on |
Beta Was this translation helpful? Give feedback.
-
|
I'll try share when I can. |
Beta Was this translation helpful? Give feedback.
-
|
Hey @BracketJohn, similarly to @rcbevans, could you please share more information with us? Which database & version are you using, which queries are much slower, what's your Prisma Schema (even reduced to the minimum to reproduce the slow queries)? Thank you! |
Beta Was this translation helpful? Give feedback.
-
|
Hey @Weakky! We are on v5.7.1 and using, here's out generator client {
provider = "prisma-client-js"
// TODO: Enabling this actually _reduces_ performance (albeit it should be increasing it). What we are experiencing is similar to the issue described here https://github.com/prisma/prisma/discussions/22288#discussioncomment-7847470
previewFeatures = ["relationJoins", "nativeDistinct"]
}-> I think someone else opened an issue that is maybe the same as this & they also added a schema to reproduce with: #22596 |
Beta Was this translation helpful? Give feedback.
-
|
I suspect this is a documentation terminology nit in the release notes (assuming the behaviour I'm seeing is expected) - but the mention of "sub-queries" led me to expect it would still use a single SQL request as per the typical usage of "subquery" to mean an inner or nested query (i.e. be some kind of alternative strategy for optimising joins, rather than effectively |
Beta Was this translation helpful? Give feedback.
-
|
Hey @andyjy, good catch, and sorry for the confusion! I went ahead and fixed the wording in the release notes on GitHub.
|
Beta Was this translation helpful? Give feedback.
-
|
(Cross-posting for visibility, hope helpful) - identified the cause of a bug causing incompatibility with driverAdapters causing fatal error when the included relation contains null fields: #22294 (comment) |
Beta Was this translation helpful? Give feedback.
-
|
Sorry for not responding here quickly @andyjy, and thanks for helping out! We were aware of it (there are a few duplicate issues, including an internal one, so the context was lost and not easily available, sorry about this) but it's very good to have a confirmation. The fix is currently being worked on, but the very root cause was a little unexpected and ultimately involves an important third-party dependency that needs to be fixed upstream. We should hopefully be able to wrap it up before the release. |
Beta Was this translation helpful? Give feedback.
-
|
Greetings Prisma Community, I've been actively running a medium-scale project in a staging environment using the preview feature, which so far hasn't presented any issues. My views on topics like this issue #22479, which I believe reflects more of developer oversight than a genuine Prisma issue, are firm. Initially, I held a subjective perspective on how Prisma developers envisioned the function's implementation, chiefly considering it from an infrastructural side. However, upon the release of version 5.8, my guidance on this has moderately shifted. Below, I detail this feature's implications on my project. My project experienced considerable advantages on numerous endpoints when executing this featureFlag. This applies even when running in a virtually zero-latency network (API and database residing on a non-VM hardware, same machine, in docker). Although, in some situations, the tradeoff between several PostgreSQL JSONB serializations and Node.JS object decoding + instantiations did not performed better to a complete transition to "relationship" queries. In that manner, considering marking bulky data tables in the schema (This could be as simple as a While we can suggest amendments to his code for superior query performance, remember that his query isn't wrong per se. Hence, as a framework, we hold the obligation to retain a decent level of performance for such queries. Also, this would help in paving a smoother path to preview this feature for architects (like marking the schema rather than modifying the query route, where the code resides in). The position Prisma decides to adopt has a direct bearing on this matter. If we lean towards the view that "developers simply employ Prisma", it supports the notion that we should provide them with options that allow for behavior modifications independent of the code they're currently working with. Conversely, if we operate under the belief that "developers are fully aware of their needs", then it would logically lead us to consider making minor amendments to the API. However, my viewpoints have been nuanced due to a recently encountered post, I'm a bit lost as I'm no part of the prisma dev team, but if the later option is the one, I have a few ideias: Given an opt-in for each query towards "get everything in two queries," (a minor rollback to the prior implementation where it always resolved in a single query for the joined field using some nifty parallel join :P). And like all magic, this is merely an illusion - developers must decide what should be joined inline (JSON with parallels) or queried independently, both happening within the same query. So, for instance, if I have a user with multiple payments having various logs, each of which has a created_by User relation, a command such as the following could come in handy: const rows = await prisma.user.findFirst({
data: {
id: user.id,
},
select: {
id: true, // get user id
payment: {
date: true, // get payment date,
amount: true,
logs: {
data: true,
creator: {
select: {
id: true,
display_name:true
}
}
}
},
},
prefetch: {
payment: {
creator: false
}
}
});From Prisma's perspective, the optimization in Prisma ensures relations are fetched with improved efficiency while minimizing the need for multiple data roundtrips. SELECT "id"
FROM "User"
WHERE "id" = :userId
LIMIT 1;-- remember the LIMIT 1 , as this ia a findFirst, but this another topic, maybe Then it would gather the payment data against the specific user and include their logs. Also, owing to the prefetch clause, Prisma would wisely omit the 'creator' data from the JOIN operation, generating a single inlined query: SELECT "Payment"."date", "Payment"."amount", "Log"."data", "Log"."creator_id"
FROM "Payment"
LEFT JOIN "Log" ON "Payment"."id" = "Log"."payment_id"
WHERE "Payment"."user_id" = :userId;Consequently, to retrieve the creator's information from the 'User' relation with the obtained creator_ids from the previous step, Prisma would generate: SELECT "id", "display_name"
FROM "User"
WHERE "id" IN (:creatorIds);These SQL queries reflect Prisma's optimization and ability to handle relational data fetching more effectively with custom control to the developer. If you read until here, thank you. |
Beta Was this translation helpful? Give feedback.
-
|
Thank you for the feedback @renatocron! I don't know if you've seen it already, but we shipped a limited form of this in Prisma 5.8.0: you can specify whether to use JOINs or separate queries on a per Prisma query basis using the Configuring it on a per-relation basis on any level of nested selections would be more powerful, and we want to have that as well, but it's not on the immediate plan right now. We'd like to consider implementing it once we are done with other improvements to |
Beta Was this translation helpful? Give feedback.
-
|
This feature is most welcome, thank you! The new db.posts.findMany({
where: {
// Only load posts for a specific author.
authorId: '123',
},
include: {
// Load `post.category` via JOIN.
// Equivalent to `category: true` when `relationJoins` is enabled.
category: { loadStrategy: 'join' },
// Load `post.author` with a separate query.
author: { loadStrategy: 'query' },
},
})Because of the I think this is similar to what @renatocron suggested above, but a different API. |
Beta Was this translation helpful? Give feedback.
-
|
Thank you @itsgiacoliketaco! We did consider it, and you even guessed the exact API suggested in an internal document 😆 Right now we are very busy working on higher priority improvements to |
Beta Was this translation helpful? Give feedback.
-
|
Done! #22759 Thanks for the transparency! Seems like a good compromise. Glad we're on the same page about the API when the per-relation strategy option is eventually introduced 😄 |
Beta Was this translation helpful? Give feedback.
-
|
Hey guys, I found a pretty concrete bug that unfortunately blocks our adoption of the relational joins. Basically, if you have a JSON[] field on a model, and you query that model directly, it resolves as an empty array or a populated array ( as it should). However, when you query that model through a join the empty Json[] fields resolve to null :O This breaks the typings, and the projects client side, because we often try to read .length of the presumed empty arrays. Even more concrete example: Querying profile alone resolves correctly with links as []. Querying User joined with Profile resolves with links to NULL (again, assuming they have not been populated). Changing the relationLoadStrategy to "query" resolves the issue for now. What do you guys think? Easy fix? |
Beta Was this translation helpful? Give feedback.
-
|
That sounds ... not great. Thanks for reporting! Can you turn this into a full, standalone issue? We definitely want to look into that, and that is easier with a full issue with a filled issue template. Thank you! |
Beta Was this translation helpful? Give feedback.
-
Beta Was this translation helpful? Give feedback.
-
|
Hey guys ! I'm currently trying this new feature (can't wait to have it in GA !), and my IDE throw me this error when i added the flag. Is my IDE tripping or am I missing something ? I currently have the latest prisma release (v5.9) 
|
Beta Was this translation helpful? Give feedback.
-
|
Your IDE is running a Prisma extension for an older version of Prisma. If you are using VS Code, then https://marketplace.visualstudio.com/items?itemName=Prisma.prisma in its newest version definitely does support |
Beta Was this translation helpful? Give feedback.
-
|
thanks for the quick reply @janpio ! I'm using we storm, I should be up-to-date. maybe an update is needed in the plugin? |
Beta Was this translation helpful? Give feedback.
-
|
https://plugins.jetbrains.com/plugin/20686-prisma-orm I assume then? I honestly do not know if and how IntelliJ updates the version of our "engine" in their plugin: https://github.com/JetBrains/intellij-plugins/tree/master/prisma That is a pretty good question. Update: Hui, they are indeed pretty outdated: https://github.com/JetBrains/intellij-plugins/blob/a73c8ef111e2d7766098e5ede1723a90caf5f357/prisma/language-server/package.json#L6-L9 Seems in the past they did that via an issue in their issue tracker: https://youtrack.jetbrains.com/issue/WEB-63544 |
Beta Was this translation helpful? Give feedback.
-
|
I'm not sure how their release process work but the update of the plugin was done in JetBrains/intellij-plugins@eba9b8c 🎊 |
Beta Was this translation helpful? Give feedback.
-
|
Very encouraging results with MySQL I have encountered a few I don't think there's anything to do on Prisma's end -- I'm planning to refactor these queries to optimize them. Thanks Prisma team <3 |
Beta Was this translation helpful? Give feedback.
-
|
Posting here for visibility -- I found a bug with the MySQL implementation that seems to convert decimals to floats when retrieving nested models: #23233 |
Beta Was this translation helpful? Give feedback.
-
|
Fixed in 5.12.0! 🎉 |
Beta Was this translation helpful? Give feedback.
-
|
3 new issues tracking the demand for the addition of this functionality for SQL Server, SQLite and MariaDB: |
Beta Was this translation helpful? Give feedback.
-
|
Thanks for the feature! We had it enabled for a little bit, but ran into an issue when using |
Beta Was this translation helpful? Give feedback.
-
|
@janpio I was hoping to opt into the There are a few queries in our setup that we want to be able to test but don't want to turn on this feature for all our queries right away. |
Beta Was this translation helpful? Give feedback.
-
Support for the relation queries using database native
JOINs is in preview behind therelationJoinspreview feature.Please share your feedback about
relationJoinsreleased in v5.7.0 for PostgreSQL and v5.10.0 for MySQL in this discussion.Beta Was this translation helpful? Give feedback.
All reactions