Rusqlite rejecting Option type in executing query

Fairly new to Rust and trying to insert CSV data into a SQLite database. One of the fields (called 'stripe_id') is optional, and so its type is Option<&str>. All the other fields are &str. When I use conn.execute to insert the values, they all insert correctly except for stripe_id, which throws an error saying it expects &str and not std::option::Option. I've put in code snippets below.

I've searched the docs and Option implements ToSQL, and when I've tried replacing my code with the Rusqlite example that includes an Option value, it throws the same error for the example code.

The relevant struct and query code snippet:

struct Merchant<'a> {
    name: &'a str,
    billing_portal: &'a str,
    billing_period: &'a str,
    stripe_id: Option<&'a str>,
}

conn.execute(
            "INSERT INTO merchants (name, billing_portal, billing_period, stripe_id)
             values (?, ?, ?, ?)",
             &[&merch.name, &merch.billing_portal, &merch.billing_period, &merch.stripe_id]
        ).expect("Error inserting merchant into database");

The error:

error[E0308]: mismatched types
  --> src/main.rs:38:75
   |
38 |              &[&merch.name, &merch.billing_portal, &merch.billing_period, &merch.stripe_id]
   |                                                                           ^^^^^^^^^^^^^^^^ expected `&str`, found enum `std::option::Option`
   |
   = note: expected reference `&&str`
              found reference `&std::option::Option<&str>`

And the complete code:

extern crate csv;
extern crate rusqlite;

use rusqlite::{Connection, Result};

#[derive(Debug)]
struct Merchant<'a> {
    name: &'a str,
    billing_portal: &'a str,
    billing_period: &'a str,
    stripe_id: Option<&'a str>,
}

fn main() -> Result<()> {
    let conn = Connection::open("data.sqlite")?;

    let mut reader = csv::ReaderBuilder::new()
                                        .has_headers(false)
                                        .from_path("merchants.csv")
                                        .expect("Failed to read csv");
    for record in reader.records() {
        let record = record.unwrap();
        let merch = Merchant {
            name: &record[0],
            billing_portal: &record[3],
            billing_period: &record[4],
            stripe_id: (match &record[5] {
                x if x == "" => None,
                x            => Some(x)
            }),
        };
        println!("{:?}", &merch);

        conn.execute(
            "INSERT INTO merchants (name, billing_portal, billing_period, stripe_id)
             values (?, ?, ?, ?)",
             &[&merch.name, &merch.billing_portal, &merch.billing_period, &merch.stripe_id]
        ).expect("Error inserting merchant into database");

    }

    Ok(())
}

Any help is greatly appreciated! I'm tearing my hair out.

The problem is that you're trying to create a slice, and all of the values in the slice have to have the same type - the other ones are &&strs, so the last one needs to be as well. You might be able to cast them to &dyn ToSql.

e: That works - playground link here.

1 Like

I had the suspicion that was the problem, since the "execute" block seems like I'm string building. Its just odd to me that none of the example code shows the need to cast to &dyn ToSql.

Over on stack overflow, someone pointed out that if I use rusqlite's params macro, it fixes the issue. I've gone with that for now, as it seems cleaner:

        conn.execute(
        "INSERT INTO merchants (name, billing_portal, billing_period, stripe_id)
         values (?, ?, ?, ?)",
         params![&merch.name, &merch.billing_portal, &merch.billing_period, &merch.stripe_id]
    ).expect("Error inserting merchant into database");

As asymmetrikon said before, this is not a rusqlite-specific issue, it's just the fact that arrays (and iterables in general) in Rust must be homogenious. And execute requires IntoIterator, i.e. it has the ordinary limitations.

However, it's possible to do this without trait objects. I was able to generate two variants - playground (I've replaced the rusqlite-related code with mocks, but the signatures are essentially the same).

First, as you have an Option in one case and raw values in other ones, you can simply wrap every always-presented value in Some:

conn.execute(
    "INSERT INTO merchants (name, billing_portal, billing_period, stripe_id)
    values (?, ?, ?, ?)",
    &[
        Some(merch.name),
        Some(merch.billing_portal),
        Some(merch.billing_period),
        merch.stripe_id,
    ]
).expect("Error inserting merchant into database");

Note also that I've removed referencing, since shared references are Copy and can be copied into the array without invalidating the original struct.

Another approach is slightly more complex. It is based on the fact that Option implements IntoIterator, and so can be treated as any other iterable value. In particular, we can combine two iterables using chain:

conn.execute(
    "INSERT INTO merchants (name, billing_portal, billing_period, stripe_id)
    values (?, ?, ?, ?)",
    [
        merch.name,
        merch.billing_portal,
        merch.billing_period,
    ].into_iter().chain(&merch.stripe_id)
).expect("Error inserting merchant into database");
2 Likes

I'm not sure that second example works; if stripe_id is None, the iterator will only return three values, but I'd bet the execute statement will expect four. Wrapping the other &strs in Options is a good call, though it's a little weird to pass something that could be theoretically null to a place that expects a value (that's kind of inherent in building SQL statements, though.)

Checked rusqlite's code - you're right, it would fail the assertion. Sorry for possible misleading.