Arrays of composite types in PostgreSQL via NodeJS

Question

I'm using Node.JS ("pg" package) to connect to a PostgreSQL database hosted on Heroku. I need to create a column in my table that will contain an array of different data types. By looking at other questions previously asked on Stackoverflow, I understand i can create composite data types that I can use to declare the array with. Like:

create type my_item as (
    field_1        text,
    field_2        text,
    field_3        text,
    field_4        number
);

However, I don't understand how to implement this when using Node.JS. Where do I put it in my files and at what point do I run it?

I have an index.JS file containing my Pool instance and the database access info. My functions are stored in a models folder. Each function has its own SqlString variable which is then passed to the query. Like:

export async function getScores() {
    const data = await query(`SELECT * FROM score`);
    return data.rows;
}

Appreciate any help.

Answer 1

There is no such thing as array of different composite types in Postgresql. You might need to store the column as json/jsonb type instead and deal with them at the application level. Or create a superset type of all possible types in the array and deal with NULLs at the application level. That only works if the subset types don't overlap different types on the same key. Also the main usecase for composites is related to INSERT/UPDATE/DELETE queries, aka anything that requires value interpolation from the application. Of course it's no use in your example code.