Objection.js: Insert an array of UUIDs

[robertrossmann]

Hello! I am attempting to insert a new row into a table with a column defined as an array or UUIDs:

alter table medias add column "order" uuid[];

const order = [
  'BFAD6B0D-D3E6-4EB3-B3AB-108244A5DD7F'
]

Medias
  .query()
  .insert({
    order: lit(order.map(id => lit(id).castType('uuid'))).castArray()
  })

But the query is malformed and therefore does not execute:

INSERT INTO medias ("order")
VALUES (ARRAY [
  {"_value":"BFAD6B0D-D3E6-4EB3-B3AB-108244A5DD7F","_cast":"uuid","_toJson":false,"_toArray":false}
])

As can be seen, the query contains the JSON-stringified representation of the LiteralBuilder object and not something that the SQL syntax understands as a typecast.

If I skip casting the individual UUID strings and just cast the whole column into an array, then Postgres rejects the query because the column is of type uuid[] but I am attempting to insert the column as text[].

Is this a bug that the individual arrays are not properly cast as UUIDs or am I doing it wrong? 🤔

Thanks for your help!

[robertrossmann]

Hello @koskimas, thanks for the tip! I tried that and unfortunately while the query looks valid, Postgres rejects it with an error along the lines of:

Cannot insert value of type text[] into column of type uuid[]
_(paraphrased)_

[koskimas]

Yeah sorry, I removed my answer as I realized that you already answered my question. I'll try out some stuff and get I'll get back to you.

[koskimas]

This doesn't work yet, but I'll fix it:

Medias
  .query()
  .insert({
    order: lit(order).castArray().castType('uuid[]')
  })

Now that I see that code, castArray should probably be called asArray since there is no casting involved... Maybe I'll add an alias and deprecate the old method. I have to think about that.

Anyway, I'll make the code above work and release a patch version as soon as possible.

[robertrossmann]

Awesome @koskimas, thank you for looking into it so quickly! ❤️

[koskimas]

@robertrossmann This is now fixed. Your original syntax should work now too. I'll release a patch version as soon as the travis tests pass.

[robertrossmann]

@koskimas thank you for your quick help with this! Much appreciated! ❤️