Support for PostgreSQL PostGIS with DB push

[nrdobie]

When attempting to use the PostgreSQL PostGIS geometry data type, drizzle-kit will currently quote it and break the ability to use DB push.

This is the custom type I am trying to use:

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

export type Point = {
  longitude: number;
  latitude: number;
};

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

    if (!matches) {
      throw new Error("Invalid point format");
    }

    const { longitude, latitude } = matches.groups as {
      longitude: string;
      latitude: string;
    };

    return {
      longitude: parseFloat(longitude),
      latitude: parseFloat(latitude),
    };
  },
});

This is the generated SQL:

CREATE TABLE IF NOT EXISTS "location_test" (
    "id" varchar(24) PRIMARY KEY NOT NULL,
    "name" varchar(256) NOT NULL,
    "location" "geometry(point, 4326)" NOT NULL
);

The issue comes specifically from this line in the Drizzle Kit code:

const type = isPgNativeType(column7.type) ? column7.type : `"${column7.type}"`;

Either extend isPgNativeType to include geometry as a native type or add support for using the sql or sql.raw as part of the dataType return.

export const point = customType<{
  data: Point;
  driverData: string;
}>({
  dataType() {
    return sql.raw`geometry(point, 4326)`;
  },
  // ...
});

This would make using drizzle with geospatial data usable.

[imoshimuir]

I'm also experiencing this issue e.g the generated SQL looks like:

`CREATE TABLE IF NOT EXISTS "my_table" (
    "areas" "geometry(Polygon,4326)"
)

with geometry in quotations

[hill]

I am also having this issue. We are having to manually remove the quotes which is a pain. I would offer a patch to the tool however the drizzle-kit source does not seem to be generally available? 😅

[Schmavery]

It looks like drizzle-kit 0.22 has added "geometry(" to the list of pgNativeTypes string prefixes. This doesn't let you use "geometry" on its own as a type, but maybe there's a way to work around that on the sql side? ~Seems like maybe postgis lets you say geometry()?~ oops nvm:

psql:/home/runner/work/drizzle-postgis/drizzle-postgis/src/test/schema-snapshot.sql:18: ERROR:  syntax error at or near ")"
LINE 3:  "geometry" geometry(),

Looks like geometry(Geometry) might work though. Passes my tests anyway.

The drizzle-kit update doesn't support box2d/box3d columns in case anyone is using that.

FWIW I've been trying to get this stuff working over at https://github.com/Schmavery/drizzle-postgis in case that helps anyone.