In this tutorial, you’ll build a simple database for adding, updating, and removing todos, this tutorial will show you how to create a REST API with Express and MySQL. The GitHub repository for this project can be cloned to follow along. Let’s get started, shall we?
Getting started
This tutorial is a hands-on demonstration. Be sure you have the following in place before getting started:
- MySQL Database installed
- Node.js installed
What is MySQL databases
MySQL is an open-source relational database management system (RDBMS) (RDBMS). It’s the most often used database system with PHP. MySQL is a cloud-native database solution that comes with full management. HeatWave, a built-in high-speed query accelerator, increases MySQL performance by 5400x.
Oracle Corporation created, distributed, and maintained MySQL, which has the following functionalities.
The data in a MySQL database is organized into tables with columns and rows.
- MySQL is a server-based database management system.
- MySQL is a great choice for both small and large projects.
- MySQL is a database system that is extremely quick, dependable, and simple to use. It makes use of normal SQL.
- MySQL runs on a variety of platforms.
Project Setup
Now that we have explored what MySQL database is all about, let’s dive into creating our RESTFul application. First, we need to create a folder for our project with the command below:
mkdir rest-todos && cd rest-todos
The above code will create a rest-todos folder and change the current directory to it. Then, using the command below, we’ll create a new node.js project:
npm init -y
Next, we’ll install the dependencies we require for our project.
npm install express MySQL cors
Creating our Express server
Now that we have our dependencies installed let’s create an app.js file and add the following code snippet below to it. We’ll import the following:
const express = require("express");
const cors = require("cors");
const AppError = require("./appError");
const errorHandler = require("./errorHandler");
Next, we create an app instance from express, using the express.json() middleware in our app to parse the URL encoded body. Finally, we make our API router middleware listen to incoming requests to the URL specified.
Then, we check for URLs missing from our endpoints and throw a 404 error to the user if they’re accessed. The global error handler will handler we will have it setup in a later section.
app.use(api, router);
app.all("*", (req, res, next) => {
next(new AppError(`The URL ${req.originalUrl} does not exists`, 404));
});
app.use(errorHandler);
const PORT = 3000;
app.listen(PORT, () => {
console.log(`server running on port ${PORT}`);
});
module.exports = app;
connecting to MySQL
Now let’s go ahead and set up our MySQL Database. First, we’ll open our MySQL shell with the command below:
mysql -u root -p
The above command will prompt for your root password. Enter the password and press the Enter key to continue.
Then, create a database from your MySQL shell with by running the SQL statements below.
CREATE DATABASE
Then, create the tasklist table by running the SQL statements below. The table will have an id, name, status, date_created fields. The id is the primary key of our table.
CREATE TABLE tasklist(id int NOT NULL AUTO_INCREMENT,
name varchar(50) NOT NULL,
status varchar(50),
date_created DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id));
Next, create a dbConfig.js file and add the following code snippets below to it to connect the application to MySQL database.
const mysql = require('mysql');
const connection = mysql.createConnection({
host: "localhost",
user: "root",
password: "1234",
database: "todo",
});
connection.connect();
module.exports = connection;
Create our controllers
Let’s get started building our application’s routes.
Create a controllers folder in our project root directory, then an app.js file in the controllers folder.
Our global error handler and MySQL database connection will be imported first.
const AppError = require("./appError");
const conn = require("./dbConfig");
The next step is to setup our getAll handler, which will retrieve all of the todos in our database. The MySQL query method will be used in this handler, which takes a SQL query and a callback function as inputs. We’ll use the AppError class to return an error to the user if an error happens during the operation.
exports.getAll = (req, res, next) => {
conn.query("SELECT * FROM tasklist", function (err, data, fields) {
if(err) return next(new AppError(err))
res.status(200).json({
status: "success",
length: data?.length,
data: data,
});
});
};
Next, create our createTask handler to add new todos to our database. First, we need to check if the user is sending an empty form before saving the data.
exports.createTask = (req, res, next) => {
if (!req.body) return next(new AppError("No form data found", 404));
const values = [req.body.name, "pending"];
conn.query(
"INSERT INTO tasklist (name, status) VALUES(?)",
[values],
function (err, data, fields) {
if (err) return next(new AppError(err, 500));
res.status(201).json({
status: "success",
message: "todo created!",
});
}
);
};
Then, to get our todos by IDs, we develop a getTask handler. First, we’ll see if the id is supplied in the request field, and if it isn’t, we’ll return an error to the client.
exports.getTask = (req, res, next) => {
if (!req.params.id) {
return next(new AppError("No todo id found", 404));
}
conn.query(
"SELECT * FROM tasklist WHERE id = ?",
[req.params.id],
function (err, data, fields) {
if (err) return next(new AppError(err, 500));
res.status(200).json({
status: "success",
length: data?.length,
data: data,
});
}
);
};
Next, to update our todos, we’ll write our updateTask handler, which will edit the todo whose id is in the request parameter to be completed.
exports.updateTask = (req, res, next) => {
if (!req.params.id) {
return next(new AppError("No todo id found", 404));
}
conn.query(
"UPDATE tasklist SET status='completed' WHERE id=?",
[req.params.id],
function (err, data, fields) {
if (err) return next(new AppError(err, 500));
res.status(201).json({
status: "success",
message: "todo updated!",
});
}
);
};
Finally, to delete a todo from our database, we’ll create a deleteTask handler. To delete a todo whose id is the request argument, we’ll use the delete statement.
exports.deleteTask = (req, res, next) => {
if (!req.params.id) {
return next(new AppError("No todo id found", 404));
}
conn.query(
"DELETE FROM tasklist WHERE id=?",
[req.params.id],
function (err, fields) {
if (err) return next(new AppError(err, 500));
res.status(201).json({
status: "success",
message: "todo deleted!",
});
}
);
}
Create our Error handlers
Now that we have all our API controllers setup, let’s go ahead and create our error class function with the code snippet below:
class AppError extends Error {
constructor(msg, statusCode) {
super(msg);
this.statusCode = statusCode;
this.error = `${statusCode}`.startsWith('4') ? 'fail' : 'error';
this.isOperational = true;
Error.captureStackTrace(this, this.constructor);
}
}
module.exports = AppError;
Then, without stopping our program, check for probable issues and deliver the associated error and status code to the client.
module.exports = (err, req, res, next) => {
err.statusCode = err.statusCode || 500;
err.status = err.status || "error";
res.status(err.statusCode).json({
status: err.status,
message: err.message,
});
};
Create routes
Now let’s create API routes to access our controllers. Create a router.js file and add the code snippets below:
const express = require("express");
const controllers = require("./controllers");
const router = express.Router();
router.route("/").get(controllers.getAllTodos).post(controllers.createTodo);
router
.route("/:id")
.get(controllers.getTodo)
.put(controllers.updateTodo)
.delete(controllers.deleteTodo);
module.exports = router;
Now update the app.js file to import our router with the code snippet below:
...
const router = require("./router")
app.use("task/", router);
...
Conclusion
Throughout this tutorial, you’ve learned how to build a REST API in Node.js by creating a todo application. You add more features like authentication, and authorisation to the application you just built.
