Add examples of using QueryBuilder generically #4188
Description
I have found these related issues/pull requests
Tangentially related to #3733 and #1978, but this seems to be a unique ask.
Description
I have a service I'm developing that runs Postgres in production, but we want to be able to use Sqlite for local development.
QueryBuilder seems well-suited for this, providing a push_bind() method that knows whether to use a ? or a $1, $2, etc. by the magic of Arguments::format_placeholder(). However, I'm having a hard time with trait bounds and lifetimes when trying to code a function around this.
In this minimal example, I have a database with just a single table, test1(line INT, response VARCHAR(256)). Here's the full code of the example:
use sqlx::{
ColumnIndex, ConnectOptions, Database, Decode, Encode, Executor, IntoArguments, QueryBuilder, Row, Type,
sqlite::{SqliteConnectOptions, SqliteConnection},
};
#[tokio::main]
async fn main() {
let mut c = SqliteConnectOptions::new().filename("test.sqlite").connect().await.unwrap();
query_direct(&mut c, 100).await;
query_generic(&mut c, 100).await;
}
async fn query_direct(c: &mut SqliteConnection, line: i32) {
let row = QueryBuilder::new("SELECT response FROM test1 WHERE line = ")
.push_bind(line)
.build()
.fetch_one(c)
.await
.unwrap();
let response: String = row.get("response");
println!("response: {}", response);
}
async fn query_generic<'c, E>(c: E, line: i32)
where
E: Executor<'c>,
for<'a> <<E as Executor<'c>>::Database as Database>::Arguments<'a>:
IntoArguments<'a, <E as Executor<'c>>::Database>,
for<'a> i32: Encode<'a, <E as Executor<'c>>::Database> + Type<<E as Executor<'c>>::Database>,
for<'a> String: Decode<'a, <E as Executor<'c>>::Database> + Type<<E as Executor<'c>>::Database>,
str: ColumnIndex<<<E as Executor<'c>>::Database as Database>::Row>,
{
// rustc suggests using this let binding to avoid a lifetime issue, but it doesn't help...
let mut qb = QueryBuilder::new("SELECT response FROM test1 WHERE line = ");
let row = qb
.push_bind(line)
.build()
.fetch_one(c)
.await
.unwrap();
let response: String = row.get("response");
println!("response: {}", response);
}As is evident, coming up with the trait bounds for the generic version of the query is difficult—I only got there through error messages from cargo build. I'm assuming that "I'm holding it wrong" here, but I've not been able to find examples (here, in the larger GitHub corpus, Discord, etc.) demonstrating how to use this.
Even with the generics, etc., this example doesn't quite compile. This still has a lifetime issue:
error[E0597]: `qb` does not live long enough
--> src/main.rs:35:15
|
34 | let mut qb = QueryBuilder::new("SELECT response FROM test1 WHERE line = ");
| ------ binding `qb` declared here
35 | let row = qb
| ^^ borrowed value does not live long enough
...
43 | }
| -
| |
| `qb` dropped here while still borrowed
| borrow might be used here, when `qb` is dropped and runs the destructor for type `QueryBuilder<'_, <E as Executor<'_>>::Database>`
There are also other issues with using "response" as a column index (which can be solved by using integer references and replacing the str: ColumnIndex<...> bound with usize: ColumnIndex<...>, but I'd like to try to keep using named columns):
error[E0277]: the trait bound `str: ColumnIndex<SqliteRow>` is not satisfied
--> src/main.rs:10:5
|
10 | query_generic(&mut c, 100).await;
| ^^^^^^^^^^^^^^^^^^^^^^^^^^ the trait `ColumnIndex<SqliteRow>` is not implemented for `str`
Obviously, there are ways to implement this specifically for each database as in the query_direct() code, but that defeats the purpose of testing code locally vs. in production.
Prefered solution
Ideally, an example in the /examples directory showing how to approach this would be terrific! However, I'd be happy to get an answer here and contribute back via a PR.
Is this a breaking change? Why or why not?
No. No code changes are requested; just an example of how to use.