[aethr]

Having recently been down this journey with kysely, these type-safe query builders still seem to have a large gap when it comes to the return types of SQL functions and opaque types.

My current project uses PostGIS which uses opaque types for storing geometry. Geometry columns are added to tables via a function instead of traditional alter table syntax, and select/where clauses on geometry columns need to use PostGIS functions to render the column into useable data.

Unless a system like Litdb includes an easy way to provide type definitions for function return types, it won't be usable with an extension like PostGIS without heavy use of escape hatches, at which point most of the value is lost.

[mythz]

litdb does include support for registering TypeConverters for mapping custom RDBMS types [1]. The drivers doesn't include any converters for custom RDBMS-specific types yet, but you should be able to register your own in your App or even better submit a PR to the postgres driver [2] so it'll work OOB (tho it'll be dependent on whatever postgres.js can be configured to support).

[1] https://litdb.dev/customize#type-converters

[2] https://github.com/litdb/postgres

[aethr]

Correct me if I'm misunderstanding, but this would allow me to register the desired conversion type for a basic postgres type, to and from JavaScript but not for the return value of a specific function or even a specific invocation of a function.

PostGIS uses a lot of functions like ST_AsEWKT, ST_AsMVT or ST_AsGeoJSON [1] to marshal data. While ST_AsGeoJSON will always return "text", ideally you'd want an invocation of ST_AsGeoJSON to return JSON to your JavaScript, but this wouldn't be true of all "text".

Even better, you would want to declare the structure of the returned JSON via a TypeScript type. GeoJSON is a structured format, so this would likely be a generic GeoJSON type wrapped around a custom type for the specific structure you expect for each geometry type / query.

Anyway, it's a tough problem to solve without introducing TS versions of each specialty function, which would be a large effort for an extension the size of PostGIS. For now I use typed raw queries via the sql<T>`` escape hatch provided by kysely, but if your library made this more ergonomic/safe I'd consider switching!

[1] https://postgis.net/docs/manual-3.5/ST_AsGeoJSON.html

[mythz]

Type Converters lets you change the parameter value that's executed with the underlying provider (postgres driver uses postgres.js [1]) and what value is converted from the provider's resultset to your class property. So it would be up to whether the underlying provider can be configured to support the custom RDBMS type. If you leave a feature request [2] I can let you know when it's implemented or it's not possible with postgres.js when I get around to it.

[1] https://github.com/porsager/postgres

[2] https://github.com/litdb/litdb/discussions/categories/ideas

[retropragma]

Hey, I'm developing a different approach that may appeal to a Kysely user such as yourself. I'd be glad to get your thoughts on it :)

Raw SQL (or PL/pgSQL which can be quite powerful) with generated, type-safe client "bindings" (TypeScript functions). It also includes "declarative SQL schema" (instantly update the schema of your dev DB on file save). Generated migrations and "seed scripts" are also on the roadmap.

If any of that interests you, check it out (https://github.com/pg-nano/pg-nano). I would also be happy to discuss it with you on Discord (@aleclarson).