Querying MySql for booleans with sqlx

Now we have a deployed Rust Netlify Function that is querying PlanetScale and responding to users with JSON representations of a subset of the Pokemon data we have stored.

There's a couple hangups that I want to cover before we move on. The first is querying for boolean values, which are stored as TinyInt in MySQL, which are i8s that can be either 0 or 1.

First we'll add the legendary_or_mythical value to our PokemonHp struct.

#[derive(Debug, sqlx::FromRow, Serialize)]
struct PokemonHp {
    name: String,
    hp: u16,
    legendary_or_mythical: bool,
}

Then we'll add the field in our SQL query as well.

SELECT name, hp, legendary_or_mythical FROM pokemon WHERE slug = ?

With these two in place, if we test our application with cargo test we'll see a warning expected bool, found i8.

error[E0277]: the trait bound `bool: From<i8>` is not satisfied
  --> crates/pokemon-api/src/main.rs:44:26
   |
44 |               let result = sqlx::query_as!(
   |  __________________________^
45 | |                 PokemonHp,
46 | |                 r#"SELECT name, hp, legendary_or_mythical fr...
47 | |                 pokemon_name
48 | |             )
   | |_____________^ the trait `From<i8>` is not implemented for `bool`
   |
   = help: the trait `From<subtle::Choice>` is implemented for `bool`
   = note: required for `i8` to implement `Into<bool>`
   = note: this error originates in the macro `$crate::sqlx_macros::expand_query` which comes from the expansion of the macro `sqlx::query_as` (in Nightly builds, run with -Z macro-backtrace for more info)

This is because of the way MySQL stores booleans, which isn't as booleans at all. We have to modify our SQL query to inform sqlx of our intent to return a boolean.

legendary_or_mythical as "legendary_or_mythical!: bool",

This will compile, and we still need to update our accepts_apigw_request test with the additional field.

assert_eq!(
    response.body(),
    &Body::Text(
        "{\"name\":\"Bulbasaur\",\"hp\":45,\"legendary_or_mythical\":false}"
            .to_string()
    )
);

Altogether we'll now get the legendary_or_mythical boolean in our API responses.

One legendary pokemon is zapdos.

curl https://<site-name>.netlify.app/api/pokemon/zapdos
{"name":"Zapdos","hp":90,"legendary_or_mythical":true}

and one non-legendary pokemon is diglet.

curl https://<site-name>.netlify.app/api/pokemon/diglett
{"name":"Diglett","hp":10,"legendary_or_mythical":false}

Lesson Details

Lesson Details