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.

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

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

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')
})

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`)

Thanks a lot! I appreciate it