Create a CRUD Application with Node.js and MySQL

Introduction

Node.js is an open-source platform for building fast and scalable network applications. MySQL is a reliable Relational Database Management System (RDBMS) and is a good choice for building CRUD (Create, Read, Update, and Delete) applications with Node.js.

A CRUD application performs the following operations:

• Create: adds data to a MySQL database using the SQL INSERT command.
• Read: queries data from a database using the SQL SELECT command.
• Update: modifies data records using the SQL UPDATE command.
• Delete: removes records from a database using the SQL DELETE command.

Many data-driven applications run on top of the CRUD programming pattern, including blogs, company portals, e-commerce software, enterprise resource planning applications, and more. This guide implements a CRUD application with Node.js and MySQL on Ubuntu 20.04 server.

Prerequisites

Before you begin:

• Deploy an Ubuntu server.
• Install and secure a MySQL server.
• Set up a Node.js programming environment. You may skip step 2 (Install Express.js) because you don’t require the Express.js dependency to test this guide.

1. Set Up a Database and a User Account

In this guide, your sample application permanently stores data in a MySQL database. Follow the steps below to initialize the database and create a user account:

1. Log in to the MySQL server as root.$ sudo mysql -u root -p
2. Enter your password and press ENTER to proceed. Then, issue the SQL commands below to create a sample my_shop database and a my_shop_user account. Replace EXAMPLE_PASSWORD with a strong password to secure the MySQL account against brute-force attacks.mysql> CREATE DATABASE my_shop; CREATE USER 'my_shop_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'EXAMPLE_PASSWORD'; GRANT ALL PRIVILEGES ON my_shop.* TO 'my_shop_user'@'localhost'; FLUSH PRIVILEGES; Output.... Query OK, 0 rows affected (0.01 sec)
3. Switch to the new my_shop database.mysql> USE my_shop; Output.Database changed
4. Create a products table. This table stores products’ information including: the unique product_id, product_name, and retail_price. Issue the AUTO_INCREMENT keyword to allow MySQL to auto-increment the product_id column when you insert new records into the products table.mysql> CREATE TABLE products ( product_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, product_name VARCHAR(50), retail_price DOUBLE ) ENGINE = InnoDB; Output.Query OK, 0 rows affected (0.01 sec)
5. Insert sample records into the products table.mysql> INSERT INTO products (product_name, retail_price) values ('LEATHER BELT', 18.45); INSERT INTO products (product_name, retail_price) values ('BLUETOOTH SPEAKER', 75.95); INSERT INTO products (product_name, retail_price) values ('RECHARGEABLE TORCH', 35.85); Output.... Query OK, 1 row affected (0.02 sec)
6. Query the products table to ensure the data is in place.mysql> SELECT product_id , product_name, retail_price FROM products; Output.+------------+--------------------+--------------+ | product_id | product_name | retail_price | +------------+--------------------+--------------+ | 1 | LEATHER BELT | 18.45 | | 2 | BLUETOOTH SPEAKER | 75.95 | | 3 | RECHARGEABLE TORCH | 35.85 | +------------+--------------------+--------------+ 3 rows in set (0.00 sec)
7. Log out from the MySQL server.mysql> QUIT; Output.Bye

2. Create a database_gateway Class

When working on a Node.js project, it is conventional to separate your application into manageable modules distributed across different files. This approach allows you to debug the application quickly and makes support easier. This step focuses on creating a central database class that you can re-use across the project to access your database.

1. Begin by creating a new project directory for the application.$ mkdir project
2. Switch to the new project directory.$ cd project
3. Open a new database_gateway.js file in a text editor.$ nano db_gateway.js
4. Add the following information to the database_gateway.js file. Remember to replace EXAMPLE_PASSWORD with the correct MySQL password for the my_shop_user account.class db_gateway { constructor() { } getDb() { const mysql = require('mysql'); const db_con = mysql.createConnection({ host: "localhost", user: "my_shop_user", password: "EXAMPLE_PASSWORD", database: "my_shop" }); db_con.connect(function(err) { if (err) { console.log(err.message); } }); return db_con; } execute(sql, params, callBack) { var db_con = this.getDb(); db_con.query(sql, params, function (err, result) { if (err) { callBack(err, null); } else { callBack(null, "Success"); } }); } query(sql, params, callBack) { var db_con = this.getDb(); db_con.query(sql, params, function (err, result) { if (err) { callBack(err, null); } else { callBack(null, result); } }); } } module.exports = db_gateway;
5. Save and close the db_gateway.js file.

