sea-orm for Postgres generating cast syntax for enum value and it doesn't work
I am trying to insert a record into Postgresql using sea-orm. I have enabled sqlx-logging to see why the insertion is crashing. Here is what I find.
[2024-08-03T07:33:35Z DEBUG sea_orm::driver::sqlx_postgres] INSERT INTO "customers" ("email", "status", "linked_acc_name", "acc_manager_name", "acc_manager_email", "acc_manager_contact_no", "created_by", "created_on", "updated_by", "updated_on") VALUES ('[email protected]', CAST('guest' AS CustomerStatus), NULL, NULL, NULL, NULL, 'a48c7582-3cac-4249-924d-5fe93b8f345a', '2024-08-03 07:33:35', 'a48c7582-3cac-4249-924d-5fe93b8f345a', '2024-08-03 07:33:35') RETURNING "id", "email", CAST("status" AS text), "linked_acc_name", "acc_manager_name", "acc_manager_email", "acc_manager_contact_no", "created_by", "created_on", "updated_by", "updated_on"
[2024-08-03T07:33:35Z INFO sqlx::query] summary="INSERT INTO \"customers\" (\"email\", …" db.statement="\n\nINSERT INTO\n \"customers\" (\n \"email\",\n \"status\",\n \"linked_acc_name\",\n \"acc_manager_name\",\n \"acc_manager_email\",\n \"acc_manager_contact_no\",\n \"created_by\",\n \"created_on\",\n \"updated_by\",\n \"updated_on\"\n )\nVALUES\n (\n $1,\n CAST($2 AS CustomerStatus),\n $3,\n $4,\n $5,\n $6,\n $7,\n $8,\n $9,\n $10\n ) RETURNING \"id\",\n \"email\",\n CAST(\"status\" AS text),\n \"linked_acc_name\",\n \"acc_manager_name\",\n \"acc_manager_email\",\n \"acc_manager_contact_no\",\n \"created_by\",\n \"created_on\",\n \"updated_by\",\n \"updated_on\"\n" rows_affected=0 rows_returned=0 elapsed=594.438µs elapsed_secs=0.000594438
thread 'tokio-runtime-worker' panicked at src/handlers/users/signup.rs:56:47:
called `Result::unwrap()` on an `Err` value: Query(SqlxError(Database(PgDatabaseError { severity: Error, code: "42704", message: "type \"customerstatus\" does not exist", detail: None, hint: None, position: Some(Original(210)), where: None, schema: None, table: None, column: None, data_type: None, constraint: None, file: Some("parse_type.c"), line: Some(270), routine: Some("typenameType") })))
stack backtrace:
The insertion statement contains a Cast clause, which is causing it to fail, when it doesn't find the enum type (actually, the enum type very much exists).
To cross check, I have run a query to find all enum types available. Here is the result.
SELECT n.nspname AS schema, t.typname AS enum_name
FROM pg_type t
JOIN pg_enum e ON t.oid = e.enumtypid
JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
GROUP BY schema, enum_name;
schema | enum_name
--------+---------------------
public | WorkflowStatus
public | DeploymentJobStatus
public | CustomerStatus
public | DatasetPurpose
public | TrainingStatus
public | DeploymentStatus
public | TrainingParamType
(7 rows)
The insertion seems to work, when we don't have the 'Cast(...)' function included. What is going wrong here, and why does Sea-orm have to generate code that doesn't work?
Type names in PostgreSQL are case sensitive, but if you don't quote them then they get converted to lower-case. That's what is happening here.
Note in the generated query: CAST('guest' AS CustomerStatus)
However, in the error message the type is lower case: type "customerstatus" does not exist
It's hard to say exactly where the error is since you haven't shown us the Rust code used to build the query. It might be a bug in sea-orm where in this case the type identifier is not quoted, even though sea-orm seems to be quoting other things (table and column names).
Having said that, it's considered best practice to use snake case for all identifiers in PostgreSQL (for example, customer_status). In particular, using uppercase letters in identifier names is strongly discouraged because it can cause this exact problem. Consider renaming your types.