Add sqlx and serde to the Cargo.toml for our pokemon-api.
cargo add -p pokemon-api sqlx serde serde_json
Then enable mysql and runtime-tokio-rustls like we did in the csv upload course. mysql because PlanetScale is mysql, and runtime-tokio-rustls because we're using the tokio async runtime.
Also enable derive on serde so that we can derive Serialize for our query results.
sqlx = { version = "0.5.7", features = ["mysql", "runtime-tokio-rustls"] }
serde = { version = "1.0.130", features = ["derive"] }
We need to
- Get the environment variable for the database connection string
- Use the env var to bootstrap a connection pool
- Use the pool to make a query
- Return the query results to the use
#[derive(Debug, sqlx::FromRow, Serialize)]
struct PokemonHp {
name: String,
hp: u16,
}
async fn handler(
_: Value,
_: Context,
) -> Result<Value, Error> {
println!("handler");
let database_url = env::var("DATABASE_URL")?;
let pool = MySqlPoolOptions::new()
.max_connections(5)
.connect(&database_url)
.await?;
let result = sqlx::query_as!(
PokemonHp,
r#"SELECT name, hp from pokemon where slug = "bulbasaur""#
)
.fetch_one(&pool)
.await?;
let json_pokemon = serde_json::to_string(&result)?;
let response = ApiGatewayProxyResponse {
status_code: 200,
headers: HeaderMap::new(),
multi_value_headers: HeaderMap::new(),
body: Some(Body::Text(json_pokemon)),
is_base64_encoded: Some(false),
};
Ok(response)
}
We'll need to bring MySqlPoolOptions into scope as well as std::env and serde::Serialize.
use sqlx::mysql::MySqlPoolOptions;
use std::env;
use serde::Serialize;
The env var uses the same approach we used in the csv upload script. env::var will get the DATABASE_URL which we need to set in the environment in our Netlify settings.
let database_url = env::var("DATABASE_URL")?;
Then we can use that env var to set up a connection pool. This pool can be passed to any sqlx query and the query will check out a connection, use it, then return it to the pool.
let pool = MySqlPoolOptions::new()
.max_connections(5)
.connect(&database_url)
.await?;
Our query uses the sqlx::query_as! macros. sqlx offers a few query macros. The advantage of this one is that it lets us specify a type to put the query result into: PokemonHp.
The query uses a raw string (r#""#) because we want to quote "bulbasaur" and using a raw string means we don't have to worry about escaping quotes.
When we call fetch_one, sqlx sets up a prepared query that uses the sql string we gave it, outputting the single row response into a PokemonHp struct.
let result = sqlx::query_as!(
PokemonHp,
r#"SELECT name, hp FROM pokemon WHERE slug = "bulbasaur""#
)
.fetch_one(&pool)
.await?;
The SQL query we're using will get the name and hp columns from the pokemon table when the slug column in any given row equals bulbasaur. This will result in one matching row for us.
SELECT name, hp
FROM pokemon
WHERE slug = "bulbasaur"
Finally we can return a JSON representation of a pokemon by calling serde_json::to_string since the body field needs to be a string of JSON.
let json_pokemon = serde_json::to_string(&result)?;
let response = ApiGatewayProxyResponse {
status_code: 200,
headers: HeaderMap::new(),
multi_value_headers: HeaderMap::new(),
body: Some(Body::Text(json_pokemon)),
is_base64_encoded: Some(false),
};
Ok(response)
The PokemonHp struct is a placeholder for us, we'll expand it to all of the data we want it to include later. For now, we need to derive sqlx::FromRow which contains the functionality that sqlx uses to build a PokemonHp from a sqlx query result. We also need to derive Serialize so that we can turn an instance of the struct into a JSON string.
#[derive(Debug, sqlx::FromRow, Serialize)]
struct PokemonHp {
name: String,
hp: u16,
}
If we run our tests now, with the DATABASE_URL set, our test will fail since we're not getting the right data any more.
If you don't have a PlanetScale connection to your database, connect.
pscale connect pokemon main
and then use that connection to run our tests
DATABASE_URL=mysql://127.0.0.1 cargo test -p pokemon-api
The updated test code for the handler_handles test includes the new json value.
assert_eq!(
handler(event.clone(), Context::default())
.await
.unwrap(),
ApiGatewayProxyResponse {
status_code: 200,
headers: HeaderMap::new(),
multi_value_headers: HeaderMap::new(),
body: Some(Body::Text(
serde_json::to_string(&PokemonHp {
name: String::from("Bulbasaur"),
hp: 45
},)
.unwrap()
)),
is_base64_encoded: Some(false),
}
)
Our Pokemon data isn't going to change, so using Bulbasaur values in our tests is fine. If it was going to change we might want to create some special data for such tests.
Our tests now hit the database and return a JSON representation of a pokemon