Manual Database Changes

**This post assumes a basic knowledge of SQL

PokéAPI typically relies on Veekun for the data, but since no data for the Let's Go or Sword/Shield games has been added, PokéAPI has begun to gradually add the data from those games themselves. As a result, the data for those games in this database are still partial and incomplete.

Using SQLiteStudio

For the SQL queries, I used SQLiteStudio, a free desktop application for browsing and editing SQLite database files. If you choose to go this route, you'll need to make sure you have SQLiteStudio installed.

1. Open a new database by clicking on the "Add a database" button (you might have to hover over the icons to see the labels).
2. Make sure the "Database Type" is set to SQLite 3.
3. For the "File" input, click on the folder icon to browse your computer for an existing database file. Navigate to the db.sqlite3 file that you're using for your server.
4. Optionally give the database a "name" input. This will just be the name that it's under in the SQLiteStudio sidebar.
5. Click "OK"

You can explore the tables by clicking on the drop down arrows for the database you just opened in SQLiteStudio. To make a query, click on the "Open SQL Editor" button. Now you can write your SQL queries! Click on the "Play" icon to run them.

Database updates to make

With the updates from Let's Go and Sword/Shield, I found that trying to fetch the different Pokédex entry descriptions, or "flavor text" were missing some important information for tying the entry to its associated Pokémon. Instead, the entries are tied to a Pokémon species. The issue arises for Pokémon species who have multiple different forms (i.e. mega evolutions, Alolan forms, etc). While they all have their own unique Pokémon ids, they all share the same Pokémon species id. Hence when trying to retrieve the Pokédex entries for, say, Alolan Raichu, you will get back all of the entries for both Kantonian Raichu and Alolan Raichu, with no way to differentiate which entry is for which form.

To help combat this, I added a new column into the pokemon_v2_pokemonspeciesflavortext table called pokemon_id, which is a foreign key tied to the id in the pokemon_v2_pokemon table. That way there is a way to see which Pokémon specifically the entry is for, rather than just the species id. This SQL query below is what I used to add the new column. Since there are hundreds of Pokémon, I gave them all the default form Pokémon id (where p.is_default = 1) and then manually went through and updated the Pokémon ids for the entries for alternate forms. After doing it manually this first time, I plan on then writing a script to do it for me so that the next time I update the database to get the most recent data from PokéAPI, I won't have to do any manual entries.

1. Create a pokemon_id column in the pokemon_v2_pokemonspeciesflavortext table:

Copy
1ALTER TABLE pokemon_v2_pokemonspeciesflavortext
2CREATE COLUMN pokemon_id INTEGER REFERENCES pokemon_v2_pokemon(id)
3Set the pokemon ids for all rows in pokemon_v2_pokemonspeciesflavortext to the default form:
4
5UPDATE pokemon_v2_pokemonspeciesflavortext as psft
6SET pokemon_id = (select p.id from pokemon_v2_pokemon as p where p.pokemon_species_id = psft.pokemon_species_id and p.is_default = 1)
7WHERE
8        EXISTS (
9                SELECT *
10                FROM pokemon_v2_pokemon as p
11                WHERE p.pokemon_species_id = psft.pokemon_species_id and p.is_default = 1
12                );

2. Manually update the pokemon_id entries for the entries that belong to an alternate form instead of the default form. You can use this query below to grab all of the English "flavor texts" based on the pokemon_species_id. Then you can manually update the pokemon_id entries in the necessary rows.

Copy
1SELECT * FROM pokemon_v2_pokemonspeciesflavortext as ft
2WHERE ft.pokemon_species_id = 6 and language_id = 9