Is there any way to make prepare/placeholder support optional parameters?

[zicjin]

maybe:

const formsPrepare = db.query.forms.findMany({
  where: and(
    eq(forms.project_id, sql.placeholder("project_id")),
    eq(forms.slug, sql.placeholder("slug"), { ignore_undefined: true })
  ),
}).prepare("query_forms")

function queryValues(
  project_id: number,
  slug: string | undefined,
) {
  const forms_result = await formsPrepare.execute({ project_id, slug })
  ...
}

Is it possible to create a config like ignore_undefined?

[AhmedBaset]

Did you find any solution?

[zicjin]

Yes. I have written a couple of helper methods for dealing with such issues：

import { eq, sql, SQLWrapper } from "drizzle-orm"

export const eq_optional_point_types = (column: SQLWrapper, placeholder: string) =>
  sql`(${sql.placeholder(placeholder)}::point_types IS NULL OR ${column} = ${sql.placeholder(placeholder)}::point_types)`

export const eq_optional_text = (column: SQLWrapper, placeholder: string) =>
  sql`(${sql.placeholder(placeholder)}::text IS NULL OR ${column} = ${sql.placeholder(placeholder)}::text)`

export const eq_optional_integer = (column: SQLWrapper, placeholder: string) =>
  sql`(${sql.placeholder(placeholder)}::integer IS NULL OR ${column} = ${sql.placeholder(placeholder)}::integer)`

export const eq_optional_boolean = (column: SQLWrapper, placeholder: string) =>
  sql`(${sql.placeholder(placeholder)}::boolean IS NULL OR ${column} = ${sql.placeholder(placeholder)}::boolean)`

export const in_integer_array = (column: SQLWrapper, placeholder: string) => {
  // https://github.com/drizzle-team/drizzle-orm/issues/1415#issuecomment-1858969552
  // inArray(column, sql`${sql.placeholder(placeholder)}`),
  return eq(column, sql`any(string_to_array(${sql.placeholder(placeholder)}, ',')::integer[])`)
}

const points_prepare = db.query.points
  .findMany({
    where: and(
      eq(model.project_id, sql.placeholder("project_id")),
      eq_optional_integer(model.id, "point_id"),
      eq_optional_point_types(model.type, "point_type"),
    ),
    ...
  })
  .prepare("points_query")

Supplement: only for postgresql