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

help
1

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: Type in sqlx - Rust

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

2

This issue might help you to find a solution.

When compatibility issues such as this one arise in SQLX, I always opt to manually implement FromRow for my Rust types.

Btw, if you crosspost it's always adviced to explicitly state so, so that people avoid doing double work when trying to help you.

1 Like
3

Thankyou for pointing out the crosspost. I added notes to both posts pointing to each other.

MySQL: rust enum not compatible with SQL ENUM · Issue #1241 · launchbadge/sqlx · GitHub does not contain a solution. I scanned it before posting. Also it is very old from 2021. Sqlx has surely developed since then. Trying to manually implement the desired behavior leads to a similar error message. I will post my try at a manual implementation later.

4

I found a workaround noted here:

I believe this is a bug. Details noted on github.