frankylee Comments - Answer Overflow

frankylee

•Created by frankylee on 7/24/2023 in #help

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

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}`)

4 replies

DTDrizzle Team

•Created by frankylee on 7/24/2023 in #help

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

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(),
})

4 replies

DTDrizzle Team

•Created by rushil1o1 on 3/28/2023 in #help

Plans to add array related support for Postgres?

@rushil1o1 Could you provide an example of how you were able to resolve this with a custom sql? All of my attempts have been met with PostgresError: malformed array literal. I am able to successfully achieve this in Postgres directly, but there seem to be issues with Drizzle ORM converting the TS array into a string and escaping the quotations. Being able to query by array comparison is essential for my use case, and if I cannot get it to work with Drizzle, I'll have to visit other options.

6 replies

Gaming

Programming