DT
Drizzle Team•17mo ago
frankylee

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
frankyleeOP•17mo 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
francis•15mo ago
Want results from more Discord servers?
Add your server