How to fix "`FromSql<Timestamptz, _>` is not implemented for `bool`" error?

Question

This problem only started when I added the datetime fields. If I remove them then it works:

[dependencies]
diesel = { version = "1.4.5", features = ["postgres", "chrono"] }
chrono = "0.4"

#[macro_use]
extern crate diesel;

use chrono::{DateTime, Utc};
use diesel::Queryable;

table! {
    store (id) {
        id -> Int4,
        name -> Varchar,
        description -> Varchar,
        created_at -> Timestamptz,
        updated_at -> Timestamptz,
        is_active -> Bool,
        created_by -> Int4,
        address_id -> Int4,
    }
}

#[derive(Queryable)]
struct Store {
    id: i32,
    name: String,
    description: String,
    is_active: bool,
    created_by: i32,
    address_id: i32,
    created_at: DateTime<Utc>,
    updated_at: DateTime<Utc>,
}

#[test]
fn it_works() {
    use diesel::pg::PgConnection;
    use diesel::prelude::*;

    let database_url = std::env::var("DATABASE_URL").expect("DATABASE_URL must be set");
    let conn = PgConnection::establish(&database_url)
        .expect(&format!("Error connecting to {}", database_url));

    use crate::store::dsl::*;

    let user = store
        .filter(id.eq(1))
        .limit(1)
        .load::<Store>(&conn)
        .expect("Error loading posts");
}

error[E0277]: the trait bound `bool: FromSql<diesel::sql_types::Timestamptz, _>` is not satisfied
    --> src/main.rs:46:24
     |
46   |         .load::<Store>(&conn)
     |          ----          ^^^^^ the trait `FromSql<diesel::sql_types::Timestamptz, _>` is not implemented for `bool`
     |          |
     |          required by a bound introduced by this call
     |
     = help: the trait `FromSql<diesel::sql_types::Bool, Pg>` is implemented for `bool`
     = note: required for `bool` to implement `Queryable<diesel::sql_types::Timestamptz, _>`
     = note: 2 redundant requirements hidden
     = note: required for `Store` to implement `Queryable<(Integer, Text, Text, diesel::sql_types::Timestamptz, diesel::sql_types::Timestamptz, diesel::sql_types::Bool, Integer, Integer), _>`
     = note: required for `SelectStatement<table, DefaultSelectClause, NoDistinctClause, WhereClause<Eq<id, Bound<Integer, i32>>>, NoOrderClause, LimitClause<Bound<BigInt, i64>>>` to implement `LoadQuery<_, Store>`

... more of the same errors snipped ...

How do I get this working?

Answer 1

Your problem is due to field order. The order of fields in your SQL does not match your structs.

From Diesel Getting Started:

Using #[derive(Queryable)] assumes that the order of fields on the Post struct matches the columns in the posts table, so make sure to define them in the order seen in the schema.rs file.

The error message said it couldn't convert a Bool to a timestamp, so it mixed up your is_active with one of the date fields. Re-order your struct to match (or vice versa):

#[derive(Queryable)]
struct Store {
    id: i32,
    name: String,
    description: String,
    created_at: DateTime<Utc>,
    updated_at: DateTime<Utc>,
    is_active: bool,
    created_by: i32,
    address_id: i32,
}

See also: Why does order of fields in struct need to match table! in Diesel?