Select exists

Hi there, I was wondering if there is currently a good way of performing a ‘select exists’, to check whether a specific row exists. I could use the exists expression as a subquery, but this is not the same as the select exists if I only want to check if a record exists without returning the values. Thanks a lot, I appreciate it. I could just perform a raw query but there might be some better solutions.
4 Replies
bloberenober
bloberenober15mo ago
Yes, we have the exists operator, you can import it from drizzle-orm/expressions
smoke
smoke15mo ago
That operator requires a subquery though, in a where condition. For example, how would I perform the following query, since this only returns a boolean which is the most efficient way of checking whether a record exists or not (using Postgres):
select exists(select 1 from contact where id=12) as exists
select exists(select 1 from contact where id=12) as exists
bloberenober
bloberenober15mo ago
Ah I see, in that case you should be able to do it like this:
select({
exists: sql`exists(${db.select({ n: sql`1` }).from(contact).where(eq(contact.id, 12))})`.as('exists')
})
select({
exists: sql`exists(${db.select({ n: sql`1` }).from(contact).where(eq(contact.id, 12))})`.as('exists')
})
Looks intimidating, I know Actually, if that's your whole query, it won't work like this, because currently you must use .from() in the main query. So you can use a semi-raw query to achieve what you need:
await db.execute<{ exists: boolean }>(sql`select exists(${db.select({ n: sql`1` }).from(contact).where(eq(contact.id, 12))}) as exists`)
await db.execute<{ exists: boolean }>(sql`select exists(${db.select({ n: sql`1` }).from(contact).where(eq(contact.id, 12))}) as exists`)
smoke
smoke15mo ago
Thanks a lot! I appreciate it
Want results from more Discord servers?
Add your server
More Posts
Custom SQL function (json_agg & json_build_object)Hello there. I'm new to Drizzle and I love it 😍. I have successfully made a `jsonAgg` helper. `Error migrating after updating to orm 0.23.3After updating to the package released an hour ago, I am getting an error on migrating: error - Ranworkflow best practices with planetscaleSo I’m building a small test app (drizzle looked amazing and will definitely want it in my main app Invalid default value for timestampHi, I have recently started learning SQL and I've run into this problem with Drizzle Kit. I have thidrizzle-orm/mysql-core has no InferModel or MySqlRawQueryResult exportsHi there, I'm not sure if I'm misunderstanding something here, but as per the documentation, `drizzlSQLite migrations are not being appliedFirst time using SQLite and drizzleORM, so forgive me if this is a stupid question. I've been tryingQuestion: Wouldnt it make sense for value to be automatically notNull if you set a defaultRight now you would need to use notNull().default(true), is there a reason for it be still nullable Code generated valueI'm using SQLite. How can I create a custom type with an auto-generated value which is generated in Randomly stopped being able to generate migrations (MySQL)Hey! Been trying things out the past day and everything was going great - until I deleted my `migratBUG: Postgres migration script generates a few syntax errorsContext: I was running migrations with drizzle-kit's migration function from `node-postgres` , and k