Mysql crate row mapping

help
#1

I'm going to do a lot of mysql queries, and I'm looking for a more flexible approach to this:

// Let's select payments from database. Type inference should do the trick here.
let selected_payments = conn
    .query_map(
        "SELECT customer_id, amount, account_name from payment",
        |(customer_id, amount, account_name)| {
            Payment { customer_id, amount, account_name }
        },
    )?;

The problem here is that

  1. I have to explicitly specify which columns I want to retrieve.
  2. I have to manually do the mapping.

It would be cool if the mysql crate could match the resultset columns with the struct's fields automatically. Is there any way to achieve something similar? I know Rust doesn't have reflection, but diesel can do it, so I guess there is a way.

If it's not possible and I have to do the struct instantiaton manually, can I access the resultset fields by name, instead of accessing a tuple? Imagine I want to SELECT * FROM payment, and acces a few columns by name instead of position in the tuple (I know I'm being a bit lazy here, but I just want to avoid errors the current approach may involve).

Any hint appreciated, thanks!

#2

I was going to suggest using Diesel (or any other ORM), but apparently you know about them.

The usual approach to automated, generic handling of struct fields is writing a procedural macro (#[derive(…)] or another kind). In this manner you could generate implementations that resemble the manually-written code, but you still don't need to write the code manually.

A lazy workaround could be to use Serde and the Deserialize derive macro, then map dynamically-named rows/columns to something like a serde_json::Value::Object and deserialize your custom types from these dynamically-typed value objects.

#3

I haven't used it this way but I can imagine you can use QueryResult::columns() to get the index number for given column and then use Row::get() to fetch the value. You can have a helper which constructs a HashMap for each row if you want to have a more convenient access.

#4

It is possible to do something like this:

let selected_payments = conn
    .query_map(
        "SELECT * from payment",
        |mut row: Row| {
            Payment {
                customer_id: row.take("customer_id").unwrap(),
                amount: row.take("amount").unwrap(),
                account_name: row.take("account_name").unwrap(),
            }
        },
    )?;

I believe some trivial macro should hide the boilerplate code here.

1 Like
#5

Awesome, I think that would do it, thanks a lot.

I've also stumbled upon Sqlx, which looks like it will be handy in order to eliminate boilerplate, as it seems it has those macros implemented.