How do I define a custom type that will be used in the database first approach?

[finleyjb]

I'm storing oklch colors in a database, so I defined a composite type:

CREATE TYPE oklch AS (
    l numeric,
    c numeric,
    h smallint,
    a numeric
);

I defined a custom data type that can handle this:

export const oklchColumn = customType<{
  data: Oklch;
  driverData: string;
  configRequired: false;
}>({
  dataType(_) {
    return 'oklch';
  },
  fromDriver(value: string): Oklch {
    const unparened = value.slice(1, -1);

    const [l, c, h, a] = unparened.split(',');

    return {
      mode: 'oklch',
      l: Number.parseFloat(l!),
      c: Number.parseFloat(c!),
      h: Number.parseInt(h!, 10),
      alpha: Number.parseFloat(a!),
    };
  },
  toDriver({ l, c, h, alpha }: Oklch) {
    assert(l <= 1, `l: ${l} is invalid`);
    assert(c <= 1, `c: ${c} is invalid`);
    assert(h <= 360, `h: ${h} is invalid`);
    assert(alpha <= 1, `a: ${alpha} is invalid`);
    return `ROW(${l.toFixed(fixedDigits)}, ${c.toFixed(fixedDigits)}, ${h.toFixed(0)}, ${alpha.toFixed(fixedDigits)})`;
  },
});

And I can use it in a schema like this:

export const themes = pgTable("themes", {
  id: uuid().default(sql`uuidv7()`).primaryKey(),
  name: varchar({ length: 20 }),
  primaryColor: oklchColumn("primary_color").notNull(),
  bgColor: oklchColumn("bg_color").notNull(),
  textColor: oklchColumn("text_color").notNull(),
});

But drizzle-kit pull clobbers these definitions every time. Is there a way to register this custom type so that drizzle-kit pull won't do that?

[beenish-majeed]

As far as I can tell, there is currently no way to register oklchColumn so that drizzle-kit pull recreates it automatically.

customType() tells Drizzle ORM how to map a TypeScript value to a SQL type and how to convert it to and from the driver. drizzle-kit pull works in the opposite direction: it introspects the PostgreSQL catalog and generates a new TypeScript schema from what it finds. It does not use the existing builders in your schema as a lookup table for database type names.

That is why a hand-written definition such as:

primaryColor: oklchColumn("primary_color").notNull()

cannot currently be preserved by pull; the generated schema is reconstructed from the database metadata and unsupported/custom PostgreSQL types fall back to generated representations rather than being mapped back to your custom builder.

Options

1. Code-first

Treat schema.ts as the source of truth and use drizzle-kit generate + drizzle-kit migrate.

Because oklch is a PostgreSQL composite type, customType() does not create the PostgreSQL type itself. Create it in a custom migration:

drizzle-kit generate --custom --name=create-oklch-type

Then put the DDL in the generated migration:

CREATE TYPE oklch AS (
    l numeric,
    c numeric,
    h smallint,
    a numeric
);

That migration has to run before the migration that creates tables containing oklch columns. Drizzle documents generate --custom specifically for DDL that Kit does not generate itself.

For an existing database where the type already exists, don't blindly apply the migration again; the migration should be treated as part of your database's existing migration history.

2. Keep using pull

Keep the generated output separate from the hand-written schema, for example:

export default defineConfig({
    dialect: "postgresql",
    out: "./drizzle/introspected",
    // ...
});

pull will then be free to overwrite the generated schema, while your oklchColumn definition lives elsewhere. You can add a small post-processing/code-generation step that replaces the generated representation of oklch columns with oklchColumn(...) and adds the appropriate import. The exact replacement should be based on what your installed version of drizzle-kit actually emits for the unknown type.

3. Avoid the composite type

If the PostgreSQL composite type is not important to the database API itself, a simpler representation may be easier to maintain.

For example, you could use four normal columns, or store the value as jsonb and use Drizzle's TypeScript-only $type<Oklch>() to keep the application type:

color: jsonb("color").$type<Oklch>()

The latter is useful for application typing, but $type() itself is not something drizzle-kit pull can reconstruct, because it does not change the underlying PostgreSQL type. So this is a good code-first alternative, not a solution for preserving the annotation through pull.

What's missing

The real missing feature is a mapping between a PostgreSQL type name and a Drizzle builder during introspection.

Conceptually, something like:

export default defineConfig({
    dialect: "postgresql",

    customTypes: {
        oklch: {
            builder: "oklchColumn",
            import: "./src/db/types/oklch",
        },
    },
});

would allow pull to recognize PostgreSQL's oklch type and generate:

import { oklchColumn } from "./src/db/types/oklch";

export const themes = pgTable("themes", {
    primaryColor: oklchColumn("primary_color").notNull(),
    bgColor: oklchColumn("bg_color").notNull(),
    textColor: oklchColumn("text_color").notNull(),
});

That mapping does not appear to exist in the current Kit configuration/introspection API, so I think this is best treated as a feature request rather than a problem with the customType() definition itself.

One unrelated issue in the example is worth mentioning: fromDriver() parsing with a plain split(",") assumes the PostgreSQL composite text representation can never contain quoted commas and that none of the fields can be NULL. PostgreSQL's composite text representation has escaping/quoting rules, and a null field is represented differently. If those constraints are guaranteed for this particular type, the current parser is fine; otherwise it would be worth using a proper composite-value parser.

[ghostkillerr]

One separate issue in the example is worth fixing while you keep the handwritten schema: toDriver() returns a string containing ROW(...). A string returned there is a bound parameter value; PostgreSQL does not execute it as a row-constructor expression.

For your numeric-only composite, return PostgreSQL's composite text input, with parentheses but no ROW prefix:

return `(${l.toFixed(fixedDigits)},${c.toFixed(fixedDigits)},${h.toFixed(0)},${alpha.toFixed(fixedDigits)})`;

Alternatively, Drizzle's toDriver type also permits a SQL object, so a parameterized row expression can be represented with sql rather than an ordinary string.

Validate finite values and the intended lower bounds as well as the upper bounds before serialization. Also account for nullable composite attributes if the database permits them: .notNull() on the column does not independently make every attribute inside the composite non-null.

This addresses insert serialization, not preservation by pull; the generated-versus-handwritten schema separation described above still applies. References: Drizzle custom types and PostgreSQL composite input syntax.