When it comes to doing the actual insertion of the PokemonTableRow into the PlanetScale database, we're going to use sqlx, an async SQL client written entirely in Rust that can check our queries against the database at compile-time and makes it easy to use prepared statements.
The fact that it is an implementation only written in Rust means that it will be easier to cross compile and generally use in different environments. For example, in serverless functions where we have less access to make sure shared libraries (such as mysql's c library) are available having a crate that's only written in Rust makes it so that we can target more environments.
Add the dependency to our Cargo.toml.
cargo add sqlx -p upload-pokemon-data
We need to go into our Cargo.toml and modify the sqlx dependency to enable the mysql feature since PlanetScale is mysql-based. Since sqlx is also an async sql client, we'll need to choose an async runtime, so enable runtime-tokio-rustls which will "turn on" compatibility with the tokio runtime.
Note: We haven't installed the tokio runtime yet, that will come later.
sqlx = { version = "0.5.7", features = ["mysql", "runtime-tokio-rustls"] }
In src/db.rs we'll create a new async function called insert_pokemon. async functions return Futures, but we don't need to care about that right now.
We'll define the function to take a shared reference to a MySqlPool and a PokemonTableRow. We'll destructure the fields from the PokemonTableRow.
The return value Result<sqlx::mysql::MySqlQueryResult, sqlx::Error> matches the return value that we get from the sqlx::query macro after we await it.
pub async fn insert_pokemon(
pool: &MySqlPool,
PokemonTableRow {
id,
slug,
name,
pokedex_id,
hp,
attack,
defense,
special_attack,
special_defense,
speed,
height,
weight,
generation,
female_rate,
genderless,
legendary_or_mythical,
is_default,
forms_switchable,
base_experience,
capture_rate,
base_happiness,
primary_color,
number_pokemon_with_typing,
normal_attack_effectiveness,
fire_attack_effectiveness,
water_attack_effectiveness,
electric_attack_effectiveness,
grass_attack_effectiveness,
ice_attack_effectiveness,
fighting_attack_effectiveness,
poison_attack_effectiveness,
ground_attack_effectiveness,
fly_attack_effectiveness,
psychic_attack_effectiveness,
bug_attack_effectiveness,
rock_attack_effectiveness,
ghost_attack_effectiveness,
dragon_attack_effectiveness,
dark_attack_effectiveness,
steel_attack_effectiveness,
fairy_attack_effectiveness,
}: &PokemonTableRow,
) -> Result<sqlx::mysql::MySqlQueryResult, sqlx::Error> {
sqlx::query!(
r#"
INSERT INTO pokemon (
id,
slug,
name,
pokedex_id,
hp,
attack,
defense,
special_attack,
special_defense,
speed,
height,
weight,
generation,
female_rate,
genderless,
legendary_or_mythical,
is_default,
forms_switchable,
base_experience,
capture_rate,
base_happiness,
primary_color,
number_pokemon_with_typing,
normal_attack_effectiveness,
fire_attack_effectiveness,
water_attack_effectiveness,
electric_attack_effectiveness,
grass_attack_effectiveness,
ice_attack_effectiveness,
fighting_attack_effectiveness,
poison_attack_effectiveness,
ground_attack_effectiveness,
fly_attack_effectiveness,
psychic_attack_effectiveness,
bug_attack_effectiveness,
rock_attack_effectiveness,
ghost_attack_effectiveness,
dragon_attack_effectiveness,
dark_attack_effectiveness,
steel_attack_effectiveness,
fairy_attack_effectiveness
)
VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
"#,
id,
slug,
name,
pokedex_id,
hp,
attack,
defense,
special_attack,
special_defense,
speed,
height,
weight,
generation,
female_rate,
genderless,
legendary_or_mythical,
is_default,
forms_switchable,
base_experience,
capture_rate,
base_happiness,
primary_color,
number_pokemon_with_typing,
normal_attack_effectiveness,
fire_attack_effectiveness,
water_attack_effectiveness,
electric_attack_effectiveness,
grass_attack_effectiveness,
ice_attack_effectiveness,
fighting_attack_effectiveness,
poison_attack_effectiveness,
ground_attack_effectiveness,
fly_attack_effectiveness,
psychic_attack_effectiveness,
bug_attack_effectiveness,
rock_attack_effectiveness,
ghost_attack_effectiveness,
dragon_attack_effectiveness,
dark_attack_effectiveness,
steel_attack_effectiveness,
fairy_attack_effectiveness,
).execute(pool).await
}
The sqlx::query macro takes a string of SQL and any arguments we need to pass in.
The SQL query we're using is an INSERT INTO then the table name we're inserting a row into followed by the list of column names we're going to provide values for. Then the VALUES keyword followed by a bunch of question marks: one question mark for each value that needs to be supplied.
INSERT INTO pokemon (column_names)
VALUES (?)
This will turn into a prepared statement when we execute it against our database. It is important that we're passing values in as arguments to a prepared statement rather than concatenating them into the SQL string. If we were building the string up ourselves we would be vulnerable to SQL injection which means that any of the variables we concatenate could modify the SQL query itself. This is especially bad when dealing with user input. It is less bad when you control the input as we do here, but we still don't want to get in the habit of doing it, so we use prepared statements.
The query macro will compile into some code that we can call .execute on. This will send the query to the MySql database.
With the query built up, we can run cargo check, which will pop up this error.
error: `DATABASE_URL` must be set to use query macros
--> crates/upload-pokemon-data/src/db.rs:223:5
|
223 | / sqlx::query!(
224 | | r#"
225 | | INSERT INTO pokemon (
226 | | id,
... |
310 | | fairy_attack_effectiveness,
311 | | ).execute(pool).await
| |_____^
This error happens because the sqlx::query macro checks our query against the database schema at compile time, so we need a database url for it to use to access the database to do that.
We can use the pscale cli to open up a secured connection to our database branch. In a new terminal, run.
pscale connect pokemon new-tables
This should show you a URL like: 127.0.0.1:3306 that your database is now accessible on.
We need to provide this environment variable to cargo when compiling or checking our application. On Mac or Linux you can do that inline.
DATABASE_URL=mysql://127.0.0.1 cargo check
VSCode Note:
If you're working with VSCode and Rust Analyzer, you can add the database url to the extraEnv for the Rust Analyzer process, which will enable your in-editor cargo check to work and check your queries.
"rust-analyzer.server.extraEnv": {
"DATABASE_URL":"mysql://127.0.0.1:3306"
}
With the database url properly set, sqlx::query shows us two new errors. One that says we need to implement the Encode trait for PokemonId and the other that says we need to implement the Type<MySql> trait for PokemonId.
These traits are how we'll tell sqlx how it should store our newtype in mysql, since MySql doesn't have a PokemonId type built in.
error[E0277]: the trait bound `PokemonId: Encode<'_, MySql>` is not satisfied
--> crates/upload-pokemon-data/src/db.rs:223:5
|
223 | / sqlx::query!(
224 | | r#"
225 | | INSERT INTO pokemon (
226 | | id,
... |
310 | | fairy_attack_effectiveness,
311 | | ).execute(pool).await
| |_____^ the trait `Encode<'_, MySql>` is not implemented for `PokemonId`
|
= note: required because of the requirements on the impl of `Encode<'_, MySql>` for `&PokemonId`
= note: required by `sqlx::Encode::size_hint`
= note: this error originates in a macro (in Nightly builds, run with -Z macro-backtrace for more info)
error[E0277]: the trait bound `PokemonId: Type<MySql>` is not satisfied
--> crates/upload-pokemon-data/src/db.rs:223:5
|
223 | / sqlx::query!(
224 | | r#"
225 | | INSERT INTO pokemon (
226 | | id,
... |
310 | | fairy_attack_effectiveness,
311 | | ).execute(pool).await
| |_____^ the trait `Type<MySql>` is not implemented for `PokemonId`
First, the Encode trait.
Encode has pre-defined types that we need to adhere to in the sqlx trait, so I've pulled those types in here. The only part we have control over is the body of the function and whether we want to be specific about MySql or be generic over databases (we want to be specific), everything else sqlx has told us we need.
impl<'q> Encode<'q, MySql> for PokemonId {
fn encode_by_ref(
&self,
buf: &mut <MySql as HasArguments<'q>>::ArgumentBuffer,
) -> IsNull {
let bytes: &[u8] = &self.0.to_base62().into_bytes();
<&[u8] as Encode<MySql>>::encode(bytes, buf)
}
}
Which leaves us with the body of the function. self is a PokemonId, so we can call .0.to_base62().into_bytes() which gives us a byte slice, similar to our debug implementation.
The second line is saying "take the type &[u8], and when looking up which encode to call, use the one on the Encode<MySql> trait implementation".
We do this because there's already an implementation to encode &[u8] in the way MySql wants it, so we're making the Ksuid type inside PokemonId into &[u8], then letting the implementation for &[u8] that already exists handle it.
let bytes: &[u8] = &self.0.to_base62().into_bytes();
<&[u8] as Encode<MySql>>::encode(bytes, buf)
The same way there's already an implementation for encode for &[u8], there's already implementations for the Type trait's type_info and compatible methods, which makes implementing the Type trait for PokemonId pretty mechanical as we are calling the relevent pre-existing function.
impl Type<MySql> for PokemonId {
fn type_info() -> <MySql as Database>::TypeInfo {
<&[u8] as Type<MySql>>::type_info()
}
fn compatible(ty: &MySqlTypeInfo) -> bool {
<&[u8] as Type<MySql>>::compatible(ty)
}
}
From here on our, we'll be using this connection to our database to check our queries, so be sure you include it however you need to in your system either as an inline environment variable, in vscode settings, or somewhere else.
The application should pass a cargo check now.