How to parse custom types?

[straldev]

We make extensive use of custom types in Postgres, both for sending and receiving data from the DB. I don’t see any mention of node-pg-type supporting this, but I wanted to check if I missed it, apologies if I did!

So given an example custom type like this:

create type car as (
   make  text,
   model text,
   style text,
   color text
);

How could I parse a JS object into that PG type? And likewise, how to parse a raw “car” result from PG into a JS object?

For data coming out of PG we can use to_jsonb() easily enough. Data going into PG though, is more difficult as we use a lot PG functions with custom type parameters.

[bendrucker]

If you're able to query the OID for that custom type, you should be able to register a parser for it normally:

https://github.com/brianc/node-pg-types#use

As for the JS -> PG formatting side, add a toPostgres method if JSON.stringify does not suitably serialize your object.

https://github.com/brianc/node-postgres/blob/master/packages/pg/lib/utils.js#L77-L88

A package that implements this strategy:

https://github.com/bendrucker/postgres-interval

[straldev]

Thanks for the quick response.

Our custom types are duplicated across multiple schemas (we have a multi-tenant app where each tenant is given an identical schema). Because of this a given custom type would have inconsistent OIDs depending on the given connection. Not to mention different environments could also produce inconsistent OIDs for the same "type". Any chance there are existing solutions for this? Either way you've given me a good starting point!

Thanks for you time and insight.

[bendrucker]

Any chance there are existing solutions for this?

Not that I know of. I think you'd have to get a little hacky to dynamically handle it. Open to PRs to make it easier but that'll probably require work on the pg side.

[straldev]

Makes sense. I'll close out this ticket, but I will update it if we come up with a decent solution. Thanks again!