Tutorials8 min read

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

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.

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:

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.

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.

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:

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.

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

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.

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.

SHOW TABLES;

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

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.

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.

Using entropy for user-friendly strong passwords

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

What we’ll build#

Resources

Create the database#

Project setup#

Create a PlanetScale password#

Seed the database with Harry Potter data#

Create Harry Potter character endpoints#

Create Harry Potter wand endpoints#

Add error handling#

Wrap up#

Share

Next post

Your business deserves a predictable database.