get drizzle to correctly infer type with limit 1

If you do something simple like this:

const user = await db
  .select()
  .from(users)
  .where(eq(users.userId, 1))
  .limit(1);
return user;

const user = await db
  .select()
  .from(users)
  .where(eq(users.userId, 1))
  .limit(1);
return user;

Drizzle seems to infer the type of user as an array

user[]

user[]

, in spite of specifying limit(1) which should only return one row from the users table. What is the best way to get the correct type of user, which should be

user | undefined

user | undefined

18 Replies

delight•15mo ago

add an .executeTakeFirst() at the end :-)#

hachoter•15mo ago

limit still returns an array, so the type is correct

zendevOP•15mo ago

not seeing this as an option anywhere in the docs, can you point me to it?

Angelelz•15mo ago

I don't thinkg there is an executeTakeFirst method on a select statement in drizzle Anyway, drizzle has no way of knowing if your database will return one value or cero values, so the best next thing is to return an array as your driver is doing. If drizzle were to return an user instead of an array, how would expect its behavior if the array returned from the db is empty? Return null, undefined or throw an error?

Angelelz•15mo ago

Take a look at this very informative thread from one of the drizzle team members: https://x.com/bloberenober/status/1644798759002357762?s=20

Dan (@bloberenober)

A lot of requests we receive about @DrizzleOrm are related to the "convenience" APIs - stuff that is present in a lot of popular ORMs and that, for a lot of people, differentiates "ORMs" from "query builders". Let me share our vision of it and a bit of Drizzle history.

Likes

116

Twitter

zendevOP•15mo ago

Just read it, I understand the approach Drizzle is taking and why they don't provide convenience APIs. So how would you propose handling this very common use case of needing to return just the one row? Do something like this:

const user = await db
  .select()
  .from(users)
  .where(eq(users.userId, 1))
  .limit(1);
return user[0];

const user = await db
  .select()
  .from(users)
  .where(eq(users.userId, 1))
  .limit(1);
return user[0];

Or would you do something else? I guess that was my original question in the first place, how are people handling this case in their own code?

Angelelz•15mo ago

There are several options, one is the one you just described. Which will be user | undefined You could do:

const response = await db
  .select()
  .from(users)
  .where(eq(users.userId, 1))
  .limit(1);
const user = response[0];
if (!user) throw new Error("whatever you want");
return user;

const response = await db
  .select()
  .from(users)
  .where(eq(users.userId, 1))
  .limit(1);
const user = response[0];
if (!user) throw new Error("whatever you want");
return user;

This will be user, you would have to handle the case when it doesn't find anything and throws

zendevOP•15mo ago

got it, thanks for your help and sharing the thread

Angelelz•15mo ago

Have you seen the convenience API we now have with drizzle? The relational query API

Angelelz•15mo ago

https://orm.drizzle.team/docs/rqb

Drizzle Queries - DrizzleORM

Drizzle ORM | %s

zendevOP•15mo ago

Yep, I've had a look but for the sake of separating concerns I prefer to only use that for relational queries. I try to keep all non-relational queries using select, insert, etc. I'm aware that you could use a findFirst() from there but didn't want to use that when I'm not querying relations

Angelelz•15mo ago

sounds good

delight•15mo ago

yes yes, I'm sorry my mindset was on the wrong project (kysely). In drizzle you can do:

import { eq } from 'drizzle-orm';


await db.query.users.findFirst({
     where: eq(users.id, 1)
})

import { eq } from 'drizzle-orm';


await db.query.users.findFirst({
     where: eq(users.id, 1)
})

delight•15mo ago

https://orm.drizzle.team/docs/rqb#find-first

Drizzle Queries - DrizzleORM

Drizzle ORM | %s

Jin•15mo ago

await db
  .select()
  .from(users)
  .where(eq(users.userId, 1))
  .limit(1).then(r => r.at(0))

await db
  .select()
  .from(users)
  .where(eq(users.userId, 1))
  .limit(1).then(r => r.at(0))

Doesn't this work?

delight•15mo ago

looks nifty ... did you try it ? Don't know if then is only executed if the [] has results

Jin•15mo ago

It worked well for me

Angelelz•15mo ago

That's a nice trick, I didn't think of that

Gaming

Programming

get drizzle to correctly infer type with limit 1