Diesel: Add support for Uuid to SQLite

[flosse]

It would be nice to be able to use uuid::Uuid within the structs in combination of SQLite!

[sgrif]

As far as I'm aware there is no data type in SQLite that maps to UUID?

[flosse]

As far as I'm aware there is no data type in SQLite that maps to UUID?

I'm sorry. You're totally right!

[theduke]

No there is not, but the UUID could just be (de)serialized to BINARY or TEXT, which would be much nicer than having to convert manually. @sgrif

[0xcaff]

I've implemented storing UUIDs in SQLite using BINARY(128) and a wrapper type in my package.

Code:
https://github.com/forte-music/core/blob/fc9cd6217708b0dd6ae684df3a53276804479c59/src/models/id.rs#L67-L121

SQL:
https://github.com/forte-music/core/blob/feature/sync-engine/migrations/2018-05-14-200933_setup/up.sql#L1-L12

Usage:
https://github.com/forte-music/core/blob/fc9cd6217708b0dd6ae684df3a53276804479c59/src/models/album.rs#L10-L21

I can make a pull request with an adaptation of this code if anyone is interested.

[sameer]

I'm interested @0xcaff, would appreciate it if you have the time to do a PR for this.

[0xcaff]

I’d like thoughts from a contributor before making a PR. Thoughts @sgrif?

[weiznich]

Our policy for including mappings between certain SQL types an rust types is like following:

• Either a rust type is able to represent all possible values of the matched SQL type and also the SQL type could represent all values of the rust type
• Or the type is a fundamental type like boolean.
  The first condition is not fulfilled because there at Text and also Binary values that are no valid uuid's (for example all shorter or longer values)
  In my opinion also the second point is not fulfilled because uuid is not that fundamental like for example booleans. (If anyone from @diesel/core disagrees, pleas response here :wink:)

Given that (and given that it is quite easy to write a new type wrapper for this outside of diesel) I would say that impl should not be part of diesel itself.

[0xcaff]

Playing devils advocate here.

I'd argue that uuid is a fundamental type. Many applications use the uuid create and diesel already has support for using it with some SQL dialects (postgres). It's useful to have a built, tested and discoverable implementation for users of diesel. Writing a new type wrapper is annoying because of the need for boilerplate type conversion code.

Also, chrono::NaiveDate violates these rules. It is serialized as TEXT for SQLite. https://github.com/diesel-rs/diesel/blob/85e0007684d7547379d1fa69de5146b04695bc19/diesel/src/sql_types/mod.rs#L267-L282

[sameer]

Either it should be implemented for both or none. It's misleading to implement it for NaiveDate and not Uuid since both can technically be serialized.

If it shouldn't be, is there some way this could be provided in a standardized manner? It is odd for users to have to manually implement ToSql and FromSql for types that can be serialized in general.

[weiznich]

In my opinion NaiveDate (and similar types) falls in the same category like booleans. They are fundamental in such a way that it is not possible to write larger application without using them. So not supporting the is not an option.
On the other hand it is possible to write large application without using uuid's, because for nearly all use cases using a 64 bit identifier should be enough.

[sgrif]

We also support it because SQLite does have a ton of built-in functions for dealing with dates. Yes, it represents them as strings, but that's ultimately an implementation detail, not something relevant to whether the type exists or not. Importantly, there is a canonical way to represent a date in SQLite.

That is not the true of UUIDs. We could store the raw bytes, or we could store the text representation. It's not obvious which we should choose, and we would be incompatible with existing databases when we choose one or the other.

Ultimately when there's a clear representation of a type and semantically it exists for a given backend, we support it even if there's some mismatch. For example, even PG's datetime type supports a different range of dates than chrono. Given that SQLite is fully dynamically typed, if we really wanted to only support what strictly could be represented, the only type that we could support for SQLite is Vec<u8>. We do have to draw the line somewhere though, and this is where we've chosen to draw it for the time being.

[MightyPork]

Here is an updated version of the code shared by @0xcaff , as I couldn't get the original to work anymore.
Really wish this was part of the crate, so we wouldn't have to use the wrapper. But, it works.

The "uuid" feature maybe should be called "postgres-uuid", took me a while to figure out it isn't implemented for sqlite. The same goes for most of the other features, like serde_json.

use uuid;
use std::io::prelude::*;
use diesel::deserialize::{self, FromSql};
use diesel::serialize::{self, IsNull, Output, ToSql};
use diesel::sql_types::{Binary};
use diesel::sqlite::Sqlite;
use diesel::backend::Backend;
use std::fmt::{Display, Formatter};
use std::fmt;

#[derive(Debug, Clone, Copy, FromSqlRow, AsExpression, Hash, Eq, PartialEq)]
#[sql_type = "Binary"]
pub struct UUID(pub uuid::Uuid);

impl UUID {
    pub fn random() -> Self {
        Self(uuid::Uuid::new_v4())
    }
}

impl From<UUID> for uuid::Uuid {
    fn from(s: UUID) -> Self {
        s.0
    }
}

impl Display for UUID {
    fn fmt(&self, f: &mut Formatter<'_>) -> fmt::Result {
        write!(f, "{}", self.0)
    }
}

impl FromSql<Binary, Sqlite> for UUID {
    fn from_sql(bytes: Option<&<Sqlite as Backend>::RawValue>) -> deserialize::Result<Self> {
        let bytes = not_none!(bytes);
        uuid::Uuid::from_slice(bytes.read_blob()).map(UUID).map_err(|e| e.into())
    }
}

impl ToSql<Binary, Sqlite> for UUID {
    fn to_sql<W: Write>(&self, out: &mut Output<W, Sqlite>) -> serialize::Result {
        out.write_all(self.0.as_bytes())
            .map(|_| IsNull::No)
            .map_err(Into::into)
    }
}

[GTB3NW]

+1 for rename of the feature. I've spent about an hour looking into this only to find out the type is only compat with PG. Using rusqlite allows for UUID usage as BLOB format, it would be really nice to see, but if it violates your standards then :(