Node-postgres: Inserting a field with composite type

[karellodewijk]

I've got an address type in my db:

CREATE TYPE Address AS (
street VARCHAR(50),
bus VARCHAR(10),
number VARCHAR(10),
zip VARCHAR(10),
city VARCHAR(30),
state VARCHAR(30),
county VARCHAR(30),
extra VARCHAR(50)
);

I've got the address as a js obj/json obj with the same field, but I'm having a hard time passing it.

I tried to pass it as json, with json_agg but it trips over empty fields '' (error: could not determine polymorphic type because input has type "unknown").

I tried passing it as an array (error: malformed record literal: "{"Some street",NULL,"38","2018","Antwerpen",NULL,NULL,NULL}" ... detail detail: 'Missing left parenthesis.'). No amount of casting or adding parentheses in the string of the statement seems to work.

Joining the array with ',', and adding the () and passing the whole thing as a string works but opens me up to sql injection.

What would be a good way to pass a field with a composite type ?

[vitaly-t]

Composite types are a legacy feature, they are terrible for parsing both in and out. JSON replaced composite types, and is what should be used instead.

[charmander]

jsonb_populate_record:

const address = {
    street: '…',
    ⋮
};

pool.query(
    'INSERT INTO addresses VALUES (jsonb_populate_record(NULL::address, $1))',
    [address])