Json type with mysql

[Ludea]

Hi,

I need to store into a mysql table an array of enum. The only way i found to do this is to use Json type.

I do

#[derive(Debug, Serialize, Deserialize, FromSqlRow, AsExpression, PartialEq, Clone)]
#[diesel(sql_type = RepositoriesPlatformsEnum)]
pub enum Platforms {
    Win64,
    Macosx8664,
    Macosarm64,
    Linux,
}

impl ToSql<RepositoriesPlatformsEnum, diesel::mysql::Mysql> for Platforms {
    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, diesel::mysql::Mysql>) -> serialize::Result {
        match *self {
            Platforms::Win64 => out.write_all(b"win64")?,
            Platforms::Macosx8664 => out.write_all(b"macos_x86_64")?,
            Platforms::Macosarm64 => out.write_all(b"macos_arm64")?,
            Platforms::Linux => out.write_all(b"linux")?,
        }
        Ok(IsNull::No)
    }
}

impl FromSql<RepositoriesPlatformsEnum, diesel::mysql::Mysql> for Platforms {
    fn from_sql(bytes: diesel::mysql::MysqlValue) -> deserialize::Result<Self> {
        match bytes.as_bytes() {
            b"win64" => Ok(Platforms::Win64),
            b"macos_x86_64" => Ok(Platforms::Macosx8664),
            b"macos_arm64" => Ok(Platforms::Macosarm64),
            b"linux" => Ok(Platforms::Linux),
            _ => Err("Unrecognized enum variant".into()),
        }
    }
}

#[derive(Insertable)]
#[diesel(table_name = repositories)]
#[diesel(check_for_backend(diesel::mysql::Mysql))]
pub struct Repository {
    pub name: String,
    pub created_at: NaiveDateTime,
    pub platforms: Vec<Platforms>,
}

pub mod sql_types {
    #[derive(diesel::sql_types::SqlType)]
    #[diesel(mysql_type(name = "Enum"))]
    pub struct RepositoriesPlatformsEnum;
}

diesel::table! {
    use diesel::sql_types::*;
    use super::sql_types::RepositoriesPlatformsEnum;
    repositories (id) {
        id -> Integer,
        #[max_length = 255]
        name -> Varchar,
        created_at -> Timestamp,
        platforms -> Json,
    }
}

But I get

the trait bound `Vec<Platforms>: diesel::Expression` is not satisfied
  --> src/models.rs:40:10
   |
40 | #[derive(Insertable)]
   |          ^^^^^^^^^^ the trait `diesel::Expression` is not implemented for `Vec<Platforms>`, which is required by `Vec<Platforms>: AsExpression<diesel::sql_types::Json>`
   |
   = help: the following other types implement trait `diesel::Expression`:
             &'a T
             (T0, T1)
             (T0, T1, T2)
             (T0, T1, T2, T3)
             (T0, T1, T2, T3, T4)
             (T0, T1, T2, T3, T4, T5)
             (T0, T1, T2, T3, T4, T5, T6)
             (T0, T1, T2, T3, T4, T5, T6, T7)
           and 153 others
   = note: required for `Vec<Platforms>` to implement `AsExpression<diesel::sql_types::Json>`
   = note: this error originates in the derive macro `Insertable` (in Nightly builds, run with -Z macro-backtrace for more info)

error[E0277]: the trait bound `Vec<Platforms>: diesel::Expression` is not satisfied
  --> src/models.rs:40:10
   |
40 | #[derive(Insertable)]
   |          ^^^^^^^^^^ the trait `diesel::Expression` is not implemented for `Vec<Platforms>`, which is required by `&'insert Vec<Platforms>: AsExpression<diesel::sql_types::Json>`
   |
   = help: the following other types implement trait `diesel::Expression`:
             &'a T
             (T0, T1)
             (T0, T1, T2)
             (T0, T1, T2, T3)
             (T0, T1, T2, T3, T4)
             (T0, T1, T2, T3, T4, T5)
             (T0, T1, T2, T3, T4, T5, T6)
             (T0, T1, T2, T3, T4, T5, T6, T7)
           and 153 others
   = note: required for `&'insert Vec<Platforms>` to implement `diesel::Expression`
   = note: required for `&'insert Vec<Platforms>` to implement `AsExpression<diesel::sql_types::Json>`
   = note: this error originates in the derive macro `Insertable` (in Nightly builds, run with -Z macro-backtrace for more info)

