Diesel: First steps and hours of frustration

help
1

Hey.

Disclaimer: I am an experienced dev, but new to Rust. I've built a bunch of pet projects with it. SQL (and Sqlite) are old hats.

I thought I'd write a quick silly web app today, a rewrite of a thing I did in another language. How hard could it be? Well, I got stuck for hours with the worst error messages I've seen in rust land so far and .. haven't solved the most basic "Read data from a single table" use case yet.

I am using Sqlite, because it's a tiny container and I don't want another service. Diesel claims it supports MySQL, Postgres and Sqlite... but does it?

My table structure over in the other ecosystem translates to

CREATE TABLE hosts (
id TEXT NOT NULL, -- This is a UUID
created_at TEXT NOT NULL
-- a bunch of other things
)

#[derive(Queryable)]
pub struct Host {
pub id: UUID,
pub created_at: Timestamp,
// more stuff
}

actually created_at is an INTEGER and a unix timestamp, but .. bear with me.

The error messages make zero sense and don't explain what the problem is. In the table above? After hours of frustration I found the "wontfix" bug saying that Diesel does have a uuid feature flag, but .. that only works for one of the "supported" databasesÂą. Okay, that's unfortunate and really not great UX (especially because the error messages don't contain anything of value). The workarounds mentioned in that ticket don't work anymore since diesel reached 2.x, but with a bit of patience and browsing the docs some more (it seems Sqlite is really a step child here) I am able to .. read and write uuids after just a few hours. Progress!

Let's uncomment the next fields in my table. Fast forward and another couple hours later I'm unsure what the solution is to get basic "serialize timestamps" to work. For reasons I cannot understand Timestamp (and Date/DateTime) maps to TEXT for Sqlite (why?) and I've tried gazillion combinations of mapping my schema with a simple TEXT NOT NULL to .. Timestamp. Or chrono::NaiveDateTime .. and whatever else came to mind. The error messages are again completely opaque and not helping. I'm at the verge of adding my own custom serialization similar to the UUID workaround .. but that can't be the way?

TL/DR: 6-8 hours in I'm unable to use Diesel with a single, simple, trivial table in Sqlite. Am I dumb? Maybe. But I could've done this a few minutes in most other stacks. What am I missing, what can I do to improve the situation? I must be overlooking something huge to get stuck this hard or Sqlite support is seriously broken. Would appreciate pointers to

a) help me understand the uuid mess
b) solve the timestamp situation
c) learning material that make all these things click or turn the diesel error messages² into something helpful

①: Add support for Uuid to SQLite · Issue #364 · diesel-rs/diesel · GitHub
②:

error[E0277]: the trait bound `(diesel::sql_types::Text, diesel::sql_types::Text, diesel::sql_types::Text, diesel::sql_types::Text, Nullable<diesel::sql_types::Text>): load_dsl::private::CompatibleType<foo::models::Host, _>` is not satisfied
    --> src/main.rs:13:38
     |
13   |     let results = hosts.load::<Host>(connection).expect("Error loading hosts");
     |                         ----         ^^^^^^^^^^ the trait `load_dsl::private::CompatibleType<foo::models::Host, _>` is not implemented for `(diesel::sql_types::Text, diesel::sql_types::Text, diesel::sql_types::Text, diesel::sql_types::Text, Nullable<diesel::sql_types::Text>)`
     |                         |
     |                         required by a bound introduced by this call
     |
     = 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<'_, _, foo::models::Host>`
note: required by a bound in `diesel::RunQueryDsl::load`
    --> /home/bam/.cargo/registry/src/github.com-1ecc6299db9ec823/diesel-2.0.4/src/query_dsl/mod.rs:1499:15
     |
1499 |         Self: LoadQuery<'query, Conn, U>,
     |               ^^^^^^^^^^^^^^^^^^^^^^^^^^ required by this bound in `RunQueryDsl::load`

error[E0277]: the trait bound `foo::models::Host: FromSqlRow<_, _>` is not satisfied
    --> src/main.rs:13:38
     |
13   |     let results = hosts.load::<Host>(connection).expect("Error loading hosts");
     |                         ----         ^^^^^^^^^^ the trait `FromSqlRow<_, _>` is not implemented for `foo::models::Host`
     |                         |
     |                         required by a bound introduced by this call
     |
     = 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<'_, _, foo::models::Host>`
