CODEWITHSUNDEEP
databasesqliterustrusqlite
Felipe
Felipe

Reputation: 17181

Using the rusqlite crate, can I pass an array as a parameter for a clause containing 'IN'?

I am using the rusqlite crate in Rust to perform SQL queries on an SQLite database. I have a query that needs to filter rows based on a list of values using the IN clause. I would like to know if there is a way to pass an array or a vector as a parameter for the IN clause directly, instead of creating placeholders for each element in the list.

For example, let's say I have the following query:

SELECT * FROM articles WHERE url IN (?);

I would like to pass a Rust vector as a parameter for the ? placeholder, like this:

let urls: Vec<String> = vec![
    "example1.com".to_string(),
    "example2.com".to_string(),
];

let result: Vec<String> = easy_query!(query, params![urls], Vec<String>)?;

Is there a more straightforward way to achieve this without having to create placeholders for each element in the array or vector and passing them as separate parameters?

Thank you in advance for your help.

Note: The easy_query! macro shown in the example is not part of the rusqlite crate. It is a custom syntactic sugar I'm using to simplify the query process with less verbosity. The question is still focused on passing an array or a vector as a parameter for the IN clause directly in rusqlite.

Upvotes: 1

Views: 1335

Answers (2)

gwen
gwen

Reputation: 46

For SQLite, there is the carray extension. And for rusqlite, there is the rarray extension.

Upvotes: 3

Paul Maxwell
Paul Maxwell

Reputation: 35603

There does not appear to be any way to directly pass an array into SQLite's IN, you do appear to need placeholders but you can make that relatively simple by using std::iter::repeat() and collect::<Vec<_>>().join(",") something like:

use rusqlite::{params, Connection};

fn main() -> Result<(), Box<dyn std::error::Error>> {
    let conn = Connection::open_in_memory()?;

    let names = vec!["Alice", "Bob", "Charlie"];
    let placeholders: String = std::iter::repeat("?").take(names.len()).collect::<Vec<_>>().join(",");

    let ids: Vec<i64> = conn
        .prepare(&format!("SELECT id FROM users WHERE name IN ({})", placeholders))
        .unwrap()
        .query_map(names, |row| row.get(0))?
        .map(|id| id.unwrap())
        .collect();

    println!("IDs: {:?}", ids);

    Ok(())
}

playground (just shows generated placeholders)

nb: Postgres does support use of arrays, but for this uses ANY instead of IN e.g.

SELECT * FROM my_table WHERE my_column = ANY(ARRAY[1, 2, 3])

but SQLite (to my limited knowledge of that product) does not offer it.

Upvotes: 1

Related Questions