Experimental / hacky custom drizzle column for PostGIS geometry (injection method)

geometryType.ts

export type GeometryTypes = {
  Point: GeoJSON.Point;
  LineString: GeoJSON.LineString;
  Polygon: GeoJSON.Polygon;
  MultiPoint: GeoJSON.MultiPoint;
  MultiLineString: GeoJSON.MultiLineString;
  MultiPolygon: GeoJSON.MultiPolygon;
  GeometryCollection: GeoJSON.GeometryCollection;
};

/**
 * Experimental custom type for PostGIS geometry, only supports Point and LineString.
 */
export const geometryType = <
  TType extends GeoJSON.Geometry["type"] = GeoJSON.Geometry["type"],
  T extends CustomTypeValues = CustomTypeValues,
>(
  dbName: string,
  fieldConfig?: T["config"] & { type: TType },
) => {
  const type = fieldConfig?.type;
  return customType<{
    data: GeometryTypes[TType];
  }>({
    dataType() {
      return type ? `geometry(${type},4326)` : "geometry";
    },
    toDriver(value) {
      throw new Error("Not implemented");
    },
    fromDriver(value) {
      try {
        const data = JSON.parse(value as string);

        if (type && data.type !== type) {
          throw new Error(`Expected geometry type ${type}, got ${data.type}`);
        }

        return data as GeometryTypes[TType];
      } catch (e) {
        throw new Error(`Failed to parse geometry`, {
          cause: e,
        });
      }
    },
  })(`public".ST_AsGeoJSON("${dbName}") as "${dbName}`, {
    ...fieldConfig,
  });
};

Thanks for providing this! Where does CustomTypeValues come from? And when executing, I get PostgresError: syntax error at or near "." I suppose this
https://gist.github.com/ItsWendell/65a107c0f8de00fdd6e4f3935789ce5a#file-geometrytype-ts-L46
is not valid syntax for migrations

I over simplified this just to get it working for my one use case where my csr is always 4326 so I didn't need extra column configs.

import { customType } from "drizzle-orm/pg-core";

export const geometry = <TData = GeoJSON.Geometry>(name: string) =>
  customType<{ data: TData; driverData: string }>({
    dataType() {
      return "geometry";
    },
    toDriver(value: TData): string {
      return JSON.stringify(value);
    },
    fromDriver(value) {
      return JSON.parse(value) as TData;
    },
  })(`public".ST_AsGeoJSON("${name}") as "${name}`);

const feature = await db.query.features.findFirst({});

// returns
{
  "title": "The Liberty View at Independence Visitor Center",
  "description": "<p>Ceremony</p>",
  "summary": "Ceremony",
  "geometry": {
    "type": "Point",
    "coordinates": [
      -75.15011697,
      39.950944977
    ]
  }
}

Hey @fvaldes33 !

This is great, thanks for sharing!

How to you use it for schema definitions and migration generation? That part is not working okay for me.

Cloud you please share more details?

Thank you,
Kristóf

@hauserkristof - I am not. I usually write migrations manually. I am only using drizzle for the typesafe / orm api not to 100% manage my database schema.

The issue is probably the hack around how the name is generated in that custom type. there is an open PR to add a custom select in custom types that will allow you to use a normal column name instead of the ST_AsGeoJson wrapper.

@madebyfabian @hauserkristof @fvaldes33 I've also written an alternative implementation by parsing the actual values from the database:

https://gist.github.com/ItsWendell/38ebe96b34d00d9138ce23cc363d7009