type "GEOMETRY(POINT)" does not exist

Hi there! I try to use PostGIS with drizzle and having a hard time. I try to run the following migration file

ALTER TABLE "locations" ADD COLUMN "geoPoint" "GEOMETRY(POINT)";

ALTER TABLE "locations" ADD COLUMN "geoPoint" "GEOMETRY(POINT)";

But it errors with

PostgresError: type "GEOMETRY(POINT)" does not exist
    at ErrorResponse (file:///Users/fabian/<redacted>/node_modules/.pnpm/[email protected]/node_modules/postgres/src/connection.js:790:26)
    at handle (file:///Users/fabian/<redacted>/node_modules/.pnpm/[email protected]/node_modules/postgres/src/connection.js:476:6)
    at Socket.data (file:///Users/fabian/<redacted>/node_modules/.pnpm/[email protected]/node_modules/postgres/src/connection.js:315:9)
    at Socket.emit (node:events:514:28)
    at addChunk (node:internal/streams/readable:324:12)
    at readableAddChunk (node:internal/streams/readable:297:9)
    at Readable.push (node:internal/streams/readable:234:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
  severity_local: 'ERROR',
  severity: 'ERROR',
  code: '42704',
  position: '47',
  file: 'parse_type.c',
  line: '270',
  routine: 'typenameType'
}

PostgresError: type "GEOMETRY(POINT)" does not exist
    at ErrorResponse (file:///Users/fabian/<redacted>/node_modules/.pnpm/[email protected]/node_modules/postgres/src/connection.js:790:26)
    at handle (file:///Users/fabian/<redacted>/node_modules/.pnpm/[email protected]/node_modules/postgres/src/connection.js:476:6)
    at Socket.data (file:///Users/fabian/<redacted>/node_modules/.pnpm/[email protected]/node_modules/postgres/src/connection.js:315:9)
    at Socket.emit (node:events:514:28)
    at addChunk (node:internal/streams/readable:324:12)
    at readableAddChunk (node:internal/streams/readable:297:9)
    at Readable.push (node:internal/streams/readable:234:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
  severity_local: 'ERROR',
  severity: 'ERROR',
  code: '42704',
  position: '47',
  file: 'parse_type.c',
  line: '270',
  routine: 'typenameType'
}

Even though PostGIS is setuped successfully. When I run this SQL in supabase directly, it correctly adds the new column with the special data type. I'm not the only one: https://github.com/drizzle-team/drizzle-orm/issues/1315 Maybe someone has an idea, would be grateful!

GitHub

[BUG]: 'type "geography(Point)" does not exist' when using push:pg ...

What version of drizzle-orm are you using? 0.28.6 What version of drizzle-kit are you using? 0.19.13 Describe the Bug drizzle-kit push:pg does not complete when using a custom type and trying to pu...

7 Replies

Angelelz•12mo ago

Can you try running that statement without the double quotes around the type? ie: ALTER TABLE "locations" ADD COLUMN "geoPoint" GEOMETRY(POINT);

Fabian B.OP•12mo ago

Thanks! This worked 🙂 Not sure why they are there, this is my customType

import { sql } from 'drizzle-orm'
import { customType } from 'drizzle-orm/pg-core'
// @ts-ignore
import type { Geometry, Point } from 'geojson'
import wkx from 'wkx'

// --- Utilities to work with PostGIS ---

type GeometryOptions = { type?: string; srid?: never } | { type: string; srid: number }

const dataType = (options?: GeometryOptions) => {
    let result = 'GEOMETRY'
    if (options?.type) {
        result += `(${options.type.toUpperCase()}`
        if (options?.srid) {
            result += `,${options.srid}`
        }

        result += ')'
    }

    return result
}

const toDriver = (value: Geometry) => {
    return sql`ST_GeomFromGeoJSON(${JSON.stringify(value)})`
}

const fromDriver = (value: string) => {
    const b = Buffer.from(value, 'hex')
    return wkx.Geometry.parse(b).toGeoJSON({ shortCrs: true }) as Geometry
}

const geometry = (name: string, options?: GeometryOptions) =>
    customType<{
        data: Geometry
        config: GeometryOptions
        driverData: string
    }>({
        dataType,
        toDriver,
        fromDriver,
    })(name, options)

type PointOptions = Omit<GeometryOptions, 'type'>
type LatLng = { lat: number; lng: number }

const point = (name: string, options?: PointOptions) =>
    customType<{
        data: LatLng
        config: PointOptions
        driverData: string
    }>({
        dataType: options => dataType({ type: 'POINT', ...options }),
        toDriver: ({ lat, lng }: LatLng) =>
            toDriver({
                type: 'Point',
                coordinates: [lng, lat],
            }),
        fromDriver: value => {
            const [lng, lat] = (fromDriver(value) as Point).coordinates
            return { lat, lng }
        },
    })(name, options)

export const postgis = {
    geometry,
    point,
}

import { sql } from 'drizzle-orm'
import { customType } from 'drizzle-orm/pg-core'
// @ts-ignore
import type { Geometry, Point } from 'geojson'
import wkx from 'wkx'

// --- Utilities to work with PostGIS ---

type GeometryOptions = { type?: string; srid?: never } | { type: string; srid: number }

const dataType = (options?: GeometryOptions) => {
    let result = 'GEOMETRY'
    if (options?.type) {
        result += `(${options.type.toUpperCase()}`
        if (options?.srid) {
            result += `,${options.srid}`
        }

        result += ')'
    }

    return result
}

const toDriver = (value: Geometry) => {
    return sql`ST_GeomFromGeoJSON(${JSON.stringify(value)})`
}

const fromDriver = (value: string) => {
    const b = Buffer.from(value, 'hex')
    return wkx.Geometry.parse(b).toGeoJSON({ shortCrs: true }) as Geometry
}

const geometry = (name: string, options?: GeometryOptions) =>
    customType<{
        data: Geometry
        config: GeometryOptions
        driverData: string
    }>({
        dataType,
        toDriver,
        fromDriver,
    })(name, options)

type PointOptions = Omit<GeometryOptions, 'type'>
type LatLng = { lat: number; lng: number }

const point = (name: string, options?: PointOptions) =>
    customType<{
        data: LatLng
        config: PointOptions
        driverData: string
    }>({
        dataType: options => dataType({ type: 'POINT', ...options }),
        toDriver: ({ lat, lng }: LatLng) =>
            toDriver({
                type: 'Point',
                coordinates: [lng, lat],
            }),
        fromDriver: value => {
            const [lng, lat] = (fromDriver(value) as Point).coordinates
            return { lat, lng }
        },
    })(name, options)

export const postgis = {
    geometry,
    point,
}

Angelelz•12mo ago

This might be a drizzle-kit issue it might just add those double quotes by default

The_JRoc•12mo ago

Just ran into this same issue trying to implement bytea on postgres

Hung Nguyen•12mo ago

I am facing the same issue, if I remove the double quotes, I still get the issue "PostgresError: type "geometry" does not exist" I also tried with @Fabian B. 's code above could someone please help? here is how I leverage it

export const locations = pgTable('locations', {
    id: serial('id').primaryKey(),
    coordinate: point('coordinate'),
    api: locationApiType('api'),
});

export const locations = pgTable('locations', {
    id: serial('id').primaryKey(),
    coordinate: point('coordinate'),
    api: locationApiType('api'),
});

Migration file SQL

--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "locations" (
    "id" serial PRIMARY KEY NOT NULL,
    "coordinate" geometry(POINT),
    "api" "location_api_type"
);

--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "locations" (
    "id" serial PRIMARY KEY NOT NULL,
    "coordinate" geometry(POINT),
    "api" "location_api_type"
);

nvm, I am stupid... the PostGIS extension failed to install on our database... It works now...

Julius Möhring•10mo ago

I have the same problem and was wondering, if there is a way to prevent drizzle-kit to wrap the column type with the quotes?

Paister•10mo ago

if i use this type in a normal select query, its working. But if I use a the query builder, the fromDriver value is already the correct type so i have to adjust the fromDriver cb :

 fromDriver: (value) => {
            const point =
                typeof value === 'string'
                    ? (fromDriver(value) as Point)
                    : (value as Point)
            const [lng, lat] = point.coordinates
            return { lat, lng }
        },

Gaming

Programming

type "GEOMETRY(POINT)" does not exist