Search code examples
postgresqlrustrust-axumsea-orm

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).

Screenshot highlighting the Cast(...) function

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?

Solution

  • 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.