Introspection Fails with PostgreSQL PostGIS Extension

The PostGIS extension is a very commonly adopted extension that is used in PostgreSQL databases so lat/lon coordinates can be stored and searches can be run to find records within a particular radius, such as nearby store locations or the closest location.

Assuming that PostGIS as well as PostgreSQL is installed on your database server, you can add the PostGIS extension into a PostgreSQL database by executing the following SQL against your db:

CREATE EXTENSION postgis;

Running drizzle-kit introspect:pg after creating the PostGIS extension will generate a schema.ts file, but it is unusable and will cause reference errors to be thrown when trying to initialize Drizzle since Drizzle didn't recognize some of the geographic and geographic data types when trying to introspect the database. I've been able to test Drizzle working fine with a non-geographic custom table in a test database then adding in the extension with no other changes and seeing the reference errors in schema.ts when trying to introspect again.

In addition to the reference errors for the extension itself, any attempt to store lat/lon coordinates as geometry points in your custom tables will also fail as introspection doesn't recognize those either. For example the location field below will have an unknown datatype in your schema if you try the following:

CREATE TABLE "Example"
(
    id SERIAL NOT NULL,
    name CHARACTER VARYING(123),
    location GEOMETRY(POINT)
);

For the many folks who have existing PostgreSQL databases with PostGIS extensions, this would be a show-stopper when it comes to adopting Drizzle. I'm very keen to use it myself on a couple of projects (Drizzle is mindblowingly awesome!) and would happily adopt it if Drizzle was usable with PostGIS. Anyhow, I'd like to also express my appreciation for the work done on this project while I'm here.

Here is part of schema.ts showing the failed introspection for the extension itself:

export const geographyColumns = pgTable("geography_columns", {
    // TODO: failed to parse database type 'name'
    fTableCatalog: unknown("f_table_catalog"),
    // TODO: failed to parse database type 'name'
    fTableSchema: unknown("f_table_schema"),
    // TODO: failed to parse database type 'name'
    fTableName: unknown("f_table_name"),
    // TODO: failed to parse database type 'name'
    fGeographyColumn: unknown("f_geography_column"),
    coordDimension: integer("coord_dimension"),
    srid: integer("srid"),
    type: text("type"),
});

export const geometryColumns = pgTable("geometry_columns", {
    fTableCatalog: varchar("f_table_catalog", { length: 256 }),
    // TODO: failed to parse database type 'name'
    fTableSchema: unknown("f_table_schema"),
    // TODO: failed to parse database type 'name'
    fTableName: unknown("f_table_name"),
    // TODO: failed to parse database type 'name'
    fGeometryColumn: unknown("f_geometry_column"),
    coordDimension: integer("coord_dimension"),
    srid: integer("srid"),
    type: varchar("type", { length: 30 }),
});

For the location field example above, here are the relevant lines from schema.ts:

        // TODO: failed to parse database type 'geometry(Point)'
    location: unknown("location").notNull(),

drizzle-team / drizzle-kit-mirror

Introspection Fails with PostgreSQL PostGIS Extension #355