Enum in Diesel ORM with implemented FromSql and ToSql

[tcpdcp22]

Hello.
I'am trying to use Enums with Diesel(postgres), but I'am stuck with serializing(Diesel ORM -> Postgres Types and via verse). Found this 1 example in master and 2.2.x branches and few similar questions, but it didn't help me. Could you please help with next errors in code and answer on questions:

1. How to correct use(for what situation I need impl this examples and by what logic (except for the DBMS type) should I choose one or another interface for implementation in (ToSql in diesel::serialize - Rust) and FromSql Implementations on Foreign Types section?
2. How to use #[derive(Selectable)] + #[diesel(check_for_backend(postgres_backend))](did I correctly indicate the name of the backend in parentheses as 'postgres_backend'?). I don't see any difference in the wrong types error output between [derive(Selectable)] + #[diesel(check_for_backend(postgres_backend))] and [derive(Queryable)]

I have:

[dependencies]
serde_json = "1.0.128"
diesel = { version = "2.2.0", features = ["postgres"] }

models.rs

use diesel::{Queryable, Insertable, Selectable};
use diesel::deserialize::{FromSqlRow, QueryableByName, FromSql};
use diesel::pg::{Pg, PgValue};
use diesel::serialize::{IsNull, Output, ToSql};
use diesel::sql_types::{Integer, SqlType};
use diesel::{serialize, deserialize};
use crate::schema;


#[derive(Debug, PartialEq, FromSqlRow, Eq)]
#[diesel(sql_type = schema::sql_types::RoleType)]
pub enum Role {
    Mr,
    Batrak,
    R,
}


#[derive(Debug, Clone, Copy, PartialEq, FromSqlRow, Eq)]
#[diesel(sql_type = schema::sql_types::SexType)]
pub enum Sex {
    M = 0,
    F = 1,
}

impl ToSql<schema::sql_types::RoleType, Pg> for Role {
    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Pg>) -> serialize::Result {
        match *self {
            Role::Mr => out.write_all(b"mr")?,
            Role::Batrak => out.write_all(b"batrak")?,
            Role::R => out.write_all(b"r")?,
        }
        Ok(IsNull::No)
    }
}

impl ToSql<Integer, Pg> for Sex {
    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Pg>) -> serialize::Result {
        match self {
            Sex::M => 0.to_sql(out)?,
            Sex::F => 1.to_sql(out)?,
        }
        Ok(IsNull::No)
    }
}


impl FromSql<schema::sql_types::RoleType, Pg> for Role {
    fn from_sql(bytes: PgValue<'_>) -> deserialize::Result<Self> {
        match bytes.as_bytes() {
            b"mr" => Ok(Role::Mr),
            b"batrak" => Ok(Role::Batrak),
            b"r" => Ok(Role::R),
            _ => Err("Unrecognized role variant".into()),
        }
    }
}


impl FromSql<Integer, Pg> for Sex {
    fn from_sql(value: PgValue<'_>) -> deserialize::Result<Self> {
        match i32::from_sql(value)? {
            0 => Ok(Sex::M),
            1 => Ok(Sex::F),
            x => Err(format!("Unrecognized variant {}", x).into()),
        }
    }
}


#[derive(Queryable, Debug)]
#[diesel(table_name = gql_entity)]
pub struct SelectEntity {
    pub id: i32,
    pub name: String,
    pub project: String,
    pub role: Role,
    pub sex: Sex,
}


#[derive(Insertable)]
#[diesel(table_name = gql_entity)]
pub struct NewEntity<'a> {
    pub name: &'a str,
    pub project: &'a str,
    pub role: &'a Role,
    pub sex: &'a Sex
}

schema.rs
// @generated automatically by Diesel CLI.

pub mod sql_types {
    #[derive(
        diesel::query_builder::QueryId,
        Clone,
        diesel::sql_types::SqlType
    )]
    #[diesel(postgres_type(name = "role_type"))]
    pub struct RoleType;

    #[derive(
        diesel::query_builder::QueryId,
        Clone,
        diesel::sql_types::SqlType
    )]
    #[diesel(postgres_type(name = "sex_type"))]
    pub struct SexType;
}

diesel::table! {
    use diesel::sql_types::*;
    use super::sql_types::RoleType;
    use super::sql_types::SexType;

    gql_entity (id) {
        id -> Int4,
        name -> Varchar,
        project -> Varchar,
        role -> RoleType,
        sex -> SexType,
    }
}

up migration
-- Your SQL goes here

CREATE TYPE role_type AS ENUM ('mr', 'batrak', 'r');
CREATE TYPE sex_type AS ENUM (0, 1);

