Our SQL is written, our Rust is running, and we're ready to deploy the schema and the data to production.
If we head over to planetscale's app, on the pokemon database and the new-tables branch, we can check out the schema.
If all looks good, we can submit a deploy request with the new schema. This will take us to the deploy request page which feels a lot like a GitHub PR.
We can see the pending changes and if there are any other active deployments.
This page will also tell us whether or not we have any conflicts with the main schema.
If all looks good we can "Add changes to deploy queue". We don't deploy directly because PlanetScale offers a workflow that enables multiple people to be approving schema changes at the same time. PlanetScale lets us know when our changes have been deployed and we're good to go to upload the pokemon data to our production database.
If we switch over to the overview before running cargo run, we'll be able to see the requests from our app come in.
pscale connect pokemon main
DATABASE_URL=mysql://127.0.0.1 cargo run
After uploading the data we can create a temporary file input.sql. This query starts with the evolutions table, then joins it with the pokemon table twice so that we have all the information for both pokemon referred to by the evolutions table ids. We group by the base pokemon name and use GROUP_CONCAT on the resulting groups to turn all of the pokemon our base pokemon can evolve into, into a comma separated string.
SELECT b.name as pokemon, GROUP_CONCAT(a.name) as evolves_into
FROM evolutions
LEFT JOIN pokemon a on a.id = evolutions.pokemon_id
LEFT JOIN pokemon b on b.id = evolves_from
GROUP BY pokemon;
We can use the mysql cli to send the input.sql file as a query through the PlanetScale connection we already have.
You can also type this query into the pscale shell if you want to. The results will show the base pokemon column alongside what it can evolve into.
❯ mysql -h 127.0.0.1 < ./input.sql
pokemon evolves_into
Abra Kadabra
Aipom Ambipom
Amaura Aurorus
Anorith Armaldo
Applin Appletun,Appletun Gmax,Flapple Gmax,Flapple
Archen Archeops
Aron Lairon
Arrokuda Barraskewda