Raw SQL queries with Diesel

help
1

Using Rust with Diesel, I want to fetch all records of a table. The below code works as intended:

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.load::<Organization>(conn).unwrap()
    }
}

However, how would I express this in raw SQL? This is primarily motivated by learning Rust/Diesel together, and wanting to benchmark performance.

Essentially, I want to do use the query:

SELECT id, name, country FROM organization and parse that into Vec<Organization> and return that from the given function.

Being new to Rust, I have no idea how to navigate around Diesel and make it happy! Some guidance would be greatly appreciated. I've looked into diesel::sql_query on here: https://docs.diesel.rs/diesel/fn.sql_query.html, however changing my code to resemble that doesn't make it happy, code example below:

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> {
        let orgs = sql_query("SELECT id, name, country FROM organization").load(&conn);
        orgs.unwrap()
    }
}

It results in a bunch of errors, namely:

error[E0277]: the trait bound `&diesel::MysqlConnection: diesel::Connection` is not satisfied
  --> src/org.rs:26:81
   |
26 |         let orgs = sql_query("SELECT id, name, country FROM organization").load(&conn);
   |                                                                                 -^^^^
   |                                                                                 |
   |                                                                                 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:76
   |
26 |         let orgs = sql_query("SELECT id, name, country FROM organization").load(&conn);
   |                                                                            ^^^^ the trait `QueryableByName<_>` is not implemented for `Organization`
   |
   = note: required because of the requirements on the impl of `LoadQuery<&diesel::MysqlConnection, Organization>` for `SqlQuery`

I'm a bit unsure of how to handle the above -- how do I use sql_query and map it to the Organization struct?

2

I don't use MySql or Diesel so this may not help at all. But perhaps there is a clue here.

I don't know what Diesel does, I always assumed it dealt only with "onbjects" not raw SQL queries.

Basically I make raw SQL queries to a postgres database using the postgres crate. Like so:

#[derive(Serialize)]
struct Trip {
    t_stamp: DateTime<Utc>,
    trid: Option<i64>,
    trav_time: Option<f64>,
    trav_dist: Option<f64>,
    stops: Option<i64>,
    object_type: Option<i64>,
    lane: Option<i64>,
}

#[derive(Serialize)]
struct Trips {
    trips: Vec<Trip>,
}

#[get("/public/api/trips/<date>", rank = 1)]
fn api_trips(model: State<Model>, date: String) -> Json<Trips> {
    // Validate date string by parsing into date structure.
    let date_valid = NaiveDate::parse_from_str(&date, "%Y-%m-%d").unwrap();

    let mut conn = model.connect();

    let query = format!(
        "SELECT * FROM trips2 WHERE DATE_TRUNC('day', t_stamp) = '{}'",
        date_valid
    );
    let prepared = conn.prepare(&query).unwrap();

    let rows = conn.query(&prepared, &[]).unwrap();

    let mut results = Trips { trips: vec![] };

    for row in &rows {
        let trip = Trip {
            t_stamp: row.get(0),
            trid: row.get(1),
            trav_time: row.get(2),
            trav_dist: row.get(3),
            stops: row.get(4),
            object_type: row.get(5),
            lane: row.get(6),
        };

        results.trips.push(trip);
    }
    Json(results)
}

As you see, after submitting the query the result is a bunch of rows. I have to iterate over those rows and extract the data from each column to place into my result struct manually.

Presumably there is some mysql crate that operates in about the same way. If Diesel itself does not.

3

For any person seeing this and assuming that this is a valid answer: Never, really never use format! to construct a SQL query and bind parameters. This will fail horribly and open up the possibility of SQL injections. Use prepared statements and explicit binds instead. Both the postgres crate and the mysql crate provide such and API.

Back to the original question: As this question was already asked exactly in this way on stackoverflow, I will just copy my answer from there:

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()
    }
}
1 Like
4

Good point. I think I justified it to myself there because I am not using raw strings from the request to make the query. It has to get through NaiveDate::parse_from_str() first.

Anyway I changed by code to use bindings on the query. Which was a royal pain to get the date/time types to line up correctly:

#[derive(Serialize)]
struct Trips {
    trips: Vec<Trip>,
}

#[get("/public/api/trips/<date>", rank = 1)]
fn api_trips(model: State<Model>, date: String) -> Json<Trips> {
    let _current_count = model.count.fetch_add(1, Ordering::Relaxed);

    // Validate date string by parsing into date structure.
    let naive_date_time = NaiveDate::parse_from_str(&date, "%Y-%m-%d").unwrap().and_hms(0, 0, 0);

    // Query requires a DateTime to for SQL Timestamptz
    let date_time = DateTime::<Utc>::from_utc(naive_date_time, Utc);

    let mut conn = model.connect();
    let rows = conn.query("SELECT * FROM trips WHERE DATE_TRUNC('day', t_stamp) = $1", &[&date_time]).unwrap();
    let mut results = Trips { trips: vec![] };

    for row in &rows {
        let trip = Trip {
            t_stamp: row.get(0),
            trid: row.get(1),
            // ... other stuff.
        };
        results.trips.push(trip);
    }
    Json(results)
}

Which I'm not happy with. I don't like that .and_hms(0, 0, 0) when creating naive_date_time. That seems a bit messy and even returns zero rows of it is a bit wrong, like .and_hms(0, 0, 1)

Anyone see a simpler way to get from the "%Y-%m-%d" in my query string to the DateTime the query needs?

5

Is it simpler for the query to take a bound string, and then use a Postgres internal function to convert it to DateTime?

6

You mean like the postgres TO_TIMESTAMP() function? PostgreSQL TO_TIMESTAMP() Function By Practical Examples

I'm using CockroachDB rather than postgres, it does not have a TO_TIMESTAMP() function. Although it does have a experimental_strftime() which I think I'll skip as it's experimental.

7

This topic was automatically closed 90 days after the last reply. We invite you to open a new topic if you have further questions or comments.