Why does Rusqlite reject the Option type when executing a query?

Question

I'm trying to insert CSV data into a SQLite database. 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 Option.

I've searched the docs and Option<T> 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(())
}

Welcome to Stack Overflow! It looks like your question might be answered by the answers of [Cannot call rusqlite's query because it expects the type &\[&rusqlite::types::ToSql\]](https://stackoverflow.com/q/46624591/155423). If not, please **[edit]** your question to explain the differences. Otherwise, we can mark this question as already answered. — Shepmaster, Jan 24 '20 at 16:10
TL;DR the duplicate: `&[&merch.name as &ToSql, &merch.billing_portal as &ToSql, &merch.billing_period as &ToSql, &merch.stripe_id as &ToSql]` — Shepmaster, Jan 24 '20 at 16:11
In the future, please include a [MRE]. This error doesn't require any CSV-related code to reproduce. It will make it easier for us to help you if you try to reproduce your error on the [Rust Playground](https://play.rust-lang.org) if possible, otherwise in a brand new Cargo project, then [edit] your question to include the additional info. There are [Rust-specific MRE tips](//stackoverflow.com/tags/rust/info) you can use to reduce your original code for posting here. Thanks! — Shepmaster, Jan 24 '20 at 16:12
[An example](https://play.rust-lang.org/?version=stable&mode=debug&edition=2018&gist=1fb26c8b690cc772e537d5762189af78) of what a smaller reproduction would look like. — Shepmaster, Jan 24 '20 at 16:15
Thanks for the info! I didn't know if the CSV code was relevent because I didn't have any idea what was causing the error. The duplicate was confusing to me because none of the example code for Rusqlite ever used "&ToSql" and it still worked, so the example might have fixed the problem but without me understanding why it happened. I'll be sure to check out the mentioned resources! — Lee Self, Jan 24 '20 at 16:21
You don't have to know what's causing the error to delete code and see if the error continues. That's the process for creating a MRE. — Shepmaster, Jan 24 '20 at 16:22

score 3 · Accepted Answer · edited Jan 24 '20 at 16:13

Using the rusqlite::params macro solves the problem:

use rusqlite::{params, Connection, Result};

fn main() -> Result<()> {
    // ...

    for record in reader.records() {
        // ...

        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");
    }

    Ok(())
}

Why does Rusqlite reject the Option type when executing a query?

1 Answers1

Linked