error[E0277]: the trait bound `Vec<Platforms>: diesel::Expression` is not satisfied
  --> src/models.rs:46:9
   |
46 |     pub platforms: Vec<Platforms>,
   |         ^^^^^^^^^ the trait `diesel::Expression` is not implemented for `Vec<Platforms>`, which is required by `Vec<Platforms>: AsExpression<diesel::sql_types::Json>`
   |
   = help: the following other types implement trait `diesel::Expression`:
             &'a T
             (T0, T1)
             (T0, T1, T2)
             (T0, T1, T2, T3)
             (T0, T1, T2, T3, T4)
             (T0, T1, T2, T3, T4, T5)
             (T0, T1, T2, T3, T4, T5, T6)
             (T0, T1, T2, T3, T4, T5, T6, T7)
           and 153 others
   = note: required for `Vec<Platforms>` to implement `AsExpression<diesel::sql_types::Json>`

error[E0277]: the trait bound `Vec<Platforms>: diesel::Expression` is not satisfied
  --> src/models.rs:46:9
   |
46 |     pub platforms: Vec<Platforms>,
   |         ^^^^^^^^^ the trait `diesel::Expression` is not implemented for `Vec<Platforms>`, which is required by `&'insert Vec<Platforms>: AsExpression<diesel::sql_types::Json>`
   |
   = help: the following other types implement trait `diesel::Expression`:
             &'a T
             (T0, T1)
             (T0, T1, T2)
             (T0, T1, T2, T3)
             (T0, T1, T2, T3, T4)
             (T0, T1, T2, T3, T4, T5)
             (T0, T1, T2, T3, T4, T5, T6)
             (T0, T1, T2, T3, T4, T5, T6, T7)
           and 153 others
   = note: required for `&'insert Vec<Platforms>` to implement `diesel::Expression`
   = note: required for `&'insert Vec<Platforms>` to implement `AsExpression<diesel::sql_types::Json>`

error[E0277]: the trait bound `Vec<Platforms>: diesel::Expression` is not satisfied
  --> src/user.rs:73:10
   |
73 |         .values(&repo)
   |          ^^^^^^ the trait `diesel::Expression` is not implemented for `Vec<Platforms>`, which is required by `&Vec<Platforms>: AsExpression<diesel::sql_types::Json>`
   |
   = help: the following other types implement trait `diesel::Expression`:
             &'a T
             (T0, T1)
             (T0, T1, T2)
             (T0, T1, T2, T3)
             (T0, T1, T2, T3, T4)
             (T0, T1, T2, T3, T4, T5)
             (T0, T1, T2, T3, T4, T5, T6)
             (T0, T1, T2, T3, T4, T5, T6, T7)
           and 153 others
   = note: required for `&Vec<Platforms>` to implement `diesel::Expression`
   = note: required for `&Vec<Platforms>` to implement `AsExpression<diesel::sql_types::Json>`

error[E0277]: the trait bound `DefaultableColumnInsertValue<ColumnInsertValue<repositories::columns::platforms, &Vec<Platforms>>>: InsertValues<_, repositories::table>` is not satisfied
   --> src/user.rs:74:18
    |
