This is an example server side (or back end) NodeJS app. The app's main components are the database, the database access code, the web server and the API routes to access the database data and send responses for requests.
The app is server side JavaScript code. The code accesses a MySQL database table data and performs CRUD (Create, Read, Update, Delete) operations on it. An ExpressJS program is used to create the web server and the GET, POST, PATCH and DELETE web APIs.
The database is a MySQL server. It has a test database has one table called items with two columns the name and the quantity.
The app's functionality is to insert some items, update item quantity for a specific item, delete an item by its name and retrieve all the items in the database table.
The tools to try the app's functionality are to use web API programs like Postman or cURL.
In addition to the functionality, we will touch aspects of the server side web app programming like data validation, error handling, using middleware and writing test scripts.
The app also shows how the data flows between the database and the client program (in this case the Postman or the cURL). In a real web app, these tools are replaced by a browser client program written in HTML, CSS and JavaScript.
Contents
Prerequisites
To follow and try the example's code you will need basic knowledge of NodeJS and JavaScript programming, web server functions and database programming.
The example includes instructions to install MySQL Server. Your computer requires an installation of NodeJS and tools like cURL or Postman to try the app's programs.
Here are some useful links: NodeJS, ExpressJS, MySQL, cURL, Postman.
The Database
MySQL is a relational database system (RDBMS) where data is stored in tables. In this app we have an items table which has rows of data. Each row in the table represents an item. The item has two attributes - name and quantity.
An example row has a name "Coca cola" and quantity of 6. Note the name is of string data type and quantity is an integer number. The name also is the primary key for the table.
A group of related tables are stored in a database. In our app let's call it test_db.
To use this database and table in the app, first we need to install the MySQL server. Then create the database named test_db and a table named items in that database.
Install MySQL and Create Table
Here are the detailed instructions to (a) install the MySQL Server Community version 8 and (b) connect to the MySQL server using mysql client program. Once connected from the client - create the database, the table, add some data to the table and query that data.
This GitHub Gist has the details: mysql_server_install_and_use
In case you already have an existing installation of the MySQL server on your computer, you can use it and skip the installation (just create the required database and table).
The App
In the previous section, the MySQL server is installed, the database and the table are created. We are ready to write code to access the MySQL database from a NodeJS program.
First, let's create a NodeJS web server program. A web server has the ability and facilitates access to a database and communications over a network using standard format of request and response.
We are working from an operating system terminal window.
Create an application directory, for example, node-mysql-app-1. Navigate to the app directory (the root directory):
cd node-mysql-app-1
Initialize a new NodeJS project using the npm command line tool (npm is available with a NodeJS installation, by default).
npm init
Install the required NodeJS modules - express and mysql. express is the backend web app framework to build web apps and APIs. mysql is the driver for MySQL - this module is used to connect to the database and access the database data.
npm install express mysql
Next we will write code for the Web Server, Database Connection, Database Data Access and API Routes. The following sections discuss this programming.
Web Server
Create the web server program, server.js, in the app's root directory with this code:
const express = require("express");
const port = 3000;
const app = express();
app.listen(port, () => {
console.log("Web server listening on port", port);
});
Start the web server:
node server.js
You should see this message on the console indicating the web server is running.
Web server listening on port 3000
To quit the web server program you can press Control and C (CTRL+C) keys together.
Database Connection
Create the MySQL Database connection program, database.js, in the app's root directory with the following code.
Substitute your database user and password details in the code. Note the host and port have the MySQL default values "localhost" and 3306.
const mysql = require('mysql');
const conn = mysql.createConnection({
host : 'localhost',
port : 3306,
user : 'root',
password : 'root',
database : 'test_db'
});
conn.connect(error => {
if (error) throw error;
console.log('Connected to database.');
});
module.exports = conn;
Next, require the database connection program from the web server program. Add the following code to server.js. With this, when the web server is started the database.js program also runs and connects to the database.
require("./database");
NOTE: Before starting the web server again, you need to do this (only if you are using the MySQL server version 8 and not for earlier versions) for the user you are using in the app to access the MySQL server. From the mysql client:
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password';
mysql> FLUSH PRIVILEGES;
Restart the web server:
node server.js
This starts the web server and also connects to the database. You will see messages like this on the console:
Web server listening on port 3000
Connected to database.
Database Data Access
Create the data access program to perform the CRUD operations, dbFunctions.js, in the app’s root directory.
Note that only the insert and read functions are shown below. The code for the update and delete queries and their routes are not shown in the article. These are available in the attached source code.
const addItem = function(conn, data, callback) {
const sql = 'INSERT INTO items SET ?';
conn.query(sql, data, function (error, results) {
callback(error, results);
});
}
const getItems = function(conn, callback) {
const sql = 'SELECT * FROM items';
conn.query(sql, function (error, results) {
callback(error, results);
});
}
module.exports = {
getItems,
addItem
};
API Routes
Create the route definitions for the add and read functions in a program, apis.js. Create this program in the app’s root directory. These definitions correspond to the database access code for the two functions.
Routing determines how the app responds to client requests to a particular web API (or endpoint). This web API is a URI (or path) and a specific HTTP request method like GET, POST, PATCH or DELETE.
const express = require("express");
const router = express.Router();
const dbFunctions = require("./dbFunctions");
const connection = require("./database");
router.post("/items", (req, res, next) => {
const data = req.body;
if (!data.name || !data.quantity || isNaN(new Number(data.quantity))) {
res.status(404).send("Name and quantity (a number) are required fields.");
return;
}
dbFunctions.addItem(connection, data, (err, result) => {
if (err) {
next(err);
}
else {
res.send({ message: result.affectedRows + " row added." });
}
});
});
router.get("/items", (req, res, next) => {
dbFunctions.getItems(connection, (err, result) => {
if (err) {
next(err);
}
else {
result = result.map(row => JSON.parse(JSON.stringify(row)));
res.send(result);
}
});
});
module.exports = router;
This also requires related changes in the server.js program:
const apiRoutes = require("./apis");
app.use(express.json());
app.use("/api", apiRoutes);
Restart the web server and make sure the web server starts without any issues: node server.js
Insert and Query Data
At this point we have all the coding completed for this app. We have four programs each with a specific functionality - server.js, database.js, dbFunctions.js and apis.js. The database has the required table definition.
Now, we can try the app's functionality using a client tool. We can use an API client tool like cURL (a command line tool) or Postman (a desktop GUI tool). We can work with the web APIs we had created earlier to perform the CRUD operations on the database.
You need to install one of these tools. Here is an example usage of cURL.
Using cURL
The following cURL commands are entered from the terminal. These commands, upon a successful API call returns the appropriate response with a message and a status code as defined in the app.
Note that the web server and the MySQL are up and running.
Here are the four commands for the POST, GET, PATCH and DELETE requests for the corresponding CRUD operations:
curl --request POST http://localhost:3000/api/items --json "{ \"name\": \"Cookies\", \"quantity\": 4 }"
curl --request GET http://localhost:3000/api/items
curl --request DELETE http://localhost:3000/api/items/Apples
curl --request PATCH http://localhost:3000/api/items/Cookies --json "{ \"quantity\": 8 }"
Let's try the POST request to add an item. On successful operation the response would be: { "message": "1 row added." }.
Lets take a case of invalid data input where the quantity is not provided in the POST request - "{ \"name\": \"Cookies\", \"quantity\": \"\" }". Then the response would be a message Name and quantity (a number) are required fields.
You can also use the cURL's -i option to get more details about the response. For example, the GET request shows the response status code of 200 OK, the content type is JSON and other details:
HTTP/1.1 200 OK
X-Powered-By: Express
Content-Type: application/json; charset=utf-8
Content-Length: 62
ETag: W/"3e-VijHvdqklKrbHUTM2ZwqU4NqTgo"
Date: Tue, 13 Sep 2022 14:02:52 GMT
Connection: keep-alive
Keep-Alive: timeout=5
[ { "name": "Cookies", "quantity": 4 } ]
Note that without the -i option, only the data is shown in the response output:
[ { "name": "Cookies", "quantity": 4 } ]
Further, we can use these web APIs in a browser client program developed using HTML, CSS and JavaScript. The web API calls can be made from such client porgram using the JavaScript fetch or axios JavaScript library. Note that this coding is not shown in this article.
The App Development
Until now we have seen how data is stored and accessed from within the web server program and from a client program using the web APIs.
There are aspects to this app which are important in the development process. These are - writing modular code, data validation, error handling, security and testing. There is a web application feature, the middleware, and it plays a part in the data validation and error handling programming.
Modular Code
The modular code here refers to having separate programs based upon the functionality within the app. For example, the database CRUD code is in a program dbFunctions.js. The other programs in this app are the server.js, database.js and the apis.js - each with its own functionality.
This facilitates easy maintenance and readability of the app's code. For example, if we want to add new APIs for another set of data within the same app we can simply create new programs for the database functions and the APIs.
Data Validation
Data validation is to assure any data input into an app has the correct quality so that it can be stored and used. Data validation can be in the client program, in the web app or defined within the database.
A user entering invalid data in the browser app can be validated and a message be shown in a popup window. When such validation is not there or is not adequate, the validation can be performed within the web app's server side code.
For example, in this app, if the item name is empty or the quantity is not a valid number, that data cannot be saved to the database. The client program receives a response message about the invalid input data and so that corrected data can be input again.
Here is an example of the POST request API definition to add data to the database - in the apis.js program.
router.post("/items", (req, res, next) => {
const data = req.body;
if (!data.name || !data.quantity || isNaN(new Number(data.quantity))) {
res.invalidInput("Name and quantity (a number) are required fields.");
return;
}
// ... code to add valid data to the database and send a okay response
});
The following middleware is used to send a response in case the validation fails. This router level middleware applies for each of the requests, which can invoke the res.invalidInput function with the provided message.
Note the HTTP response status code 404 is used in this case. Typically this status is used with client data errors. An okay response would have a 200 status code.
router.use((req, res, next) => {
res.invalidInput = (message) => {
console.log(message);
res.status(404).send(message);
}
next();
});
The middleware defines a function invalidInput which takes a message as a parameter, logs the message and sends a response. In a route's handler function this method is invoked, for example: res.invalidInput("Name and quantity (a number) are required fields.");
Database Level Validation
Data validation can also be defined at database level.
For example, if we try to insert a non-numeric data for quantity into the database directly from the mysql client using SQL insert, like "abc", we will see an error somewhat similar to this: ERROR 1366 (HY000): Incorrect integer value: 'abc' for column 'quantity'.
In our program we already have the data validation for such situation. But, an app can have data stored in many tables, with more fields, data, and functionality. All the validation cannot be handled at client or the web app level alone.
Databases allow define validation rules like unique indexes, not null and check constraints. The validation failures can be intercepted and interpreted within the program using SQL error codes and the program can respond to the client appropriately.
One such example is that we have defined a Primary Key constraint on the items table name column. That is if we try to insert a row with an already existing name, there will be an error like this: ERROR 1062 (23000): Duplicate entry 'Books' for key 'items.PRIMARY'. The error has a number 1062. This number is used within the app to identify the type of error and act appropriately.
In the app this is handled by the error handling middleware (more details in the next section), to send a message to the client - "Duplicate data entered - try again".
router.use((err, req, res, next) => {
console.error(err);
let msg = "Internal server error";
let code = 500;
if (err.code === "ER_DUP_ENTRY" && err.errno === 1062) {
// Message for the duplicate key error
msg = "Duplicate data entered - try again";
code = 409;
}
res.status(code).send(msg);
});
Error Handling
A data validation error, discussed above, is not a severe error and the client can supply valid data upon reviewing the warning message.
In a web app, when there is a database error it is considered a severe error. Typically, these errors cannot be recovered from by the app or by the user. An example is that when the app cannot connect to the database.
Let's take an example of the MySQL SELECT query, defined in dbFunctions.js:
const getItems = function(conn, callback) {
conn.query('SELECT * FROM items', function (error, results) {
callback(error, results);
});
}
In case of a database error, the route handler function has the code to catch the error in, apis.js:
router.get("/items", (req, res, next) => {
dbFunctions.getItems(connection, (err, result) => {
if (err) {
// This triggers the error handling middleware
// (discussed below)
next(err);
}
else {
// ... send normal response
}
...
The error handling middleware logs the error and sends a response with status 500 indicating a server side error. Now the client knows it's a severe error and this cannot be recovered from. The user can ask for help from the server side developers.
router.use((err, req, res, next) => {
console.error(err);
let msg = "Internal server error";
let code = 500;
if (err.code === "ER_DUP_ENTRY" && err.errno === 1062) {
// Message for the duplicate key error
msg = "Duplicate data entered - try again";
code = 409;
}
res.status(code).send(msg);
});
Note that the middleware handler function has error object as the first parameter. It indicates the middleware is an error handling middleware.
Middleware Usage
In a web app the middleware functions have access to request and response objects and can transform them and perform some action on them.
We have already seen the usage of middleware in the previous two sections - Data Validation and Error Handling. Here are more of them.
The request handler accessess the incoming JSON payload. For example, in case of the POST request the item name and quantity are supplied as JSON, { "name": "Cookies", "quantity": 8 }. For the request handler to access this data we use the middleware in the server.js program: app.use(express.json());
The express.json() is a built in middleware function. It parses requests for JSON payloads and results in req.body with the data.
Another usage is to warn a client trying to access other than the defined web APIs. This is defined at the application level.
app.use((req, res) => {
console.log("Invalid path", req.path);
res.status(404).send( { message: "Invalid path!" } );
});
The last one is to specify the router as a middleware for a path:
app.use("/api", apiRoutes);
Security
An app's security can be defined at various levels - the web app and the database. The common concepts are the authentication and authorization. This app is not configured with any of these, except for the basic access to the database.
At the time of MySQL server data initialization, MySQL creates a root user (the section Install MySQL and Create Table has details). In addition, we can create other users with specific privileges, for example, specific to an app.
Testing
The main functionality of this app is the web APIs. These allow us to perform CRUD operations from a web client.
Now, we create tests to validate the API functionality. These would be the functional tests.
The tests use Mocha framework for a test runner and the SuperTest for API test scripts.
The APIs using the four HTTP methods (POST, GET, PATCH and DELETE) are tested for their basic CRUD functionality. These are the four success scenarios. Here we are assuming the input data is valid, the web server and database are up and running.
We also test for data validation failure and database failure - one case for each.
The tests are defined in the ../test/appTest.js program. Note the test program is in a separate directory within the app's root directory.
We install the required packages:
npm install mocha supertest --save-dev
Next, modify the package.json to run the tests using Mocha. This will allow starting the tests by typing, npm run test.
"scripts": {
"start": "node server.js",
"test": "mocha ./test/ -u tdd"
},
Here is an example test script, for the POST request success scenario:
test('Add an item: POST request to /api/items', function(done) {
const dataToAdd = {
name: 'Apples',
quantity: 5
};
const expected = {
message: '1 row added.'
};
request(app)
.post('/api/items')
.set('Accept', 'application/json')
.send(dataToAdd)
.expect(200)
.expect('Content-Type', /json/)
.expect(expected)
.end(function(err, res) {
if (err) return done(err);
return done();
});
});
Note the test will pass (assuming the web server is running, the MySQL database is created and the app has a connection to it). The response will have a 200 status with the message defined as expected and is of type JSON.
Here is an example with all tests completed with success in a screenshot:
Conclusion
We have tried an example server side NodeJS and JavaScript app. This is a web server built using the ExpressJS. This has functionality to access a MySQL database and perform CRUD operations on it using web APIs. The app's functionality is tried using a cURL client.
An app's programming has various aspects – like writing clear and modular code, data validation so that the input data is stored as required, inform users of validation errors and handle database level errors. The web app's middleware is used to perform these functions.
Finally, there are scripts to test the functionality using testing tools Mocha and SuperTest.
The source code for the programs used in this example app are at this GitHub repo: programming-a-nodejs-app
Top comments (0)