note: required by a bound in `diesel::RunQueryDsl::load`
    --> /home/bam/.cargo/registry/src/github.com-1ecc6299db9ec823/diesel-2.0.4/src/query_dsl/mod.rs:1499:15
     |
1499 |         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 `masters-dashboard-service` due to 2 previous errors
2

Diesel claims it supports MySQL, Postgres and Sqlite... but does it?

I don't normally use sqlite but I was able to set up a sqlite database and query stuff from it with diesel, so it seems like it.

The the trait bound (diesel::sql_types::Text, ..) error is a convoluted way of saying that the struct and schema don't match. When you get an error like this, the best thing to do first is to make sure the types on the SQL side and Rust side match up. From diesel's docs:

Types which represent the result of a SQL query implement a trait called Queryable.

Diesel maps “Rust types” (e.g. i32) to and from “SQL types” (e.g. diesel::sql_types::Integer). You can find all the types supported by Diesel in the sql_types module.

In the linked module:

If you’d like to know the rust types which can be used for a given SQL type, see the documentation for that SQL type.

In the module we can find the sql_types::Text type, and in the docs the only compatible types listed is String (FromSql is the trait to look at when moving stuff from the database to the Rust side), notably Uuid and chrono::NaiveDateTime are missing. So the id and created_at fields in the Host struct should be Strings.

The reason uuids and dates are supported with postgres is that postgres has an actual UUID and date/time types, which have corresponding Rust types in diesel in sql_types::Uuid and sql_types::Timestamp which have the FromSql implementations that would allow you to set the struct fields to use the uuid::Uuid and chrono::NaiveDateTime types.

The maintainers could in principle just implement FromSql so you can go from TEXT to uuid::Uuid, but I think that would go against the purpose of the crate. As a type-safe query builder, it's intended to check the validity of your database operations at compile time as much as possible, and there's no way to guarantee at compile time that your id column actually contains only valid uuids or that created_at is always a valid timestamp.

1 Like
3

I really appreciate any input.

