Node, Express, MySQL, CRUD – Create a Single Page Application with Node, EJS and MySQL

by Posted on

Previously, I have made a lot of inquiries to understand how to create an API using Node. I guess, I have satisfied almost all my curiosity and it was the occasion to discover testing.

Nevertheless, 2 questions remain : find a solid alternative to the PHP-MySQL tandem on how to build a rudimentary Single Page Application, find also the way to leverage on MySQL rather than MongoDB.

My reason is easy to guess. It boils down to one word : Legacy. First, I had a lot of MySQL databases, for my personal usage, so having to migrate both code and data may require a harder work. Also, all these MySQL databases can be also queried by some handy other tools such as phpMyAdmin, Sequel Pro or MySQL Workbench.

One other aspect, in the Node’s API creation that I have neglected so far was how to easily expose data to users through a GUI in a browser. This is the second objective of this post, make more explorations on view engines available in Node or Express to be more precise.

Due mostly of the resources that I have read. It seems that there are 2 popular view engines: EJS, Pug (former named Jade). This is way you expose the data of your node application. Your API outputs JSON, fine, but the content can be wrapped it up with EJS or PUG into GUI (graphical user interface).

Let’s give a quick definition of “view engine”, it is the module that does the actual rendering of views. Pug and EJS are view engines. It will populate the data of your API into true web page.

According to me, EJS seems to be easier for those you have made already HTML integration, it is very similar to Mustache, Smarty or Twig. At the risk of bordering on silliness, with EJS, you just sprinkle variables through HTML files and abuse extensively of includes. Pug has more abstraction in it so it appears less intuitive.

The code is available on github at https://github.com/bflaven/node-countries-mysql-crud-ejs

In my example, I have chosen the EJS template engine maybe later I will add to this project the same views but based on PUG (ex Jade). But first, I have decided to create a dead simple Single Page Application with Node, EJS and MySQL.

Create your MySQL Database

The purpose is to create an application backed up to a MySQL database. In my example, here is the database name node_countries and the table name t_countries, some the scheme plus some records.

 
	DROP TABLE IF EXISTS `node_countries`;
 
CREATE TABLE `node_countries` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `tld` VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `cca2` VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `capital` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `callingCode` INT(3) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
 
INSERT INTO `node_countries` (`id`, `name`, `tld`, `cca2`, `capital`, `callingCode`)
VALUES
	(NULL,'Afghanistan','.af','AF','Kabul',93),
	(NULL,'Italy','.it','IT','Rome',39),
	(NULL,'France','.fr','FR','Rome',33),
  (NULL,'Malaysia','.my','MY','Kuala Lumpur',60),
  (NULL,'Mauritania','.mr','MR','Nouakchott',222),
  (NULL,'Tunisia','.tn','TN','Tunis',216),
  (NULL,'Tanzania','.tz','TZ','Dodoma',255),
  (NULL,'Seychelles','.sc','SC','Victoria',248),
  (NULL,'Norway','.no','NO','Oslo',47),
  (NULL,'Nepal','.np','NP','Kathmandu',977);

The MySQL database
Node, Express, MySQL, CRUD - Create a Single Page Application with Node, EJS and MySQL

Just add the proper values in the configuration file config.js

	var config = {
	database: {
		// host:	  'localhost', 		// database host
		host:	  '127.0.0.1', 		// database host
		user: 	  'root', 			// your database username
		password: 'root', 			// your database password
		port: 	  3306, 			// default MySQL port
		db: 	  'node_countries' // your database name
	},
	server: {
		host:'127.0.0.1', // the host for the server
		port: '3000' // the port for the server
	}
}
 
module.exports = config

Start the application, do not forget to start you MySQL instance before typing npm start
Node, Express, MySQL, CRUD - Create a Single Page Application with Node, EJS and MySQL

The dead simple application: Node Countries Light Application

Here some screens captures of the web application. Just to enhance the user experience even for POC, it is nice to add some CSS. Making things ugly make potentials users downhearted even though the development is great! The web application contains 3 main destinations: 1. Homepage, 2. Countries listing where you can edit or delete a single country, 3. Add a new country.

1. Homepage at http://127.0.0.1:3000/
Node, Express, MySQL, CRUD - Create a Single Page Application with Node, EJS and MySQL

2. Countries listing at http://127.0.0.1:3000/countries with Edit and Delete
Node, Express, MySQL, CRUD - Create a Single Page Application with Node, EJS and MySQL

3. Add a new country at http://127.0.0.1:3000/countries/add
Node, Express, MySQL, CRUD - Create a Single Page Application with Node, EJS and MySQL

Using APIDOC to add some documentation to your web application

Even though APIDOC is dedicated to the documentation of an API, why don’t we leverage on this tool to provide some information about this new web application. You can give detailed information for each action you are coding in your web application via comments in the routes file routes/countries.js.

	// DELETE COUNTRY
/**
    * @api {post} /delete/(:id) Delete a country
    * @apiGroup Countries
    * @apiSuccess {Number} countries.id Country id
    * @apiParam {String} countries.name Country name
    * @apiParam {String} countries.tld Country tld
    * @apiParam {String} countries.cca2 Country cca2
    * @apiParam {String} countries.capital Country capital
    * @apiParam {Number} countries.callingCode Country callingCode
    * @apiExample {sql} Example usage:
    *       DELETE FROM t_countries t_countries WHERE id = ' + req.params.id
    */

Create the documentation, be sure to be in the directory of your application.

apidoc -e "(node_modules|public)" -o public/apidoc

The documentation made with apidoc available at http://127.0.0.1:3000/apidoc/. The output of the delete action in the documentation.
Node, Express, MySQL, CRUD - Create a Single Page Application with Node, EJS and MySQL

Using Cheerio to make some tests

I have written some tests with the help of Cheerios to test the 3 main entrances of the web application. Cheerios is a JavaScript library, very powerful to grab HTML element in a page so you can definitely test the presence or the absence of some HTML element.

As the project contains a directory test and this directive in the package.json, it will launch any .js file available in the test directory with the command npm test.

	"scripts": {
	....
    "test": "mocha test/*.js --timeout 15000"
  },

Check the file test_webpage_1.js in the test directory.

Run the test
Node, Express, MySQL, CRUD - Create a Single Page Application with Node, EJS and MySQL

Read more

Published by Bruno Flaven

Bruno Flaven has been a Project Manager in a wide variety of Internet business applications both in Mobile and in Desktop for 20 years now. You can find more information about his professional life on his personal website (www.flaven.fr) or his linkedin profile: https://fr.linkedin.com/in/brunoflaven. He is currently working In France Media Monde (FMM) mostly on mobile applications (iOS and Android). He is currently P.O for a Backoffice project made with Symfony. He also made few trainings to facilitate the handling of the tools that he helps to make.

Related Posts