{"id":11156,"date":"2018-06-09T10:12:02","date_gmt":"2018-06-09T08:12:02","guid":{"rendered":"http:\/\/flaven.fr\/?p=11156"},"modified":"2026-09-16T10:07:39","modified_gmt":"2026-09-16T08:07:39","slug":"node-express-mysql-crud-create-a-single-page-application-with-node-ejs-and-mysql","status":"publish","type":"post","link":"https:\/\/flaven.fr\/2018\/06\/node-express-mysql-crud-create-a-single-page-application-with-node-ejs-and-mysql\/","title":{"rendered":"Node, Express, MySQL, CRUD – Create a Single Page Application with Node, EJS and MySQL"},"content":{"rendered":"

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.<\/p>\n

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.<\/p>\n

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.<\/p>\n

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.<\/p>\n

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).<\/p>\n

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.<\/p>\n

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.<\/p>\n

The code is available on github at https:\/\/github.com\/bflaven\/node-countries-mysql-crud-ejs<\/a><\/b><\/p>\n

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.<\/p>\n

Create your MySQL Database<\/h4>\n

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

\r\n\t\r\n\tDROP TABLE IF EXISTS `node_countries`;\r\n\r\nCREATE TABLE `node_countries` (\r\n  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,\r\n  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',\r\n  `tld` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',\r\n  `cca2` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',\r\n  `capital` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',\r\n  `callingCode` int(3) NOT NULL DEFAULT '0',\r\n  PRIMARY KEY (`id`)\r\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;\r\n\r\n\r\nINSERT INTO `node_countries` (`id`, `name`, `tld`, `cca2`, `capital`, `callingCode`)\r\nVALUES\r\n\t(NULL,'Afghanistan','.af','AF','Kabul',93),\r\n\t(NULL,'Italy','.it','IT','Rome',39),\r\n\t(NULL,'France','.fr','FR','Rome',33),\r\n  (NULL,'Malaysia','.my','MY','Kuala Lumpur',60),\r\n  (NULL,'Mauritania','.mr','MR','Nouakchott',222),\r\n  (NULL,'Tunisia','.tn','TN','Tunis',216),\r\n  (NULL,'Tanzania','.tz','TZ','Dodoma',255),\r\n  (NULL,'Seychelles','.sc','SC','Victoria',248),\r\n  (NULL,'Norway','.no','NO','Oslo',47),\r\n  (NULL,'Nepal','.np','NP','Kathmandu',977);\r\n\r\n<\/pre>\n
The MySQL database<\/b>
\n\"Node,<\/p>\n
Just add the proper values in the configuration file config.js<\/code><\/b><\/p>\n
\r\n\tvar config = {\r\n\tdatabase: {\r\n\t\t\/\/ host:\t  'localhost', \t\t\/\/ database host\r\n\t\thost:\t  '127.0.0.1', \t\t\/\/ database host\r\n\t\tuser: \t  'root', \t\t\t\/\/ your database username\r\n\t\tpassword: 'root', \t\t\t\/\/ your database password\r\n\t\tport: \t  3306, \t\t\t\/\/ default MySQL port\r\n\t\tdb: \t  'node_countries' \/\/ your database name\r\n\t},\r\n\tserver: {\r\n\t\thost:'127.0.0.1', \/\/ the host for the server\r\n\t\tport: '3000' \/\/ the port for the server\r\n\t}\r\n}\r\n\r\nmodule.exports = config\r\n<\/pre>\n
Start the application, do not forget to start you MySQL instance before typing npm start<\/code><\/b>
\n\"Node,<\/p>\n
The dead simple application: Node Countries Light Application<\/h4>\n
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.<\/p>\n
1. Homepage at http:\/\/127.0.0.1:3000\/<\/b>
\n\"Node,<\/p>\n
2. Countries listing at http:\/\/127.0.0.1:3000\/countries with Edit and Delete<\/b>
\n\"Node,<\/p>\n
3. Add a new country at http:\/\/127.0.0.1:3000\/countries\/add<\/b>
\n\"Node,<\/p>\n
Using APIDOC to add some documentation to your web application<\/h4>\n
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<\/code>.<\/p>\n
\r\n\t\/\/ DELETE COUNTRY\r\n\/**\r\n    * @api {post} \/delete\/(:id) Delete a country\r\n    * @apiGroup Countries\r\n    * @apiSuccess {Number} countries.id Country id\r\n    * @apiParam {String} countries.name Country name\r\n    * @apiParam {String} countries.tld Country tld\r\n    * @apiParam {String} countries.cca2 Country cca2\r\n    * @apiParam {String} countries.capital Country capital\r\n    * @apiParam {Number} countries.callingCode Country callingCode\r\n    * @apiExample {sql} Example usage:\r\n    *       DELETE FROM t_countries t_countries WHERE id = ' + req.params.id\r\n    *\/\r\n<\/pre>\n
Create the documentation, be sure to be in the directory of your application.<\/b><\/p>\n
\r\napidoc -e \"(node_modules|public)\" -o public\/apidoc\r\n<\/pre>\n
The documentation made with apidoc available at http:\/\/127.0.0.1:3000\/apidoc\/. The output of the delete action in the documentation.<\/b>
\n\"Node,<\/p>\n
Using Cheerio to make some tests<\/h4>\n
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.<\/p>\n
As the project contains a directory test<\/code> and this directive in the package.json, it will launch any .js file available in the test<\/code> directory with the command npm test<\/code>.<\/p>\n
\r\n\t\"scripts\": {\r\n\t....\r\n    \"test\": \"mocha test\/*.js --timeout 15000\"\r\n  },\r\n<\/pre>\n
Check the file test_webpage_1.js<\/code> in the test directory.<\/p>\n
Run the test<\/b>
\n\"Node,<\/p>\n
Read more<\/h2>\n