Hello and welcome to the 2nd article of Let's Build a MySQL Node.js CRUD App series! In the previous article, we have initialized our book review app as a React project and set up a simple database on MySQL.

If you haven't read the previous article, please find it here.

In this part, we will be integrating our database and routes into our app.

Step 1: Create an Express server

Create a server.js file in our project's root directory for the back-end. We can create an Express app with:

const express = require("express");
const mysql = require("mysql");
require("dotenv").config();

const app = express();
app.use(express.json()); // parses incoming requests with JSON payloads

Step 2: Create connection to database

Create an .env file with the variables in the form NAME=VALUE like:

DB_HOST=localhost
DB_USER=root
DB_PASSWORD=password
DB=ravenbooks

Then, we create a pool connection to our database. We can use the environment variables to protect our configurations from being seen.

//create connection to database
const db = mysql.createPool({
  host: process.env.DB_HOST, //localhost
  user: process.env.DB_USER, //root
  password: process.env.DB_PASSWORD, //password
  database: process.env.DB, //ravenbooks
});

Step 3: Create listener

Then, below it, we ask our server to listen for a request.

const listener = app.listen(process.env.PORT || 3000, () => {
    console.log('App is listening on port ' + listener.address().port)
})

By default, we set our app to listen on port 3000. However, in cases where the port number is designated from an environment variable, the app will listen on process.env.PORT.

Now run the command node server.js and the console should print out that the App is listening on port 3000.

Creating Routes

Now that we have everything set up, all we need is to create the GET, POST, PUT and DELETE routes in order to allow our app to make CRUD functions on our database.

For now, we will use POSTman, a super handy tool to test our back-end routes without having to build the front-end.

In the later parts of this series, I will implement the React front-end for this app. We are still in our server.js file to write our endpoints.

1. GET

Our app should be able to get and return all the book reviews in the MySQL database. Let's create a GET route /reviews to achieve this.

app.get("/reviews", (req, res) => {
  db.query("SELECT * FROM book_reviews", (err, result) => {
    if (err) {
      console.log(err);
    } else {
      res.send(result);
    }
  });
});

Testing the Route

We can insert some dummy data directly using the MySQL Workbench like so:

Then, while the server is still running and listening on port 3000, open POSTman and type: http://localhost:3000/reviews. Make sure the HTTP method is set to GET as shown in the image below.

2. POST

Next, let's create our POST route which will allow the app to add new data into our database.

app.post("/reviews", (req, res) => {
  const insertQuery = "INSERT INTO book_reviews SET ?";
  db.query(insertQuery, req.body, (err, result) => {
    if (err) {
      console.log(err);
    } else {
      res.send("Review Added to Database");
    }
  });
});

Testing the Route

On POSTman, configure the settings as shown in the screenshot below to POST a sample data to our database.

If our request is successful, our sample data will be added to our database like so:

3. PUT

The PUT route will be responsible for updating any data to our database. The user is allowed to update the book_rating and book_review columns.

app.put("/reviews", (req, res) => {
  const updateQuery =
    "UPDATE book_reviews SET book_review = ?, book_rating = ? WHERE id = ?";
  db.query(
    updateQuery,
    [req.body.book_review, req.body.book_rating, req.body.id],
    (err, result) => {
      if (err) {
        console.log(err);
      } else {
        res.send(result);
      }
    }
  );
});

Testing the Route

Configure the following settings on POSTman.

If the request is successful, our database should display the newly updated data like so:

4. DELETE

Last but no least, we have our DELETE route which will delete a specific row from the table based on the id value supplied in the parameters.

app.delete("/reviews/:id", (req, res) => {
  db.query(
    "DELETE FROM book_reviews WHERE id = ?",
    req.params.id,
    (err, result) => {
      if (err) {
        console.log(err);
      } else {
        res.send(result);
      }
    }
  );
});

Testing the Route

To test our route on POSTman, make sure to add the id parameters in the URL. For this example, we want to delete our sample data which we added earlier using the POST route.

So let's set the id of the row we want to delete to 4.

If successful, we will see that our sample data has been deleted from our database.

Stay tuned

And that's all for now! In this part, we have successfully created our Express server, the GET POST PUT DELETE routes we need and even tested them with POSTman.

Our back-end is done so stay tuned for the next part where we will shift the focus to implement the front-end with React. Thanks for reading this article. If it is helpful, do give a like and share. Also feel free to ask questions in the comments below. Cheers!