Modular SELECT statements in Drizzle ORM

Question

I have a function that queries a database using Drizzle ORM and accepts various options, such as enabled, limit, and potentially more in the future. I'm looking for a more concise and modular way to handle these options in my function.

Here's the current implementation:

type Options = {
    enabled?: boolean,
    limit?: number,
    // Potentially more options in the future
}

async function getAddresses(contactId: number, options: Options): Promise<InferSelectModel<typeof addressSchema>[]> {
    if (options.enabled !== undefined && options.limit) {
        return db.select().from(addressSchema).where(and(eq(addressSchema.contactId, contactId), eq(addressSchema.enabled, options.enabled))).limit(options.limit)
    } else if (options.enabled !== undefined && options.limit === 0) {
        return db.select().from(addressSchema).where(and(eq(addressSchema.contactId, contactId), eq(addressSchema.enabled, options.enabled)))
    } else if (options.limit) {
        return db.select().from(addressSchema).where(eq(addressSchema.contactId, contactId)).limit(options.limit)
    } else {
        return db.select().from(addressSchema).where(and(eq(addressSchema.contactId, contactId))
    }
}

I'd like to simplify this code while using Drizzle ORM, make it more modular, and prepare for potential future options (e.g., an offset option). How can I achieve a more elegant and maintainable solution with Drizzle ORM?

Answer 1

You can use dynamic query building as demonstrated in the drizzle docs: https://orm.drizzle.team/docs/dynamic-query-building

Define a function for each parameter you want to conditionally apply, e.g.

function withLimit<T extends PgSelect>(qb: T, limit) {
  if (limit !== undefined) {
    return qb.limit(limit)
  }
  return qb
}

Then you can build up your query by applying each function, e.g.

let qb = db.select().from(addressSchema).$dynamic()
qb = withLimit(qb, options.limit)
// use await qb or qb.then to get your result