CREATE TABLE gql_entity (
id SERIAL PRIMARY KEY,
name VARCHAR NOT NULL UNIQUE,
project VARCHAR NOT NULL,
role role_type NOT NULL,
sex sex_type NOT NULL
)
and few methods that raises errors

pub fn create_gql_entity(&mut self, new_entity: NewEntity) {
        diesel::insert_into(schema::gql_entity::table)
            .values(&new_entity)
            .execute(&mut self.connection)
            .expect("Fail to add new entity");
    }

pub fn get_all(&mut self) -> Vec<SelectEntity> {
        schema::gql_entity::table
            .load::<SelectEntity>(&mut self.connection)
            .expect("Fail to get all entities")
    }

in

schema::gql_entity::table
            .load::<SelectEntity>(&mut self.connection)

have error:

error[E0277]: the trait bound (Integer, diesel::sql_types::Text, diesel::sql_types::Text, RoleType, SexType): load_dsl::private::CompatibleType<SelectEntity, _> is not satisfied
--> src\db\db_connection.rs:35:35
|
35 | .load::(&mut self.connection)
| ---- ^^^^^^^^^^^^^^^^^^^^ the trait load_dsl::private::CompatibleType<SelectEntity, > is not implemented for (Integer, diesel::sql_types::Text, diesel::sql_types::Text, RoleType, SexType), which is required by table: LoadQuery<', _, SelectEntity>

in

diesel::insert_into(schema::gql_entity::table)
            .values(&new_entity)
            .execute(&mut self.connection)

have error:
error[E0277]: the trait bound &NewEntity<'>: diesel::Insertable

is not satisfied
--> src\db\db_connection.rs:28:21
|
28 | .values(&new_entity)
| ------ ^^^^^^^^^^^ the trait diesel::Insertable is not implemented for &NewEntity<'>

[weiznich]

How to correct use(for what situation I need impl this examples and by what logic (except for the DBMS type) should I choose one or another interface for implementation in (ToSql in diesel::serialize - Rust) and FromSql Implementations on Foreign Types section?

There is a complete draft of a guide explaining this topic (and other stuff) here in the repo: https://github.com/diesel-rs/diesel/tree/master/examples/postgres/custom_arrays#custom-array-and-data-types-in-postgres

Otherwise it just seems that you don't correctly handle the conversion for your Sex enum. You don't provide any matching ToSql/FromSql impls there, only unrelated impls from/to Integer columns.

That written: It's currently hard to read your question as the formatting is off. You might want to fix that and also include the complete compiler error (as emitted by rustc) instead of only truncated versions if you look for further help.

How to use #[derive(Selectable)] + #[diesel(check_for_backend(postgres_backend))](did I correctly indicate the name of the backend in parentheses as 'postgres_backend'?). I don't see any difference in the wrong types error output between [derive(Selectable)] + #[diesel(check_for_backend(postgres_backend))] and [derive(Queryable)]

See the examples in the getting started guide for an explanation how to use that derive.

[tcpdcp22]

Hello. I changed the code a little based on your example (thx!)

new schema.rs

pub mod sql_types {
    #[derive(diesel::query_builder::QueryId, Clone, diesel::sql_types::SqlType)]
    #[diesel(postgres_type(name = "role_type"))]
    pub struct RoleType;

    #[derive(diesel::query_builder::QueryId, Clone, diesel::sql_types::SqlType)]
    #[diesel(postgres_type(name = "sex_type"))]
    pub struct SexType;
}

diesel::table! {
    use diesel::sql_types::*;
    use super::sql_types::RoleType;
    use super::sql_types::SexType;

    gql_entity (id) {
        id -> Int4,
        name -> Varchar,
        project -> Varchar,
        role -> RoleType,
        sex -> SexType,
    }
}

models.rs

use std::io::Write;
use diesel::{Insertable, Selectable, Queryable};
use diesel::deserialize::{FromSqlRow, FromSql};
use diesel::pg::{Pg, PgValue};
use diesel::serialize::{IsNull, Output, ToSql};
use diesel::expression::AsExpression;
use diesel::*;
use crate::schema;

#[derive(Debug, PartialEq, FromSqlRow, AsExpression, Eq)]
#[diesel(sql_type = schema::sql_types::RoleType)]
pub enum Role {
    Mr,
    Batrak,
    R,
}


#[derive(Debug, PartialEq, FromSqlRow, AsExpression, Eq)]
#[diesel(sql_type = schema::sql_types::SexType)]
pub enum Sex {
    M,
    F,
}


impl ToSql<schema::sql_types::RoleType, Pg> for Role {
    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Pg>) -> serialize::Result {
        match *self {
            Role::Mr => out.write_all(b"mr")?,
            Role::Batrak => out.write_all(b"batrak")?,
            Role::R => out.write_all(b"r")?,
        }
        Ok(IsNull::No)
    }
}


