CompatibleType<SampleEntity, _>` is not satisfied for Bytes newtype

[Jaltaire]

Consider the following implementation:

schema.rs

diesel::table! {
    use diesel::sql_types::*;

    sample_entities (id) {
        id -> Uuid,
        contents -> Bytea,
    }
}

sample.rs

use crate::schema::*;
use diesel::prelude::*;
use diesel::query_builder::QueryId;
use diesel::{AsChangeset, Identifiable, Insertable, Queryable};
use uuid::Uuid;

#[derive(Debug, Clone, Identifiable, Queryable, Insertable, AsChangeset)]
#[diesel(table_name = sample_entities)]
#[diesel(primary_key(id))]
pub struct SampleEntity {
    pub id: Uuid,
    pub contents: Contents,
}

use bytes::Bytes;
use diesel::deserialize::FromSql;
use diesel::expression::AsExpression;
use diesel::serialize::{Output, ToSql};
use diesel::sql_types::Bytea;
use diesel::FromSqlRow;

#[derive(Default, Debug, Clone, PartialEq, Eq, Hash, FromSqlRow, AsExpression)]
#[diesel(sql_type = diesel::sql_types::Bytea)]
pub struct Contents(Bytes);

impl From<Vec<u8>> for Contents {
    fn from(value: Vec<u8>) -> Self {
        Contents {
            0: Bytes::from(value),
        }
    }
}

impl From<serde_json::Value> for Contents {
    fn from(value: serde_json::Value) -> Self {
        Contents {
            0: Bytes::from(value.to_string()),
        }
    }
}

impl<DB: diesel::backend::Backend> ToSql<Bytea, DB> for Contents
where
    Bytes: ToSql<Bytea, DB>,
{
    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, DB>) -> diesel::serialize::Result {
        self.0.to_sql(out)
    }
}

impl<DB: diesel::backend::Backend> FromSql<Bytea, DB> for Contents
where
    Bytes: FromSql<Bytea, DB>,
{
    fn from_sql(bytes: DB::RawValue<'_>) -> diesel::deserialize::Result<Self> {
        let bytes = Bytes::from_sql(bytes)?;
        Ok(Contents { 0: bytes })
    }
}

main.rs

use std::env;

use diesel::associations::HasTable;
use diesel_async::{AsyncConnection, AsyncPgConnection, RunQueryDsl};
use crate::types::sample::SampleEntity;

#[tokio::main]
async fn main() {
    let database_url = env::var("DATABASE_URL").expect("DATABASE_URL must be set");
    let mut conn = AsyncPgConnection::establish(&database_url).await.unwrap();
    SampleEntity::table()
        .get_results::<SampleEntity>(&mut conn)
        .await
        .unwrap();
}

When trying to run main.rs, I get the following compilation error:

error[E0277]: the trait bound `(diesel::sql_types::Uuid, diesel::sql_types::Binary): CompatibleType<SampleEntity, _>` is not satisfied
   |
44 |                 .get_results::<SampleEntity>(manager.conn())
   |                  ^^^^^^^^^^^ the trait `CompatibleType<SampleEntity, _>` is not implemented for `(diesel::sql_types::Uuid, diesel::sql_types::Binary)`, which is required by `schema::sample_entities::table: diesel_async::methods::LoadQuery<'_, _, SampleEntity>`
   |
   = note: this is a mismatch between what your query returns and what your type expects the query to return
   = note: the fields in your struct need to match the fields returned by your query in count, order and type
   = note: consider using `#[derive(Selectable)]` or #[derive(QueryableByName)] + `#[diesel(check_for_backend(_))]` 
           on your struct `SampleEntity` and in your query `.select(SampleEntity::as_select())` to get a better error message
   = help: the following other types implement trait `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 `schema::sample_entities::table` to implement `diesel_async::methods::LoadQuery<'_, _, SampleEntity>`

How can I go about fixing this error? Curiously, this only seems to occur if Contents is defined as a newtype in terms of Bytes. If defined as Contents(String), and updated with the associated ToSql and FromSql implementations, then this compiles just fine.

[weiznich]

The error message already contains the first hint on how to fix that problem:

= note: consider using `#[derive(Selectable)]` or #[derive(QueryableByName)] + `#[diesel(check_for_backend(_))]` 
          on your struct `SampleEntity` and in your query `.select(SampleEntity::as_select())` to get a better error message

Doing that gives you the following error message:

error[E0277]: cannot deserialize a value of the database type `diesel::sql_types::Binary` as `bytes::Bytes`
  --> src/sample.rs:12:19
   |
12 |     pub contents: Contents,
   |                   ^^^^^^^^ the trait `FromSql<diesel::sql_types::Binary, Pg>` is not implemented for `bytes::Bytes`, which is required by `Contents: FromSqlRow<diesel::sql_types::Binary, Pg>`
   |
   = note: double check your type mappings via the documentation of `diesel::sql_types::Binary`

If you look at the documentation of diesel::sql_types::Binary you will notice that there is indeed no FromSql (and ToSql impl for bytes::Bytes, therefore it's expected to not compile.

You can make this work by changing the bounds in your FromSql and ToSql impl to some supported type (again see the documentation for the specific types) and then change the bodies of these impls to correctly convert the values from/to bytes::Bytes to/from one of the supported types.