Does it support postgres extensions?

[capaj]

I am most interested in CITEXT and POSTGIS. Is there a way to work with those?

[AndriiSherman]

@capaj
Not right now, but we have extensions support in backlog for Q1. We are going to add the most popular extensions at first releases and then even give a possibility for users to create their own extensions

But you still have a possibility to use those extension types even now. It won't be fully automated but will still work

Example with adding custom CITEXT type

1. You need to create custom type called citext() and use it in table schema definition. Here you can find full guide on how to add custom datatype in drizzle orm -> link

In this doc I had already used CITEXT as an example

After adding new datatype you can use it in your table schema definition. It will look like this

const tableWithCustomType = pgTable('table_with_custom_type', {
	id: integer('id').primaryKey(),
	ciname: citext('ciname')
})

2. When you run drizzle-kit generate cli command - you'll see generated sql file with used CITEXT type. Like this:

CREATE TABLE  table_with_custom_type (
	"id" integer PRIMARY KEY NOT NULL,
	"ciname" "citext"
);

All you left to do is to add CREATE EXTENSION IF NOT EXISTS citext; So your final sql file will look like this:

CREATE EXTENSION IF NOT EXISTS citext;

CREATE TABLE  table_with_custom_type (
	"id" integer PRIMARY KEY NOT NULL,
	"ciname" "citext"
);

Warning
Make sure, that you add modifications to sql file before running it first time on database

Same could be done with any other extension or datatype that is missing. But you are free to create an Issue for missed datatype or extension or even to make a contribution by create a PR with new datatype you want to see in Drizzle ORM. I had covered this topic at the end of custom-type.md

[AndriiSherman]

@capaj

We actually have prepared even better way to create custom types in postgresql and mysql. You can check those docs here

CITEXT example will look like this

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

[hyzyla]

It works!

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

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

[stunaz]

what about postgis?

[ben-xD]

Just remember to add CREATE EXTENSION IF NOT EXISTS citext; in the .sql/generated-migration files.

[ArnaudD]

The following code seems to work for the postgis geometry type. I haven't tested it extensively, my experience with drizzle is limited to 2 hours (awesome btw!).

This code is inspired by what sequelize does :

import { sql } from "drizzle-orm";
import { customType } from "drizzle-orm/pg-core";
import type { Geometry, Point } from "geojson";
import wkx from "wkx";

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;
};

export 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 };

export 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);

Usage :

export const events = pgTable("events", {
  geometry: geometry("geometry"),
  location: point("location", { srid: 4326 }),
});

type Event = InferModel<typeof events, "select">;
// type Event = {
//   geometry: Geometry | null, // https://www.jsdocs.io/package/@types/geojson#Geometry
//   location: { lat: number; lng: number } | null
// }

Unfortunatly I don't feel confident enough with postgis and drizzle to create a PR ::(

[ehsan2003]

but it's not working with drizzle kit it has a bug with those types