impl ToSql<schema::sql_types::SexType, Pg> for Sex {
    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Pg>) -> serialize::Result {
        match *self {
            Sex::M => out.write_all(b"m")?,
            Sex::F => out.write_all(b"f")?,
        }
        Ok(IsNull::No)
    }
}


impl FromSql<schema::sql_types::RoleType, Pg> for Role {
    fn from_sql(bytes: PgValue<'_>) -> deserialize::Result<Self> {
        match bytes.as_bytes() {
            b"mr" => Ok(Role::Mr),
            b"batrak" => Ok(Role::Batrak),
            b"r" => Ok(Role::R),
            _ => Err("Unrecognized role variant".into()),
        }
    }
}


impl FromSql<schema::sql_types::SexType, Pg> for Sex {
    fn from_sql(bytes: PgValue<'_>) -> deserialize::Result<Self> {
        match bytes.as_bytes() {
            b"m" => Ok(Sex::M),
            b"f" => Ok(Sex::F),
            _ => Err("Unrecognized sex variant".into()),
        }
    }
}


#[derive(Selectable, Debug)]
#[diesel(table_name = schema::gql_entity)]
#[diesel(check_for_backend(diesel::pg::Pg))]
pub struct SelectEntity {
    pub id: i32,
    pub name: String,
    pub project: String,
    pub role: Role,
    pub sex: Sex,
}


#[derive(Insertable)]
#[diesel(table_name = schema::gql_entity)]
pub struct NewEntity<'a> {
    pub name: &'a String,
    pub project: &'a String,
    pub role: &'a Role,
    pub sex: &'a Sex
}

up migration

CREATE TYPE role_type AS ENUM ('mr', 'batrak', 'r');
CREATE TYPE sex_type AS ENUM ('m', 'f');

CREATE TABLE gql_entity (
id SERIAL PRIMARY KEY,
name VARCHAR NOT NULL UNIQUE,
project VARCHAR NOT NULL,
role role_type NOT NULL,
sex sex_type NOT NULL
)

When I run the command 'cargo check -v' I get the following message
Output:

error[E0277]: the trait bound (Integer, diesel::sql_types::Text, diesel::sql_types::Text, RoleType, SexType): load_dsl::private::CompatibleType<SelectEntity, _> is not satisfied
--> src/main.rs:37:39
|
37 | .load::models::SelectEntity(con)
| ---- ^^^ the trait load_dsl::private::CompatibleType<SelectEntity, _> is not implemented for (Integer, diesel::sql_types::Text, diesel::sql_types::Text, RoleType, SexType), which is required by table: LoadQuery<'_, _, SelectEntity>
| |
| required by a bound introduced by this call
|
= note: this is a mismatch between what your query returns and what your type expects the query to return
= note: the fields in your struct need to match the fields returned by your query in count, order and type
= note: consider using #[derive(Selectable)] or #[derive(QueryableByName)] + #[diesel(check_for_backend(_))]
on your struct SelectEntity and in your query .select(SelectEntity::as_select()) to get a better error message
= help: the following other types implement trait load_dsl::private::CompatibleType<U, DB>:
(ST0, ST1)
(ST0, ST1, ST2)
(ST0, ST1, ST2, ST3)
(ST0, ST1, ST2, ST3, ST4)
(ST0, ST1, ST2, ST3, ST4, ST5)
(ST0, ST1, ST2, ST3, ST4, ST5, ST6)
(ST0, ST1, ST2, ST3, ST4, ST5, ST6, ST7)
(ST0, ST1, ST2, ST3, ST4, ST5, ST6, ST7, ST8)
and 24 others
= note: required for table to implement LoadQuery<'_, _, SelectEntity>
note: required by a bound in diesel::RunQueryDsl::load
--> C:\Users\t.cargo\registry\src\index.crates.io-6f17d22bba15001f\diesel-2.2.4\src\query_dsl\mod.rs:1542:15
|
1540 | fn load<'query, U>(self, conn: &mut Conn) -> QueryResult<Vec>
| ---- required by a bound in this associated function
1541 | where
1542 | Self: LoadQuery<'query, Conn, U>,
| ^^^^^^^^^^^^^^^^^^^^^^^^^^ required by this bound in RunQueryDsl::load

