Ignored WHERE clause when using custom query builder

[riculum]

I'm encountering an unexpected behavior when using Drizzle ORM alongside a custom query builder function. The problem seems to arise from the interaction between my function and the query builder. Here's a simplified version of the code:

I have a function getSize:

async function getSize(id: string, options: Pick<Options, "enabled"> = {
  enabled: DEFAULT_OPTIONS.enabled,
}): Promise<InferSelectModel<typeof sizeSchema>> {
  const { enabled = DEFAULT_OPTIONS.enabled } = options;
  let query = db.select().from(sizeSchema).where(and(eq(sizeSchema.id, id))).$dynamic();
  query = withEnabled(query, sizeSchema, enabled);
  query = withLimit(query, 1);
  const sizes = await query;
  return sizes[0];
}

Accompanied by a query builder function withEnabled:

export function withEnabled<T extends PgSelect>(
  qb: T,
  schema: any,
  enabled: boolean | undefined,
) {
  if (enabled !== undefined) {
    return qb.where(eq(schema.enabled, enabled));
  }
  return qb;
}

Despite setting the initial where clause in the getSize function (where(and(eq(sizeSchema.id, id)))), it appears that when I use withEnabled, the initial where clause is being ignored, resulting in unexpected behavior in my query execution.

I suspect the issue might be related to how the custom query builder interacts with Drizzle ORM's query building process. I'm seeking advice on how to ensure that the initial where clause is preserved when using custom query builders alongside Drizzle ORM.

[Angelelz]

Yes, I think the documentation around the $dynamic() queries is not perfectly clear that you still cannot call a method more than once.
In your case, the where method used in the function withEnabled is overriding the where used in getSize.
To solve your problem, you don't really need to use the withEnabled function, you just need to add the filter conditionally like this:

async function getSize(id: string, options: Pick<Options, "enabled"> = {
  enabled: DEFAULT_OPTIONS.enabled,
}): Promise<InferSelectModel<typeof sizeSchema>> {
  const { enabled = DEFAULT_OPTIONS.enabled } = options;
  let query = db.select().from(sizeSchema).where(and(
      eq(sizeSchema.id, id),
      enabled !== undefined ? eq(sizeSchema.enabled, enabled) : undefined
  )).$dynamic();
  query = withEnabled(query, sizeSchema, enabled);
  query = withLimit(query, 1);
  const sizes = await query;
  return sizes[0];
}

The and function will not include undefined by default.

[ghost]

$dynamic is introduced in 0.29.0.

Before that this worked for me.

async function getSize(id: string, options: Pick<Options, "enabled"> = {
  enabled: DEFAULT_OPTIONS.enabled,
}, recordsToFetch: number): Promise<InferSelectModel<typeof sizeSchema>> {
  const { enabled = DEFAULT_OPTIONS.enabled } = options;
  const whereClauses: any[] = [eq(sizeSchema.id, id)];
  if (!enabled) {
    whereClauses.push(eq(sizeSchema.enabled, enabled));
  }
  let query = db.select().from(sizeSchema).where(and(...whereClauses));
  if(recordsToFetch) {
    query.limit(recordsToFetch);
  }
  const sizes = await query;
  return sizes[0];
}

[riculum]

That would be nice if it worked, but I get a type error at whereClauses.push:

TS2769: No overload matches this call.
Overload 1 of 3,
(left: PgColumn<{ name: "enabled"; tableName: "sizes"; dataType: "boolean"; columnType: "PgBoolean"; data: boolean; driverParam: boolean; notNull: true; hasDefault: true; enumValues: undefined; baseColumn: never; }, {}, {}>, right: boolean | SQLWrapper): SQL<...>
, gave the following error.
Argument of type false | undefined is not assignable to parameter of type boolean | SQLWrapper
Type undefined is not assignable to type boolean | SQLWrapper
Overload 2 of 3,
(left: Aliased<false | undefined>, right: false | SQLWrapper | undefined): SQL<unknown>
, gave the following error.
Argument of type
PgColumn<{     name: "enabled";     tableName: "sizes";     dataType: "boolean";     columnType: "PgBoolean";     data: boolean;     driverParam: boolean;     notNull: true;     hasDefault: true;     enumValues: undefined;     baseColumn: never; }, {}, {}>
is not assignable to parameter of type Aliased<false | undefined>
Type
PgColumn<{     name: "enabled";     tableName: "sizes";     dataType: "boolean";     columnType: "PgBoolean";     data: boolean;     driverParam: boolean;     notNull: true;     hasDefault: true;     enumValues: undefined;     baseColumn: never; }, {}, {}>
is missing the following properties from type Aliased<false | undefined>: sql, fieldAlias
Overload 3 of 3, (left: never, right: unknown): SQL<unknown>, gave the following error.
Argument of type
PgColumn<{     name: "enabled";     tableName: "sizes";     dataType: "boolean";     columnType: "PgBoolean";     data: boolean;     driverParam: boolean;     notNull: true;     hasDefault: true;     enumValues: undefined;     baseColumn: never; }, {}, {}>
is not assignable to parameter of type never

[Angelelz]

So, I'll share a little bit of the history of how we arrived to this new .$dynamic() method.

Drizzle has never supported calling the same method more than once on the query builders. But people new to it, just didn't know, and they were assuming that doing db.select().from(users).where(someFilter).where(someOtherFilter) would apply those 2 filters, but as always, the last one is the only one that gets applied.

This behavior mirrors the SQL syntax, in which using more than one where clause in a query is not allowed.

To address that, the team made some changes at the type level, only at the type level, where now it would show an error if you call the same method more than once. But this change made the types so restrictive that they also had to introduce the .$dynamic() method. Notice how all of this is only at the type level, the runtime has always stayed the same. So you couldn't call where more than once before this changes, and also after these changes. .$dynamic() only disables the new type restrictions and nothing more.