74  |         .execute(&mut conn)
    |          ------- ^^^^^^^^^ the trait `InsertValues<_, repositories::table>` is not implemented for `DefaultableColumnInsertValue<ColumnInsertValue<repositories::columns::platforms, &Vec<Platforms>>>`, which is required by `diesel::query_builder::InsertStatement<repositories::table, ValuesClause<(DefaultableColumnInsertValue<ColumnInsertValue<repositories::columns::name, diesel::expression::bound::Bound<diesel::sql_types::Text, &std::string::String>>>, DefaultableColumnInsertValue<ColumnInsertValue<repositories::columns::created_at, diesel::expression::bound::Bound<diesel::sql_types::Timestamp, &NaiveDateTime>>>, DefaultableColumnInsertValue<ColumnInsertValue<repositories::columns::platforms, &Vec<Platforms>>>), repositories::table>>: diesel_async::methods::ExecuteDsl<_, _>`
    |          |
    |          required by a bound introduced by this call
    |
    = help: the following other types implement trait `InsertValues<DB, T>`:
              `DefaultableColumnInsertValue<ColumnInsertValue<Col, Expr>>` implements `InsertValues<DB, <Col as Column>::Table>`
              `DefaultableColumnInsertValue<ColumnInsertValue<Col, Expr>>` implements `InsertValues<Sqlite, <Col as Column>::Table>`
    = note: required for `(DefaultableColumnInsertValue<ColumnInsertValue<name, ...>>, ..., ...)` to implement `InsertValues<_, repositories::table>`
    = note: required for `ValuesClause<(DefaultableColumnInsertValue<...>, ..., ...), ...>` to implement `diesel::query_builder::QueryFragment<_>`
    = note: 1 redundant requirement hidden
    = note: required for `InsertStatement<table, ValuesClause<(..., ..., ...), ...>>` to implement `diesel::query_builder::QueryFragment<_>`
    = note: required for `InsertStatement<table, ValuesClause<(..., ..., ...), ...>>` to implement `diesel_async::methods::ExecuteDsl<_, _>`
note: required by a bound in `diesel_async::RunQueryDsl::execute`
   --> /root/.cargo/registry/src/index.crates.io-6f17d22bba15001f/diesel-async-0.5.2/src/run_query_dsl/mod.rs:224:15
    |
221 |     fn execute<'conn, 'query>(self, conn: &'conn mut Conn) -> Conn::ExecuteFuture<'conn, 'query>
    |        ------- required by a bound in this associated function
...
224 |         Self: methods::ExecuteDsl<Conn> + 'query,
    |               ^^^^^^^^^^^^^^^^^^^^^^^^^ required by this bound in `RunQueryDsl::execute`
    = note: the full name for the type has been written to '/workspaces/lucle/target/debug/deps/lucle-ddb2780e965dcc22.long-type-17171674657647691997.txt'
    = note: consider using `--verbose` to print the full type name to the console
    = note: the full name for the type has been written to '/workspaces/lucle/target/debug/deps/lucle-ddb2780e965dcc22.long-type-11159082815338074784.txt'
    = note: consider using `--verbose` to print the full type name to the console
    = note: the full name for the type has been written to '/workspaces/lucle/target/debug/deps/lucle-ddb2780e965dcc22.long-type-5968327894496715042.txt'
    = note: consider using `--verbose` to print the full type name to the console

I miss something ?

[weiznich]

This is expected behavior as you only defined how to convert a rust value of the type Platforms to a database value of the type RepositoriesPlatformsEnum. There is no conversion defined for rust values of the type Vec<Platforms> to database types of the value Json

[Ludea]

using

#[derive(Insertable)]
#[diesel(table_name = repositories)]
#[diesel(check_for_backend(diesel::mysql::Mysql))]
pub struct Repository {
    pub name: String,
    pub created_at: NaiveDateTime,
    pub platforms: Vec<Platforms>,
}

#[derive(Debug, Serialize, Deserialize, FromSqlRow, AsExpression, PartialEq, Clone)]
#[diesel(sql_type = RepositoriesPlatformsEnum)]
pub enum Platforms {
    Win64,
    Macosx8664,
    Macosarm64,
    Linux,
}

impl ToSql<RepositoriesPlatformsEnum, diesel::mysql::Mysql> for Platforms {
    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, diesel::mysql::Mysql>) -> serialize::Result {
        match *self {
            Platforms::Win64 => out.write_all(b"win64")?,
            Platforms::Macosx8664 => out.write_all(b"macos_x86_64")?,
            Platforms::Macosarm64 => out.write_all(b"macos_arm64")?,
            Platforms::Linux => out.write_all(b"linux")?,
        }
        Ok(IsNull::No)
    }
}