error[E0277]: the trait bound SelectEntity: FromSqlRow<_, _> is not satisfied
--> src/main.rs:37:39
|
37 | .load::models::SelectEntity(con)
| ---- ^^^ the trait FromSqlRow<_, _> is not implemented for SelectEntity, which is required by table: LoadQuery<'_, _, SelectEntity>
| |
| required by a bound introduced by this call
|
= note: double check your type mappings via the documentation of _
= note: diesel::sql_query requires the loading target to column names for loading values.
You need to provide a type that explicitly derives diesel::deserialize::QueryableByName
= help: the following other types implement trait FromSqlRow<ST, DB>:
<(T1, T0) as FromSqlRow<(ST1, Untyped), __DB>>
<(T1, T2, T0) as FromSqlRow<(ST1, ST2, Untyped), __DB>>
<(T1, T2, T3, T0) as FromSqlRow<(ST1, ST2, ST3, Untyped), __DB>>
<(T1, T2, T3, T4, T0) as FromSqlRow<(ST1, ST2, ST3, ST4, Untyped), __DB>>
<(T1, T2, T3, T4, T5, T0) as FromSqlRow<(ST1, ST2, ST3, ST4, ST5, Untyped), __DB>>
<(T1, T2, T3, T4, T5, T6, T0) as FromSqlRow<(ST1, ST2, ST3, ST4, ST5, ST6, Untyped), __DB>>
<(T1, T2, T3, T4, T5, T6, T7, T0) as FromSqlRow<(ST1, ST2, ST3, ST4, ST5, ST6, ST7, Untyped), __DB>>
<(T1, T2, T3, T4, T5, T6, T7, T8, T0) as FromSqlRow<(ST1, ST2, ST3, ST4, ST5, ST6, ST7, ST8, Untyped), __DB>>
and 23 others
= note: required for table to implement LoadQuery<'_, _, SelectEntity>
note: required by a bound in diesel::RunQueryDsl::load
--> C:\Users\t.cargo\registry\src\index.crates.io-6f17d22bba15001f\diesel-2.2.4\src\query_dsl\mod.rs:1542:15
|
1540 | fn load<'query, U>(self, conn: &mut Conn) -> QueryResult<Vec>
| ---- required by a bound in this associated function
1541 | where
1542 | Self: LoadQuery<'query, Conn, U>,
| ^^^^^^^^^^^^^^^^^^^^^^^^^^ required by this bound in RunQueryDsl::load

For more information about this error, try rustc --explain E0277.
error: could not compile dsl_exmpl (bin "dsl_exmpl") due to 2 previous errors

When I run rustc -v D:\dsl_exmpl\src\main.rs
Output:

error[E0433]: failed to resolve: maybe a missing crate diesel?
--> D:\dsl_exmpl\src\main.rs:2:5
|
2 | use diesel::prelude::*;
| ^^^^^^ maybe a missing crate diesel?
|
= help: consider adding extern crate diesel to use the diesel crate

error[E0432]: unresolved import dotenvy
--> D:\dsl_exmpl\src\main.rs:3:5
|
3 | use dotenvy::dotenv;
| ^^^^^^^ maybe a missing crate dotenvy?
|
= help: consider adding extern crate dotenvy to use the dotenvy crate

error[E0433]: failed to resolve: maybe a missing crate diesel?
--> D:\dsl_exmpl\src\models.rs:3:5
|
3 | use diesel::deserialize::{FromSqlRow, FromSql};
| ^^^^^^ maybe a missing crate diesel?
|
= help: consider adding extern crate diesel to use the diesel crate

error[E0433]: failed to resolve: maybe a missing crate diesel?
--> D:\dsl_exmpl\src\models.rs:4:5
|
4 | use diesel::pg::{Pg, PgValue};
| ^^^^^^ maybe a missing crate diesel?
|
= help: consider adding extern crate diesel to use the diesel crate

error[E0433]: failed to resolve: maybe a missing crate diesel?
--> D:\dsl_exmpl\src\models.rs:5:5
|
5 | use diesel::serialize::{IsNull, Output, ToSql};
| ^^^^^^ maybe a missing crate diesel?
|
= help: consider adding extern crate diesel to use the diesel crate

error[E0433]: failed to resolve: maybe a missing crate diesel?
--> D:\dsl_exmpl\src\models.rs:6:5
|
6 | use diesel::expression::AsExpression;
| ^^^^^^ maybe a missing crate diesel?
|
= help: consider adding extern crate diesel to use the diesel crate

error[E0432]: unresolved import diesel
--> D:\dsl_exmpl\src\models.rs:2:5
|
2 | use diesel::{Insertable, Selectable};
| ^^^^^^ maybe a missing crate diesel?
|
= help: consider adding extern crate diesel to use the diesel crate

