[FEATURE]: Postgres extensions - ltree & geospatial types support

[trompx]

Hello, I have been waiting a long time for ltree support in prisma but want to make the switch to drizzle (mainly for performance). I found nothing in the docs and wanting to know if it was already possible to use postgres ltree (via unknown column type that I saw mentioned in this issue https://github.com/drizzle-team/drizzle-orm/issues/230 or raw queries)? If not what are your plans for postgres extensions support and ETA? For more details, here is the main "ltree support" prisma issue https://github.com/prisma/prisma/issues/2568. Thanks

[LeonAlvarez]

I'm currently working with ltee only had to setup a custom type

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

const ltree = customType<{ data: string }>({
  dataType() {
    return 'ltree';
  },
});

export default ltree;

Then simply import and use it in your schema

path: ltree('path').notNull(),

[AvidDabbler]

This is currently an issue for me where I cannot use drizzle migrations due to finding geography_columns, geometry_columns, and spatial_ref_sys "tables"

[trburger]

Same issue here with PostGIS table and views. Would be nice to see an object (table, view) exclude configuration option that would work similar to the existing tablesFilter. It could support basic wild matching like tablesFilter plus allow for an explicit list of objects to exclude.

https://orm.drizzle.team/kit-docs/conf#multi-project-schema

[mauriciabad]

Did anyone find a workarround for it?

When I try to run drizzle-kit push:pg I get this warning:

 Warning  Found data-loss statements:
· You're about to delete geometry_columns table with 3 items
· You're about to delete spatial_ref_sys table with 8500 items

And if I choose to push anyways it thorws this error:

error: "geography_columns" is not a table
    at /Users/maui/Projects/begursecret/node_modules/.pnpm/drizzle-kit@0.20.14/node_modules/drizzle-kit/bin.cjs:24462:21
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async PgPostgres.query (/Users/maui/Projects/begursecret/node_modules/.pnpm/drizzle-kit@0.20.14/node_modules/drizzle-kit/bin.cjs:25423:21)
    at async Command.<anonymous> (/Users/maui/Projects/begursecret/node_modules/.pnpm/drizzle-kit@0.20.14/node_modules/drizzle-kit/bin.cjs:63261:9) {
  length: 137,
  severity: 'ERROR',
  code: '42809',
  detail: undefined,
  hint: 'Use DROP VIEW to remove a view.',
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'tablecmds.c',
  line: '1332',
  routine: 'DropErrorMsgWrongType'
}

It started happening after I manually added this to the top of the migration file (drizzle/0000_last_xorn.sql):

CREATE EXTENSION IF NOT EXISTS postgis;

Maybe this is not the proper way of "enabling" PostGIS... But I can't find any other.

[mauriciabad]

Related posts in discord:

• https://discord.com/channels/1043890932593987624/1218672045425033397
• https://discord.com/channels/1043890932593987624/1196524721152458862

[AvidDabbler]

I ended up just using prisma for schema management and drizzle for orm. It's not ideal but it's better than just prisma

[LeonAlvarez]

@AvidDabbler what the exact issue you had on schemas?

[Dakuan]

i've found myself here looking for a next gen ORM that can handle gis datatypes. Prisma can't, seems Drizzle can't either. This is unfortunate because Objection.js is being sunsetted, leaving node without a ORM that can handle GIS. while objection (and knex) still work, they don't play nicely with nextjs and other 'new world' environments.

[dartmoordunbar]

Agreed, this is where I am.

[RDeluxe]

i've found myself here looking for a next gen ORM that can handle gis datatypes. Prisma can't, seems Drizzle can't either. This is unfortunate because Objection.js is being sunsetted, leaving node without a ORM that can handle GIS. while objection (and knex) still work, they don't play nicely with nextjs and other 'new world' environments.

Mikro ORM does : https://mikro-orm.io/docs/custom-types

[Dakuan]

@RDeluxe but doesn't play nicely with nextjs https://github.com/mikro-orm/mikro-orm/discussions/3907

[oristian]

My current workflow for using Drizzle with PostGIS -

1. drizzle-kit introspect:pg
2. look through all the todos in the generated schema that reference "unknown" // Point, etc
3. comment out those lines
4. add relations which are not inferred from the schema
5. change schema, add tables, do local development
6. try to push - "oops - you're going to delete these Point columns!"
7. decide not to push
8. issue DDL commands to my database directly to add tables and columns
9. save all the schema and relations changes I've made to the clipboard
10. drizzle-kit introspect:pg
11. look through all the todos in the generated schema that reference "unknown" // Point, etc
12. comment out those lines
13. paste back in my relations from the clipboard
14. find gratitude in the ORM capabilites of Drizzle and check these open issues every so often for updates

With so many applications relying on PostGIS and proximity features, a simple "ignore those columns" feature would allow us all to enjoy the other benefits of the Drizzle/Kit lifecycle tools