impl FromSql<RepositoriesPlatformsEnum, diesel::mysql::Mysql> for Platforms {
    fn from_sql(bytes: diesel::mysql::MysqlValue) -> deserialize::Result<Self> {
        match bytes.as_bytes() {
            b"win64" => Ok(Platforms::Win64),
            b"macos_x86_64" => Ok(Platforms::Macosx8664),
            b"macos_arm64" => Ok(Platforms::Macosarm64),
            b"linux" => Ok(Platforms::Linux),
            _ => Err("Unrecognized enum variant".into()),
        }
    }
}

diesel::table! {
    use diesel::sql_types::*;
   // use super::sql_types::RepositoriesPlatformsEnum;
    repositories (id) {
        id -> Integer,
        #[max_length = 255]
        name -> Varchar,
        created_at -> Timestamp,
        platforms -> Json,
    }
}

gives

the trait bound `Vec<models::Platforms>: diesel::Expression` is not satisfied
  --> src/models.rs:40:10
   |
40 | #[derive(Insertable)]
   |          ^^^^^^^^^^ the trait `diesel::Expression` is not implemented for `Vec<models::Platforms>`, which is required by `Vec<models::Platforms>: AsExpression<diesel::sql_types::Json>`
   |
   = help: the following other types implement trait `diesel::Expression`:
             &'a T
             (T0, T1)
             (T0, T1, T2)
             (T0, T1, T2, T3)
             (T0, T1, T2, T3, T4)
             (T0, T1, T2, T3, T4, T5)
             (T0, T1, T2, T3, T4, T5, T6)
             (T0, T1, T2, T3, T4, T5, T6, T7)
           and 153 others
   = note: required for `Vec<models::Platforms>` to implement `AsExpression<diesel::sql_types::Json>`
   = note: this error originates in the derive macro `Insertable` (in Nightly builds, run with -Z macro-backtrace for more info)

error[E0277]: the trait bound `Vec<models::Platforms>: diesel::Expression` is not satisfied
  --> src/models.rs:40:10
   |
40 | #[derive(Insertable)]
   |          ^^^^^^^^^^ the trait `diesel::Expression` is not implemented for `Vec<models::Platforms>`, which is required by `&'insert Vec<models::Platforms>: AsExpression<diesel::sql_types::Json>`
   |
   = help: the following other types implement trait `diesel::Expression`:
             &'a T
             (T0, T1)
             (T0, T1, T2)
             (T0, T1, T2, T3)
             (T0, T1, T2, T3, T4)
             (T0, T1, T2, T3, T4, T5)
             (T0, T1, T2, T3, T4, T5, T6)
             (T0, T1, T2, T3, T4, T5, T6, T7)
           and 153 others
   = note: required for `&'insert Vec<models::Platforms>` to implement `diesel::Expression`
   = note: required for `&'insert Vec<models::Platforms>` to implement `AsExpression<diesel::sql_types::Json>`
   = note: this error originates in the derive macro `Insertable` (in Nightly builds, run with -Z macro-backtrace for more info)

error[E0277]: the trait bound `Vec<models::Platforms>: diesel::Expression` is not satisfied
  --> src/models.rs:46:9
   |
46 |     pub platforms: Vec<Platforms>,
   |         ^^^^^^^^^ the trait `diesel::Expression` is not implemented for `Vec<models::Platforms>`, which is required by `Vec<models::Platforms>: AsExpression<diesel::sql_types::Json>`
   |
   = help: the following other types implement trait `diesel::Expression`:
             &'a T
             (T0, T1)
             (T0, T1, T2)
             (T0, T1, T2, T3)
             (T0, T1, T2, T3, T4)
             (T0, T1, T2, T3, T4, T5)
             (T0, T1, T2, T3, T4, T5, T6)
             (T0, T1, T2, T3, T4, T5, T6, T7)
           and 153 others
   = note: required for `Vec<models::Platforms>` to implement `AsExpression<diesel::sql_types::Json>`