The db_gateway.js file explained:

• The single db_gateway {} class wraps all the database functions that you require to connect to the database and perform CRUD operations.class db_gateway { .... }
• The constructor() {} method is empty because you’re not passing or initializing any default variables for this class.
• The db_gateway {} class has three main methods (functions) as explained below:
  • getDb(), this method connects to the MySQL database and returns a re-usable connection (return db_con;) implemented by the execute() and query() methods.
  • execute(): this method performs the SQL INSERT, UPDATE, and DELETE commands and returns a Success message in a callBack() function.
  • query(): this method performs the SQL SELECT command and returns an associative array containing data from the products table.
• Mapping the database functions in a separate class allows you to use the following classic code to call the methods later in this guide:var db_gateway = require('./db_gateway.js'); var dg = new db_gateway(); dg.execute(sql, params, callBack); dg.query(sql, params, callBack);
• As you might have noticed, Node.js uses a lot of callbacks. A callback is a function that runs when a task completes. This callback model allows Node.js to support high concurrency because it prevents functions from blocking each other.
• The module.exports = db_gateway; line at the end allows you to avail the db_gateway class in other files using the require('./db_gateway.js'); statement.

The db_gateway class is now ready. This sample application later implements the db_gateway class in different files to perform database operations.

3. Create a products Class

The number of resources or endpoints in a CRUD application can range from one to a few hundred. That number depends on the complexity of the application. For instance, the following are a few resources and HTTP endpoints for a typical e-commerce application:

Resource    Endpoint
----------------------

products    /products

categories  /categories

customers   /customers

orders      /orders

payments    /payments

...

When designing your application, you must create separate classes for all resources to make a neat code that you can debug and fix with little effort. This guide has one resource/endpoint (products) for fetching data from the products table. Follow the steps below to create a class for the resource:

1. Open a new products.js file in a text editor:$ nano products.js
2. Enter the following information into the products.js file.class products { constructor(dg) { this.dg = dg; } insertRecord(jsonData, callBack) { var sql = "insert into products (product_name, retail_price) values (?, ?)"; var params = []; params.push(jsonData["product_name"]); params.push(jsonData["retail_price"]); this.dg.execute(sql, params, callBack); } getRecords(resourceId, callBack) { var sql = "select product_id, product_name, retail_price from products"; var params = []; if (resourceId != "") { sql = sql + " where product_id = ?"; params.push(resourceId); } this.dg.query(sql, params, callBack); } updateRecord(resourceId, jsonData, callBack) { var sql = "update products set product_name = ?, retail_price = ? where product_id = ?"; var params = []; params.push(jsonData["product_name"]); params.push(jsonData["retail_price"]); params.push(resourceId); this.dg.execute(sql, params, callBack); } deleteRecord(resourceId, callBack) { var sql = "delete from products where product_id = ?"; var params = []; params.push(resourceId); this.dg.execute(sql, params, callBack); } } module.exports = products;
3. Save and close the products.js file when you’re through with editing.

The products.js file explained:

• The products{} class wraps all the resource’s method in a single file.class products { ... }
• The constructor (dg) {..} method accepts one dg argument. The dg refers to your previous database_gateway class that exposes the different database functions.
• The this.dg = dg; statement initializes a new class property (dg) and assigns the value of the database_gateway object to the property.
• The products{...} class features four other methods that correspond to the CRUD operations:
  • insertRecord(jsonData, ...): accepts a JSON payload (jsonData) and constructs an INSERT statement.
  • getRecords(resourceId, ...): accepts a resourceId parameter and constructs a SELECT statement. When you define the resourceId during an HTTP call, MySQL only returns the product that matches the resourceId value.
  • updateRecord(resourceId, jsonData, ...): accepts a resourceId and a JSON payload and constructs an UPDATE command to change the record that matches the resourceId.
  • deleteRecord(resourceId, ...): accepts a resourceId and constructs a DELETE command to remove the record that matches the resourceId.
