Enums

[AleksandrSherman]

In the recent release of [email protected], we've introduced extended support for handling PostgreSQL enums.

Key Updates:

1. Adding values to enums in a specific order (before or after)
2. Dropping enum types
3. Dropping values from enums
4. Renaming enums
5. Changing enum type schemas

Let's dive into each of these features! 👀

1. Adding Values Before or After in an Enum

This is a critical update for users who actively use enums.

As PostgreSQL documentation says:

Enumerated (enum) types are data types that comprise a static, ordered set of values.

This means that the order of values is significant, and now Drizzle-kit fully supports managing the order within enums 🎉

For example, if you already have an enum in your database and need to insert a new value:

// Original enum
const myEnum = pgEnum("my_enum", ["1", "2", "3"]);

// Modified enum
const myEnum = pgEnum("my_enum", ["1", "2", "4", "3"]);

Previously, Drizzle-kit would ignore the order and generate the SQL statement as:

ALTER TYPE "public"."my_enum" ADD VALUE "4";

This could lead to potential issues when adding values in the future.

Now, Drizzle-kit respects the order of enum values and generates the correct SQL statement:

ALTER TYPE "public"."my_enum" ADD VALUE '4' BEFORE '3';

2. Dropping Enum Types

Previously, Drizzle-kit did not generate SQL for dropping enum types, requiring users to manually run the statement. Now, Drizzle-kit automates this process.

Example of SQL statement:

DROP TYPE "my_enum";

💡Note: If the enum is used by any column, the database will throw an error

3. Removing a Value from an Enum

Imagine you are designing a database for an application that tracks user statuses. You start by defining an enum type user_status to standardize possible states for each user. Here’s how initial schema looks like:

CREATE TYPE "public"."user_status" AS ENUM ('active', 'closed', 'banned', 'registered');

CREATE TABLE "public"."users" (
    "id" SERIAL PRIMARY KEY NOT NULL,
    "name" VARCHAR(255) NOT NULL,
    "status" "public"."user_status" NOT NULL
);

Visualization of users table

Column	Data Type	Constraints
id	SERIAL	Primary Key, NOT NULL
name	VARCHAR(255)	NOT NULL
status	ENUM('active', 'closed', 'banned', 'registered')	NOT NULL

Over time, you decide that the registered status is no longer necessary and should be removed from the list of possible statuses for users.

- ['active', 'closed', 'banned', 'registered']
+ ['active', 'closed', 'banned']

After removing registered, the updated enum values should only contain:

['active', 'closed', 'banned']

However, removing a value from a PostgreSQL enum is tricky since PostgreSQL does not support this directly. Due to this limitation, Drizzle-kit follows a workaround strategy:

1. Alter all columns from enum to type text.
2. Drop the existing enum.
3. Create a new enum with the updated set of values.
4. Alter the columns back to the enum type.

Following SQL statements will be generated:

ALTER TABLE "public"."users" ALTER COLUMN "status" SET DATA TYPE text;

DROP TYPE "public"."user_status";

CREATE TYPE "public"."user_status" AS ENUM ('active', 'closed', 'banned');

ALTER TABLE "public"."users" ALTER COLUMN "status" SET DATA TYPE "public"."user_status" USING "status"::"public"."user_status";

Result schema visualization

Column	Data Type	Constraints
id	SERIAL	Primary Key, NOT NULL
name	VARCHAR(255)	NOT NULL
status	ENUM('active', 'closed', 'banned')	NOT NULL

4. Renaming Enums

Renaming enums is now fully supported:

// Original enum
const myEnum = pgEnum("my_enum", ["1", "2", "3"]);

// Renamed enum
const myEnum = pgEnum("new_my_enum", ["1", "2", "3"]);

This will generate the following SQL statement:

ALTER TYPE "public"."my_enum" RENAME TO "new_my_enum";

5. Changing Enum Type Schema

Migrating enums across schemas is a breeze with Drizzle-kit:

// Original enum
const myEnum = pgEnum("my_enum", ["1", "2", "3"]);

// Migrated enum to a new schema
const myEnum = newSchema.enum("my_enum", ["1", "2", "3"]);

This will generate the following SQL:

ALTER TYPE "public"."my_enum" SET SCHEMA "my_schema";

With these update, Drizzle-kit makes managing PostgreSQL enums more powerful and flexible

[kravetsone]

Great work! Thanks!