In order to be able to follow along you will need:

• Initial Project Source

• VS Code

• Node.js

• Postman or cURL

• First pull down this github repo.

• Then install it by running npm init in a terminal within the project root.

• Then create these new files ./config/db.js, ./database.js and ./setup.js in the root folder.

• This means that your project root should now look like so:

It's almost time for us to actually start writing some code! Only thing we need to do is install a dependency called mysql2 which will help us connect to and make queries to our MySQL Server.

• So in the root of our project, run the following command

npm i mysql2

Next up, we'll want to input all the credentials required to connect to our MySQL Server.

• Add the following to db.js located in the config folder

• Replace password123 with the root password that you decided earlier when setting up MySQL Community Server

module.exports = {
  host: "localhost",
  user: "root",
  password: "password123",
  database: "mydb",
};

Next, we'll create a setup script which will connect to our MySQL Server, create a database and then add a messages table to it.

• In setup.js copy/paste the following code which I will explain in detail further down.

const mysql = require("mysql2");
const CONFIG = require("./config/db");

const SETUP_DB_QUERY = `
  CREATE DATABASE IF NOT EXISTS ${CONFIG.database} DEFAULT CHARACTER SET 'utf8';

  USE ${CONFIG.database};

  CREATE TABLE IF NOT EXISTS messages (
    id int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
    text varchar(255) NOT NULL
  );
`;

function createConnection(config, query) {
  const { host, user, password } = config;
  const connection = mysql.createConnection({
    host,
    user,
    password,
    multipleStatements: true,
  });

  connection.connect(function (err) {
    if (err) throw err;
    console.log("Connected to MySQL server!");
    createAndSetupDatabase(connection, query);
  });
}

function createAndSetupDatabase(connection, query) {
  connection.query(query, function (err) {
    if (err) throw err;
    console.log("Database created and setup!");
  });
}
createConnection(CONFIG, SETUP_DB_QUERY);

Now it's time to save the changes you made and soon we're going to run the script to see if it works! But first, we're going to modify our package.json so that it looks like this:

1{
2  "name": "node_express_mysql_tutorial_p1",
3  "version": "1.0.0",
4  "description": "",
5  "main": "index.js",
6  "scripts": {
7    "start": "nodemon index.js",
8    "setup": "node setup.js"
9  },
10  "keywords": [],
11  "author": "Christopher Af Bjur",
12  "license": "ISC",
13  "dependencies": {
14    "express": "^4.17.1",
15    "mysql2": "^2.1.0"
16  },
17  "devDependencies": {
18    "nodemon": "^2.0.4"
19  }
20}
21

• Note that you should add the highlighted line to your package.json in case you're following along based on the Github repository that I linked earlier.

• Save the changes.

• Now run the following script in a terminal at the root of your project:

npm run setup

And voilá! You should get a terminal response printing the successful console messages that we defined in our functions letting you know that the database has been created and setup.

Now that you've created and setup the database it's time that we make use of it!

We'll start off by modifying the file database.js in our project root. It should then look like this:

const mysql = require("mysql2");
const CONFIG = require("./config/db");

const pool = mysql.createPool({
  connectionLimit: 10,
  ...CONFIG,
});

module.exports = pool;

Now we are done with database.js. Good job so far! Next - this is what I want you to do:

• So, save the changes and head over to index.js

• Import the connection from database.js and store it in a variable called db.

• We can also remove the defined MESSAGES variable now, since we're going to replace the simulated database from the previous tutorial with a real database.

• This means that we can also get rid of the file messagesTable.js so go ahead and delete that file.

• With these new changes made, the first part of index.js (before the actual routes/endpoints are defined) should look like this:

const express = require("express");
const db = require("./database");
const app = express();
const port = 3000;

app.use(express.json());

Now the time has come to migrate our request handlers that we created in the last section. Yay!

There's going to be quite a bit of changes for each handler so my advice is that you copy/paste the upcoming code snippets and replace them with the old ones in your project and then after each snippet, I'll explain in detail what we're doing. Note that since I've already explained how the app request methods and their callbacks work, I'll focus on the code within the callback functions. Incase you need a recap, have a look here. Sound good? Ok. Let's go!

