In this lesson we'll query the database and return JSON data in the lambda response.
Add sqlx, serde, and serde_json to the Cargo.toml for our pokemon-api.
When we add multiple crates to a package, we have to specify the features we want to enable with fully qualified paths. So the mysql feature from sqlx becomes the sqlx/mysql feature, and so on.
cargo add -p pokemon-api sqlx serde serde_json -F sqlx/mysql -F sqlx/runtime-tokio -F sqlx/tls-rustls -F serde/derive
This results in a Cargo.toml that looks like this, alongside our other dependencies.
[dependencies]
lambda_http = { version = "0.8.1", default-features = false, features = [
"apigw_rest",
] }
serde = { version = "1.0.188", features = ["derive"] }
serde_json = "1.0.107"
sqlx = { version = "0.7.1", features = [
"mysql",
"runtime-tokio",
"tls-rustls",
] }
tokio = { version = "1.29.1", features = ["macros"] }
With our dependencies installed, we need to
- Get the environment variable for the database connection string
- Use the environment variable to bootstrap a connection pool
- Use the connection pool to make a query to the database
- Return the query results to the user as JSON
Here is the code we'll end up with, including the items we'll need to bring into scope.
use lambda_http::{
http::header::CONTENT_TYPE, run, service_fn, Body,
Error, Request, Response,
};
use serde::Serialize;
use sqlx::mysql::MySqlPoolOptions;
use std::env;
#[derive(Debug, sqlx::FromRow, Serialize)]
struct PokemonHp {
name: String,
hp: u16,
}
async fn function_handler(
_event: Request,
) -> Result<Response<Body>, Error> {
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 pokemon = serde_json::to_string(&result)?;
let resp = Response::builder()
.status(200)
.header(CONTENT_TYPE, "application/json")
.body(Body::Text(pokemon.to_string()))?;
Ok(resp)
}
// fn main() and our tests still exist down here
Setting up a MySql Pool
The env var uses the same approach we used in the csv upload workshop. env::var will get the DATABASE_URL which we will need to set in the environment in our Netlify settings as well.
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?;
Querying for a result
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 use double quotes for "bulbasaur" and using a raw string means we don't have to worry about escaping the 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"
Serializing structs to JSON
Finally we can return a JSON representation of a pokemon by calling serde_json::to_string since the body of our response needs to be a string of JSON.
let pokemon = serde_json::to_string(&result)?;
let resp = Response::builder()
.status(200)
.header(CONTENT_TYPE, "application/json")
.body(Body::Text(pokemon))?;
Ok(resp)
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,
}
Running the new function
To run the function locally, we need our database connection open, cargo lambda running, and then we need to invoke our function.
In one terminal connect to the database using the pscale cli.
❯ pscale connect pokemon
? Select a branch to connect to: main
Secure connection to database pokemon and branch main is established!.
Local address to connect your application: 127.0.0.1:3306 (press ctrl-c to quit)
In another, start cargo lambda using the pscale connection.
❯ DATABASE_URL=mysql://127.0.0.1:3306 cargo lambda watch
INFO invoke server listening on [::]:9000
Then in yet another terminal, invoke the function using an example payload.
❯ cargo lambda invoke pokemon-api --data-example apigw-request
The invoke call should result in our response being shown, along with the json string body.
{
"statusCode": 200,
"headers":
{
"content-type": "application/json"
},
"multiValueHeaders":
{
"content-type":
[
"application/json"
]
},
"body": "{\"name\":\"Bulbasaur\",\"hp\":45}",
"isBase64Encoded": false
}
Testing
If we run our tests now, with the DATABASE_URL set, our test will pass but we're not testing the response yet.
DATABASE_URL=mysql://127.0.0.1:3306 cargo test -p pokemon-api
The updated test code for the accepts_apigw_request test includes the new JSON value as a string, wrapped in Body::Text.
#[tokio::test]
async fn accepts_apigw_request() {
let input = include_str!("apigw-request.json");
let request = lambda_http::request::from_str(input)
.expect("failed to create request");
let response = function_handler(request)
.await
.expect("failed to handle request");
assert_eq!(
response.body(),
&Body::Text(
"{\"name\":\"Bulbasaur\",\"hp\":45}"
.to_string()
)
);
}
Our Pokemon data isn't going to change, so using the 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