Rust diesel orm queries

Question

I am new to rust and diesel orm. I am trying to execute below things on my query:

• count
• select
• order
• limit

but I am getting error.
I am using postgres database.

I have added the exact error above the queries in comment.
Here are my code:

schema.rs

table! {
    employee (employee_id) {
        employee_id -> Int4,
        name -> Nullable<Text>,
        age -> Nullable<Int4>,
        address -> Nullable<Text>,
        email -> Nullable<Text>,
        dept_id -> Int4,
        salary -> Nullable<Numeric>,
        created_on -> Nullable<Timestamp>,
        created_by -> Nullable<Text>,
        modified_on -> Nullable<Timestamp>,
        modified_by -> Nullable<Text>,
        is_active -> Nullable<Bool>,
    }
}

models.rs

#![allow(unused)]
#![allow(clippy::all)]
use super::schema::employee;

use bigdecimal::BigDecimal;
use chrono::NaiveDateTime;

#[derive(Queryable, Debug, Identifiable)]
#[table_name = "employee"]
#[primary_key(employee_id)]
pub struct Employee {
    pub employee_id: i32,
    pub name: Option<String>,
    pub age: Option<i32>,
    pub address: Option<String>,
    pub email: Option<String>,
    pub dept_id: i32,
    pub salary: Option<BigDecimal>,
    pub created_on: Option<NaiveDateTime>,
    pub created_by: Option<String>,
    pub modified_on: Option<NaiveDateTime>,
    pub modified_by: Option<String>,
    pub is_active: Option<bool>,
}

cargo.toml

[dependencies]
diesel = { version = "1.4.5", features = ["postgres","chrono","numeric"] }
dotenv = "0.15.0"
chrono = { version = "0.4.19" , features = ["serde"] }
bigdecimal = { version = "0.1.0" }

main.rs

#[macro_use]
extern crate diesel;
extern crate bigdecimal;
extern crate chrono;
extern crate dotenv;

use crate::models::Employee;
use crate::models::Players;
use crate::schema::employee::dsl::*;

use diesel::{pg::PgConnection, prelude::*};
use dotenv::dotenv;
use std::env;

mod models;
mod schema;

fn main() {
    dotenv().ok();
    let data_url: String = env::var("DATABASE_URL").expect("DATABASE_URL must be set");
    let connection: PgConnection =
        PgConnection::establish(&data_url).expect(&format!("Error connect to {}", data_url));

    //get all employees name
    //This is working fine
    let _employee: Vec<Employee> = employee
        .load::<Employee>(&connection)
        .expect("Error loading department");

    for emp in _employee {
        println!("{}", emp.name.unwrap_or_default());
    }


    //----------------------------------------------
    //get employees count
    /*
    Error: error[E0282]: type annotations needed
           ^^^^^^^^^^^^^^^ consider giving `total_employees` a type
    */
    let total_employees = employee.count().get_result(&connection).expect("Error");
    println!("{}", total_employees);


    //-----------------------------------------------
     //get all names
    /*
        Error: error[E0277]: the trait bound `*const str: FromSql<diesel::sql_types::Nullable<diesel::sql_types::Text>, _>` is not satisfied
         ^^^^ the trait `FromSql<diesel::sql_types::Nullable<diesel::sql_types::Text>, _>` is not implemented for `*const str`
    */
    let all_names = employee.select(name).load::<String>(&connection)?;
    println!("{}", all_names);


    //----------------------------------------------
    //order name
    /*
    Error: error[E0277]: the trait bound `*const str: FromSql<diesel::sql_types::Nullable<diesel::sql_types::Text>, _>` is not          satisfied
    ^^^^ the trait `FromSql<diesel::sql_types::Nullable<diesel::sql_types::Text>, _>` is not implemented for `*const str`
    */
    let ordered_names = employee
        .select(name)
        .order(name.desc())
        .load::<String>(&connection)?;
    println!("{}", ordered_names);


   //------------------------------------------------
   /*
    Error: error[E0277]: the trait bound `*const str: FromSql<diesel::sql_types::Nullable<diesel::sql_types::Text>, _>` is not     satisfied
    ^^^^ the trait `FromSql<diesel::sql_types::Nullable<diesel::sql_types::Text>, _>` is not implemented for `*const str`
     */
    let limited = employee
        .select(name)
        .order(employee_id)
        .limit(1)
        .load::<String>(&connection)?;
    println!("{}", limited);
}

Am I missing something ? Can anybody correct me ?
Thanks!

Overset

Answer 1

First of all: Your code is missing a crucial information to actual reproduce the problems described there. Without knowing your underlying database schema it's only possible to guess how that looked like. For my answer I will assume the following schema:

table! {
    employee(employee_id) {
        employee_id -> Integer,
        name -> Nullable<Text>,
        age -> Nullable<Integer>,
        address -> Nullable<Text>,
        email -> Nullable<Text>,
        dept_id -> Integer,
        salary -> Nullable<Numeric>,
        created_on -> Nullable<Timestamp>,
        created_by -> Nullable<Text>,
        modified_on -> Nullable<Timestamp>,
        modified_by -> Nullable<Text>,
        is_active -> Nullable<Bool>,
    }
}

Now as general rule: Diesel uses this definition as source of truth and only supports certain type mappings. If you encounter an compile error that likely means one of the following things are wrong:

• You've tried to map the result of an query to a struct containing incompatible fields for some returned column
• You've tried to map the result of an query to a struct containing more or less fields than returned by your query

To answer your specific error messages:

    //----------------------------------------------
    //get employees count
    /*
    Error: error[E0282]: type annotations needed
           ^^^^^^^^^^^^^^^ consider giving `total_employees` a type
    */
    let total_employees = employee.count().get_result(&connection).expect("Error");
    println!("{}", total_employees);

Rustc needs to know the type of total_employees here as get_result returns a generic type and println consumes a generic type on the other hand. Rustc needs to know exactly which type should be used there. Now the diesel documentation is a bit sparse here on what's the correct return type, but the error message in Daniel Porteous indicates that this query returns a BigInt, which is compatible to a i64 as documented here. That means this query would work:

    let total_employees: i64 = employee.count().get_result(&connection).expect("Error");

You next three queries fail to compile esentially with the same error message:

    //-----------------------------------------------
     //get all names
    /*
        Error: error[E0277]: the trait bound `*const str: FromSql<diesel::sql_types::Nullable<diesel::sql_types::Text>, _>` is not satisfied
         ^^^^ the trait `FromSql<diesel::sql_types::Nullable<diesel::sql_types::Text>, _>` is not implemented for `*const str`
    */
    let all_names = employee.select(name).load::<String>(&connection)?;
    println!("{}", all_names);


    //----------------------------------------------
    //order name
    /*
    Error: error[E0277]: the trait bound `*const str: FromSql<diesel::sql_types::Nullable<diesel::sql_types::Text>, _>` is not          satisfied
    ^^^^ the trait `FromSql<diesel::sql_types::Nullable<diesel::sql_types::Text>, _>` is not implemented for `*const str`
    */
    let ordered_names = employee
        .select(name)
        .order(name.desc())
        .load::<String>(&connection)?;
    println!("{}", ordered_names);


   //------------------------------------------------
   /*
    Error: error[E0277]: the trait bound `*const str: FromSql<diesel::sql_types::Nullable<diesel::sql_types::Text>, _>` is not     satisfied
    ^^^^ the trait `FromSql<diesel::sql_types::Nullable<diesel::sql_types::Text>, _>` is not implemented for `*const str`
     */
    let limited = employee
        .select(name)
        .order(employee_id)
        .limit(1)
        .load::<String>(&connection)?;
    println!("{}", limited);

Now this error messages indicate that you try to map a field returned by a query to an incompatible type on rust side. In this case mapping a Text field that is not NOT NULL to a String is not supported because how would diesel represent a NULL value in this case. The documentation states that you need to wrap nullable types in a Option on rust side. That means if you change the return type to Option<String> in all cases everything will compile successfully.

Ammonate

You got it right. My schema is same as you mentioned above. I have also edited the question and now it has "schema.rs". Sorry I missed that. – Overset

Changing the return type !!! Did you mean to say that my other queries should like like this "let all_names: Option<String> = employee.select(name).load::<String>(&connection)?;" and this for limit: let limited: Option<String> = employee .select(name) .order(employee_id) .limit(1) .load::<String>(&connection)?; Is this way you talking about ? – Overset

By changing the return type I mean changing the type returned by those queries. You already set them by using the turbofish syntax on .load, so just change .load::<String>(&connection)? to .load::<Option<String>>(&connection)?. – Ammonate

Explaining the problem was very helpful, thank you @Ammonate – Unguent

Answer 2

let total_employees = employee.count().get_result(&connection).expect("Error");

For this one, try annotating total_employees, like this:

let total_employees: u64 = employee.count().get_result(&connection).expect("Error");

This will help you proceed. Even if the function doesn't return a type like this, the error will now at least tell you what kind it is.

Newness

I notice for loading your employees, you use the turbofish, like .load::<Employee>(). Do you need that for count() too? – Newness

It's not working. I am still getting an error "^^^^^^^^^^ the trait FromSql<diesel::sql_types::BigInt, Pg> is not implemented for u64" – Overset

This answer is misleading as u64 is not a type supported by diesel (as the error message is telling you) – Ammonate

The answer helps because now the question asker knows that they need to use BigInt. The general advice was to add a type annotation to get the compiler to tell you what type you're working with. – Newness