As for the uuid thing: I .. believe that is mostly a philosophical or design question. The linked bug report talks about how, for example, Timestamp/Date/DateTime supposedly (because I can't make it work) ARE supported for Sqlite, but mapped to Text. As in, there's a "The DB doesn't understand it, but we support it" case for that, potentially, but uuids aren't supported. The bug also suggests that it might make sense to rename the feature to pg_uuid or something if .. it doesn't work across the supported databases.

That being said and uuids working using a custom serializer, your own link to sql_types::Timestamp mentions

#### [`ToSql` impls](https://docs.rs/diesel/latest/diesel/sql_types/struct.Timestamp.html#tosql-impls)

* [`std::time::SystemTime`](https://doc.rust-lang.org/nightly/std/time/struct.SystemTime.html) (PG only)
* [`chrono::NaiveDateTime`](https://docs.rs/chrono/0.4.24/x86_64-unknown-linux-gnu/chrono/naive/datetime/struct.NaiveDateTime.html) with `feature = "chrono"`
* [`time::PrimitiveDateTime`](https://docs.rs/time/0.3.20/x86_64-unknown-linux-gnu/time/primitive_date_time/struct.PrimitiveDateTime.html) with `feature = "time"`
* [`time::OffsetDateTime`](https://docs.rs/time/0.3.20/x86_64-unknown-linux-gnu/time/offset_date_time/struct.OffsetDateTime.html) with `feature = "time"`

#### [`FromSql` impls](https://docs.rs/diesel/latest/diesel/sql_types/struct.Timestamp.html#fromsql-impls)

* [`std::time::SystemTime`](https://doc.rust-lang.org/nightly/std/time/struct.SystemTime.html) (PG only)
* [`chrono::NaiveDateTime`](https://docs.rs/chrono/0.4.24/x86_64-unknown-linux-gnu/chrono/naive/datetime/struct.NaiveDateTime.html) with `feature = "chrono"`
* [`time::PrimitiveDateTime`](https://docs.rs/time/0.3.20/x86_64-unknown-linux-gnu/time/primitive_date_time/struct.PrimitiveDateTime.html) with `feature = "time"`
* [`time::OffsetDateTime`](https://docs.rs/time/0.3.20/x86_64-unknown-linux-gnu/time/offset_date_time/struct.OffsetDateTime.html) with `feature = "time"`

Note that only the first option is pg-only. Given that I'm really really green here: Wouldn't you read that as "The DB schema is TEXT (okay... no idea why not INTEGER but I don't care all that much) and it will be deserialized to a date related type (if you use feature "time" or "chrono")? Why would I want a String on the Rust side of things/in my struct?

Code is here (mapping to TEXT but seemingly(?) supporting various proper date types on the Rust side): diesel/mod.rs at master · diesel-rs/diesel · GitHub

4

sql_types::Timestamp is the Rust representation for the timestamp SQL type, such as the one in postgres: PostgreSQL: Documentation: 15: 8.5. Date/Time Types. Since the columns are all TEXT, it's not being used here. The correct way to read it is "The column in the DB schema is TIMESTAMP...". Same goes for the mod.rs file, it's mapping a TIMESTAMP SQL type, hence the comment "The timestamp SQL type."

I don't think uuids are supported in a meaningful way in sqlite, at least from diesel's perspective. A UUID column in a postgres database will always be a valid Uuid, but the same is not true for any sqlite column.

Why would I want a String on the Rust side of things/in my struct?

It's not that you want a String on the Rust side, it's just that given a TEXT column that's the only thing you can reliably turn it into.

1 Like
5

Argh. Way to prove that rubber ducking works.

So the magic sauce is: Declare the schema on the Sqlite side to be

created_at TIMESTAMP

I set it to Text because it's not a "real" Sqlite type and I misread the Diesel docs: diesel/mod.rs at master · diesel-rs/diesel · GitHub seemed to say a Timestamp is a TEXT¹ (which made some sort of sense, given that there's no native Timestamp support), so I declared my schema with created_at TEXT.

The solution instead is to use created_at TIMESTAMP for the schema, which then can map to chrono::NaiveDateTime (or an Option, if nullable). That was a rough start, but at least it kinda sorta makes sense in hindsight?

â‘ : #[diesel(sqlite_type(name = "Text"))]

6

See my reply above. I only just figured out my error (and tried to explain where I was taking the wrong turn, looking at the SqlType for Timestamp and then making the schema follow that..)

Thanks a ton for your attention and support here

1 Like
7

For the record: You can run update sometable set some_timestamp = "Rust Is Fun" against a Sqlite DB and .. that works. So the distinction between Date/DateTime/Timestamp and UUID is still kinda arbitrary (and arguably, biased opinion here, the explicit decision of not supporting this for Sqlite is underdocumented).

8

According to Datatypes In SQLite

SQLite does not have a storage class set aside for storing dates and/or times

so I guess it makes sense that it's basically the same as TEXT. I believe the timestamp type is part of the SQL standard so sqlite has to support it, it just does the bare minimum to do so. AFAIK the other backends that diesel supports do actually make sure columns contain valid date/time data for each type, so I would just consider it a quirk of sqlite. At least if you only interact with the database through diesel it's not a problem, since diesel will prevent you from storing "invalid" strings in there. Uuids can't be handled the same way, since sqlite has no uuid column type at all, not even one that's just an alias for text.

9

I understand what you're saying, but I'm confused why that doesn't offer the same possibilities (and same guarantees) as a Timestamp? To recap: Yes, Sqlite "recognizes" a Timestamp, but doesn't know how to handle them in a special way. Raw SQL can put anything in a "Timestamp" column. It's safe to use it exclusively through Diesel, because .. Diesel will verify the data for me and handle the consistent (de)serialization.

UUIDs: Sqlite does NOT have a special keyword for it. But they can be easily stored as BINARY or TEXT and Diesel will give me the same guarantees if I only access the DB through its API .. and with the same drawbacks? The custom UUID type I scrambled together is implementing FromSql and ToSql for Text and Sqlite and works the same way as Date/DateTime/Timestamp as far as I can tell.

My irritation here is that it feels like a rather arbitrary exclusion for a common DB datatype (nevermind my bad uuid primary key, but storing uuids should be extremely common) and it's really not well-documented. If a crate has features "sqlite" and "uuid", I kinda expect uuid support in sqlite unless the documentation states otherwise in rather bold letters. Which it doesn't.

(On a sidenote: Both my uuid implementation and the official Timestamp wrapper panic when the data isn't correct. Which seems quite a bit extreme/harsh for my liking?)

10

Since there's nothing in the schema indicating that it's a special kind of text column, the generated schema.rs will have the type of that column be sql_types::Text, and so diesel will let you store arbitrary strings in there. The same is not true for timestamp, as it will be set as a sql_types::Timestamp in schema.rs, and so you'll get a compiler error if you try to put a string in there. I don't see it as an arbitrary exclusion, sqlite doesn't support uuids so diesel doesn't support uuids for sqlite.

11

For SQLite there is no obvious mapping between column types and rust types as columns declared with any type can contain any other type. For example it's just fine to perform an UPDATE sometabe SET some_text = 5 statement to set a column that is declared as TEXT to the integer value 5.
Diesel makes assumptions there based on the type name of the column. So for columns with the type TEXT it will assume that it only contains text values, for INTEGER only integers and so on. As sqlite accepts nearly anything as column name that's extended to other types that are not "supported" directly by sqlite via the TIMESTAMP type.
Now why is this done for timestamps and not for uuid? Mostly because there is a clear format specification for timestamps, while there is no such thing for uuid from in the sqlite documentation. For uuid there are only extensions that need to be loaded to work with uuid's at SQL level. That makes it unclear whether the "right" way to store uuid's is to use a TEXT based representation or a BINARY based representation. There are arguments for both and in the end it depends on the users choice -> From diesels perspective that means it's up to the user to implement the solution that's right for them. I understand that this can be frustrating if you start using diesel, but on the other hand it gives you the choice to explicitly customize this kind of stuff.

That's not correct for the official timestamp wrapper. It returns an error if there was no matching timestamp format. That's different from panicing.

We are always happy to accept improvements to our documentation. Please make concrete suggestions or even better open a PR.

We are aware of these error messages and we are working on improving the situation. Unfortunately that requires interacting with the compiler in a way that's not supported in stable rust, so that means either changing the language (we are on that) or having crude workarounds (we are also on that). It's just something that takes quite a lot of time and effort to get it right. You are welcome to help there.
That written: These error messages look bad at the first look, but they already contain quite a lot of important information. (In that case a ordered list of sql side types that your query returns, so you need "just" go through your struct field by field and see if that types match). Also there are really only a few error kind of error messages that show up again and again if you are working with diesel. It might sound bad, but usually you get a feeling on how to solve each of them quite quickly. The documentation from diesels master branch already contains a help on how to read some of these error messages. (Please note that this documentation links unreleased features, so not everything suggested there will work with the currently released diesel version).

I would be interested in understanding why you get that feeling, because sqlite gets the same amount of support than any other supported backend from the diesel team.

4 Likes
12

Let me start with saying that after re-reading this thread I appreciate the thorough and friendly replies I received.. in spite of my frustrated and whiny tone in parts. Thanks to both of you for your help and I appreciate the work you're doing on Diesel.

I understand the argument. Would I wish that this was built-in? Absolutely. But the rationale is clear. That being said, I would still argue that a "uuid" feature flag is .. unfortunate, if that's more of a "pguuid" in spirit.

You're correct, of course. My quick and dirty "make uuids and timestamps work" sample code had a load<MyType>(connection).unwrap(), so silly me. I didn't think that the whole load() would fail if I manually break one row at that point in time, but thinking about it now I wouldn't know what else it could or should do in this case

I should look into that. Truth be told, what isn't obvious to me might not matter for more experienced Rust developers, but documenting it (in a toned down/matter-of-fact way) might be worth a try.
The "maybe 'uuid' is a poor feature name" suggestion (not mine, stolen from the linked bug) does resonate with me though. Looking at the docs a few nights later I can see that both the Uuid docs and the sql_types module docs list postgres_backend/have a line saying that this is a Postgres-only feature.

It was also a surprise (as can be seen in the thread above of course) how the mapping works. I really really tried reading the docs and got confused by the fact that Timestamp does map to Text on Sqlite .. but cannot be used with Text columns. My clueless mind expected some sort of symmetry here that doesn't exist.

Please discard that part of my comment as frustrated venting without substance.

13

I just wanted to add here: We are explicitly interested in suggestions/contributions on how to improve the documentation from non-experienced rust developers as these are the persons that need a helpful documentation more than others. It's just hard to get that perspective back after you reach some level of knowledge with a certain thing. Therefore please share these kind of suggestions.
As for the uuid flag name: I can see what you want to say here, it's just not really possible to change that feature flag name anymore due to the given stability guarantees. (It would require a major version bump)