Add support for Uuid to SQLite #364

flosse · 2016-07-01T20:13:07Z

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

The text was updated successfully, but these errors were encountered:

sgrif · 2016-07-01T21:23:49Z

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

flosse · 2016-07-01T21:45:30Z

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 · 2017-04-09T15:21:26Z

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 · 2018-05-23T17:47:37Z

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 · 2018-08-15T14:07:24Z

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

0xcaff · 2018-08-15T14:26:21Z

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

weiznich · 2018-08-15T20:28:55Z

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 😉)

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 · 2018-08-15T20:51:17Z

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.

diesel/diesel/src/sql_types/mod.rs

Lines 267 to 282 in 85e0007

    
           /// The date SQL type. 
        
           /// 
        
           /// ### [`ToSql`](../serialize/trait.ToSql.html) impls 
        
           /// 
        
           /// - [`chrono::NaiveDate`][NaiveDate] with `feature = "chrono"` 
        
           /// 
        
           /// ### [`FromSql`](../deserialize/trait.FromSql.html) impls 
        
           /// 
        
           /// - [`chrono::NaiveDate`][NaiveDate] with `feature = "chrono"` 
        
           /// 
        
           /// [NaiveDate]: /chrono/naive/date/struct.NaiveDate.html 
        
           #[derive(Debug, Clone, Copy, Default, QueryId, SqlType)] 
        
           #[postgres(oid = "1082", array_oid = "1182")] 
        
           #[sqlite_type = "Text"] 
        
           #[mysql_type = "Date"] 
        
           pub struct Date;

sameer · 2018-08-16T01:20:12Z

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 · 2018-08-16T06:51:00Z

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 · 2018-08-16T18:25:37Z

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 · 2021-03-11T23:30:04Z

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 · 2021-04-12T15:34:59Z

+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 :(

This was a little bit more complicated than expected. I had to get rid of the uuid, because sqlite doesn't support it and diesel refuses to include a converter for that. :-( diesel-rs/diesel#364 adding my own converter will come later. until then I just convert from uuid -> string -> uuid by myself.

w4 · 2021-09-18T01:35:18Z

Following on from @MightyPork's code, you can make the backend generic for all database types supporting the Binary type:

impl<B: diesel::backend::Backend> diesel::deserialize::FromSql<Binary, B> for Uuid
where
    Vec<u8>: diesel::deserialize::FromSql<Binary, B>,
{
    fn from_sql(bytes: Option<&B::RawValue>) -> diesel::deserialize::Result<Self> {
        let value = <Vec<u8>>::from_sql(bytes)?;
        uuid::Uuid::from_slice(&value)
            .map(Uuid)
            .map_err(|e| e.into())
    }
}

impl<B: diesel::backend::Backend> diesel::serialize::ToSql<Binary, B> for Uuid
where
    [u8]: diesel::serialize::ToSql<Binary, B>,
{
    fn to_sql<W: Write>(
        &self,
        out: &mut diesel::serialize::Output<W, B>,
    ) -> diesel::serialize::Result {
        out.write_all(self.0.as_bytes())
            .map(|_| diesel::serialize::IsNull::No)
            .map_err(Into::into)
    }
}

teschmitt · 2024-03-26T16:12:15Z

Based on w4's and MightyPork's solutions (which seem to require diesel v1.x), I adapted their UUID module for diesel v2.1.5:

use diesel::{
    backend::Backend,
    deserialize::{self, FromSql},
    serialize::{self, Output, ToSql},
    sql_types::Binary,
    AsExpression, FromSqlRow,
};
use std::fmt::{self, Display, Formatter};
use uuid;

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

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

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

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

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

impl<B: Backend> FromSql<Binary, B> for UUID
where
    Vec<u8>: FromSql<Binary, B>,
{
    fn from_sql(bytes: <B as Backend>::RawValue<'_>) -> deserialize::Result<Self> {
        let value = <Vec<u8>>::from_sql(bytes)?;
        uuid::Uuid::from_slice(&value)
            .map(UUID)
            .map_err(|e| e.into())
    }
}

impl<B: Backend> ToSql<Binary, B> for UUID
where
    [u8]: ToSql<Binary, B>,
{
    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, B>) -> serialize::Result {
        self.0.as_bytes().to_sql(out)
    }
}

This has worked for me in a small SQLite-based project.

adam-53-r · 2024-03-27T11:55:15Z

Based on w4's and MightyPork's solutions (which seem to require diesel v1.x), I adapted their UUID module for diesel v2.1.5:

use diesel::{
    backend::Backend,
    deserialize::{self, FromSql},
    serialize::{self, Output, ToSql},
    sql_types::Binary,
    AsExpression, FromSqlRow,
};
use std::fmt::{self, Display, Formatter};
use uuid;

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

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

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

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

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

impl<B: Backend> FromSql<Binary, B> for UUID
where
    Vec<u8>: FromSql<Binary, B>,
{
    fn from_sql(bytes: <B as Backend>::RawValue<'_>) -> deserialize::Result<Self> {
        let value = <Vec<u8>>::from_sql(bytes)?;
        uuid::Uuid::from_slice(&value)
            .map(UUID)
            .map_err(|e| e.into())
    }
}

