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!

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.

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.