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?

Answer 1

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?;