We have the Pokemon type we want to store in our PlanetScale database in PokemonTableRow. Being a SQL database, we need to set up the schema our table will use before we start sticking data into it.
PlanetScale will stop us from modifying the main production database, for example if we tried to drop a table.
pokemon/|⚠ main ⚠|> drop table pokemon;
ERROR 1105 (HY000): direct DDL is disabled
Instead PlanetScale offers us a way to fork and PR changes to the database schema much like we do for git. To modify the schema in our planetscale database, we're going to have to
- fork the database
- modify the schema on the forked branch
- merge it into the main production database
Using the pscale cli, we can fork our production database onto a branch called new-tables.
pscale branch create pokemon new-tables
We've now created a branch on which we can apply any changes we want, however we want to. If you open a shell to your PlanetScale database, it should now ask you which branch you want to connect to.
Note: if you run the shell command too fast you might see Error: database branch is not ready yet because it can take a couple seconds for the branch to be created.
pscale shell pokemon
To do that we'll create a SQL script in crates/upload-pokemon-data/create-tables.sql. This will create the tables that we'll need to insert data into our database. If you ever get lost here, remember that PlanetScale is backed by MySQL and most things you can do with MySQL will work here, so you can google "X in MySQL" rather than "X in PlanetScale".
We'll start with creating a new table, called pokemon. This is the same name as our database but as we won't be using the database name in our queries this should never matter to us.
If SQL is still new to you, note that the ALL CAPS in the following sql script is a convention for identifying keywords. The capitals are not necessary and some people don't write them like this.
SQL uses the CREATE TABLE syntax to create a new table. We can add the modifier IF NOT EXISTS to only create the table if it doesn't already exist. This makes it easier to run this script multiple times if for example, we messed something up in one of the other tables we'll be creating.
After CREATE TABLE IF NOT EXISTS we specify the new table name: pokemon. The types for each of the columns we'll be using come next inside of parentheses: ().
Each field in our table definition will match a field in our PokemonTableRow type. The names for the database types that match the Rust types in our struct will not be the same. Here's a few of the differences:
- Strings are stored as
VARCHAR(aka "variable characters").VARCHARs accept a max-length argument, so aVARCHAR(30)would store up to 30 characters. SMALLINTis a number that uses 2 bytes (that's 16 bits) of storage space. When combined withUNSIGNEDthis is the same as au16in Rust.MySQLdoesn't have boolean values, which may be confusing since we're usingBOOLEANhere. In MySQL,BOOLEANis an alias for a number that stores either0, for false, or1, for true. This will mostly be transparent to us.- a
FLOATis anf32. NOT NULLis a condition that tests to make sure we're not inserting null values into the specified column.VARBINARYis similar toVARCHARexcept that when we sayVARBINARY(27)we mean 27 bytes, not 27 characters. We'll use this for the ksuid bytes.
Finally there is the -- syntax, which is "comment to end of line". I've left a couple in here for ease of reading, but they don't do anything.
CREATE TABLE IF NOT EXISTS pokemon(
id VARBINARY(27) NOT NULL, -- ksuid
slug VARCHAR(30) NOT NULL, -- generated
name VARCHAR(30) NOT NULL,
pokedex_id SMALLINT UNSIGNED NOT NULL,
-- abilities -- new table
-- typing -- new table
hp SMALLINT UNSIGNED NOT NULL,
attack SMALLINT UNSIGNED NOT NULL,
defense SMALLINT UNSIGNED NOT NULL,
special_attack SMALLINT UNSIGNED NOT NULL,
special_defense SMALLINT UNSIGNED NOT NULL,
speed SMALLINT UNSIGNED NOT NULL,
height SMALLINT UNSIGNED NOT NULL,
weight SMALLINT UNSIGNED NOT NULL,
generation SMALLINT UNSIGNED NOT NULL,
female_rate FLOAT,
genderless BOOLEAN NOT NULL,
legendary_or_mythical BOOLEAN NOT NULL,
is_default BOOLEAN NOT NULL,
forms_switchable BOOLEAN NOT NULL,
base_experience SMALLINT UNSIGNED NOT NULL,
capture_rate SMALLINT UNSIGNED NOT NULL,
-- egg_groups -- new table
base_happiness SMALLINT UNSIGNED NOT NULL,
-- evolves_from -- new table
primary_color VARCHAR(6) NOT NULL,
number_pokemon_with_typing FLOAT NOT NULL,
normal_attack_effectiveness FLOAT NOT NULL,
fire_attack_effectiveness FLOAT NOT NULL,
water_attack_effectiveness FLOAT NOT NULL,
electric_attack_effectiveness FLOAT NOT NULL,
grass_attack_effectiveness FLOAT NOT NULL,
ice_attack_effectiveness FLOAT NOT NULL,
fighting_attack_effectiveness FLOAT NOT NULL,
poison_attack_effectiveness FLOAT NOT NULL,
ground_attack_effectiveness FLOAT NOT NULL,
fly_attack_effectiveness FLOAT NOT NULL,
psychic_attack_effectiveness FLOAT NOT NULL,
bug_attack_effectiveness FLOAT NOT NULL,
rock_attack_effectiveness FLOAT NOT NULL,
ghost_attack_effectiveness FLOAT NOT NULL,
dragon_attack_effectiveness FLOAT NOT NULL,
dark_attack_effectiveness FLOAT NOT NULL,
steel_attack_effectiveness FLOAT NOT NULL,
fairy_attack_effectiveness FLOAT NOT NULL,
PRIMARY KEY ( id ),
UNIQUE ( slug )
);
Right at the bottom you'll see PRIMARY KEY. A primary key specifies the unique identifier for a row in a table. This is not necessarily a key that means something to your data (such as the pokedex id) and people often use auto-incrementing numbers or other "meaningless" identifiers for this field. The id field is our PRIMARY KEY, which also implicitly means UNIQUE so we don't need to also have our own UNIQUE constraint on id.
We do need a UNIQUE constraint on slug, since we'll be using that to uniquely identify each pokemon. You may be thinking that we could use slug as an id, and we could... but if we ever wanted to change the slug for a particular pokemon it is far easier to change it if it's not the PRIMARY KEY.
PRIMARY KEY and UNIQUE are called constraints because they constrain what values are valid for a given column.
Now that we have a .sql script we can apply it to our PlanetScale branch.
PlanetScale gives us a way to open a mysql shell to any branch on our database.
pscale shell pokemon new-tables
after opening a mysql shell to our new branch, we can execute the sql script against the database by sourcing it.
Note: Where you opened the mysql shell matters. We'll be using a path on our filesystem relative to where you opened the shell to execute the sql script.
source ./crates/upload-pokemon-data/create-tables.sql
After sourcing the create-tables.sql script, you can test to make sure it worked by running show tables; (the semicolon on the end is important!). The pokemon table inside of our pokemon database should now exist.
pokemon/new-tables> show tables;
+-----------------------------+
| Tables_in_pokemon |
+-----------------------------+
| pokemon |
+-----------------------------+
We now have a new pokemon table on our new-tables database branch. You can exit the mysql shell using Control and d at the same time. This is often written as C-d.