Diesel: Issues with `sql_query` and `Uuid`

[treyhakanson]

Setup

Versions

• Rust: rustc 1.40.0-nightly (9e346646e 2019-11-08)
• Diesel: 1.4.3
• Database: PostgreSQL 11
• Operating System macOS Mojave 10.14.5

Feature Flags

• diesel: ["uuidv07", "chrono", "postgres", "serde_json"]

Problem Description

When using sql_query, I am unable to load a Uuid with the error:

|     .load(&conn.0)
|      ^^^^ the trait `diesel::deserialize::QueryableByName<diesel::pg::Pg>` is not implemented for `uuid::Uuid`

Here is my schema and model:

// schema.rs
table! {
    usr (id) {
        id -> Uuid,
        created_at -> Timestamptz,
        username -> Varchar,
        email -> Varchar,
        password_hash -> Varchar,
        profile_image_url -> Nullable<Varchar>,
    }
}

// models.rs
use chrono::prelude::*;
use serde::ser::{Serialize, SerializeStruct, Serializer};
use uuid::Uuid;

use crate::schema::*;

#[derive(Debug, Queryable, QueryableByName, Deserialize)]
#[table_name = "usr"]
pub struct User {
  #[sql_type = "Uuid"]
  pub id: Uuid,
  pub created_at: DateTime<Utc>,
  pub username: String,
  pub email: String,
  pub password_hash: String,
  pub profile_image_url: Option<String>,
}

impl Serialize for User {
  fn serialize<S>(&self, serializer: S) -> Result<S::Ok, S::Error>
  where
    S: Serializer,
  {
    let mut state = serializer.serialize_struct("User", 5)?;
    state.serialize_field("id", &self.id)?;
    state.serialize_field("created_at", &self.created_at)?;
    state.serialize_field("username", &self.username)?;
    state.serialize_field("email", &self.email)?;
    state.serialize_field("profile_image_url", &self.profile_image_url)?;
    state.end()
  }
}

Here is the broken diesel statement:

sql_query(include_str!("../sql/login.sql"))
    .bind(&identifier) // a String
    .bind(&password) // a String
    .load(&conn) // should return a `Uuid` object

But this one works fine:

let user = usr.find(id).first(&conn.0); // a `User` object

What are you trying to accomplish?

I am trying to call raw SQL to authenticate a user using pgcrypto:

SELECT id
FROM usr
WHERE ($1 IN (email, username))
      AND password_hash = crypt($2, password_hash);

What is the expected output?

The Uuid of the user being authenticated

What is the actual output?

An error.

Are you seeing any additional errors?

No.

[weiznich]

That's expected behaviour. sql_query expects to return into a struct that implements QueryableByName because we need to know the field name there to resolve the result in a sane not error prone way.

[treyhakanson]

Ok wonderful, I missed that in the docs but see it now; yet it still doesn't appear to work:

// models.rs
use uuid::Uuid;

#[derive(Debug, QueryableByName, Serialize, Deserialize)]
#[table_name = "usr"]
pub struct UserIdentifier(#[column_name = "id"] pub Uuid);

// foo.rs
sql_query(include_str!("../sql/login.sql"))
    .bind(&identifier) // a String
    .bind(&password) // a String
    .load::<UserIdentifier>(&conn)

With error:

     .load::<UserIdentifier>(&conn.0)
      ^^^^ the trait `diesel::query_dsl::LoadQuery<_, models::UserIdentifier>` is not implemented for `diesel::query_builder::sql_query::UncheckedBind<diesel::query_builder::sql_query::UncheckedBind<diesel::query_builder::sql_query::UncheckedBind<diesel::query_builder::SqlQuery, &std::string::String, std::string::String>, &std::string::String, std::string::String>, &std::string::String, std::string::String>`

What am I missing here? What documentation should I consult? Unless I'm missing something, I'd expect this to work based on the QueryableByName documentation

[weiznich]

I think the problem is the way you are implementing QueryableByName. Using the table attribute assumes that all columns of that table are returned. I would assume that your table has more than one column. Try the other variant using #[sql_type] that is described in the docs.

[treyhakanson]

I tried that as well, but still no luck:

#[derive(Debug, QueryableByName, Serialize, Deserialize)]
pub struct UserIdentifier(
    #[column_name = "id"]
    #[sql_type = "Uuid"]
    pub Uuid
);

Error is the same as before

[weiznich]

Oh yes. The problem is not your return type but the type parameters for bind. I assume you just removed them from the example? They need to be .bind::<Text,_>(...).

[treyhakanson]

That'll do it, I had .bind::<String, _> like a neanderthal 🤦‍♀️

Thanks so much for your help and patience!