error[E0432]: unresolved import diesel
--> D:\dsl_exmpl\src\models.rs:7:5
|
7 | use diesel::*;
| ^^^^^^ maybe a missing crate diesel?
|
= help: consider adding extern crate diesel to use the diesel crate

error[E0433]: failed to resolve: use of undeclared crate or module diesel
--> D:\dsl_exmpl\src\schema.rs:5:14
|
5 | #[derive(diesel::query_builder::QueryId, Clone, diesel::sql_types::SqlType)]
| ^^^^^^ use of undeclared crate or module diesel

error[E0433]: failed to resolve: use of undeclared crate or module diesel
--> D:\dsl_exmpl\src\schema.rs:5:53
|
5 | #[derive(diesel::query_builder::QueryId, Clone, diesel::sql_types::SqlType)]
| ^^^^^^ use of undeclared crate or module diesel

error[E0433]: failed to resolve: use of undeclared crate or module diesel
--> D:\dsl_exmpl\src\schema.rs:9:14
|
9 | #[derive(diesel::query_builder::QueryId, Clone, diesel::sql_types::SqlType)]
| ^^^^^^ use of undeclared crate or module diesel

error[E0433]: failed to resolve: use of undeclared crate or module diesel
--> D:\dsl_exmpl\src\schema.rs:9:53
|
9 | #[derive(diesel::query_builder::QueryId, Clone, diesel::sql_types::SqlType)]
| ^^^^^^ use of undeclared crate or module diesel

error[E0433]: failed to resolve: use of undeclared crate or module diesel
--> D:\dsl_exmpl\src\schema.rs:14:1
|
14 | diesel::table! {
| ^^^^^^ use of undeclared crate or module diesel

error: cannot find attribute diesel in this scope
--> D:\dsl_exmpl\src\models.rs:12:3
|
12 | #[diesel(sql_type = schema::sql_types::RoleType)]
| ^^^^^^

error: cannot find attribute diesel in this scope
--> D:\dsl_exmpl\src\models.rs:21:3
|
21 | #[diesel(sql_type = schema::sql_types::SexType)]
| ^^^^^^

error: cannot find attribute diesel in this scope
--> D:\dsl_exmpl\src\models.rs:75:3
|
75 | #[diesel(table_name = schema::gql_entity)]
| ^^^^^^

error: cannot find attribute diesel in this scope
--> D:\dsl_exmpl\src\models.rs:76:3
|
76 | #[diesel(check_for_backend(diesel::pg::Pg))]
| ^^^^^^

error: cannot find attribute diesel in this scope
--> D:\dsl_exmpl\src\models.rs:87:3
|
87 | #[diesel(table_name = schema::gql_entity)]
| ^^^^^^

error: cannot find attribute diesel in this scope
--> D:\dsl_exmpl\src\schema.rs:6:7
|
6 | #[diesel(postgres_type(name = "role_type"))]
| ^^^^^^

error: cannot find attribute diesel in this scope
--> D:\dsl_exmpl\src\schema.rs:10:7
|
10 | #[diesel(postgres_type(name = "sex_type"))]
| ^^^^^^

error: aborting due to 20 previous errors

Some errors have detailed explanations: E0432, E0433.
For more information about an error, try rustc --explain E0432.

[tcpdcp22]

Perhaps I'm somehow using compiler flags incorrectly to output compilation errors, but I didn't notice a significant difference or any special clues about what's wrong with the type. What is there #[derive(Selectable, Debug)] + #[diesel(check_for_backend(diesel::pg::Pg))] what is there#[derive(Queryable, Debug)] - the place with the type error is not visible

Can you also please tell me how to understand when to choose one of the signature implementations in the 'Implementations on Foreign Types' section for ToSql, FromSql traits?

In the examples you provided there was only general information about what these traits do

[weiznich]

Perhaps I'm somehow using compiler flags incorrectly to output compilation errors, but I didn't notice a significant difference or any special clues about what's wrong with the type. What is there #[derive(Selectable, Debug)] + #[diesel(check_for_backend(diesel::pg::Pg))] what is there#[derive(Queryable, Debug)] - the place with the type error is not visible

Given that you just don't have a #[derive(Queryable)] on your struct it's expected to get that error message. You need to have both: Selectable and Queryable to get the better error messages.

Can you also please tell me how to understand when to choose one of the signature implementations in the 'Implementations on Foreign Types' section for ToSql, FromSql traits?

These are automatically choosen by the compiler based on what type is in your table! definition and what type in in your rust struct.

[tcpdcp22]

Issue was in the compiler settings, not in diesel. Sorry for bothering you with a problem unrelated to your library