• The four CRUD methods passes a callBack function to the database_gateway class using the this.dg.query(sql, params, callBack); and this.dg.execute(sql, params, callBack); statements. The callBack() function runs when the operations complete.
• The module.exports = products; line at the end allows you to use the class in other files using the require('./products.js'); statement.

4. Create a http_requests Class

This step describes creating a http_requests class that returns most HTTP variables required in this project.

1. Open a new http_requests.js in a text editor.$ nano http_requests.js
2. Enter the following information into the http_requests.js file.class http_requests { constructor(httpRequest) { var url = require("url"); this.httpRequest = httpRequest; var pathname = url.parse(this.httpRequest.url).pathname; this.resourcePath = pathname.split("/"); this.resourceId = ""; this.httpMethod = httpRequest.method if (this.resourcePath.length >= 3) { this.resourceId = this.resourcePath[2] } } } module.exports = http_requests;
3. Save and close the http_requests.js file.

The http_requests.js file explained:

• The http_requests class wraps the different HTTP variables in a constructor(...) method.class http_requests { constructor(httpRequest) { } }
• The constructor(httpRequest){} method accepts a httpRequest object. This object comes from an http library that you must later import and include in a different file.
• The pathname.split("/"); function splits the URL using the / character and returns the resourceId. For instance, if you request the URL http://127.0.0.1:8080/products/3, the pathname.split("/"); function returns 3 as the resourceId. The logical if (this.resourcePath.length >= 3) {...} statement ensures the resourceId is available from the request URL.

5. Create a main.js File

Your Node.js requires a main file that executes when the application starts. The main file is the entry point to your application. Follow the steps below to create the file:

1. Open a new main.js in a text editor.$ nano main.js
2. Enter the following information into the main.js file.var db_gateway = require('./db_gateway.js'); var http_requests = require('./http_requests.js'); var products = require('./products.js'); const http = require('http'); const hostname = '127.0.0.1'; const port = 8080; const server = http.createServer((req, res) => { var dg = new db_gateway(); var httpRequest = new http_requests(req); var product = new products(dg); var payload = ""; req.on('data', function (data) { payload += data; }); req.on('end', function () { function callBack(err, result) { res.statusCode = 200; res.setHeader('Content-Type', 'application/json'); var response = {} if (err) { response["error"] = err.message; } else { response["data"] = result; } res.write(JSON.stringify(response, null, 4)); res.end(); } resourceId = httpRequest.resourceId; switch (req.method) { case "POST": jsonData = JSON.parse(payload); product.insertRecord(jsonData, callBack); break; case "PUT": jsonData = JSON.parse(payload); product.updateRecord(resourceId, jsonData, callBack); break; case "DELETE": product.deleteRecord(resourceId, callBack); break; case "GET": product.getRecords(resourceId, callBack); break; } }); }); server.listen(port, hostname, () => { console.log(`Server running at http://${hostname}:${port}/`); });
3. Save and close the main.js file.

The main.js file explained:

• The first three lines import all the classes you’ve created for this project.var db_gateway = require('./db_gateway.js'); var http_requests = require('./http_requests.js'); var products = require('./products.js');
• The following lines import the http module. The http module runs a web server that listens for incoming HTTP connections on port 8080.const http = require('http'); const hostname = '127.0.0.1'; const port = 8080;
• The following statement starts an HTTP server.... const server = http.createServer((req, res) => { ... }); ...
• The following lines create the new objects from the imported classes.... var dg = new db_gateway(); var httpRequest = new http_requests(req); var product = new products(dg); ...
• The following callBack() function runs when your application completes the CRUD operations. The callBack() function displays the response in JSON format.... function callBack(err, result) { res.statusCode = 200; res.setHeader('Content-Type', 'application/json'); var response = {} if (err) { response["error"] = err.message; } else { response["data"] = result; } res.write(JSON.stringify(response, null, 4)); res.end(); } ...
• The switch() {} statement examines the HTTP method (req.method) and matches the methods with the correct product's methods to complete HTTP operations per the following list:
  • POST: runs the product.insertRecord(jsonData, callBack); statement.
  • PUT: runs the product.updateRecord(resourceId, jsonData, callBack); statement.
  • DELETE: runs the product.deleteRecord(resourceId, callBack); statement.
  • GET: runs the product.getRecords(resourceId, callBack); statement.

