prisma
Implement `exists` function
This function have on prisma-binding, it's very helpful, so I thought would be a good idea to insert on the new prisma client. This functions returns a boolean, you are able to set as many filters (like where from findMany) as you want, if all pass is going to return true, if one fail it's going to return false.
There are some images to help understand on this issue: prisma/prisma-client-js#469.
I agree! exists function is very beneficial for implementing permissions where you want to know whether something exists in the backend with specific criteria.
As a current workaround, you may define a utility function $exists, and chain it with findMany.
/**
* Determines whether something exists.
* @param ts
*/
export function $exists<T>(ts: T[]): boolean {
return ts.length > 0
}
const isHost = ctx.prisma.meal
.findMany({
where: {
id,
host: { id: user.id },
},
})
.then($exists)
You may also perform a count + Boolean cast like
const isHost = await prisma.meal
.count({
where: {
id,
host: { id: user.id },
},
})
.then(Boolean)
for many cases a count is not desired since it is more expensive than an exists.
count needs to iterate over every returned value where exists can stop after the first matched row.
Same with a query of all matching rows even if you only need to know there is any.
Compare https://blog.jooq.org/avoid-using-count-in-sql-when-you-could-use-exists/
To get some inspiration how other ORMs deal with this:
- https://semaphoreci.com/blog/2017/03/14/faster-rails-how-to-check-if-a-record-exists.html
- https://api.rubyonrails.org/classes/ActiveRecord/FinderMethods.html#method-i-exists-3F
Build.where(:created_at => 7.days.ago..1.day.ago).passed.exists?
# SELECT 1 AS one FROM "builds" WHERE ("builds"."created_at" BETWEEN
# '2017-02-22 21:23:04.066301' AND '2017-02-28 21:23:04.066443') AND
# "builds"."result" = $1 LIMIT 1 [["result", "passed"]]
Hey @greguintow 👋 I'd also love to see this feature but as of now there doesn't seem to be a timeline for it unfortunately since it's not yet placed on the roadmap. In the meantime, the workaround by @SebastienElet is probably the best way to achieve the same outcome, sorry for the inconvenience here 🙏
@nikolasburk i have several api's which checks existence of a value. for example for each product i would like to see if the user has liked that product or not. again there are so many other use cases. thank you for the response. and i hope this feature will be implemented soon. :)
Avoid using count as much as possible. It's very, very bad for performance. Don't do it, it's not necessary!
Why would you calculate conditions to count through virtually all records when you only need to stop after first match?
Implementation without count is simple, just take: 1 (a further optimization might be selecting only id, if your table has fields with too much data)
const isHost = await prisma.meal
.findMany({
where: {
id,
host: { id: user.id },
},
select: { id: true }, // this line might not be necessary
take: 1 // this is the important bit
})
.then(r => r.length > 0)
Performance in SQL (postgres and mysql) of SELECT id ... LIMIT 1 is practically equal to SELECT ... EXISTS ... (although EXISTS may not do some operations like join if the optimizer finds that is faster)
Count is slower by several orders of magnitude, in tables with millions of records, count can take seconds to return value.
Edit: Alternative implementation, simpler in js by my coworker @gabymorgi, findFirst returns null if not found. You can use the value in a condition as a truthy value if record exists, or cast to boolean for safety.
const isHost = await prisma.meal
.findFirst({ // using findFirst instead of findMany will not return an array
where: {
id,
host: { id: user.id },
},
select: { id: true }, // this line might not be necessary
})
.then(r => Boolean(r)) // optional line. Can use truthiness of one record directly (findfirst returns null if not found)
Looks like that's an argument in favor of adding .exists(). The implementation is simpler but at least I can confess that until now I assumed count was as fast as the take solution, and it wouldn't have occurred to me to reach for take.
I vote with exists, it's easier for newcomers to understand code's readability.
I'd love this too. Am I correct to think that this existed in a previous version of Prisma? See here: https://v1.prisma.io/docs/1.34/prisma-client/features/check-existence-JAVASCRIPT-pyl1/
@anton-johansson Yes, looks like it. My google search earlier actually landed me in that page making me confused why I can't find prisma.$exists
I'd love this too. Am I correct to think that this existed in a previous version of Prisma? See here: https://v1.prisma.io/docs/1.34/prisma-client/features/check-existence-JAVASCRIPT-pyl1/
Landed here with same thought why the exists "existed" on V1 but not current version. Anyway, does prisma development team plan to implement this in the near time ?
I got it working with Client Extensions:
const prisma_ = new PrismaClient();
export const prisma = prisma_
.$extends({
model: {
user: {
exists(where: Parameters<typeof prisma_.user.findFirst>[0]['where']) {
return prisma.user.findFirst({
where,
select: {
id: true
}
}).then(Boolean);
}
},
// Other models go here
}
});
The biggest downside is that I had to repeat it for every single model.
@serg06 ~we are a little bit behind on docs with that, but~ as of latest Prisma version it is possible to implement that universally with $allModels and a couple of helper types:
const prisma = new PrismaClient().$extends({
model: {
$allModels: {
async exists<T>(this: T, where: Prisma.Args<T, 'findFirst'>['where']): Promise<boolean> {
const context = Prisma.getExtensionContext(this)
const result = await (context as any).findFirst({ where })
return result !== null
},
},
},
})
For now we won't close any of the issues for use cases that are now possible via Client extensions until those have stabilized, and are packaged up and documented.
Exists implementation has a problem when you use transactions.
ctx.prisma.$transaction(prisma => {
const { id } = await prisma.user.create({});
const result = await prisma.user.exists({ id });
console.log(result);
});
prisma:query BEGIN
prisma:query INSERT INTO "public"."Equipment" ("id","name") VALUES ($1,$2) RETURNING "public"."Equipment"."id"
prisma:query SELECT "public"."Equipment"."id", "public"."Equipment"."name" FROM "public"."Equipment" WHERE "public"."Equipment"."id" = $1 LIMIT $2 OFFSET $3
prisma:query SELECT "public"."Equipment"."id", "public"."Equipment"."name" FROM "public"."Equipment" WHERE "public"."Equipment"."id" = $1 LIMIT $2 OFFSET $3
false
prisma:query COMMIT
It creates a new context vs using the existing one. So you cant select the one that was created above.
Reproduction:
datasource postgres {
provider = "postgresql"
url = env("DATABASE_URL")
}
generator client {
provider = "prisma-client-js"
output = "../prisma/generated/client"
previewFeatures = ["clientExtensions"]
}
model Equipment {
id String @id @default(cuid())
name String?
}
import { Prisma, PrismaClient } from "./prisma/generated/client";
async function main() {
const rootPrisma = new PrismaClient({
log: [
{
emit: "event",
level: "query",
},
],
}).$extends({
model: {
$allModels: {
async exists<T>(
this: T,
where: Prisma.Args<T, "findFirst">["where"],
): Promise<boolean> {
const context = Prisma.getExtensionContext(this);
const result = await (context as any).findFirst({ where });
console.log("exists", { result });
return result !== null;
},
},
},
});
rootPrisma.$on("query", e => {
console.log("Query: " + e.query);
console.log("Params: " + e.params);
console.log("Duration: " + e.duration + "ms");
});
await rootPrisma.$connect();
rootPrisma.$transaction(async prisma => {
const { id } = await prisma.equipment.create({ data: { name: "test" } });
const findResult = await prisma.equipment.findFirst({ where: { id } });
const existsResult = await prisma.equipment.exists({ id });
console.log("transaction", { findResult, existsResult });
});
}
main().catch(console.error);
Query: BEGIN
Params: []
Duration: 0ms
Query: INSERT INTO "public"."Equipment" ("id","name") VALUES ($1,$2) RETURNING "public"."Equipment"."id"
Params: ["cleh26nmv0001tpt3bm1rafdy","test"]
Duration: 1ms
Query: SELECT "public"."Equipment"."id", "public"."Equipment"."name" FROM "public"."Equipment" WHERE "public"."Equipment"."id" = $1 LIMIT $2 OFFSET $3
Params: ["cleh26nmv0001tpt3bm1rafdy",1,0]
Duration: 0ms
Query: SELECT "public"."Equipment"."id", "public"."Equipment"."name" FROM "public"."Equipment" WHERE "public"."Equipment"."id" = $1 LIMIT $2 OFFSET $3
Params: ["cleh26nmv0001tpt3bm1rafdy",1,0]
Duration: 0ms
Query: SELECT "public"."Equipment"."id", "public"."Equipment"."name" FROM "public"."Equipment" WHERE "public"."Equipment"."id" = $1 LIMIT $2 OFFSET $3
Params: ["cleh26nmv0001tpt3bm1rafdy",1,0]
Duration: 0ms
exists { result: null }
transaction {
findResult: { id: 'cleh26nmv0001tpt3bm1rafdy', name: 'test' },
existsResult: false
}
Query: COMMIT
Params: []
Duration: 42ms
@serg06 ~we are a little bit behind on docs with that, but~ as of latest Prisma version it is possible to implement that universally with
$allModelsand a couple of helper types:const prisma = new PrismaClient().$extends({ model: { $allModels: { async exists<T>(this: T, where: Prisma.Args<T, 'findFirst'>['where']): Promise<boolean> { const context = Prisma.getExtensionContext(this) const result = await (context as any).findFirst({ where }) return result !== null }, }, }, })
Is there a way of accessing the model itself without type-casting to any?
For now we won't close any of the issues for use cases that are now possible via Client extensions until those have stabilized, and are packaged up and documented.
@janpio this is possible with the client extensions, and those are now "stabilized", but to me it seems like an exists function ought to have first class support without extensions
To get some inspiration how other ORMs deal with this:
- https://semaphoreci.com/blog/2017/03/14/faster-rails-how-to-check-if-a-record-exists.html
- https://api.rubyonrails.org/classes/ActiveRecord/FinderMethods.html#method-i-exists-3F
Build.where(:created_at => 7.days.ago..1.day.ago).passed.exists? # SELECT 1 AS one FROM "builds" WHERE ("builds"."created_at" BETWEEN # '2017-02-22 21:23:04.066301' AND '2017-02-28 21:23:04.066443') AND # "builds"."result" = $1 LIMIT 1 [["result", "passed"]]
Imo, this is very desirable. Implementations of the exists function using Client Extensions are always limited by the existing Prisma functions. We cannot select "nothing" which adds a removable performance overheap to the custom exists function. Something like the quoted approach would be better because it does not select data from the database.
I feel like exists() should be built into the ORM by default, and not require creating a ClientExtension to add it as a helper method. It is pretty common in other ORMs:
drizzle: https://orm.drizzle.team/docs/operators#exists kysely: https://kysely.dev/docs/examples/WHERE/complex-where-clause
