Create a Harry Potter API with Node.js/Express, MySQL, and PlanetScale

Create a Harry Potter API with Node.js/Express, MySQL, and PlanetScale

James Q Quick
James Q Quick
1/26/20228 min read

Node.js and Express are a great combination for quickly creating APIs that you can share with the world. Let’s see how to do just that with PlanetScale and the mysql2 NPM package.

You can find the final code in this repository.

What we’ll build

In this tutorial, we are going to build a replica of an existing Harry Potter API. Users will be able to query a list of all characters and wands and query each one by an id. The endpoints will look like this:

  • /characters - get a list of all characters
  • /characters/:id - get a character by id
  • /wands - get a list of all wands
  • /wands/:id - get a wand by id
Resources

Create the database

To create your database, you’ll first need to sign up for a free PlanetScale account.

After you’ve created your account and signed in, click New database -> Create new database. Then, give your database a name and choose the appropriate region.

Create New Database
Create New Database

Congratulations, you have successfully created your database. We’ll come back to this in a second.

Project setup

To set up your project, create a new folder and open it in your text editor. To initialize the project, run the following command:

sh
npm init -y

This will initialize the project and accept the defaults for a package.json file. Because I prefer to use the import syntax in JavaScript, you’ll need to add this one property somewhere in your package.json.

json
"type": "module"

Next, we’ll need to install a few packages.

  • express - the framework we’ll use for building our API
  • axios - used by the seed script to retrieve Harry Potter
  • dotenv - used for working with environment variables
  • mysql2 - MySQL client to connect to PlanetScale
  • nodemon (optional) - allows our server to automatically restart when a file is changed

Run the following command to install those dependencies.

sh
npm install express axios dotenv mysql2

Next, we can create a basic Express application. Create an app.js file with the following code.

javascript
import express from 'express';
import dotenv from 'dotenv';
dotenv.config();

const app = express();

app.get('/', (req, res) =>{
  res.json({msg: 'Hello World'});
});

app.listen(3001, () =>{
  console.log('App is running');
});

We can now run the Express server with node app.js, or if you are using nodemon for automatic refreshes, nodemon app.js. Then, open your browser to localhost:3001 to make sure it’s running.

Create a PlanetScale password

Next, we’ll need to create a password for our PlanetScale database that our application can use to connect to it. Inside of the PlanetScale dashboard for your new database, click on the Branches tab, and choose main. Once it’s finished initializing, click Connect -> Generate new password.

After your password has been generated, choose Node.js MySQL from the dropdown labeled as General. Then, copy the code snippet by clicking the copy button to the right.

PlanetScale Password Creation
PlanetScale Password Creation

This snippet shows how to create a connection to our PlanetScale database in Node.js using the mysql2 package. In this case, we want to extract the connection string (the part in quotes) and add it as an environment variable.

Create a .env file with one property of DATABASE_URL. Copy the value of your connection string into that property. It should look something like this:

sh
DATABASE_URL=mysql://xxxxxxxx:pscale_pw_xxxxxxxxxxx@xxxxxxx.us-east-4.psdb.cloud/hp-api?ssl={"rejectUnauthorized":true}

Seed the database with Harry Potter data

With the connection string created, we can use a seed script to automatically create the character and wand tables and populate them with data. Create a seed folder in the root of your project.

Then, copy over the two files, seed.js and sql.js, from the seed directory of the final source code repository.

* The seed.js file has a MAX_RECORDS property set to 20 by default. This property is used to limit the number of rows that get saved to the database for demo purposes.

Your project setup should now look like this.

Project File Structure
Project File Structure

After you’ve set up your files correctly. You can run the seed script with the following command.

sh
node seed/seed.js

You should get successful log messages in the console telling you that the tables have been created and data has been populated.

Seed Script Output
Seed Script Output

To confirm this, navigate back to the PlanetScale dashboard and click on the Console tab. Inside there you can run raw SQL commands to view your tables.

sh
SHOW TABLES;

Show Tables Query
Show Tables Query

Lastly, run a query to see that data has actually been populated.

sh
SELECT * FROM hp_character LIMIT 5;

Create Harry Potter character endpoints

Now that you know you have successfully seeded your database with data, create the endpoints to query your characters. First, you’ll need to initialize the connection with the mysql2 package. Since I prefer to work with promise-based libraries, I recommend importing the main object from mysql2/promise. Import the mysql client at the top of your app.js file:

javascript
import mysql from 'mysql2/promise';

Then, initialize your connection using the DATABASE_URL environment variable after the imports and after dotenv.config();.

javascript
const connection = await mysql.createConnection(process.env.DATABASE_URL);

The first few lines of your file should look like this.

javascript
import express from 'express';
import dotenv from 'dotenv';
import mysql from 'mysql2/promise';
 
dotenv.config();
 
const connection = await mysql.createConnection(process.env.DATABASE_URL);
 
const app = express();

Next, below const app = express();, create the skeleton of the endpoint for querying all characters.

