Drizzle Team•2y ago

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

Dan•2y ago

Yes, we have the exists operator, you can import it from drizzle-orm/expressions

smokeOP•2y 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

Dan•2y 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`)

smokeOP•2y ago

Thanks a lot! I appreciate it

Gaming

Programming

Select exists