In this getting started article, let’s talk about how we can develop a simple RESTful API which performs basic CRUD operations (Create, Retrieve, Update and Delete) on table data by connecting and integrating with MySql database.
Getting started with NodeJs and Express:
NodeJs is one of the popular server-side programming languages which can be used to develop APIs or web applications in a simple manner. It uses JavaScript for scripting and runs on Chrome V8 engine offering a simple but powerful asynchronous programming stack. Its widely used for applications and APIs which involve I/O operations while its not so recommended for APIs which require huge CPU and computation complexities. To develop applications in NodeJS, we must first install NodeJS Sdk which is available for all operating systems.
To create our first API lets begin by creating a package.json file which is the configuration file for NodeJs applications that maintains the libraries used, the application metadata and so on. Let’s create a directory :node-express-mysql-app, which hosts our NodeJs project. In the directory, open a terminal or a command prompt and type:
> npm init
which shall create a package.json after taking inputs like the js file name, the package, version, description, test commands and so on. Once created, let’s create a new file called app.js, which contains the API script and is executed when the Node API runs. Make sure that the property "main" in the package.json has the value "app.js" which is the name of the script file we have now created.
:node-express-mysql-app>npm init
This utility will walk you through creating a package.json file.
It only covers the most common items, and tries to guess sensible defaults.
See `npm help json` for definitive documentation on these fields
and exactly what they do.
Use `npm install <pkg>` afterwards to install a package and
save it as a dependency in the package.json file.
Press ^C at any time to quit.
package name: (express-mysql-app)
Next, lets install ExpressJs in the project. ExpressJs is a minimalistic framework which helps building APIs and WebApps in NodeJS faster and simpler. It offers a rich library and utilities for simpler but powerful Routing, Request/Response content handling, Middlewares and so on. Simply put, it makes scripting APIs in NodeJs a lot easier when compared to script without it.
To install express in our application, run the below command in the same terminal or command prompt we used before.
> npm install express --save
It installs the express framework as a node module inside the application, and the source library sits inside the node_modules folder which is now created. We shall also install another library called "body-parser", which helps us in accessing the request body in a simpler way.
> npm install body-parser --save
In NodeJS, all the libraries which we wish to use in our application are installed and added to the node_modules and a mapping is added in the "dependencies" property of the package.json file. The package.json keeps track of the libraries referenced in the project using the "dependencies" property in the json.
When we check-in our applications to any repository, we generally add the other files barring these node_modules folder, and when we want to run the application we start by installing the node_modules first:
> npm install
Back to app.js, we start by importing the libraries which we shall be using in our application. In NodeJs, we import the libraries express and body-parser to use in our API as below:
const express = require('express');
const bodyParser = require('body-parser');
const port = 3000;
And then create an Express application, which holds all the routes and handlers as below:
// create a new Express application
// the object holds the entire API
// we're gonna design
const app = express();
// tranform the request object into json
// useful for handling application/json
// contentTypes in a simpler way
app.use(bodyParser.json());
Finally, we configure the "express app" we’ve just created to listen on a port for requests and generate responses.
app.listen(port, () => {
console.log("server started to listen on " + port);
});
This completes our initial setup of our API. To run this, let’s add a "start" command to our list of commands in package.json. The value is as follows:
"scripts": {
"start": "node app.js",
"test": "echo "Error: no test specified" && exit 1"
},
We can then simply give the following command:
> npm start
Which starts up the Node server and runs the Express application on top of it. We can see the following in the terminal window which confirms the same:
node-express-mysql-app>npm start
> express-mysql-app@1.0.0 start node-express-mysql-app
> node app.js
server started to listen on 3000
The next step is to add endpoints to this application, through which the application receives requests and returns responses. These endpoints are configured to run on "routes" with a variety of configurations such as path params, query params and so on. You also have the method types such as GET, POST, PATCH, PUT an DELETE which represent the nature of operation these endpoints perform.
Creating an endpoint is super-easy in express, we have methods for each of the method type: app.get(), app.post(), app.patch(), app.delete() and app.put() with each having two parameters: a route string and a callback in which the processing happens.
app.method_type(route_string,
(requestObject, responseObject) => {
// magic happens here
});
Now that we have created an Express application and have looked at how to create endpoints, let’s jump into real-world scenarios of integrating these endpoints with a real database.
Working with Database – Enter MySql:
In a real-world application we work with real datastores for querying and updating data by means of RESTful APIs. These datastores or databases contain data stored in the form of entities, which the APIs are responsible for change. To integrate capability for working with data from database, we make use of database clients which form the missing link between the API and the database. In our example, we make use of a MySql database that stores the data pushed using these APIs and fetch data from the same.
To begin with, we work with a local MySql server which contains the database we’re interested to work with. To make things simple, we make use of a MySql docker image we’ve created in a previous article, to serve as a localhost database server for us.
Setting up and Using MySQL as a Container via Docker Compose
The localhost server contains a database "localdb" which has a table "users" representing a collection of users. Our goal is to connect to this database server and integrate our APIs to work with this "users" table for querying, insertion, deletion and updation. The schema of the table looks like below:
describe users;
+---------+-------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+-------------------+-----------------------------------------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | | NULL | |
| email | varchar(50) | NO | | NULL | |
| addedon | timestamp | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+---------+-------------+------+-----+-------------------+-----------------------------------------------+
For a headstart, let’s add two rows to this table so that we can begin with a GET call. Observe that since the id is auto_increment and the addedon has a Default value, we are required to only work with the two fields email and name.
insert into users(name, email) values('killua', 'killua@abc.com');
insert into users(name, email) values('gon', 'gon@abc.com');
To connect and work with this database in our application, we require a mysql client library which is available as a node module. To install the client library run the following command:
> npm install mysql --save
Import the mysql client in our application in the same way as we did before using the require() statement.
const express = require('express');
const bodyParser = require('body-parser');
const mysql = require('mysql');
const port = 3000;
We can connect to MySQL using two ways:
- we can use the standard way of mysql.createConnection() method with the database configuration as a parameter which returns a connection object.
- we can create a pool of connections from which we can pick an available connection object when required and release it once our job is over.
In this article we follow the second approach, which is a safe way of handling connections in scenarios when multiple connections can be made for multiple requests.
const pool = mysql.createPool({
host: "127.0.0.1",
port: 3306,
user: "root",
password: "abcd@1234",
database: "localdb"
});
Let’s say we have a table "users" in our database "localdb" in the local MySQL server which we’re interested in designing APIs for. We’d want to design APIs to Create, Update, Delete User from the "users" table along with Retrieving all the "users" from the table.
To achieve this, we shall design four API endpoints which are POST, PATCH, DELETE and GET API calls. In each of these API endpoints, we follow a similar approach:
- Create connection with database
- Run the command / querying
- Close connection
- Return the result
Fetch all records – GET:
To fetch all the "users" from the table, we use a GET endpoint that performs a simple SELECT * statement on the table and returns the rows. To create a GET route which returns all the "users", we design as follows:
app.get("/api/users", (req, res) => {
// req holds the REQUEST object
// res is the RESPONSE to be sent
});
In this function, we follow the above four steps to connect, query and return the result.
app.get("/api/users", (req, res) => {
// 1. fetch a connection from pool
pool.getConnection((err, conn) => {
if (err) throw err;
// 2. run the query / command
conn.query("SELECT * from users",
(error, results, fields) => {
// 3. release the connection
conn.release();
if (error) throw error;
// 4. return the results in response
// res.send() writes the content to
// the RESPONSE stream
res.send(results);
});
});
});
In the above code snippet, we have created an endpoint "/api/users" which queries for ALL records in "users" table and writes the resultant records as response.
The Request / Response shall be as follows:
GET /api/users HTTP/1.1
Host: localhost:3000
Response:
[
{
"id": 1,
"name": "killua",
"email": "killua@abc.com",
"addedon": "2020-06-27T13:01:47.000Z"
},
{
"id": 2,
"name": "gon",
"email": "gon@abc.com",
"addedon": "2020-06-27T13:01:48.000Z"
}
]
Create a Record – POST:
To write a new Record to the "users" table, we use the POST endpoint which receives the payload in the request body and following the same four steps we issue a "command" to insert new row into the table. The POST endpoint looks like below:
app.post("/api/users", (req, res) => {
// the body is a JSON object
let body = req.body;
// the payload passed in the POST
// request can be accessed similar to
// a JSON object because of the bodyParser
// middleware we have added before to the app
let user = {
"name": body["name"],
"email": body["email"]
};
// 1. fetch a connection from pool
pool.getConnection((err, conn) => {
if (err) throw err;
// 2. run the query / command
conn.query("INSERT INTO users SET ?", user,
(error, results, fields) => {
// 3. release the connection
conn.release();
if (error) throw error;
// 4. return the results in response
res.send(results);
});
});
});
In the conn.query() method, we’ve used an overload which accepts an object that shall be substituted in the SQL statement we’re running. In this case, we’re running a special INSERT syntax of MySQL that "SETs" values into the table collectively.
The command can be read as:
INSERT INTO users
SET
name = user.name
email = user.email
since these two are the only columns which are to be explicitly specified (id is an auto_increment and added_on defaults to current time), the INSERT statement works without issues.
The Request and Response shall be as follows:
POST /api/users HTTP/1.1
Host: localhost:3000
Content-Type: application/json
{
"name": "leorio",
"email":"leorio@abc.com"
}
Response:
{
"fieldCount": 0,
"affectedRows": 1,
"insertId": 4,
"serverStatus": 2,
"warningCount": 0,
"message": "",
"protocol41": true,
"changedRows": 0
}
The results object contains the INSERT summary: the number of records added, the insertId which is a sort of current_row_id and so on.
Update a Record – PATCH:
Update goes in similar lines with Insert, except that the record already exists and the values need to be "modified". To do this, we can either employ a PUT call or a PATCH call. In this example, let’s go by PATCH call which represents "MODIFY" one or more properties in the object and not replace the entire object itself. We receive two kinds of inputs from the client, a path parameter which represents the userId on which the MODIFY works and the payload containing the list of properties and their new values. The PATCH endpoint looks like below:
// the user_id is a PATH parameter
app.patch("/api/users/:user_id", (req, res) => {
// read the path parameter from the
// req object
let userid = req.params["user_id"];
// read the payload
let body = req.body;
let name = body["name"];
let email = body["email"];
// prepare the UPDATE command
let query = `UPDATE users SET name='${name}',email='${email}' WHERE id=${userid}`;
// 1. fetch a connection from pool
pool.getConnection((err, conn) => {
if (err) throw err;
// 2. run the query / command
conn.query(query, (error, results, fields) => {
// 3. release the connection
conn.release();
if (error) throw error;
// 4. return the results in response
res.send(results);
});
});
});
The Request/Response looks like below:
PATCH /api/users/1 HTTP/1.1
Host: localhost:3000
Content-Type: application/json
{
"name":"kurapika",
"email":"kurapika@abc.com"
}
Response:
{
"fieldCount": 0,
"affectedRows": 1,
"insertId": 0,
"serverStatus": 2,
"warningCount": 0,
"message": "(Rows matched: 1 Changed: 1 Warnings: 0",
"protocol41": true,
"changedRows": 1
}
Similar to the INSERT, the UPDATE result contains metadata about the number of rows affected, the status and so on. In this case, there was one row which matched that was changed.
Delete a Record – DELETE:
Deleting a record works similar to a GET call – there are no payloads whatsoever, and the only parameter passed is the recordId which is to be deleted from the table as a path parameter. The endpoint is as follows:
app.delete("/api/users/:user_id", (req, res) => {
// receive parameter from the path
let userid = req.params["user_id"];
// prepare the command
let query = `DELETE FROM users WHERE id=${userid}`;
// 1. fetch a connection from pool
pool.getConnection((err, conn) => {
if (err) throw err;
// 2. run the query / command
conn.query(query, (error, results, fields) => {
// 3. release the connection
conn.release();
if (error) throw error;
// 4. return the results in response
res.send(results);
});
});
});
The Request/Response is as follows:
DELETE /api/users/1 HTTP/1.1
Host: localhost:3000
Response:
{
"fieldCount": 0,
"affectedRows": 1,
"insertId": 0,
"serverStatus": 2,
"warningCount": 0,
"message": "",
"protocol41": true,
"changedRows": 0
}
The Result object shows that there was one row affected; in this case it was due to deletion. In this way, we have now created a simple API which does all the standard operations on an entity in mysql database. In real-world scenarios, we replace the localhost database with a production scale database provider and our APIs shall be running in a production server with a mapped domain.
The source code for the example used in this article is available under the repo: https://github.com/referbruv/node-express-mysql-simple-api
In the next article, we shall see how we can deploy this express application as an AWS Lambda and access it via API Gateway, all like in a real-world application.
Found this article helpful? Please consider supporting!
