PostgreSQL ENUM Types in Drizzle ORM

ENUM Types in Drizzle

PostgreSQL supports custom ENUM types that restrict a column to a predefined set of values. Drizzle provides the pgEnum() builder for this purpose, though the SQL converter maps inline ENUM types to text() since the enum values cannot always be extracted from a CREATE TABLE statement alone.

PostgreSQL ENUM in SQL

CREATE TYPE status AS ENUM ('active', 'inactive', 'suspended');

CREATE TABLE accounts (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  status status NOT NULL DEFAULT 'active'
);

Drizzle with pgEnum

Drizzle's pgEnum() is defined outside the table:

import { pgEnum, pgTable, serial, varchar } from "drizzle-orm/pg-core";

export const statusEnum = pgEnum("status", ["active", "inactive", "suspended"]);

export const accounts = pgTable("accounts", {
  id: serial("id").primaryKey(),
  email: varchar("email", { length: 255 }).notNull(),
  status: statusEnum("status").notNull().default("active"),
});

Converter Behavior

The SQL converter maps columns with ENUM type to text() because:

1. CREATE TYPE is separate: The CREATE TYPE ... AS ENUM statement is separate from CREATE TABLE, and the converter focuses on CREATE TABLE parsing.
2. Inline ENUM syntax: MySQL's inline ENUM('a', 'b', 'c') syntax would need special parsing to extract the values.

After conversion, you should manually replace text() columns with pgEnum() where appropriate.

MySQL ENUM Alternative

MySQL supports inline ENUM in column definitions:

CREATE TABLE accounts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  role ENUM('admin', 'user', 'moderator') NOT NULL
);

Drizzle's mysql-core provides mysqlEnum():

import { mysqlEnum, mysqlTable, serial } from "drizzle-orm/mysql-core";

export const accounts = mysqlTable("accounts", {
  id: serial("id").primaryKey(),
  role: mysqlEnum("role", ["admin", "user", "moderator"]).notNull(),
});

Text with Check Constraint

For SQLite (which lacks native ENUM), use text() with a TypeScript union type for type safety:

export const accounts = sqliteTable("accounts", {
  role: text("role", { enum: ["admin", "user", "moderator"] }).notNull(),
});