Rust Query bb8_postgres with integer params

Question

I get the error expected &(dyn ToSql + Sync), found i32 when passing an integer as a param to query a row in a PostgreSQL database on the following line:

let rows = client.query("SELECT value FROM test WHERE id = $1;", &[i]).await.unwrap();

Complete Code:

async fn test_postgresql() -> (Duration, Duration) {

    let pg_mgr = PostgresConnectionManager::new_from_stringlike(
        "postgresql://postgres:postgres@localhost:5432/my_db",
        NoTls,
    )
    .unwrap();

    let pool = match Pool::builder().build(pg_mgr).await {
        Ok(pool) => pool,
        Err(e) => panic!("bb8 error {e}"),
    };

    println!("Writing");
    let write_duration = test_postgresql_write(&pool).await;
    println!("Reading");
    let read_duration = test_postgresql_read(&pool).await;

    (write_duration, read_duration)
}


async fn test_postgresql_write(pool: &bb8::Pool<bb8_postgres::PostgresConnectionManager<NoTls>>) -> Duration {
    let client = pool.get().await.unwrap();

    client.batch_execute("DROP TABLE IF EXISTS test;").await.unwrap();
    client.batch_execute("CREATE TABLE test (id SERIAL PRIMARY KEY, value TEXT);").await.unwrap();

    let start_time = Instant::now();

    for i in 0..1000 {
        let value = format!("value_{}", i);
        client.execute("INSERT INTO test (value) VALUES ($1);", &[&value]).await.unwrap();
    }

    start_time.elapsed()
}


async fn test_postgresql_read(pool: &bb8::Pool<bb8_postgres::PostgresConnectionManager<NoTls>>) -> Duration {
    let client = pool.get().await.unwrap();

    let start_time = Instant::now();

    for i in 0..1000 {
        let id = i + 1;
        // Line with Error
        let rows = client.query("SELECT value FROM test WHERE id = $1;", &[id]).await.unwrap();
        let value: &str = rows[0].get(0);
        assert_eq!(value, format!("value_{}", i));
    }

    start_time.elapsed()
}

How do I convert the integer into the right type for the query?

Add a `&` if front of the `i` (like you did for `value` 10 lines above): `&[&i]`? — Jmb, May 02 '23 at 15:55
having `&[&i]` results in `expected &(dyn ToSql + Sync), found &i32`, which is the same message but with `&i32` instead of `i32`. I'm not sure what to do with the `ToSql + Sync`. Other examples seem to require it being bound to traits. — DogEatDog, May 02 '23 at 17:00

score 0 · Accepted Answer · answered May 02 '23 at 18:08

I was able to work-around this issue by not passing any parameters at all and encoding them into the query string. It would be nicer to allow the parameters to be passed in the params field of the client.query function.

Here is a alternative Solution:

async fn test_postgresql_read(
    pool: &bb8::Pool<bb8_postgres::PostgresConnectionManager<NoTls>>,
) -> Duration {
    let client = pool.get().await.unwrap();

    let start_time = Instant::now();

    for i in 0..1000 {
        let statement = format!("SELECT value FROM test WHERE id = {}", i + 1);
        let select = client.prepare(&statement).await.unwrap();
        let rows = client.query(&select, &[]).await.unwrap();
        let value: &str = rows[0].get(0);
        assert_eq!(value, format!("value_{}", i));
    }

    start_time.elapsed()
}

Rust Query bb8_postgres with integer params

1 Answers1