6. Test the Node.Js CRUD Application

The application is now ready for testing. Your database and source code files are in place. Execute the steps below to initialize your Node.js project directory, download the necessary modules, and run tests using the Linux curl command:

1. Initialize your project directory.$ npm init Enter the following responses:package name: (project) : Press ENTER to leave as default. version: (1.0.0) : Press ENTER to leave as default. description: Press ENTER to leave as default. entry point: (main.js) Press ENTER to leave as default. test command: Press ENTER to leave as default. git repository: Press ENTER to leave as default. keywords: Press ENTER to leave as default. author: Press ENTER to leave as default. license: (ISC) Press ENTER to leave as default.The Node.js npm package displays the following response.About to write to /home/francis/project/package.json: { "name": "project", "version": "1.0.0", "description": "", "main": "main.js", "scripts": { "test": "echo \"Error: no test specified\" && exit 1" }, "author": "", "license": "ISC" } Is this OK? (yes) Key in yes and press ENTER to confirm the changes.
2. Use the Node.js package manager (npm) to download and import the mysql library into your project.$ npm install mysql Output.npm notice created a lockfile as package-lock.json. You should commit this file. npm WARN project@1.0.0 No description npm WARN project@1.0.0 No repository field. + mysql@2.18.1 added 11 packages from 15 contributors and audited 11 packages in 1.079s found 0 vulnerabilities
3. Run the main.js file using the node command. The command below establishes an HTTP server that listens for incoming connections on port 8080. The node main.js command has a blocking function. Therefore don’t run any other commands in your active terminal window.$ node main.js Output.Server running at http://127.0.0.1:8080/
4. Establish a new SSH connection to your server on a second terminal window and use curl to run the following CRUD operations:
   • Create operation: adds a new record to the products table.$ curl -X POST http://127.0.0.1:8080/ -H 'Content-Type: application/json' -d '{"product_name":"DOUBLE-SIDED TAPE","retail_price": 4.95}' Output.{ "data": "Success" }
   • Read operation - all records: retrieves records from the products table.$ curl -X GET http://127.0.0.1:8080/products Output.{ "data": [ { "product_id": 1, "product_name": "LEATHER BELT", "retail_price": 18.45 }, { "product_id": 2, "product_name": "BLUETOOTH SPEAKER", "retail_price": 75.95 }, { "product_id": 3, "product_name": "RECHARGEABLE TORCH", "retail_price": 35.85 }, { "product_id": 4, "product_name": "DOUBLE-SIDED TAPE", "retail_price": 4.95 } ] }
   • Read operation - one record: retrieves a record from the products table that matches a resourceId at the end of the URL (For instance, 4).$ curl -X GET http://127.0.0.1:8080/products/4 Output.{ "data": [ { "product_id": 4, "product_name": "DOUBLE-SIDED TAPE", "retail_price": 4.95 } ] }
   • Update operation: modifies the details of a product that matches the resourceId (For instance, 3).$ curl -X PUT http://127.0.0.1:8080/products/3 -H 'Content-Type: application/json' -d '{"product_name":"RECHARGEABLE LED TORCH","retail_price": 40.20}' Output.{ "data": "Success" } Request the product (product_id 3) to check if the update command was successful. $ curl -X GET http://127.0.0.1:8080/products/3 Output.{ "data": [ { "product_id": 3, "product_name": "RECHARGEABLE LED TORCH", "retail_price": 40.2 } ] }
   • Delete Operation: removes a product that matches the resourceId (For instance, 4).$ curl -X DELETE http://127.0.0.1:8080/products/4 Output. { "data": "Success" } Query the product again to check if the delete operation was successful. $ curl -X GET http://127.0.0.1:8080/products/4 Output. { "data": [] }

The outputs above show that your Node.js CRUD application is working as expected.

Conclusion

This guide is a complete walkthrough for implementing a CRUD application with Node.js and MySQL database server on Ubuntu 20.04. Use the knowledge in this guide to code your next Node.js project. You can add as many endpoints/resources in your Node.js application depending on the complexity of your project. Remember to separate each resource in a different class file to ease future support.