So let's begin with our post request handler.

app.post("/", function (req, res) {
  const query = "INSERT INTO messages (text) VALUES (?)";
  const values = [req.body.text];

  db.query(query, values, function (err, result) {
    if (err) throw err;
    const message = { id: result.insertId, text: values[0] };
    res.status(200).send(message);
  });
});

Time to migrate our GET request handlers. As you probably remember from the last tutorial of these series, we have two types of GET endpoints. One for getting all the messages and the second for getting a message with a specific id.

So let's begin with the one for getting all the messages.

app.get("/", function (req, res) {
  const query = "SELECT * FROM messages";

  db.query(query, function (err, result) {
    if (err) throw err;
    const messages = result;
    res.status(200).send(messages);
  });
});

Now it's time to migrate our second GET request endpoint.

app.get("/:id", function (req, res) {
  const id = parseInt(req.params.id);
  const query = "SELECT * FROM messages WHERE id = ?";
  const values = [id];

  db.query(query, values, function (err, result) {
    if (err) throw err;
    const found = result;

    if (found.length > 0) {
      res.status(200).send(found);
    } else {
      res.status(404).send({ msg: `No message with id ${values[0]} found!` });
    }
  });
});

Time to migrate the PUT request endpoint.

app.put("/:id", function (req, res) {
  const id = parseInt(req.params.id);
  const query = "UPDATE messages SET text = ? WHERE id = ?";
  const values = [req.body.text, id];

  db.query(query, values, function (err, result) {
    if (err) throw err;
    const updated = [{ id: values[1], message: values[0] }];

    if (result.affectedRows === 0)
      return res
        .status(404)
        .send({ msg: `No message with id ${values[1]} found!` });

    res.status(200).send(updated);
  });
});

Almost done now! Time for migration of the final endpoint where we handle DELETE requests.

app.delete("/:id", function (req, res) {
  const id = parseInt(req.params.id);
  const query = "DELETE FROM messages WHERE id = ?";
  const values = [id];

  db.query(query, values, function (err, result) {
    if (err) throw err;
    const deleted = [{ id: values[0] }];

    if (result.affectedRows === 0)
      return res
        .status(404)
        .send({ msg: `No message with id ${values[0]} found!` });

    res.status(200).send(deleted);
  });
});

If you have curl installed on your computer you can follow along in this section. Note that all Mac computers, with operating system version equal to or greater than OSX, comes with cURL pre-installed.

Obviously your Express server needs to be running before you try to make any requests to it. So if it's not already running, start it.

npm run start

Executing a POST request

Next we'll make a POST request from a terminal. We'll send the request to http://127.0.0.1:3000 which is equal to localhost, at port 3000 which is the port that we use for our application. We pass a JSON request body with the message text. We also pass a request header that defines that the data that we're sending is JSON data.

curl -X POST http://127.0.0.1:3000/ -d '{"text":"New message"}' -H "Content-Type: application/json"

When sending this request from your terminal, you should get a response back that looks like this:

{"id":1,"text":"New message"}

Cool! So now we've successfully created a new message with a POST request!

We can also confirm that this message was saved in our database by doing the following:

• Open up MySQL Workbench.

• Click on the Local Instance that you created earlier.

• From the Schemas tab, right click the messages table and from the menu that pops up select Select Rows - Limit 1000 as shown in the image below.

You should then see the message that we just added through our POST request.

Executing GET requests

Now let's try to make a GET request, fetching all the messages that our server has stored so far. We should be able to fetch the 1 message that we added with the POST request. And of course if you were to add more messages, these messages should also be fetched with this request.

So let's test our GET endpoint which will return all the messages stored in the database so far.

curl -X GET http://127.0.0.1:3000/

Boom! Here's all of our messages (currently just one if you haven't sent multiple POST requests).

[{"id":1,"text":"New message"}]

Next, let's try the GET endpoint which will return a specific message based on the passed request parameter id.

So, this time, let's pass a request parameter 1 when making the GET request.

curl -X GET http://127.0.0.1:3000/1

Voila! We get the message with the requested id.

[{"id":1,"text":"New message"}]

