[FEATURE]: Default select for custom types

[drepkovsky]

Describe what you want

When creating a custom type that doesn't have a straightforward select like a postgis geometry, for example.

export type Point = {
  lat: number;
  lng: number;
};

export const pointType = customType<{ data: Point; driverData: string }>({
  dataType() {
    return 'geometry(Point,4326)';
  },
  toDriver(value: Point): string {
    return `SRID=4326;POINT(${value.lng} ${value.lat})`;
  },
  fromDriver(value: string) {
    const matches = value.match(/POINT\((?<lng>[\d.-]+) (?<lat>[\d.-]+)\)/);
    const { lat, lng } = matches?.groups ?? {};

    return { lat: parseFloat(String(lat)), lng: parseFloat(String(lng)) };
  },
});

We need to create a select function wrapper to properly select the field with this custom type.

export const selectPoint = (column: string, decoder: DriverValueMapper<any, any>) => {
  return sql<Point>`st_astext(${sql.identifier(column)})`.mapWith(decoder).as(column);
};

// then select it like this:
db.select({
   ...allOtherFields
  coords: selectPoint('coords', location.coords),
}).from(location);

That means we always need to specify this custom select when working with this table, also we are not able to use the relational query syntax because the there is no way to provide a custom sql fragment in the columns object (only true/false boolean specifying the field inclusion)

Proposal:

Add a selectFromDb option to customType factory function like so:

export const pointType = customType<{ data: Point; driverData: string }>({
  dataType() {
    return 'geometry(Point,4326)';
  },
  toDriver(value: Point): string {
    return `SRID=4326;POINT(${value.lng} ${value.lat})`;
  },
  fromDriver(value: string) {
    const matches = value.match(/POINT\((?<lng>[\d.-]+) (?<lat>[\d.-]+)\)/);
    const { lat, lng } = matches?.groups ?? {};

    return { lat: parseFloat(String(lat)), lng: parseFloat(String(lng)) };
  },

 /** this is new */
  selectFromDb(column, decoder) {
    return sql<Point>`st_astext(${sql.identifier(column)})`.mapWith(decoder).as(column);
  },
});

Now we when the field is being selected from db the selectFromDb function will be automatically called if specified for given field.

[ItsWendell]

@drepkovsky this would be super useful, especially for PostGIS or any other data types you might want to cast within the database query. I've been experimenting with temporary workarounds, there's two ways to potentially do this now without this 'selector'.

1. Manually parse PostGIS hex / wkx coming from / to the database driver
   • Experimental: https://gist.github.com/ItsWendell/38ebe96b34d00d9138ce23cc363d7009
2. Wrap the customType and SQL inject the public".ST_AsGeoJSON("${dbName}") as "${dbName} as a columnName. (This hardcodes the public schema in here though so this wouldn't work if you need this work on any other schemas too, but could be an config option in the column)
   • Experimental: https://gist.github.com/ItsWendell/65a107c0f8de00fdd6e4f3935789ce5a

If we add support for custom selectors, as described here, the performance and implementation of the custom column would be a lot simpeler, since you could use the ST_ functions to cast as text, or GeoJSON, like in the examples above.

Edit: I've later edited this comment to include another example for point Nr 1, and further extend / improve the text

[ItsWendell]

Related issue by one of the maintainers: https://github.com/drizzle-team/drizzle-orm/issues/554

[matannahmani]

need this asap!

[algora-pbc]

💎 $30 bounty created by @john-griffin 👉 To claim this bounty, submit your pull request on Algora 📝 Before proceeding, please make sure you can receive payouts in your country 💵 Payment arrives in your account 2-5 days after the bounty is rewarded 💯 You keep 100% of the bounty award 🙏 Thank you for contributing to drizzle-team/drizzle-orm!

[Angelelz]

/attempt #1423