Custom type converters support

[radzish]

It would be very nice to have ability to standard override type conversion. This could be quite helpful with handling such types as numeric (where I could use some other type than String), or dates and datetimes.

[hurrba]

+1, would love this as well!

to give a concrete example - we are using Freezed and JsonSerializable together with this package, and it's a headache every time we need to fetch timestamps from the database.

We either need to

• cast them to ::text, but this causes the mappedResult to end up in an unnamed collection
• extract them and call .toIso8601String() and put them into a new map to be passed to the fromJson()

We are never interested in getting the timstamps as DateTime, and just want them as strings

[ryanaston]

+1, ability to override and also the option to define additional types would be really helpful

[isoos]

@ryanaston @radzish @hurrba @ankane: the current 3.4 prerelease has the outline for custom type converters. Please check them and give me feedback on it.

[ankane]

Hi @isoos, thanks for adding! A few initial thoughts:

1. It'd be nice to be able to register types by name (in addition to OID). Otherwise, for types with a dynamic OID (like hstore, citext, and types from other extensions), you need to first connect to get the OID (SELECT regtype('hstore')::oid;), and then reconnect with a custom TypeRegistry.
2. Documentation would be great (I'm having trouble getting encoding working for a custom class).

[isoos]

1. It'd be nice to be able to register types by name (in addition to OID). Otherwise, for types with a dynamic OID (like hstore, citext, and types from other extensions), you need to first connect to get the OID (SELECT regtype('hstore')::oid;), and then reconnect with a custom TypeRegistry.

I'm not entirely opposed to this, but there are downsides:

• Best case at connection opening, worst case at query preparation/result processing we would need to run that extra query. The v2 version of this package had a similar dynamic resolution for table names, and it created non-trivial complexity and a few tricky bugs.
• It would be repeated in every connection, even short-lived connections.

I think in the ideal world we should do this at the initialization of a connection Pool, and with that the results are shared between the connections that are opened by the Pool. I'll check how we can make sure we can add this in the future without much breaking changes elsewhere.

2. Documentation would be great.

As always :) Was there any specific thing you would highlight that should definitely be improved?

[ankane]

Thanks @isoos.

1. I think it's better for the library to handle the complexity rather than each developer. Thinking about it more, it'd be nice to be able to register a type after the connection has been established. This way, the user can connect, run CREATE EXTENSION, and then register the type (this is how libraries like psycopg 3 do it).

2. I didn't see any existing documentation for it, but one idea would be a full hstore example.

[isoos]

@ankane: Do you happen to know how psycopg does it? Will it open a new connection for the dynamic queries?

I agree that the library should add support for this, but that may be a support for initialization (and not necessarily dynamic runtime behavior). E.g. you typically CREATE EXTENSION at schema migrations, and not do it for every connection you open. The resulting schema OIDs may be queried and stored with the support of the library, and codec registration can be upfront.

[ankane]

Here are the docs, but you register the type on an open connection (TypeInfo.fetch runs a query to get the OID).

Edit: asyncpg uses a similar approach, fwiw