Trouble getting #[derive(sqlx::Type)] to work with a rust enum using MySQL

[LittleEntity]

I have found these related issues/pull requests

#1241

Here I am not using a MySQL enum but a VARCHAR field

Description

The sqlx documentation states:

Enumerations may be defined in Rust and can match SQL by integer discriminant or variant name.

With #[repr(_)] the integer representation is used when converting from/to SQL and expects that SQL type (e.g., INT). Without, the names of the variants are used instead and expects a textual SQL type (e.g., VARCHAR, TEXT).

source: https://docs.rs/sqlx/latest/sqlx/trait.Type.html#enumeration

When I implement this in the example below a runtime error is thrown:

thread 'omp_db::test_sqlx_types::test_sqlx_locale' (84932) 
panicked at src/omp_db.rs:403:62:
fetched locale: ColumnDecode { 
  index: "0",
  source: "mismatched types; 
  Rust type `oai_to_alma::omp_db::test_sqlx_types::Locale` (as SQL type `ENUM`) 
  is not compatible with SQL type `VARCHAR`"
}

What is wrong here?

#[cfg(test)]
mod test_sqlx_types {
    use std::sync::LazyLock;

    use sqlx::{MySql, Pool, mysql::MySqlPoolOptions};

    use crate::config::Config;

    pub static DB: LazyLock<Pool<MySql>> = LazyLock::new(|| {
        async_std::task::block_on(async {
            let url = format!(
                "mysql://{}:{}@{}:{}/{}",
                // fill in your values when testing
                "db.user",
                "db.password",
                "db.host",
                "db.port",
                "db.database"
            );
            MySqlPoolOptions::new()
                .max_connections(5)
                .connect(&url)
                .await
                .expect("successfully connected to MySQL OMP  database")
        })
    });

    #[derive(Debug, sqlx::Type)]
    #[allow(non_camel_case_types)]
    pub enum Locale {
        de_DE,
        en_US,
    }

    #[test]
    fn test_sqlx_locale() {
        let query = sqlx::query_scalar(r#"select 'en_US'"#);
        let locale: Locale = async_std::task::block_on(query.fetch_one(&*DB))
            .expect("fetched locale");
        dbg!(locale);
    }
}

Note: I posted the same issue here: https://stackoverflow.com/questions/79895959/trouble-getting-sqlx-derivesqlxtype-to-work-with-a-rust-enum-using-mysql

Reproduction steps

use the code above and a mysql instance to replicate this.

SQLx version

0.8.6

Enabled SQLx features

"mysql", "runtime-async-std"

Database server and version

mysql Ver 8.0.45-0ubuntu0.24.04.1 for Linux on x86_64 ((Ubuntu))

Operating system

Ubuntu 24

Rust version

rustc 1.91.0 (f8297e351 2025-10-28)

[LittleEntity]

so, this is hacky but works:

#[cfg(test)]
mod test_sqlx_types_2 {
    use std::{any::Any, ops::Deref, sync::LazyLock};

    use sqlx::{MySql, Pool, TypeInfo, mysql::{MySqlPoolOptions, MySqlTypeInfo}};

    use crate::config::Config;

    pub static DB: LazyLock<Pool<MySql>> = LazyLock::new(|| {
        async_std::task::block_on(async {
            let config = Config::read();
            let url = format!(
                "mysql://{}:{}@{}:{}/{}",
                config.omp_db.user,
                config.omp_db.password,
                config.omp_db.host,
                config.omp_db.port,
                config.omp_db.database
            );
            MySqlPoolOptions::new()
                .max_connections(5)
                .connect(&url)
                .await
                .expect("successfully connected to MySQL OMP  database")
        })
    });

    #[derive(Debug, sqlx::Decode, sqlx::Encode)]
    #[allow(non_camel_case_types)]
    pub enum Locale {
        de_DE,
        en_US,
    }

    impl sqlx::Type<MySql> for Locale {
        fn type_info() -> <MySql as sqlx::Database>::TypeInfo {
            <String as sqlx::Type<MySql>>::type_info()
        }

        fn compatible(ty: &<MySql as sqlx::Database>::TypeInfo) -> bool {
            dbg!(ty);
            dbg!(Self::type_info());
            // Self::type_info() == *ty
            true
        }
    } 

    #[test]
    fn test_sqlx_locale() {
        let query = sqlx::query_scalar(r#"select 'en_US'"#);
        let locale: Locale =
            async_std::task::block_on(query.fetch_one(&*DB)).expect("fetched locale");
        dbg!(locale);
    }
}

manually implemented compatible to always return true. That is the hack.

I found where it breaks: sqlx-mysql-0.8.6/src/type_info.rs

impl PartialEq<MySqlTypeInfo> for MySqlTypeInfo {
    fn eq(&self, other: &MySqlTypeInfo) -> bool {
        ...
        match self.r#type {
            ...
            // for string types, check that our charset matches
            ColumnType::VarChar
            | ColumnType::Blob
            | ColumnType::TinyBlob
            | ColumnType::MediumBlob
            | ColumnType::LongBlob
            | ColumnType::String
            | ColumnType::VarString
            | ColumnType::Enum => {
                return self.flags == other.flags;
            }
            _ => {}
        }

        true
    }
}

this snippet prints the difference:

        fn compatible(ty: &<MySql as sqlx::Database>::TypeInfo) -> bool {
            dbg!(ty);
            dbg!(Self::type_info());
            // Self::type_info() == *ty
            true
        }

output:

[src/omp_db.rs:447:13] ty = MySqlTypeInfo {
    type: VarString,
    flags: ColumnFlags(
        NOT_NULL,
    ),
    max_size: Some(
        20,
    ),
}
[src/omp_db.rs:448:13] Self::type_info() = MySqlTypeInfo {
    type: VarString,
    flags: ColumnFlags(
        0x0,
    ),
    max_size: None,
}

We observe:

• the MySqlTypeInfo correctly states the table field from the database is NOT_NULL
• MySqlTypeInfo created by <String as sqlx::Type<MySql>>::type_info() has 0x0 as ColumnFlags

These flags do not match and therefore compatible(...) returns false.

• A fix would be to let us override the default implementation by examining the MySqlTypeInfo internals. Currently the MySqlTypeInfo internals are crate private so we cannot check them.
• Another fix would be <String as sqlx::Type<MySql>>::type_info() should have its ColumnFlags set to NOT_NULL because a String (and &str) in Rust cannot be None. Instead only <Option<String> as sqlx::Type<MySql>>::type_info() should ommit the NOT_NULL, because that actually can be None.