Compiling with `QueryBuilder::push_values` based on `AnyPool` reports an error

[EthanYuan]

I want to use QueryBuilder::push_values based on AnyPool.

Based on SqlitePoolOptions, the following code using QueryBuilder::push_values ​​can be compiled and executed correctly, but only if SqlitePoolOptions is replaced with AnyPoolOptions, the compilation will report an error. Are there any restrictions?

error[E0597]: `query_builder` does not live long enough
  --> src/main.rs:32:17
   |
32 |     let query = query_builder.build();
   |                 ^^^^^^^^^^^^^^^^^^^^^ borrowed value does not live long enough
...
51 | }
   | -
   | |
   | `query_builder` dropped here while still borrowed
   | borrow might be used here, when `query_builder` is dropped and runs the destructor for type `QueryBuilder<'_, sqlx::Any>`

The following code works fine based on SqlitePoolOptions:

use sqlx::any::AnyPoolOptions;
use sqlx::sqlite::SqlitePoolOptions;
use sqlx::{QueryBuilder, Row};

#[tokio::main]
async fn main() {
    let pool_options = SqlitePoolOptions::new();
    let uri = "sqlite://:memory:";
    let pool = pool_options.connect(uri).await.unwrap();
    let mut tx = pool.begin().await.unwrap();

    // create table
    sqlx::query(
        "CREATE TABLE users(
        user_id int PRIMARY KEY,
        user_age int,
        user_weight int NOT NULL
    )",
    )
    .execute(&mut tx)
    .await
    .unwrap();

    // insert
    let users = (0..).map(|i: i32| (i, i + 1, i + 2));
    let mut query_builder = QueryBuilder::new("INSERT INTO users(user_id, user_age, user_weight)");
    query_builder.push_values(users.into_iter().take(20), |mut b, user| {
        b.push_bind(user.0);
        b.push_bind(user.1);
        b.push_bind(user.2);
    });
    let query = query_builder.build();
    query.execute(&mut tx).await.unwrap();

    // fetch
    let ret_fetch = sqlx::query("SELECT * from users")
        .fetch_all(&mut tx)
        .await
        .unwrap();

    // commit
    tx.commit().await.unwrap();

    assert_eq!(20, ret_fetch.len());
    assert_eq!(Some(0), ret_fetch[0].get::<Option<i32>, _>("user_id"));
    assert_eq!(Some(1), ret_fetch[0].get::<Option<i32>, _>("user_age"));
    assert_eq!(Some(2), ret_fetch[0].get::<Option<i32>, _>("user_weight"));
    assert_eq!(Some(1), ret_fetch[1].get::<Option<i32>, _>("user_id"));
    assert_eq!(Some(2), ret_fetch[1].get::<Option<i32>, _>("user_age"));
    assert_eq!(Some(3), ret_fetch[1].get::<Option<i32>, _>("user_weight"));
}

[dubek]

Or, put another way, is there a way to get a QueryBuilder of the correct kind, at runtime, from a instance of AnyPool or AnyConnection ? something like:

let pool_options = SqlitePoolOptions::new();
let uri = "sqlite://:memory:";
let pool = pool_options.connect(uri).await.unwrap();

let mut query_builder = pool.create_query_builder();  // <---- this is the function I'm looking for :-)

query_builder.push("SELECT * FROM users WHERE user_id = ");
query_builder.push_bind(12345i32);
let query = query_builder.build();
let sql = query.sql();

// If the pool is connected to mysql/sqlite this will print:
//     SELECT * FROM users WHERE user_id = ?
// but if it's connected to postgres it'll print:
//     SELECT * FROM users WHERE user_id = $1
println!("sql={}", sql);

Thanks!

[tmpfs]

I hit the same problem here. It appears that without this fixed the Any types can't be used in practice.

Any idea how this could be fixed so I could use the Any types and build my queries using QueryBuilder?

[tgmichel]

+1 also noticed this issue

[mradley3002]

It’s possible to recreate this problem without even adding a bind parameter. If you simply call let query = QueryBuilder::new(data), and then query.build(), compilation will fail where the concrete (non-lifetime) type of the builder is Any. I’m struggling to see how one can effectively use the Any type in any serious practical sense while this bug remains unresolved.

[EthanYuan]

With version 0.7, the previous example works fine

[dependencies]
seq-macro = "0.3"
sqlx = { version = "0.7", features = ["runtime-tokio-native-tls", "any", "sqlite", "postgres"] }
tokio = { version = "1", features = ["macros", "rt-multi-thread", "sync"] }

use seq_macro::seq;
use sqlx::any::{install_default_drivers, AnyPoolOptions};
use sqlx::{QueryBuilder, Row};

#[tokio::main]
async fn main() {
    install_default_drivers();
    let pool_options = AnyPoolOptions::new();
    let uri = "sqlite:file:foo?mode=memory";
    let pool = pool_options.connect(uri).await.unwrap();
    let mut tx = pool.begin().await.unwrap();

    // create table
    sqlx::query(
        "CREATE TABLE users(
        user_id int PRIMARY KEY,
        user_age int,
        user_weight int NOT NULL
    )",
    )
    .execute(&mut *tx)
    .await
    .unwrap();

    // insert
    let users = (0..).map(|i: i32| (i, i + 1, i + 2));
    let mut query_builder = QueryBuilder::new("INSERT INTO users(user_id, user_age, user_weight)");
    query_builder.push_values(users.into_iter().take(20), |mut b, user| {
        seq!(i in 0..=2 {
            b.push_bind(user.i);
        });
    });
    let query = query_builder.build();
    query.execute(&mut *tx).await.unwrap();

    // fetch
    let ret_fetch = sqlx::query("SELECT * from users")
        .fetch_all(&mut *tx)
        .await
        .unwrap();

    // commit
    tx.commit().await.unwrap();

    assert_eq!(20, ret_fetch.len());
    assert_eq!(Some(0), ret_fetch[0].get::<Option<i32>, _>("user_id"));
    assert_eq!(Some(1), ret_fetch[0].get::<Option<i32>, _>("user_age"));
    assert_eq!(Some(2), ret_fetch[0].get::<Option<i32>, _>("user_weight"));
    assert_eq!(Some(1), ret_fetch[1].get::<Option<i32>, _>("user_id"));
    assert_eq!(Some(2), ret_fetch[1].get::<Option<i32>, _>("user_age"));
    assert_eq!(Some(3), ret_fetch[1].get::<Option<i32>, _>("user_weight"));
}