But what if we request an id that doesn't exist in our database? Let's see.

curl -X GET http://127.0.0.1:3000/1337

Ah! We get back the error object that we previously defined - telling us that the message with id 1337 was not found. Perfect!

{"msg":"No message with id 1337 found!"}

Executing a PUT request

Now let's try updating a message. Let's update message with id 1.

curl -X PUT http://127.0.0.1:3000/1 -d '{"text":"Updated message"}' -H "Content-Type: application/json"

We get back our updated message as a response. Awesome!

[{"id":1,"text":"Updated message"}]

Let's also confirm the update in MySQL Workbench.

As you can see, the text column was updated from New message to Updated message. Awesome!

Executing a DELETE request

Finally, let's try to delete a message. Let's delete message with id 1.

curl -X DELETE http://127.0.0.1:3000/1

Works like a charm!

[{"id":1}]

And the confirmation:

Wooha! No message with id 1 in our database, which means that our DELETE request worked!

So if you were unable to test your endpoints with cURL, or just didn't feel like doing so, the time has come to test our endpoints using Postman.

Let's start off by making sure that our Express server is running. As you already know, we run it by executing the following command from our project root.

npm run start

Executing a POST request

So, open up Postman and from the top menu, select File > New Tab.

Then select POST from the dropdown menu and input http://127.0.0.1:3000/ as the request url. Note that this is the same as inputting http://localhost:3000/ so choose whatever you prefer.

Next click on Body just below the request url bar.

Select raw in the radio button selection and in the dropdown to the right of the radio buttons, select JSON. Then press the Send button.

Now in the bottom section of Postman, you should see a response consisting of the message that we just created, as well as a Status of "200 OK".

Awesome! We've just created our first message by sending our first POST request.

We can also confirm that this message was saved in our database by doing the following:

• Open up MySQL Workbench.

• Click on the Local Instance that you created earlier.

• From the Schemas tab, right click the messages table and from the menu that pops up select Select Rows - Limit 1000 as shown in the image below.

You should then see the message that we just added through our POST request.

Executing GET requests

Now let's try to make a GET request, fetching all the messages that our database has stored so far. Since we've only made one POST request so far, we should be able to get that message. How ever if you've made more than one POST request to create more messages, you should be able to fetch them all from this GET request.

So let's test our GET endpoint which will return all the messages stored in the database so far. We can do this by simply just changing the request type to GET from the top left drop-down menu. Note that we can leave the request body data as is - it will not be sent with the GET request anyways. Press Send when you've applied that change to the request type.

Now we should get some data back which we can find in the response body section of Postman.

Cool! So we were able to get all the messages that we had stored in our database (only one in my case, since I've only made 1 POST request so far). Now let's test our other GET endpoint which will return a specific message based on the request parameter passed with the request.

Simply add an id to the request URL. Let's get message with id 1. Then press Send again.

This time the response body should look like so (Yes I know it looks exactly the same as the previous response if you haven't made any extra POST requests):

Doesn't it just feel good when you get whatever you request? Love it.

Ok, so what happens if we try to request a message with an id that doesn't exist? Let's try it out. Let's use 1337 as an id.

Press Send and see what happens.

Wonderful! We're getting our error message that we defined earlier, with the statuscode of 404 which means that the item that we requested was not found on the server.

Executing a PUT request

Now let's try updating a message. Let's update message with id 1.

So first, change the request type to PUT and make sure that the request parameter is 1 in the request url.

Then before sending this, let's modify the request body.

Now press Send.

And the response body should now return our updated message.

Let's also confirm the update in MySQL Workbench.

As you can see, the text column was updated from New message to Updated message. Awesome!

Executing a DELETE request

Finally, let's try to delete a message. Let's delete message with id 1. So change the request type to DELETE and make sure that you pass the id 1 as a request parameter.

We get the deleted message back as a response within the response body.

And the confirmation:

Wooha! No message with id 1 in our database, which means that our DELETE request worked!

You're now one step closer to being table to construct a fully functional REST api using Node.js, Express & MySQL. In the next tutorial of these series, we will take the API that we just built to the next level and deploy it to a cloud service.

You can find the final source code for this tutorial here.