Composite types with pg-promise

Question

Here is an example code:

battle.heroes = [{ id: hero.id, name: hero.name }]; //This is my array I want to insert
await db.one('INSERT INTO battles(mode, params, heroes) VALUES(${mode}, ${params}, ${heroes}) RETURNING id', {
    mode: battle.mode,
    params: battle.params,
    heroes: battle.heroes,
});

PostgreSQL type 'hero_info':

id int4
name varchar

Answer 1

Present each array element via Custom Type Formatting, either by extending the existing objects with rawType and toPostgres, or using your own custom type, like one below:

const hero = (id, name) => ({
   rawType: true,
   toPostgres: () => pgp.as.format('($1, $2)::hero_info', [id, name])
});

Usage example:

const heroes = [hero(1, 'first'), hero(2, 'second')];

await db.one('INSERT INTO battles(mode, params, heroes) VALUES(${mode}, ${params}, ${heroes}) RETURNING id', {
    mode: battle.mode,
    params: battle.params,
    heroes
});

For the code above, your array of heros will be correctly formatted as:

array[(1, 'first')::hero_info, (2, 'second')::hero_info]