Dynamic SQL queries in sqlx

[mydoghasfleas]

Rust newbie here (though it seems I have been a newbie for an awful long time and will continue to be! )

Using the sqlx library in Rust and PostgreSQL, I would like to perform some dynamic queries, i.e. the tables and columns and WHERE clauses will not be known until runtime.

When I use .execute, I get back a PgQueryResult and when I use .fetch_all I get back a Vec<PgRow>. But neither seems to offer a way to iterate over rows returned and then identify the columns from the response metadata and obtain the field values individually.

    // Returns PgQueryResult
    let result  = sqlx::query("select * from category")
    .execute(&pool)
    .await.unwrap();

    // Returns Vec<PgRow>
    let results = sqlx::query("select * from category")
    .fetch_all(&pool)
    .await.unwrap();

There is an example pointed in the sqlx README which seems to come close:

// provides `try_next`
use futures::TryStreamExt;

let mut rows = sqlx::query("SELECT * FROM users WHERE email = ?")
    .bind(email)
    .fetch(&mut conn);

while let Some(row) = rows.try_next().await? {
    // map the row into a user-defined domain type
    let email: &str = row.try_get("email")?;
}

But this does not work for me. (There is no try_get function).

But even this suggests that I need, at compile time, to know what the data looks like in order to map it.

Perhaps there is an example I have not seen of how to do this completely dynamically, i.e. obtain a list from the column, then obtain each field individually using that for each row.

Also, what worries me a little about that last example with the stream: does it go back to the database to fetch every row? I wouldn't want that.

[mydoghasfleas]

UPDATE: I found out that to get try_get to work requires using the sqlx::Row trait. That was quite helpful.

[weiznich]

As a general note: SQLx is not really designed to be used with dynamic queries like that. You essentially loose all the advantages it offers (compile time checks) and fall back to a low level string based query building interface. That is generally agreed to be error prone and might result in possible security issues if you build your queries via format! and use user supplied values there.

Instead prefer using a real query builder like SeaQuery or Diesel. The later also offers extensive (more extensive than sqlx) compile time checks.

[mydoghasfleas]

Thanks for the reply. I think I might go with tokio_postgres.
I am looking specifically for something raw, the reason being that I am using Rust to build a system (I heard somewhere it's a systems programming language ) rather than an application.
So, whereas the application layer would typically be concerned with knowing the details of the database tables, the system layer is not.
The system in my context must provide the database connectivity while the application layer built on top of it will just use that functionality it exposes.
Sorry, that is probably an unnecessary and uninteresting explanation as most people are used to just building their applications in Rust, but I am after something different.