How do I make and parse a raw SQL query with Diesel?

Question

I'm testing out Rust using the Rocket framework. For the database, I'm using Diesel to interact with my MySQL database.

Following a few examples, there are a lot of things happening under the hood. I've got a MySQL Database running with a populated tables that I'd like to make a raw query on.

The below code works:

use diesel::{prelude::*};

mod schema {
    table! {
        organization {
            id -> Nullable<Integer>,
            name -> Text,
            country -> Text,
        }
    }
}

use self::schema::organization;
use self::schema::organization::dsl::{organization as all_orgs};

#[table_name="organization"]
#[derive(Serialize, Deserialize, Queryable, Insertable, Debug, Clone)]
pub struct Organization {
    pub id: Option<i32>,
    pub name: String,
    pub country: String
}

impl Organization {
    pub fn all(conn: &MysqlConnection) -> Vec<Organization> {
        all_orgs.order(organization).load::<Organization>(conn).unwrap()
    }
}

However, I do not need to order by anything. In fact, I'd just like to make a raw query as SELECT id, name, country FROM organization and call it a day.

I've attempted the below after looking at Diesel documentation and found something called sql_query.

My code now looks like:

use diesel::{prelude::*, sql_query};

mod schema {
    table! {
        organization {
            id -> Nullable<Integer>,
            name -> Text,
            country -> Text,
        }
    }
}

use self::schema::organization;

#[table_name="organization"]
#[derive(Serialize, Deserialize, Queryable, Insertable, Debug, Clone)]
pub struct Organization {
    pub id: Option<i32>,
    pub name: String,
    pub country: String
}

impl Organization {
    pub fn all(conn: &MysqlConnection) -> Vec<Organization> {
         sql_query("SELECT id, name, country FROM organization")
            .load(&conn).unwrap()
    }
}

However, upon doing cargo run on on this change, I am faced with:

error[E0277]: the trait bound `&diesel::MysqlConnection: diesel::Connection` is not satisfied
  --> src/org.rs:26:19
   |
26 |             .load(&conn).unwrap()
   |                   -^^^^
   |                   |
   |                   the trait `diesel::Connection` is not implemented for `&diesel::MysqlConnection`
   |                   help: consider removing the leading `&`-reference
   |
   = help: the following implementations were found:
             <diesel::MysqlConnection as diesel::Connection>
   = note: required because of the requirements on the impl of `LoadQuery<&diesel::MysqlConnection, _>` for `SqlQuery`

error[E0277]: the trait bound `Organization: QueryableByName<_>` is not satisfied
  --> src/org.rs:26:14
   |
26 |             .load(&conn).unwrap()
   |              ^^^^ the trait `QueryableByName<_>` is not implemented for `Organization`
   |
   = note: required because of the requirements on the impl of `LoadQuery<&diesel::MysqlConnection, Organization>` for `SqlQuery`

How do I satisfy these errors, while being allowed to do a raw SQL query and parse it correctly from the function, given the input parameters and desired output?

Answer 1

To solve your problem, you can remove the .order(organization) to avoid performing a sort.

To answer the question explicitly asked: The error messages mention two problems:

error[E0277]: the trait bound `&diesel::MysqlConnection: diesel::Connection` is not satisfied
  --> src/org.rs:26:19
   |
26 |             .load(&conn).unwrap()
   |                   -^^^^
   |                   |
   |                   the trait `diesel::Connection` is not implemented for `&diesel::MysqlConnection`
   |                   help: consider removing the leading `&`-reference
   |
   = help: the following implementations were found:
             <diesel::MysqlConnection as diesel::Connection>
   = note: required because of the requirements on the impl of `LoadQuery<&diesel::MysqlConnection, _>` for `SqlQuery`

This one is easy to fix: do what the compiler suggests and remove the leading & reference for your call to .load(&conn).

error[E0277]: the trait bound `Organization: QueryableByName<_>` is not satisfied
  --> src/org.rs:26:14
   |
26 |             .load(&conn).unwrap()
   |              ^^^^ the trait `QueryableByName<_>` is not implemented for `Organization`
   |
   = note: required because of the requirements on the impl of `LoadQuery<&diesel::MysqlConnection, Organization>` for `SqlQuery`

This one points to the actual problem that disallows loading results in your Organization struct. Diesel needs to be told how to construct your Organization struct out of the query response. Diesel provides 2 traits for this :

• Queryable for constructing a struct out of the result of a type safe query (== a query build by using the dsl provided by diesel, so that it is checked at compile time). `
• QueryableByName to construct a struct out of the result of a non-type safe query (so basically a query executed via diesel::sql_query)

As the error message mentions, your struct does not implement QueryableByName, which is required to load the result of diesel::sql_query in that struct. You need to implement this trait. The easiest option is to use the derive provided by Diesel.

A fixed version of your code would look like this then:

use diesel::{prelude::*, sql_query};

mod schema {
    table! {
        organization {
            id -> Nullable<Integer>,
            name -> Text,
            country -> Text,
        }
    }
}

use self::schema::organization;

#[table_name="organization"]
#[derive(Serialize, Deserialize, Queryable, QueryableByName, Insertable, Debug, Clone)]
pub struct Organization {
    pub id: Option<i32>,
    pub name: String,
    pub country: String
}

impl Organization {
    pub fn all(conn: &MysqlConnection) -> Vec<Organization> {
         sql_query("SELECT id, name, country FROM organization")
            .load(&conn).unwrap()
    }
}