Suggestion: Read data from SQL row based on column name

I find myself repeating a pattern of building a column name to index hash map. I thought I might suggest it as a possible improvement for the SDK. I'm pretty new to WebAssembly and Rust in general so let me know if I'm missing something here.

Example

When I am reading a row from a table and into a common struct, I would like to decouple the logic for converting from a MySQL Row into my struct. However, it seems like the only way to read from a row instance is to use an index on that row. To me, this means that the order of fields in my select statement is now tied directly to the conversion function. I'm also unsure of the expected behavior if I were to use a select * query so I have just been explicitly adding the column names into the select statement. Here's a quick snippet of how I've been doing it thus far.

#[derive(Serialize, Deserialize, Debug)]
pub struct Profile {
    pub id: Option<String>,
    pub handle: String,
    pub avatar: Option<String>,
}

impl Profile {
    fn from_row(row: &spin_sdk::mysql::Row) -> Result<Self> {
        let id = String::decode(&row[0]).ok();
        let handle = String::decode(&row[1])?;
        let avatar = String::decode(&row[2]).ok();
        Ok(Profile {
            id,
            handle,
            avatar,
        })
    }

    pub fn get_by_handle(handle: &str, db_url: &str) -> Result<Profile> {
        let params = vec![ParameterValue::Str(handle)];
        let row_set = mysql::query(db_url, "SELECT id, handle, avatar from profiles WHERE handle = ?", &params)?;
        match row_set.rows.first() {
            Some(row) => Profile::from_row(row),
            None => Err(anyhow!("Profile not found for handle '{:?}'", handle))
        }
    }

    pub fn get_by_id(id: &str, db_url: &str) -> Result<Profile> {
        let params = vec![ParameterValue::Str(id)];
        let row_set = mysql::query(db_url, "SELECT id, handle, avatar from profiles WHERE id = ?", &params)?;
        match row_set.rows.first() {
            Some(row) => Profile::from_row(row),
            None => Err(anyhow!("Profile not found for id '{:?}'", id))
        }
    }
}

Workaround

As this table and therefore struct grows in number of fields, it is going to get a little unwieldy to maintain. Sometime's I find myself using a table or view that has 20+ columns in it. I don't expect the SDK to bind a struct to a Row for me but it would be nice if I could instead read from the row by column name. Currently I can accomplish this by building a lookup of HashMap<&str, usize> and passing that to the conversion function like this:

#[derive(Serialize, Deserialize, Debug)]
pub(crate) struct Profile {
    pub id: Option<String>,
    pub handle: String,
    pub avatar: Option<String>,
}

impl Profile {
    fn from_row(row: &spin_sdk::mysql::Row, columns: &HashMap<&str, usize>) -> Result<Self> {
        let id = String::decode(&row[columns["id"]]).ok();
        let handle = String::decode(&row[columns["handle"]])?;
        let avatar = String::decode(&row[columns["avatar"]]).ok();
        Ok(Profile {
            id,
            handle,
            avatar,
        })
    }

    fn get_column_lookup<'a>(columns: &'a Vec<Column>) -> HashMap<&'a str, usize> {
        columns
            .iter()
            .enumerate()
            .map(|(i, c)| (c.name.as_str(), i))
            .collect::<HashMap<&str, usize>>()
    }

    pub(crate) fn get_by_handle(handle: &str, db_url: &str) -> Result<Profile> {
        let params = vec![ParameterValue::Str(handle)];
        let row_set = mysql::query(db_url, "SELECT * from profiles WHERE handle = ?", &params)?;

        // build the column lookup hashmap
        let columns = get_column_lookup(&row_set.columns);

        match row_set.rows.first() {
            Some(row) => Profile::from_row(row, &columns),
            None => Err(anyhow!("Profile not found for handle '{:?}'", handle))
        }
    }

    pub fn get_by_id(id: &str, db_url: &str) -> Result<Profile> {
        let params = vec![ParameterValue::Str(id)];
        let row_set = mysql::query(db_url, "SELECT * from profiles WHERE id = ?", &params)?;

        // build the column lookup hashmap
        let columns = get_column_lookup(&row_set.columns);

        match row_set.rows.first() {
            Some(row) => Profile::from_row(row, &columns),
            None => Err(anyhow!("Profile not found for id '{:?}'", id))
        }
    }
}

Suggestion

This works just fine for me but I was wondering if it might make sense to include something like this in the SDK types? In an ideal world, I could use a function on the Row type and avoid having to re-construct the name->index lookup. Here's one option to start the discussion:

fn from_row(row: &spin_sdk::mysql::Row, columns: &HashMap<&str, usize>) -> Result<Self> {
    let id = String::decode(&row.get("id")).ok();
    let handle = String::decode(&row.get("handle"))?;
    let avatar = String::decode(&row.get("avatar")).ok();
    Ok(Profile {
        id,
        handle,
        avatar,
    })
}

Again, I'm pretty new to WebAssembly and Rust so I'm not even sure if it's feasible. It's totally fine if the answer is no, I just thought it might be more ergonomic if it was part of the SDK. Happy to try and build a prototype myself but TBH I'm not entirely sure where to begin.

Start with the spin_sdk::mysql::Row type and the mysql::query entry point described in the issue. Review how Row values and row_set.columns are currently exposed, then define the expected behavior for looking up a value by column name, including missing names and select * results. Done means the SDK has an agreed, tested API for name-based row access.