Diesel: Add an example of FromSql/ToSql for custom types

[RazrFalcon]

I've spent a few hours trying to understand how to use custom types instead of one from sql_types, but not results.

Let's say I have a SmallInt column that I want to represent as enum. How can I do this?

[Eijebong]

https://github.com/diesel-rs/diesel/blob/master/diesel_tests/tests/custom_types.rs You can have a look here

[RazrFalcon]

@Eijebong Looks like it is postgres only. I'm using mysql.

[mbilker]

~Replace Pg with Mysql and you have an example that works with MySQL. You could also use the generic DB: Backend, but I wouldn't bother with the generics if you are only going to use your project with MySQL.~

Disregard my comment. I didn't notice the #[postgres(type_name = ...)] when I looked at this earlier.

[weiznich]

For mysql it is basically the same. Just use #[mysql(typename = ...)]

[RazrFalcon]

@weiznich but the example uses CREATE TYPE SQL pragma, which MySql doesn't support.

[weiznich]

For the SQL side simply see the mysql documentation.
(Also: the only databases system that supports real custom types is postgresql)

[RazrFalcon]

@weiznich I don't need an MySql enum. I simply want to represent a random Sql type with my own.

[weiznich]

Then you only need the MyEnum part from that example. Simpley replace MyType with your own random SQL type (that is already supported in diesel).
Basically you just need to implement FromSql and ToSql and derive AsExpression and FromSqlRow for your type. And don't forget the sql_type annotation.

[RazrFalcon]

Just use #[mysql(typename = ...)]

error[E0658]: The attribute `mysql` is currently unknown to the compiler and may have meaning added to it in the future (see issue #29642)
  --> src/db/changes.rs:30:1
   |
30 | #[mysql(type_name = "my_type")]
   | ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

[weiznich]

Just use #[mysql(typename = ...)]

My mistake it is #[mysql = "..."]. You also need to derive SqlType
(For representing a own type as already implemented type this is not needed.)

[RazrFalcon]

It doesn't work either. Do you have a complete example?

[weiznich]

Something like this: (basically the same as the example above)

#[derive(AsExpression, SqlRow, Debug, Clone)]
#[sql_type = "Text"]
enum MyEnum {
   Foo,
   Bar
}

impl ToSql<MyType, Mysql> for MyEnum {
    fn to_sql<W: Write>(&self, out: &mut Output<W, Mysql>) -> serialize::Result {
        let t = match *self {
            MyEnum::Foo => "foo",
            MyEnum::Bar => "bar"
        }
        <&str as ToSql<Text, Mysql>>::to_sql(t, out)
    }
}

impl FromSql<MyType, Mysql> for MyEnum {
    fn from_sql(bytes: Option<&[u8]>) -> deserialize::Result<Self> {
        match <String as FromSql<Text, Mysql>>::from_sql(bytes)? {
            "foo" => Ok(MyEnum::Foo),
            "bar" => Ok(MyEnum::Bar),
            _ => Err("Unrecognized enum variant".into()),
        }
    }
}

For defining own sql types: Just see the definition of types inside of diesel

[RazrFalcon]

Finally!

Here is a complete example:

#[macro_use] extern crate diesel;

use diesel::*;
use diesel::deserialize::{self, FromSql};
use diesel::serialize::{self, Output, ToSql};
use diesel::mysql::{Mysql, MysqlConnection};
use diesel::sql_types::{Unsigned, Smallint};
use std::io::Write;

table! {
    custom_types {
        id -> Integer,
        action -> Unsigned<Smallint>,
    }
}

#[derive(AsExpression, FromSqlRow, PartialEq, Debug, Clone)]
#[sql_type = "Unsigned<Smallint>"]
pub enum Action {
    Added = 0,
    Updated = 1,
    Removed = 2,
}

impl ToSql<Unsigned<Smallint>, Mysql> for Action {
    fn to_sql<W: Write>(&self, out: &mut Output<W, Mysql>) -> serialize::Result {
        let t = match *self {
            Action::Added   => 0,
            Action::Updated => 1,
            Action::Removed => 2,
        };
        <u16 as ToSql<Unsigned<Smallint>, Mysql>>::to_sql(&t, out)
    }
}

impl FromSql<Unsigned<Smallint>, Mysql> for Action {
    fn from_sql(bytes: Option<&[u8]>) -> deserialize::Result<Self> {
        match <u16 as FromSql<Unsigned<Smallint>, Mysql>>::from_sql(bytes)? {
            0 => Ok(Action::Added),
            1 => Ok(Action::Updated),
            2 => Ok(Action::Removed),
            _ => Err("Unrecognized enum variant".into()),
        }
    }
}

#[derive(Insertable, Queryable, Identifiable, Debug, PartialEq)]
#[table_name = "custom_types"]
struct HasCustomTypes {
    id: i32,
    action: Action,
}

pub fn select() {
    let conn = MysqlConnection::establish("test").unwrap();

    let rows: Vec<HasCustomTypes> = custom_types::table.load(&conn).unwrap();
}

[0xcaff]

Here's a complete example of storing a custom type in a SQL BINARY column.

https://github.com/forte-music/core/blob/ddb49db78080ef661834657b66de4e2c74675149/src/models/id.rs#L73-L85