error[E0277]: the trait bound `Vec<models::Platforms>: diesel::Expression` is not satisfied
  --> src/models.rs:46:9
   |
46 |     pub platforms: Vec<Platforms>,
   |         ^^^^^^^^^ the trait `diesel::Expression` is not implemented for `Vec<models::Platforms>`, which is required by `&'insert Vec<models::Platforms>: AsExpression<diesel::sql_types::Json>`
   |
   = help: the following other types implement trait `diesel::Expression`:
             &'a T
             (T0, T1)
             (T0, T1, T2)
             (T0, T1, T2, T3)
             (T0, T1, T2, T3, T4)
             (T0, T1, T2, T3, T4, T5)
             (T0, T1, T2, T3, T4, T5, T6)
             (T0, T1, T2, T3, T4, T5, T6, T7)
           and 153 others
   = note: required for `&'insert Vec<models::Platforms>` to implement `diesel::Expression`
   = note: required for `&'insert Vec<models::Platforms>` to implement `AsExpression<diesel::sql_types::Json>`

error[E0277]: the trait bound `Vec<models::Platforms>: diesel::Expression` is not satisfied
  --> src/user.rs:73:10
   |
73 |         .values(&repo)
   |          ^^^^^^ the trait `diesel::Expression` is not implemented for `Vec<models::Platforms>`, which is required by `&Vec<models::Platforms>: AsExpression<diesel::sql_types::Json>`
   |
   = help: the following other types implement trait `diesel::Expression`:
             &'a T
             (T0, T1)
             (T0, T1, T2)
             (T0, T1, T2, T3)
             (T0, T1, T2, T3, T4)
             (T0, T1, T2, T3, T4, T5)
             (T0, T1, T2, T3, T4, T5, T6)
             (T0, T1, T2, T3, T4, T5, T6, T7)
           and 153 others
   = note: required for `&Vec<models::Platforms>` to implement `diesel::Expression`
   = note: required for `&Vec<models::Platforms>` to implement `AsExpression<diesel::sql_types::Json>`

error[E0277]: the trait bound `DefaultableColumnInsertValue<ColumnInsertValue<repositories::columns::platforms, &Vec<models::Platforms>>>: InsertValues<_, repositories::table>` is not satisfied
   --> src/user.rs:74:18
    |
74  |         .execute(&mut conn)
    |          ------- ^^^^^^^^^ the trait `InsertValues<_, repositories::table>` is not implemented for `DefaultableColumnInsertValue<ColumnInsertValue<repositories::columns::platforms, &Vec<models::Platforms>>>`, which is required by `diesel::query_builder::InsertStatement<repositories::table, ValuesClause<(DefaultableColumnInsertValue<ColumnInsertValue<repositories::columns::name, diesel::expression::bound::Bound<diesel::sql_types::Text, &std::string::String>>>, DefaultableColumnInsertValue<ColumnInsertValue<repositories::columns::created_at, diesel::expression::bound::Bound<diesel::sql_types::Timestamp, &NaiveDateTime>>>, DefaultableColumnInsertValue<ColumnInsertValue<repositories::columns::platforms, &Vec<models::Platforms>>>), repositories::table>>: diesel_async::methods::ExecuteDsl<_, _>`
    |          |
    |          required by a bound introduced by this call
    |
    = help: the following other types implement trait `InsertValues<DB, T>`:
              `DefaultableColumnInsertValue<ColumnInsertValue<Col, Expr>>` implements `InsertValues<DB, <Col as Column>::Table>`
              `DefaultableColumnInsertValue<ColumnInsertValue<Col, Expr>>` implements `InsertValues<Sqlite, <Col as Column>::Table>`
    = note: required for `(DefaultableColumnInsertValue<ColumnInsertValue<name, Bound<Text, &String>>>, ..., ...)` to implement `InsertValues<_, repositories::table>`
    = note: required for `ValuesClause<(DefaultableColumnInsertValue<ColumnInsertValue<name, ...>>, ..., ...), ...>` to implement `diesel::query_builder::QueryFragment<_>`
    = note: 1 redundant requirement hidden
    = note: required for `InsertStatement<table, ValuesClause<(DefaultableColumnInsertValue<...>, ..., ...), ...>>` to implement `diesel::query_builder::QueryFragment<_>`
    = note: required for `InsertStatement<table, ValuesClause<(DefaultableColumnInsertValue<...>, ..., ...), ...>>` to implement `diesel_async::methods::ExecuteDsl<_, _>`
