PgSeqlDB<\/a>, or copy the code below:<\/p>\n<\/pre>\nvar Dbconfig = require('..\/..\/config\/config.json');\nvar Sequelize = require('sequelize');\n\n\/**\n * Database service using Sequelize and PostgreSQL.\n *\n * @class PgSeqlDB\n *\/\nclass PgSeqlDB {\n\n dbconfig;\n sequelize;\n createTables = false;\n userModel;\n\n configuration() {\n \/\/ process.env.APP_ENV captures what was set in the run.bat in the line: SET APP_ENV=dev\n if (process.env.APP_ENV == 'dev') {\n console.log('[INFO]: database configuration set to development.')\n return Dbconfig.development;\n } else if (process.env.APP_ENV == 'prod') {\n console.log('[INFO]: database configuration set to production.');\n return Dbconfig.production;\n } else if (process.env.APP_ENV == 'cloud') {\n console.log('[INFO]: database configuration set to cloud.');\n return Dbconfig.cloud;\n } else if (process.env.APP_ENV == 'test') {\n console.log('[INFO]: database configuration set to test environment.')\n return Dbconfig.test;\n }\n console.log('[WARN]: database configuration not defined, assuming default (dev): ' + Dbconfig.development.database);\n return Dbconfig.development;\n }\n\n\n constructor(createTables) {\n this.createTables = createTables;\n this.dbconfig = this.configuration();\n this.sequelize = this.connection();\n this.defineUserModel();\n this.syncDb(this.createTables);\n }\n\n defineUserModel() {\n this.userModel = this.sequelize.define('User', {\n id: {\n type: Sequelize.INTEGER,\n allowNull: false,\n autoIncrement: true,\n primaryKey: true\n },\n username: Sequelize.STRING,\n password: Sequelize.STRING\n }, {\n tableName: 'user', \/\/ this will define the table's name\n \/\/timestamps: false \/\/ this will deactivate the timestamp columns\n });\n\n }\n\n connection() {\n console.log('[INFO]: authenticating database: ' + this.dbconfig.database)\n let sequelize = new Sequelize({\n username: this.dbconfig.username,\n password: this.dbconfig.password,\n database: this.dbconfig.database,\n host: this.dbconfig.host,\n port: this.dbconfig.port,\n dialect: this.dbconfig.dialect\n });\n\n sequelize.authenticate().then(function () {\n console.log('[INFO]: database authenticated');\n }, function (err) {\n console.log('[EXCP]: database authentication failed due to: ' + err)\n });\n return sequelize;\n }\n\n \/**\n * @param {boolean} createTables if true, creates the tables.\n *\/\n syncDb(createTables) {\n this.sequelize.sync({\n force: createTables\n })\n .then(function (err) {\n \/\/console.log('[INFO]: sequelize sync done');\n }, function (err) {\n console.log('An error occurred while creating the table:', err);\n });\n }\n\n\n insertUser(name, pass) {\n this.userModel.create({\n username: name,\n password: pass\n }).then(function (user) {\n console.log('[INFO]: ' + user.dataValues.username + ' persisted');\n });\n }\n\n populate() {\n this.insertUser(\"John Doe\", \"secret1\");\n this.insertUser(\"Mary Doe\", \"secret2\");\n this.insertUser(\"Jane Doe\", \"secret3\");\n }\n\n showConfig() {\n console.log(this.dbconfig);\n }\n\n findById(id) {\n this.userModel.findByPk(id).then(function (user) {\n console.log('------------------------------------------');\n console.log('\\n[INFO]: found: ' + user.id + \", \" + user.username + '\\n');\n });\n }\n\n findByName(name) {\n this.userModel.findAll({\n where: {\n username: name\n }\n }).then(function (data, err) {\n if (!data) {\n console.log('------------------------------------------');\n console.log('[FAIL]: user instance not found due to: ' + err);\n console.log('------------------------------------------');\n } else {\n console.log('------------------------------------------');\n \/\/console.log(data);\n for (let u in data) {\n console.log(data[u].dataValues);\n }\n console.log('------------------------------------------');\n }\n })\n }\n\n findAll(res) {\n this.userModel.findAll().then(function (data, err) {\n if (!data) {\n console.log('------------------------------------------');\n console.log('[FAIL]: user instance not found due to: ' + err);\n console.log('------------------------------------------');\n } else {\n let list = [];\n for (let u of data) {\n list.push(u.dataValues);\n }\n return res.render('users', {\n title: 'USERS',\n users: list\n });\n\n }\n })\n }\n\n};\n\n\nmodule.exports = PgSeqlDB;\n<\/pre>\n <\/p>\n
<\/p>\n
<\/span>Register the PgSeqlDB in app.js<\/span><\/h3>\nAppend the following in the app.js file, always before the module.exports statement.<\/p>\n
<\/p>\n
const PgSeqlDB = require('.\/models\/persistence\/PgSeqlDB');\ncreateTable = true;\npopulate = false;\nlet pgSeqDB = new PgSeqlDB(createTable);\n\/\/ pgSeqDB.showConfig();\nif (!createTable && populate) {\n pgSeqDB.populate();\n}\nif(!createTable && !populate) {\n pgSeqDB.findById(1);\n pgSeqDB.findByName('John Doe');\n pgSeqDB.findAll();\n}\n<\/pre>\n <\/p>\n
You shall get the app.js like this:<\/p>\n
<\/h4>\n<\/span>How does this code work?<\/span><\/h4>\nIt follows the same logic used in the 1st step except that you now have the code implemented in its own file.<\/p>\n
Follow this procedure:<\/p>\n
\nCreating the tables.createTable = true;<\/em><\/strong>populate = false;<\/em><\/strong>Without the need to stop the app switch the flags as follow and save:createTable = false;<\/strong>populate = true;<\/strong> select * from public.user;<\/em>Again, without the need to stop the app switch the flags as follow:createTable = false;<\/em><\/strong>populate = false;<\/em><\/strong>The new persistence layer was tested successfully.<\/p>\n
Ok! We’ve refactored the code to a better design and now it is time to increment the view to show users.<\/div>\n
For the “user table” we’ll not implement the CRUD (create, restore, update and delete) GUI interface.
\nThis will be done later in the next steps using todo and todoItem use cases.
\nFor now, the target is to understand the concepts and test the code we’ve implemented in this step.<\/div>\n
<\/div>\n
<\/span>REFACTORING: app.js and User Router<\/span><\/h2>\nThe block highlighted below in the app.js will be moved to the Router.<\/pre>\nATTENTION:<\/strong>This is not the right way of doing things, and we are implementing that way, for now, to evolve later.<\/strong><\/p>\n <\/p>\n
const PgSeqlDB = require('.\/models\/persistence\/PgSeqlDB');\ncreateTable = false;\npopulate = false;\nlet pgSeqDB = new PgSeqlDB(createTable);\n\/\/ pgSeqDB.showConfig();\nif (!createTable && populate) {\n pgSeqDB.populate();\n}\nif(!createTable && !populate) {\n pgSeqDB.findById(1);\n pgSeqDB.findByName('John Doe');\n pgSeqDB.findAll();\n}<\/b>\n<\/pre>\n <\/p>\n
Remove the highlighted block above from app.js.<\/p>\n
Edit routes\\user.js and replace the code with the following:<\/p>\n
var express = require('express');\nvar router = express.Router();\nconst PgSeqlDB = require('..\/models\/persistence\/PgSeqlDB');\n\n\nrouter.get('\/', function(req, res, next) {\n \/\/res.render('users', { title: 'Express' });\n pg = new PgSeqlDB(false,false);\n return pg.findAll(res);\n});\n\n\nmodule.exports = router;\n<\/pre>\nYou app.js shall be like this from the line where PgSeqlDB is declared up to the end.<\/p>\n
<\/p>\n
<\/p>\n
<\/span>REFACTORING THE VIEW TO SHOW THE USER INSTANCES<\/span><\/h2>\nNow it is time to refactor the view’s code to list the users using the persistence class created before.
<\/span>Adding Bootstrap and the Static Files<\/span><\/h3>\nNotice that the view uses Bootstrap and other references, so it is required to add the respective library.node\\public<\/strong> folder.<\/span>Refactoring the user.ejs View<\/span><\/h3>\nEdit the users.ejs file and copy the following code:<\/p>\n
<\/span>users.ejs<\/span><\/h3>\n<!DOCTYPE html>\n<html lang=\"en\">\n\n<head>\n\t<meta charset=\"utf-8\">\n\t<meta http-equiv=\"X-UA-Compatible\" content=\"IE=edge\">\n\t<meta name=\"viewport\" content=\"width=device-width, initial-scale=1, shrink-to-fit=no\">\n\t<meta name=\"description\" content=\"\">\n\t<meta name=\"author\" content=\"\">\n\t<title><%= title %><\/title>\n\t<!-- Bootstrap core CSS -->\n\t<link href=\"\/stylesheets\/pinegrow\/bootstrap\/css\/bootstrap.css\" rel=\"stylesheet\">\n\t<!-- Custom styles for this template -->\n\t<link href=\"\/stylesheets\/pinegrow\/starter-template.css\" rel=\"stylesheet\">\n<\/head>\n\n<body>\n\t<nav class=\"navbar navbar-expand-md navbar-dark bg-dark fixed-top\">\n\t\t<a class=\"navbar-brand\" href=\"#\">node.js<\/a>\n\t\t<ul class=\"navbar-nav\">\n\t\t\t<li class=\"nav-item dropdown\">\n\t\t\t\t<a class=\"nav-link dropdown-toggle\" href=\"#\" id=\"navbarDropdownMenuLink22\" data-toggle=\"dropdown\"\n\t\t\t\t\taria-haspopup=\"true\" aria-expanded=\"false\">Resources<\/a>\n\t\t\t\t<div class=\"dropdown-menu\" aria-labelledby=\"navbarDropdownMenuLink22\">\n\t\t\t\t\t<a class=\"dropdown-item\" href=\"#\">Action<\/a>\n\t\t\t\t\t<a class=\"dropdown-item\" href=\"#\">Another action<\/a>\n\t\t\t\t\t<a class=\"dropdown-item\" href=\"#\">Something else here<\/a>\n\t\t\t\t<\/div>\n\t\t\t<\/li>\n\t\t<\/ul>\n\t<\/nav>\n\t<div class=\"container\">\n\t\t<div class=\"row\">\n\t\t\t<div class=\"col-md-3\">\n\t\t\t\t<img class=\"border border-0 border-light img-thumbnail w-50\" src=\"..\/..\/images\/todo_150.jpg\" alt=\"\">\n\t\t\t<\/div>\n\t\t\t<div class=\"align-middle col-md-6 mt-5 text-center\">\n\t\t\t\t<h1 class=\"m-n3\">User list<\/h1>\n\t\t\t<\/div>\n\t\t\t<div class=\"col-md-3\"><\/div>\n\t\t\t<div class=\"col-md-3\"><\/div>\n\t\t\t<div class=\"col-md-3\"><\/div>\n\t\t<\/div>\n\t\t<div class=\"starter-template\"><\/div>\n\t\t<div>\n\t\t\t<div class=\"row\" style=\"margin-bottom: 30px; padding-bottom: 10px; border-bottom: 1px solid gray;\">\n\t\t\t\t<div class=\"col-md-4\">\n\t\t\t\t\t<h3>Name<\/h3>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"col-md-8\">\n\t\t\t\t\t<h3>Last Update<\/h3>\n\t\t\t\t<\/div>\n\t\t\t<\/div>\n\t\t<\/div>\n\n\t\t<% users.forEach(user => { %>\n\t\t\t<div>\n\t\t\t\t\t<div class=\"row\" style=\"margin-top: 15px;\">\n\t\t\t\t\t\t\t<div class=\"col-md-4\">\n\t\t\t\t\t\t\t\t\t<p><%= user.username %><\/p>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\t\t\t\t<div class=\"col-md-4\">\n\t\t\t\t\t\t\t\t\t<p><%= user.updatedAt.toLocaleDateString(\"en-US\") %><\/p>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t\t<\/div>\n\t<% }) %>\n\n\t<\/div>\n\t<!-- \/.container -->\n\t<!-- Bootstrap core JavaScript\n ================================================== -->\n\t<!-- Placed at the end of the document so the pages load faster -->\n\t<script src=\"\/stylesheets\/pinegrow\/assets\/js\/jquery.min.js\"><\/script>\n\t<script src=\"\/stylesheets\/pinegrow\/assets\/js\/popper.js\"><\/script>\n\t<script src=\"\/stylesheets\/pinegrow\/bootstrap\/js\/bootstrap.min.js\"><\/script>\n<\/body>\n\n<\/html>\n<\/pre>\n<\/span> REFACTORING THE PERSISTENCE LAYER ONCE MORE<\/span><\/h2>\n <\/p>\n
var Dbconfig = require('..\/..\/config\/config.json');\nvar Sequelize = require('sequelize');\n\n\/**\n * Database service using Sequelize and PostgreSQL.\n *\n * @class PgSeqlDB\n *\/\nclass PgSeqlDB {\n\n dbconfig;\n sequelize;\n createTables = false;\n userModel;\n\n configuration() {\n \/\/ process.env.APP_ENV captures what was set in the run.bat in the line: SET APP_ENV=dev\n if (process.env.APP_ENV == 'dev') {\n console.log('[INFO]: database configuration set to development.')\n return Dbconfig.development;\n } else if (process.env.APP_ENV == 'prod') {\n console.log('[INFO]: database configuration set to production.');\n return Dbconfig.production;\n } else if (process.env.APP_ENV == 'cloud') {\n console.log('[INFO]: database configuration set to cloud.');\n return Dbconfig.cloud;\n } else if (process.env.APP_ENV == 'test') {\n console.log('[INFO]: database configuration set to test environment.')\n return Dbconfig.test;\n } else {\n console.log('[WARN]: database configuration not defined, assuming default (dev): ' + Dbconfig.development.database);\n return Dbconfig.development;\n }\n }\n\n\n constructor(createTables) {\n this.createTables = createTables;\n this.dbconfig = this.configuration();\n this.sequelize = this.connection();\n this.defineUserModel();\n this.syncDb(this.createTables);\n }\n\n defineUserModel() {\n this.userModel = this.sequelize.define('User', {\n id: {\n type: Sequelize.INTEGER,\n allowNull: false,\n autoIncrement: true,\n primaryKey: true\n },\n username: Sequelize.STRING,\n password: Sequelize.STRING\n }, {\n tableName: 'user', \/\/ this will define the table's name\n \/\/timestamps: false \/\/ this will deactivate the timestamp columns\n });\n\n }\n\n connection() {\n console.log('[INFO]: authenticating database: ' + this.dbconfig.database)\n let sequelize = new Sequelize(this.dbconfig.database, this.dbconfig.username, this.dbconfig.password, {\n dialect: this.dbconfig.dialect,\n port: this.dbconfig.port,\n });\n sequelize.authenticate(this.dbconfig).then(function () {\n console.log('[INFO]: database authenticated');\n }, function (err) {\n console.log('[EXCP]: database authentication failed due to: ' + err)\n });\n return sequelize;\n }\n\n \/**\n * @param {boolean} createTables if true, creates the tables.\n *\/\n syncDb(createTables) {\n this.sequelize.sync({\n force: createTables\n })\n .then(function (err) {\n \/\/console.log('[INFO]: sequelize sync done');\n }, function (err) {\n console.log('An error occurred while creating the table:', err);\n });\n }\n\n\n insertUser(name, pass) {\n this.userModel.create({\n username: name,\n password: pass\n }).then(function (user) {\n console.log('[INFO]: ' + user.dataValues.username + ' persisted');\n });\n }\n\n populate() {\n this.insertUser(\"John Doe\", \"secret1\");\n this.insertUser(\"Mary Doe\", \"secret2\");\n this.insertUser(\"Jane Doe\", \"secret3\");\n }\n\n showConfig() {\n console.log(this.dbconfig);\n }\n\n findById(id) {\n this.userModel.findByPk(id).then(function (user) {\n console.log('------------------------------------------');\n console.log('\\n[INFO]: found: ' + user.id + \", \" + user.username + '\\n');\n });\n }\n\n findByName(name) {\n this.userModel.findAll({\n where: {\n username: name\n }\n }).then(function (data, err) {\n if (!data) {\n console.log('------------------------------------------');\n console.log('[FAIL]: user instance not found due to: ' + err);\n console.log('------------------------------------------');\n } else {\n console.log('------------------------------------------');\n \/\/console.log(data);\n for (let u in data) {\n console.log(data[u].dataValues);\n }\n console.log('------------------------------------------');\n }\n })\n }\n\n findAll(res) {\n this.userModel.findAll().then(function (data, err) {\n if (!data) {\n console.log('------------------------------------------');\n console.log('[FAIL]: user instance not found due to: ' + err);\n console.log('------------------------------------------');\n } else {\n let list = [];\n for (let u of data) {\n list.push(u.dataValues);\n }\n return res.render('users', {\n title: 'USERS',\n users: list\n });\n\n }\n })\n }\n\n};\n\n\nmodule.exports = PgSeqlDB;\n<\/pre>\n