Not able to do an insert based on a custom type

[Zehelein]

I have the following (simplified) definition of a type and a table:

CREATE TYPE source_field AS (
  field_name text_not_null, 
  value JSONB
);
CREATE TABLE my_field (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  field source_field NOT NULL
);

I tried to do an insert with the following construct:

await insert('my_field', {
    field: {
      field_name: 'title',
      value: 'my value',
    },
  }).run(txn);

I get the error:

 error: {
   length: 152,
   name: 'error',
   severity: 'ERROR',
   code: '22P02',
   detail: 'Missing left parenthesis.',
...
   file: 'rowtypes.c',
   line: '153',
   routine: 'record_in',
   message: 'malformed record literal: "{"field_name":"title","value":"my value"}"',
   stack: 'error: malformed record literal: "{"field_name":"title","value":"my value"}"\n' +
     '    at Parser.parseErrorMessage (.../node_modules/pg/node_modules/pg-protocol/dist/parser.js:278:15)\n' +

Using plain SQL the following works:

insert into my_field (field) VALUES (ROW('title', '"some value"'));

Is it possible to do this with zapatos? In my case I have an array of such objects so manually doing a manual sql<...> insert does not work either (or I don't know how) as I need multiple ROWs.

[remidewitte]

Hi, have you tried with db.param ?
See : https://jawj.github.io/zapatos/#casting-parameters-to-json

[Zehelein]

Thanks for your answer @remidewitte. I tried with this but it manages JSON vs. non JSON (plus a cast). A custom type requires a ROW(...) to be used (and for arrays with an explicit cast).

I was able to solve it with two mapping functions to map a single row and to map an array:

Updated database table to include an array as well:

CREATE TABLE app_public.my_field (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  field source_field,
  fields source_field[]
);

The two helper functions I created that manually build the SQL fragment:

  const createArray = (rows: SQLFragment[]): SQLFragment =>
    new SQLFragment(
      ['ARRAY[', ...Array.from({ length: rows.length - 1 }, () => ', '), ']'],
      rows,
    );

  /** The type values must be provided in the order of how the SQL type defined its fields */
  const createRow = (typeValues: any[], sqlType?: string): SQLFragment =>
    new SQLFragment(
      [
        'ROW(',
        ...Array.from({ length: typeValues.length - 1 }, () => ', '),
        sqlType ? `)::${sqlType}` : ')',
      ],
      typeValues.map((r) => param(r)),
    );

Then I can use it like this:

  const row = createRow(
    ['title', JSON.stringify('some value')],
    'source_field',
  );
  const array = createArray(
    [
      ['title', JSON.stringify('some value')],
      ['description', JSON.stringify('my description')],
    ].map((r) => createRow(r, 'source_field')),
  );
  const res = await insert(
    'my_field',
    {
      field: row,
      fields: array,
    },
    {
      returning: ['fields'],
    },
  ).run(txn);

I will leave it open - maybe that could be added in a nice way directly to zapatos. But feel free to just close it as I have a solution that works for me. But it is likely not easy to generalize it as the sort order on how to create the row is important so just providing a JS object would not be possible.

[jawj]

Thanks. It's on my TODO list to take a look at this, but I have a lot going on at present.