note: required by a bound in `diesel_async::RunQueryDsl::execute`
   --> /root/.cargo/registry/src/index.crates.io-6f17d22bba15001f/diesel-async-0.5.2/src/run_query_dsl/mod.rs:224:15
    |
221 |     fn execute<'conn, 'query>(self, conn: &'conn mut Conn) -> Conn::ExecuteFuture<'conn, 'query>
    |        ------- required by a bound in this associated function
...
224 |         Self: methods::ExecuteDsl<Conn> + 'query,
    |               ^^^^^^^^^^^^^^^^^^^^^^^^^ required by this bound in `RunQueryDsl::execute`
    = note: the full name for the type has been written to '/workspaces/lucle/target/debug/deps/lucle-ddb2780e965dcc22.long-type-16921592556120681880.txt'
    = note: consider using `--verbose` to print the full type name to the console
    = note: the full name for the type has been written to '/workspaces/lucle/target/debug/deps/lucle-ddb2780e965dcc22.long-type-11073437262734235260.txt'
    = note: consider using `--verbose` to print the full type name to the console
    = note: the full name for the type has been written to '/workspaces/lucle/target/debug/deps/lucle-ddb2780e965dcc22.long-type-10108177361947437125.txt'
    = note: consider using `--verbose` to print the full type name to the console

Maybe we cannot transform Vec<Enum> to Json ?

[weiznich]

To repeat again what I wrote above: Your code does not provide a way to convert a Vec<models::Platforms> to sql side json. It merly defines how to turn an instance of the type models::Platforms into something thats RepositoriesPlatformsEnum on the sql side. That's completely unrelated to what you are trying to do and therefore expected to not working.

[Ludea]

I do this now

#[derive(Debug, Serialize, Deserialize, FromSqlRow, AsExpression, PartialEq, Clone)]
#[diesel(sql_type = Json)]
pub enum Platforms {
    Win64,
    Macosx8664,
    Macosarm64,
    Linux,
}

impl ToSql<Json, diesel::mysql::Mysql> for Platforms {
    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, diesel::mysql::Mysql>) -> serialize::Result {
        match *self {
            Platforms::Win64 => out.write_all(b"win64")?,
            Platforms::Macosx8664 => out.write_all(b"macos_x86_64")?,
            Platforms::Macosarm64 => out.write_all(b"macos_arm64")?,
            Platforms::Linux => out.write_all(b"linux")?,
        }
        Ok(IsNull::No)
    }
}

impl FromSql<Json, diesel::mysql::Mysql> for Platforms {
    fn from_sql(bytes: diesel::mysql::MysqlValue) -> deserialize::Result<Self> {
        match bytes.as_bytes() {
            b"win64" => Ok(Platforms::Win64),
            b"macos_x86_64" => Ok(Platforms::Macosx8664),
            b"macos_arm64" => Ok(Platforms::Macosarm64),
            b"linux" => Ok(Platforms::Linux),
            _ => Err("Unrecognized enum variant".into()),
        }
    }
}

It build but I need Vec
I have to implement

impl FromSql<Json, diesel::mysql::Mysql> for Vec<Platforms> {

?

[Ludea]

Finally I use serde_json to create array, store it into json and store into mysql from String