impl<B: Backend> ToSql<Binary, B> for UUID
where
    [u8]: ToSql<Binary, B>,
{
    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, B>) -> serialize::Result {
        self.0.as_bytes().to_sql(out)
    }
}

This has worked for me in a small SQLite-based project.

Could it be possible to save it in Text instead of Binary?

teschmitt · 2024-03-28T11:24:21Z

Could it be possible to save it in Text instead of Binary?

Saving it as text is the default behavior which you get when you declare the field as uuid_text in the migration:

create table posts
(
    id uuid_text not null primary key,
    text text not null,
);

Then you just need to remember to always convert to and from String when writing and reading from the DB. If you want to map it transparently, I suppose you could do it analog to the implementation above:

Replace Binary with Text and fiddle around with the to_sql() and from_sql() methods:

// ...

#[diesel(sql_type = Text)]
pub struct UUID(pub uuid::Uuid);

// ...

impl<B: Backend> FromSql<Text, B> for UUID
where
    String: FromSql<Text, B>,
{
    fn from_sql(bytes: <B as Backend>::RawValue<'_>) -> deserialize::Result<Self> {
        let value = String::from_sql(bytes)?;
        uuid!(value).map(UUID).map_err(|e| e.into())
    }
}

impl<B: Backend> ToSql<Binary, B> for UUID
where
    str: ToSql<Text, B>,
{
    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, B>) -> serialize::Result {
        self.0.to_string().to_sql(out)
    }
}

I haven't tested this so you might have to put some more work into the methods, but that would be the rough idea.

adam-53-r · 2024-03-29T10:23:28Z

impl<B: Backend> ToSql<Binary, B> for UUID
where
    str: ToSql<Text, B>,
{
    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, B>) -> serialize::Result {
        self.0.to_string().to_sql(out)
    }
}

It would'nt let me do this because of str needing lifetime annotations or something like that (sorry I'm pretty new).

Eventually I saw this in the documentation and ended up with this implementation that works fine:

// ...

impl<B: Backend> FromSql<Text, B> for UUID
where
    String: FromSql<Text, B>,
{
    fn from_sql(bytes: <B as Backend>::RawValue<'_>) -> deserialize::Result<Self> {
        let value = String::from_sql(bytes)?;
        uuid::Uuid::from_str(&value.as_str())
            .map(UUID)
            .map_err(|e| e.into())
    }
}

impl ToSql<Text, Sqlite> for UUID
where
    String: ToSql<Text, Sqlite>,
{
    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Sqlite>) -> serialize::Result {
        out.set_value(self.0.as_hyphenated().to_string());
        Ok(IsNull::No)
    }
}

Thank you very much for your help @teschmitt

flosse closed this as completed Jul 1, 2016

weiznich mentioned this issue Aug 18, 2023

Unsupported type: uuid #3757

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Add support for Uuid to SQLite #364

Add support for Uuid to SQLite #364

flosse commented Jul 1, 2016

sgrif commented Jul 1, 2016

flosse commented Jul 1, 2016

theduke commented Apr 9, 2017

0xcaff commented May 23, 2018 •

edited

sameer commented Aug 15, 2018

0xcaff commented Aug 15, 2018

weiznich commented Aug 15, 2018

0xcaff commented Aug 15, 2018 •

edited

sameer commented Aug 16, 2018

weiznich commented Aug 16, 2018

sgrif commented Aug 16, 2018

MightyPork commented Mar 11, 2021 •

edited

GTB3NW commented Apr 12, 2021

w4 commented Sep 18, 2021 •

edited

teschmitt commented Mar 26, 2024

adam-53-r commented Mar 27, 2024

teschmitt commented Mar 28, 2024 •

edited

adam-53-r commented Mar 29, 2024

Add support for Uuid to SQLite #364

Add support for Uuid to SQLite #364

Comments

flosse commented Jul 1, 2016

sgrif commented Jul 1, 2016

flosse commented Jul 1, 2016

theduke commented Apr 9, 2017

0xcaff commented May 23, 2018 • edited

sameer commented Aug 15, 2018

0xcaff commented Aug 15, 2018

weiznich commented Aug 15, 2018

0xcaff commented Aug 15, 2018 • edited

sameer commented Aug 16, 2018

weiznich commented Aug 16, 2018

sgrif commented Aug 16, 2018

MightyPork commented Mar 11, 2021 • edited

GTB3NW commented Apr 12, 2021

w4 commented Sep 18, 2021 • edited

teschmitt commented Mar 26, 2024

adam-53-r commented Mar 27, 2024

teschmitt commented Mar 28, 2024 • edited

adam-53-r commented Mar 29, 2024

0xcaff commented May 23, 2018 •

edited

0xcaff commented Aug 15, 2018 •

edited

MightyPork commented Mar 11, 2021 •

edited

w4 commented Sep 18, 2021 •

edited

teschmitt commented Mar 28, 2024 •

edited