Actually, when creating an enum column in a table using a postres connector, a varchar with checks is created. For example:
with self.schema.create("users") as blueprint:
blueprint.enum("status", ["active", "inactive"]).default("active")
returns
CREATE TABLE "users" ("status" VARCHAR(255) CHECK(status IN ('active', 'inactive')) NOT NULL DEFAULT 'active')
Postgres doesn't have an ENUM type, but there is way to create ENUM types as can been seen here
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
Manipulating these enum require their own separate ddl query, and, as the PostgresPlatform is only responsible for sql translation and doesn't have any way to look over the actual table, it requires (without significal changes):
A "normized" name, such as {table}_{column}_enum that allows the enums to be retrived without being kept in memory
Tracking of these enums in order to, for example, delete the enum when the associated colmun or table is dropped. This require things like renaming the enum if the column is renamed.
Keep in mind that this solution is a proposal, and can of couse be implemented in other ways, but in any case, it's a complex addition that requires a good understanding of both masonite-orm and PostgreSQL.
What do we currently have to do now?
Varchar with checks.
Additional context
Here's a quick idea of what the solution I proposed could look like. I don't have neither the time or the experience to work on a clean PR, so I'm leaving this here:
Index: src/masoniteorm/schema/platforms/PostgresPlatform.py
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/src/masoniteorm/schema/platforms/PostgresPlatform.py b/src/masoniteorm/schema/platforms/PostgresPlatform.py
--- a/src/masoniteorm/schema/platforms/PostgresPlatform.py (revision ec31a17aea4ac08cc1cb7a23f05aaab24e35b27b)
+++ b/src/masoniteorm/schema/platforms/PostgresPlatform.py (date 1718889033107)
@@ -143,20 +143,20 @@
default = ""
constraint = ""
- column_constraint = ""
if column.primary:
constraint = "PRIMARY KEY"
if column.column_type == "enum":
values = ", ".join(f"'{x}'" for x in column.values)
- column_constraint = f" CHECK({column.name} IN ({values}))"
+ sql.append(
+ f"CREATE TYPE {self.wrap_column(name + '_enum')} AS ENUM ({values})"
+ )
sql.append(
self.columnize_string()
.format(
name=self.wrap_column(column.name),
- data_type=self.type_map.get(column.column_type, ""),
- column_constraint=column_constraint,
+ data_type=self.type_map.get(column.column_type, "") if column.column_type != "enum" else f"{name}_enum",
length=length,
constraint=constraint,
nullable=self.premapped_nulls.get(column.is_null) or "",
@@ -194,11 +194,17 @@
else:
default = ""
+ if column.column_type == "enum":
+ values = ", ".join(f"'{x}'" for x in column.values)
+ sql.append(
+ f"CREATE TYPE {self.wrap_column(name + '_enum')} AS ENUM ({values})"
+ )
+
add_columns.append(
self.add_column_string()
.format(
name=self.wrap_column(column.name),
- data_type=self.type_map.get(column.column_type, ""),
+ data_type=self.type_map.get(column.column_type, "") if column.column_type != "enum" else f"{name}_enum",
length=length,
constraint="PRIMARY KEY" if column.primary else "",
nullable="NULL" if column.is_null else "NOT NULL",
@@ -221,6 +227,9 @@
renamed_sql = []
for name, column in table.get_renamed_columns().items():
+ # TODO: rename the enum type if exists to ensure correct tracking of the enum in the future
+ # see https://stackoverflow.com/questions/7624919/check-if-a-user-defined-type-already-exists-in-postgresql
+
if column.length:
length = self.create_column_length(column.column_type).format(
length=column.length
@@ -247,6 +256,9 @@
dropped_sql = []
for name in table.get_dropped_columns():
+ sql.append(
+ f"DROP TYPE IF EXISTS {self.wrap_column(name + '_enum')}")
+
dropped_sql.append(
self.drop_column_string()
.format(name=self.wrap_column(name))
@@ -263,11 +275,20 @@
changed_sql = []
for name, column in table.changed_columns.items():
+ sql.append(
+ f"DROP TYPE IF EXISTS {self.wrap_column(name + '_enum')}")
+
+ if column.column_type == "enum":
+ values = ", ".join(f"'{x}'" for x in column.values)
+ sql.append(
+ f"CREATE TYPE {self.wrap_column(name + '_enum')} AS ENUM ({values})"
+ )
+
changed_sql.append(
self.modify_column_string()
.format(
name=self.wrap_column(name),
- data_type=self.type_map.get(column.column_type),
+ data_type=self.type_map.get(column.column_type) if column.column_type != "enum" else f"{name}_enum",
nullable="NULL" if column.is_null else "NOT NULL",
length="(" + str(column.length) + ")"
if column.column_type not in self.types_without_lengths
@@ -392,7 +413,7 @@
return "RENAME COLUMN {old} TO {to}"
def columnize_string(self):
- return "{name} {data_type}{length}{column_constraint} {nullable}{default} {constraint}"
+ return "{name} {data_type}{length} {nullable}{default} {constraint}"
def constraintize(self, constraints, table):
sql = []
@@ -452,9 +473,11 @@
return f"ALTER TABLE {self.wrap_table(current_name)} RENAME TO {self.wrap_table(new_name)}"
def compile_drop_table_if_exists(self, table):
+ # TODO: for each column (or by wildcard), drop the enum type if exists
return f"DROP TABLE IF EXISTS {self.wrap_table(table)}"
def compile_drop_table(self, table):
+ # TODO: for each column (or by wildcard), drop the enum type if exists
return f"DROP TABLE {self.wrap_table(table)}"
def compile_column_exists(self, table, column):
Please note that i'm only using the colmun name to generate the enum names in this example, because the columnize method doesn't have a reference to the table (so it'll probably need to be changed too as it could lead to duplicate names).
Describe the feature as you'd like to see it
Actually, when creating an enum column in a table using a postres connector, a varchar with checks is created. For example:
returns
Postgres doesn't have an ENUM type, but there is way to create ENUM types as can been seen here
Manipulating these enum require their own separate ddl query, and, as the
PostgresPlatformis only responsible for sql translation and doesn't have any way to look over the actual table, it requires (without significal changes):{table}_{column}_enumthat allows the enums to be retrived without being kept in memoryKeep in mind that this solution is a proposal, and can of couse be implemented in other ways, but in any case, it's a complex addition that requires a good understanding of both masonite-orm and PostgreSQL.
What do we currently have to do now?
Varchar with checks.
Additional context
Here's a quick idea of what the solution I proposed could look like. I don't have neither the time or the experience to work on a clean PR, so I'm leaving this here:
Please note that i'm only using the colmun name to generate the enum names in this example, because the
columnizemethod doesn't have a reference to the table (so it'll probably need to be changed too as it could lead to duplicate names).Also, here's a link about a discussion on this issue that occured in the Discord Server.