Select query returns no data

Question

I have the following code:

use sqlx::mysql::*;

mod db;

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    println!("Hello, world!");

    let pool = MySqlPoolOptions::new()
        .max_connections(5)
        .connect("connection-string").await?;

    #[derive(Debug, PartialEq, Eq)]
    struct Room {
        name: String
    }
    let mut rooms: Vec<Room> = vec![];

    let mut stream = sqlx::query("SELECT name FROM rooms")
        .map(|row: MySqlRow| {
            // map the row into a user-defined domain type
            //rooms.push( Room { row.name } );
            println!("Tester print");
        })
        .fetch(&pool);

    println!("{:?}", rooms);
    Ok(())
}

it seems to connect, it doesnt error out, but its not getting any data, or at least the print inside the map function is not getting executed. Anyone know why?

Because `fetch` returns a `Stream` that doesn't do anything until you consume it with e.g. `collect` or `for_each`. — Jmb, Apr 05 '22 at 15:00
the example on the git read me uses the map, i thought that would loop through them, similar to map in js. is that not the case? — discodowney, Apr 05 '22 at 15:01
Map will apply operation to each element as it is being consumed. In rust iterators are lazy. — Ivan C, Apr 05 '22 at 15:41
so I should just do something like stream.for_each(|row|{...}); ? — discodowney, Apr 05 '22 at 16:01
If you are expecting multiple rows, it might be better to use `fetch_all()`, otherwise you will need to `collect()` the result into a `Vec` yourself. — Herohtar, Apr 05 '22 at 16:33
Also, you should be returning a value from `map`, not performing a mutation. — Herohtar, Apr 05 '22 at 16:37

score 0 · Answer 1 · answered Apr 06 '22 at 06:37

In sqlx, map and fetch are lazy, meaning that they don't do anything unless and until explicitly consumed. This is a very common pattern in Rust and avoids wasting time if you only need part of the results. For your use-case where you want to store (transformed) values into a Vec, the best way to do this is with collect or fetch_all.

collect applies to all kinds of Streams, not just the results from an sqlx query:

let mut rooms = sqlx::query("SELECT name FROM rooms")
    .map(|row: MySqlRow| { Room { row.name } })
    .fetch(&pool)
    .collect::<Result<Vec<_>>>()
    .await?;

or fetch_all makes for slightly shorter code but is specific to sqlx (if you look at its source code, it does basically the same thing):

let mut rooms = sqlx::query("SELECT name FROM rooms")
    .map(|row: MySqlRow| { Room { row.name } })
    .fetch_all(&pool)
    .await?;

So I was trying stuff like this yesterday. The .map line fails because it says .name doest not exist on row, which makes sense cos its not a Room object. So how do I reference the fields from the query on the row object? — discodowney, Apr 06 '22 at 13:01
That's a different question. Looks like the answer is along the lines of `row.get:: (0)` or use `query_as` to automatically convert rows to tuples `(String,)` and use `row.0` in `map` or implement [`FromRow`](https://docs.rs/sqlx/latest/sqlx/trait.FromRow.html) for `Room` and dispense with the `map` altogether. — Jmb, Apr 06 '22 at 14:43

Select query returns no data

1 Answers1

Linked