javascript
app.get('/characters', async (req, res) => {
   res.json({});
});

Create a query variable that holds the raw SQL. Then, pass that SQL along by calling the query() function on our connection variable. This returns an array where the first item is called rows. We can then destructure the rows property out of the array and return that data as JSON. The final endpoint looks like this.

javascript
app.get('/characters', async (req, res) => {
   const query = 'SELECT * FROM hp_character';
   const [rows] = await connection.query(query);
   res.json(rows);
});

Next, we can do something very similar for the endpoint that allows users to query a character by its id. However, we’ll need to add a route parameter to the route definition for the id of the character the user is looking for.

javascript
app.get('/characters/:id', async (req, res) => {
  
};

Next, we can grab the value of the id through req.params.

javascript
const {id} = req.params;

We now want to use that id value inside of a SQL query. However, we don’t want to pass that value directly into the SQL string because that will make us susceptible to a SQL Injection attack. Instead, we can use a placeholder (?) in our SQL string.

javascript
const query = `SELECT * FROM hp_character WHERE hp_character.id=?`;

Then, when we call the query function, we pass along the values of the placeholder as the second parameter.

javascript
const [rows] = await connection.query(query, [id]);

Lastly, we return the first item from the rows array. If the rows array is empty (meaning no character was found), we can return back a string to the user explaining that no such character was found. The full endpoint should look like this:

javascript
app.get('/characters/:id', async (req, res) => {
   const {id} = req.params;
 
   const query = `SELECT * FROM hp_character WHERE hp_character.id=?`;
   const [rows] = await connection.query(query, [id]);
   if(!rows[0]){
       return res.json({msg: "Couldn't find that character"});
   }
   res.json(rows[0]);
});

Create Harry Potter wand endpoints

Your wand endpoints will look almost the same as the character endpoints with the exception of two changes. You’ll need to update the path for the endpoint and then refer to the wand table instead of the hp_character table.

javascript
app.get('/wands’, async (req, res) => {
   const query = 'SELECT * FROM wand';
   const [rows] = await connection.query(query);
   res.json(rows);
});

app.get('/wands/:id', async (req, res) => {
   const {id} = req.params;
   const query = `SELECT * FROM wand WHERE wand.id=?`;
   const [rows] = await connection.query(query, [id]);
   if(!rows[0]){
       return res.json({msg: "Couldn't find that wand"});
   }
   res.json(rows[0]);
});

Add error handling

One thing we have avoided so far is error handling, but this is an important aspect of writing good code. To do this, we will surround our code with try/catch. This way we can handle errors and send back an appropriate response. The simplest version would handle the error by logging it out like so.

javascript
app.get('/characters', async (req, res) => {
   try {
       const query = 'SELECT * FROM hp_character';
       const [rows] = await connection.query(query);
       res.json(rows);
   }catch(err){
       console.error(err);
   }
});

You could take this one step further by providing a little more detail to the user as to what happened in the form of an error message and a status. One trick I like to do is to initialize a status variable to the value of 200 by default. Then, you can update it accordingly if needed.

javascript
app.get('/characters', async (req, res) => {
   let status = 200;
   try {
       const query = 'SELECT * FROM hp_character';
       const [rows] = await connection.query(query);
       res.status(status).json(rows);
   }catch(err){
       console.error(err);
       res.status(status).json();
   }
});

What if you wanted to return a custom error message to the user? Well, you can combine the data and a message (if present) into an object that you return as a JSON object. I like to do this by initializing a retVal object that can be updated with the appropriate properties.

javascript
app.get('/characters', async (req, res) => {
   let status = 200;
   let retVal = {};
   try {
       const query = 'SELECT * FROM hp_character';
       const [rows] = await connection.query(query);
       retVal.data = rows;
       res.status(status).json(retVal);
   }catch(err){
       console.error(err);
       retVal.message = "Something went wrong";
       res.status(status).json(retVal);
   }
});

One additional optimization. Right now, you are repeating the code that responds to the incoming request. Since you are returning the status and retVal object regardless of an error, you can move that logic to a finally clause.

javascript
app.get('/characters', async (req, res) => {
   let status = 200;
   let retVal = {};
   try {
       const query = 'SELECT * FROM hp_character';
       const [rows] = await connection.query(query);
       retVal.data = rows;
   }catch(err){
       console.error(err);
       retVal.message = "Something went wrong";
   }finally{
       res.status(status).json(retVal);
   }
});

You can apply this same format to the rest of your endpoints. To test your endpoints, you can enter one of the following URLs into your browser.

You should see an appropriate JSON response returned. Here’s an example using http://localhost:3001/characters/1.

Harry Potter Character JSON Data
Harry Potter Character JSON Data

Wrap up

I hope that you were able to understand the basics of creating APIs with Node.js and Express while connecting to PlanetScale using the mysql2 package. If you haven’t already, make sure to sign up for a free PlanetScale account so you can follow along.

If you have any additional questions or requests for future tutorials, let us know on Twitter.

The last database you’ll ever need
Start your database in seconds