Drizzle Team•16mo ago

Cannot create custom `sql` query with casted array of uuid values in Postgres

A necessary use case is to search a column of Postgres type uuid[] by comparing against a dynamically provided array of values. Due to the way Drizzle escapes quotations in the parameter input, I am unable to get this to work with more than one value. I have tried using the Drizzle inArray() helper, supplying a custom sql query in db.execute(), supplying the sql in the db.select().from(tableSchema).when(), and using the PgDialect class to construct the query. If I write the query in my Postgres GUI, I am able to query successfully. The problem seems to be around providing Drizzle the dynamic array of values and the way it is constructed in the query. The Postgres query I need to construct:

SELECT * FROM providers
WHERE providers.services && ARRAY['4102b85c-66c5-48ad-96e3-e46cd57ab292','67a35e49-c9c5-4242-8274-8c98c7c17d62']::UUID[];

SELECT * FROM providers
WHERE providers.services && ARRAY['4102b85c-66c5-48ad-96e3-e46cd57ab292','67a35e49-c9c5-4242-8274-8c98c7c17d62']::UUID[];

What I end up getting in the Drizzle logging is some variation of the following:

Query: SELECT * FROM "providers" WHERE "providers"."services" && ARRAY[$1]::UUID[] -- params: ["4102b85c-66c5-48ad-96e3-e46cd57ab292\",\"67a35e49-c9c5-4242-8274-8c98c7c17d62"]

PostgresError: invalid input syntax for type uuid: "4102b85c-66c5-48ad-96e3-e46cd57ab292","67a35e49-c9c5-4242-8274-8c98c7c17d62"

Query: SELECT * FROM "providers" WHERE "providers"."services" && ARRAY[$1]::UUID[] -- params: ["4102b85c-66c5-48ad-96e3-e46cd57ab292\",\"67a35e49-c9c5-4242-8274-8c98c7c17d62"]

PostgresError: invalid input syntax for type uuid: "4102b85c-66c5-48ad-96e3-e46cd57ab292","67a35e49-c9c5-4242-8274-8c98c7c17d62"

This is the closest I have gotten, after having tried numerous variations of constructing the sql string as to manipulate where the Drizzle double quotations would be placed. The other common error I see is:

Query: SELECT * FROM "providers" WHERE "providers"."services" && $1 -- params: ["ARRAY['4102b85c-66c5-48ad-96e3-e46cd57ab292','67a35e49-c9c5-4242-8274-8c98c7c17d62']::UUID[]"]

PostgresError: malformed array literal: "ARRAY['4102b85c-66c5-48ad-96e3-e46cd57ab292','67a35e49-c9c5-4242-8274-8c98c7c17d62']::UUID[]"

Query: SELECT * FROM "providers" WHERE "providers"."services" && $1 -- params: ["ARRAY['4102b85c-66c5-48ad-96e3-e46cd57ab292','67a35e49-c9c5-4242-8274-8c98c7c17d62']::UUID[]"]

PostgresError: malformed array literal: "ARRAY['4102b85c-66c5-48ad-96e3-e46cd57ab292','67a35e49-c9c5-4242-8274-8c98c7c17d62']::UUID[]"

Any insight would be highly appreciated. Thank you so much!

2 Replies

frankylee•16mo ago

If it helps, here is an example of the schemas:

export const services = pgTable('services', {
  id: uuid('id').defaultRandom().primaryKey(),
  ...
})
export const providers = pgTable('providers', {
  id: uuid('id').defaultRandom().primaryKey(),
  ...
  services: uuid('services')
    .references(() => services.id)
    .notNull()
    .default(sql`{}::uuid[]`)
    .array(),
})

export const services = pgTable('services', {
  id: uuid('id').defaultRandom().primaryKey(),
  ...
})
export const providers = pgTable('providers', {
  id: uuid('id').defaultRandom().primaryKey(),
  ...
  services: uuid('services')
    .references(() => services.id)
    .notNull()
    .default(sql`{}::uuid[]`)
    .array(),
})

For anyone else who happens upon this, my solution for a dynamic array of type uuid was the following:

const arrayLiteral = `{${serviceIds.join(',')}}`
return await this.repo
  .execute(sql`SELECT * FROM ${providers} WHERE ${providers.services} && ${arrayLiteral}`)

const arrayLiteral = `{${serviceIds.join(',')}}`
return await this.repo
  .execute(sql`SELECT * FROM ${providers} WHERE ${providers.services} && ${arrayLiteral}`)

francis•14mo ago

I just solved this as well. See: https://discord.com/channels/1043890932593987624/1151648988165058658/1151651354100633683

Gaming

Programming

Cannot create custom `sql` query with casted array of uuid values in Postgres