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?

[uxduck]

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

[ronnyandre]

what about postgis?

@stunaz Did you manage to solve this?

[cstoneham]

is there any way to have this work with introspection? when i pull my db would be great to have citext automatically generated so that i don't have to manually import into my schema file

[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 ::(

[dev-a-loper]

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

[dev-a-loper]

this changed in the drizzle kit 0.22

[daveycodez]

ALTER TABLE "todos" ALTER COLUMN "task" SET DATA TYPE "undefined"."citext";--> statement-breakpoint

Tried to alter a column to citext, and now I'm getting this in my migration..

import { type InferSelectModel, sql } from "drizzle-orm"
import { authenticatedRole, authUid, crudPolicy } from "drizzle-orm/neon"
import {
    boolean,
    customType,
    jsonb,
    pgTable,
    serial,
    text,
    timestamp,
    uuid
} from "drizzle-orm/pg-core"

// Enable RLS on all auth tables and re-export them
export const users = authSchema.users.enableRLS()
export const sessions = authSchema.sessions.enableRLS()
export const accounts = authSchema.accounts.enableRLS()
export const verifications = authSchema.verifications.enableRLS()
export const jwkss = authSchema.jwkss.enableRLS()

const citext = customType<{ data: string }>({
    dataType() {
        return "citext"
    }
})

export const todos = pgTable(
    "todos",
    {
        id: uuid().primaryKey().defaultRandom(),
        userId: text()
            .notNull()
            .default(sql`(auth.user_id())`)
            .references(() => profiles.id, { onDelete: "cascade" }),
        projectId: uuid().references(() => projects.id, {
            onDelete: "cascade"
        }),
        task: citext().notNull().default(""),
        isComplete: boolean().notNull().default(false),
        createdAt: timestamp({ withTimezone: true }).defaultNow().notNull(),
        updatedAt: timestamp({ withTimezone: true })
            .defaultNow()
            .$onUpdate(() => new Date())
            .notNull(),
        _deleted: boolean().notNull().default(false)
    },
    (table) => [
        crudPolicy({
            role: authenticatedRole,
            read: authUid(table.userId),
            modify: authUid(table.userId)
        })
    ]
)

[kinthaiofficial]

Drizzle supports Postgres extensions well — the approach depends on whether you need the extension at the type level (DDL) or query level.

DDL: declaring extension usage in schema:

// schema.ts
import { pgSchema, vector, point } from 'drizzle-orm/pg-core';

// pgvector extension
export const embeddings = pgTable('embeddings', {
  id: serial('id').primaryKey(),
  content: text('content').notNull(),
  embedding: vector('embedding', { dimensions: 1536 }),  // pgvector type
  createdAt: timestamp('created_at').defaultNow(),
});

// PostGIS point type
export const locations = pgTable('locations', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  coords: point('coords', { mode: 'xy' }),  // PostGIS
});

Query: using extension functions:

import { sql } from 'drizzle-orm';

// pgvector similarity search (cosine distance)
const results = await db.select({
    id: embeddings.id,
    content: embeddings.content,
    similarity: sql<number>`1 - (${embeddings.embedding} <=> ${queryVector}::vector)`,
  })
  .from(embeddings)
  .orderBy(sql`${embeddings.embedding} <=> ${queryVector}::vector`)
  .limit(10);

// Full text search with ts_vector/ts_rank
const posts = await db.select()
  .from(articles)
  .where(sql`to_tsvector('english', ${articles.content}) @@ plainto_tsquery('english', ${searchQuery})`)
  .orderBy(sql`ts_rank(to_tsvector('english', ${articles.content}), plainto_tsquery('english', ${searchQuery})) DESC`);

Migration: enable the extension:

-- migrations/0001_add_pgvector.sql
CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS postgis;

Drizzle's custom type helpers allow you to add any Postgres type not natively supported. The raw sql tag covers any function call the extension provides.