6

Note: this is a similar but NOT duplicate question with How to use sqlx to query mysql IN a slice?. I'm asking for the Rust one.

This is what I try to do.

let v = vec![..];
sqlx::query("SELECT something FROM table WHERE column IN (?)").bind(v)
 ...

Then I got the following error

the trait bound `std::vec::Vec<u64>: sqlx::Encode<'_, _>` is not satisfied
Jason Lee
  • 502
  • 6
  • 15
  • You currently can't. – Ivan C Nov 19 '21 at 07:23
  • Any workaround (making the SQL query manually? Any tips of preventing injection if I do so?) is also welcomed. – Jason Lee Nov 19 '21 at 07:41
  • According to the [docs](https://docs.rs/sqlx/0.5.9/sqlx/trait.Encode.html#impl-Encode%3C%27q%2C%20Postgres%3E-for-Vec%3CT%2C%20Global%3E), it looks like this is only possible for the PostgreSQL backend. – Jmb Nov 19 '21 at 07:52

2 Answers2

6

Answer is in first on FAQ https://github.com/launchbadge/sqlx/blob/master/FAQ.md

How can I do a SELECT ... WHERE foo IN (...) query? In 0.6 SQLx will support binding arrays as a comma-separated list for every database, but unfortunately there's no general solution for that currently in SQLx itself. You would need to manually generate the query, at which point it cannot be used with the macros.

Zeppi
  • 1,175
  • 6
  • 11
4

The error shows Vec is not an Encode that is required to be as a valid DB value. The Encode doc lists all the Rust types that have implemented the trait. Vec is not one.

You can use the following way to bind the parameters in IN with the values of a vector. Firstly, you need to expand the number of '?' in the IN expression to be the same number of the parameters. Then, you need to call bind to bind the values one by one.

let v = vec![1, 2];

let params = format!("?{}", ", ?".repeat(v.len()-1));
let query_str = format!("SELECT id FROM test_table WHERE id IN ( { } )", params);

let mut query = sqlx::query(&query_str);
for i in v {
    query = query.bind(i);
}

let row = query.fetch_all(&pool).await?;

Please note if the target database is not MySql, you need to use $n, like $1, $2, instead of ?, as the parameter placeholder.

Joe_Jingyu
  • 1,024
  • 6
  • 9