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.
We'll need three features because sqlx supports many underlying technologies. These features choose our database type, our async runtime, and our tls implementation.
- mysql
- runtime-tokio
- tls-rustls
We need the mysql feature because PlanetScale is mysql-based and we need to use a mysql-based client.
We're choosing to use the tokio runtime because that is the most widely used feature-complete async runtime available for standard Rust applications.
Note: We haven't installed the tokio runtime yet, that will come later.
We also choose to use rustls, a modern approach to TLS written in Rust.
Add the dependency to our Cargo.toml using cargo add:
cargo add sqlx -p upload-pokemon-data -F mysql -F runtime-tokio -F tls-rustls
or by writing an entry in Cargo.toml
sqlx = { version = "0.7.1", features = ["mysql", "runtime-tokio", "tls-rustls"] }
In src/db.rs we'll create a new async function called insert_pokemon.
We'll define the function to take a shared reference to a MySqlPool and a PokemonTableRow; then we'll destructure the fields from the PokemonTableRow.
use sqlx::MySqlPool;
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. Make sure you note that there is a mysql:// added to the host here, and that I'm able to leave 3306 off because that's the default port for mysql.
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. Both of these are phrased as a trait bound not being "satisfied".
These traits (Encode and Type<MySql>) define how sqlx should store our newtype in mysql, since MySql doesn't have a type that is compatible with our PokemonId type built in.
error[E0277]: the trait bound `db::PokemonId: Encode<'_, MySql>` is not satisfied
--> crates/upload-pokemon-data/src/db.rs:217:5
|
217 | / sqlx::query!(
218 | | r#"
219 | | INSERT INTO pokemon (
220 | | id,
... |
304 | | fairy_attack_effectiveness,
305 | | ).execute(pool).await
| |_____^ the trait `Encode<'_, MySql>` is not implemented for `db::PokemonId`
|
= help: the following other types implement trait `Encode<'q, DB>`:
<bool as Encode<'q, sqlx::Any>>
<bool as Encode<'_, MySql>>
<i8 as Encode<'_, MySql>>
<i16 as Encode<'q, sqlx::Any>>
<i16 as Encode<'_, MySql>>
<i32 as Encode<'q, sqlx::Any>>
<i32 as Encode<'_, MySql>>
<i64 as Encode<'q, sqlx::Any>>
and 23 others
= note: required for `&db::PokemonId` to implement `Encode<'_, MySql>`
= note: this error originates in the macro `$crate::sqlx_macros::expand_query` which comes from the expansion of the macro `sqlx::query` (in Nightly builds, run with -Z macro-backtrace for more info)
error[E0277]: the trait bound `db::PokemonId: Type<MySql>` is not satisfied
--> crates/upload-pokemon-data/src/db.rs:217:5
|
217 | / sqlx::query!(
218 | | r#"
219 | | INSERT INTO pokemon (
220 | | id,
... |
304 | | fairy_attack_effectiveness,
305 | | ).execute(pool).await
| |_____^ the trait `Type<MySql>` is not implemented for `db::PokemonId`
|
= help: the following other types implement trait `Type<DB>`:
<bool as Type<MySql>>
<bool as Type<sqlx::Any>>
<i8 as Type<MySql>>
<i16 as Type<MySql>>
<i16 as Type<sqlx::Any>>
<i32 as Type<MySql>>
<i32 as Type<sqlx::Any>>
<i64 as Type<MySql>>
and 23 others
= note: required for `&db::PokemonId` to implement `Type<MySql>`
= note: 1 redundant requirement hidden
= note: required for `&&db::PokemonId` to implement `Type<MySql>`
note: required by a bound in `sqlx::Arguments::add`
--> /Users/chris/.cargo/registry/src/index.crates.io-6f17d22bba15001f/sqlx-core-0.7.1/src/arguments.rs:19:53
|
17 | ...fn add<T>(&mut self, value: T)
| --- required by a bound in this associated function
18 | ...where
19 | ... T: 'q + Send + Encode<'q, Self::Database> + Type<Self::Databas...
| ^^^^^^^^^^^^^^^^^^^^ required by this bound in `Arguments::add`
= note: this error originates in the macro `$crate::sqlx_macros::expand_query` which comes from the expansion of the macro `sqlx::query` (in Nightly builds, run with -Z macro-backtrace for more info)
Implementing Encode in sqlx
First, the Encode trait.
Encode is a trait and thus the functions that it wants us to define have pre-defined types that we need to. Our sqlx imports will grow to include Encode, MySql, IsNull, and HasArguments.
use sqlx::{
database::HasArguments, encode::IsNull, Encode, MySql,
MySqlPool,
};
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 a variety databases (we want to be specific in this case), everything else the Encode trait 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 function to call on that type, 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)
Implementing Type
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.
Make sure to additionally bring Type, Database, and MySqlTypeInfo into scope.
use sqlx::{
database::HasArguments, encode::IsNull,
mysql::MySqlTypeInfo, Database, Encode, MySql,
MySqlPool, Type,
};
and then we can use our implementation that delegates to &[u8].
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.