DT
Drizzle Team•16mo ago
zendev

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
delight•16mo ago
add an .executeTakeFirst() at the end :-)#
hachoter
hachoter•16mo ago
limit still returns an array, so the type is correct
zendev
zendevOP•16mo ago
not seeing this as an option anywhere in the docs, can you point me to it?
Angelelz
Angelelz•16mo 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
Angelelz•16mo 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
zendev
zendevOP•16mo 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
Angelelz•16mo 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
zendev
zendevOP•16mo ago
got it, thanks for your help and sharing the thread
Angelelz
Angelelz•16mo ago
Have you seen the convenience API we now have with drizzle? The relational query API
Angelelz
Angelelz•16mo ago
zendev
zendevOP•16mo 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
Angelelz•16mo ago
sounds good
delight
delight•16mo 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
delight•16mo ago
Jin
Jin•16mo 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
delight•16mo ago
looks nifty ... did you try it ? Don't know if then is only executed if the [] has results
Jin
Jin•16mo ago
It worked well for me
Angelelz
Angelelz•16mo ago
That's a nice trick, I didn't think of that

Did you find this page helpful?