Shoplazza geekZone: try building a CRUD application with Express and NoSql

  • Create a static website with Express.
  • Manage an SQL database (PostgresSQL in this case).
  • Next time, we will talk about how t deploy your static website or node.js framework to AWS S3 buckets.
  1. Relational database, where a type of database that stores and provides access to data points that are related to one another. … The columns of the table hold attributes of the data, and each record usually has a value for each attribute, making it easy to establish the relationships among data points. When you heard MySQL, Oracle DB, or cloud relational DB, such as AWS cloud RDS, IBM DB2 on cloud, SQL Azure etc, there are perfect examples of relational databases.
  2. Non-relational database, is a database that does not use the tabular schema of rows and columns found in most traditional database systems. … The term NoSQL refers to data stores that do not use SQL for queries, and instead use other programming languages and constructs to query the data. In simple terms, NoSQL involves actions of pairing keys and values to any data objects. When people refer to MongoDB, Apache Cassandra, Reddis, they are the examples of NoSQL.
  1. Create NodeJs project
  2. Add modules to the Node project
  3. Create the Express application
  4. Add EJS
  5. Add Views in Express
  6. Start with node-postgres module
  7. Work on an existing row
  8. Create a new row
  9. Delete an existing row
  10. Conclusion

1. Create a new Node project

Create a folder for the project

E:\> cd Code
E:\Code> mkdir AppTestPG

Open the folder with Visual Code

E:\Code> cd AppTestPG
E:\Code\AppTestPG> code .

Init the Node project

PS E:\Code\AppTestPG> npm init -y
Wrote to E:\Code\AppTestPG\package.json:{
"name": "AppTestPG",
"version": "1.0.0",
"description": "",
"main": "index.js",
"scripts": {
"test": "echo \"Error: no test specified\" && exit 1"
},
"keywords": [],
"author": "",
"license": "ISC"
}

2. Add modules

Technologies

Install dependencies

PS E:\Code\AppTestPG> npm install express
PS E:\Code\AppTestPG> npm install ejs
PS E:\Code\AppTestPG> npm install pg
PS E:\Code\AppTestPG> npm install express ejs pg
{
"name": "AppTestPG",
"version": "1.0.0",
"description": "",
"main": "index.js",
"scripts": {
"test": "echo \"Error: no test specified\" && exit 1"
},
"keywords": [],
"author": "",
"license": "ISC",
"dependencies": {
"ejs": "^2.7.1",
"express": "^4.17.1",
"pg": "^7.12.1"
}
}

The “node_modules” folder

  • It’s usually a huge file.
  • The npm install command without argument allows to (re)install dependencies
PS E:\Code\AppTestPG> rd node_modules /s /q
PS E:\Code\AppTestPG> npm install

3. Create the Express application

Check that it can work…

const express = require("express");const app = express();app.listen(3000, () => { {
console.log("Server started (http://localhost:3000/) !");
});
app.get("/", (req, res) => { {
res.send ("Hello world...");
});
PS E:\Code\AppTestPG> node index
Server started (http://localhost:3000/) !

How does it work?

const express = require("express");
const app = express();
app.listen(3000, () => { {
console.log("Server started (http://localhost:3000/) !");
});
app.get("/", (req, res) => { {
res.send ("Hello world...");
});

And more precisely?

(req, res) => {
res.send ("Hello world...");
}
  • the variable req which contains a Request object
  • the variable res that contains a Response object
  • It returns the text in the body part of the HTTP response
  • It terminates the connection

Improve the launch of the Node application

"start": "node index"
{
"name": "AppTestPG",
"version": "1.0.0.0",
"description":"",
"hand": "index.js",
"scripts": {
"start": "node index",
"test": "echo \"Error: no test specified\" && exit 1"
},
"keywords": [],
"author": "",
"license": "ISC",
"dependencies": {
"ejs": "^2.7.1",
"express": "^4.17.1",
"pg": "^7.12.1"
}
}
PS E:\Code\AppTestPG> npm start
> AppTestPG@1.0.0 start E:\Code\AppTestPG
> node index.js
Server started (http://localhost:3000/) !

4. Add EJS views

Partial view “views/_header.ejs”

<!doctype html>
<html lang="fr">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<title>AppTestPG</title>
<link rel="stylesheet" href="/css/bootstrap.min.css">
</head>
<body> <div class="container"> <nav class="navbar navbar-expand-lg navbar-light bg-light">
<a class="navbar-brand" href="/">AppTestPG</a>
<ul class="navbar-nav mr-auto">
<li class="nav-item">
<a class="nav-link" href="/about">About</a>
</li>
<li class="nav-item">
<a class="nav-link" href="/data">Data</a>
</li>
<li class="nav-item">
<a class="nav-link" href="/books">Books</a>
</li>
</ul>
</nav>

View “views/index.ejs”

<%- include("_header") -%><h1>Hello world...</h1><%- include("_footer") -%>

Partial view “views/_footer.ejs”

<footer>
<p>&copy; 2019 - AppTestPG</p>
</footer>
</div></body></html>

Add a style sheet

5. Use views in Express

Changes to “index.js”

  • Notify that the EJS template engine must be used.
app.set("view engine", "ejs");
  • Specify that the views are saved in the “views” folder.
app.set("views", __dirname + "/views");
const path = require("path");
...
app.set("views", path.join(__dirname, "views"));
  • Indicate that static files are saved in the “public” folder and its subdirectories. It is a setting that is necessary for the file “bootstrap.min.css” previously copied into “public/css” to be accessible.
app.use(express.static(path.join(__dirname, "public")));
app.get("/", (req, res) => { {
// res.send("Hello world...");
res.render("index");
});

Check that it works

  • Make an npm start in the Visual Code terminal
  • Navigate to “http://localhost:3000/" with Chrome
  • The following page should appear:

Add the “/about” path

  • In “index.js”, add a function to answer a request to “/about” and return the “about.ejs” view in this case.
app.get("/about", (req, res) => {
res.render("about");
});
  • Create a new “about.ejs” view in the “views” folder (by re-using both partial views).
<%- include("_header") -%><h1>About AppTestPG</h1><p>Blah blah blah blah blah...</p><%- include("_footer") -%>
  • Stop the server with Ctrl+C (if this had not been done before).
  • Restart the server with npm start (this is mandatory to take into account the changes made to the project).
  • Navigate to “http://localhost:3000/".
  • Click on the “About” menu, which gives you:

Send data from the server to the view

app.get("/data", (req, res) => {
const test = {
title: "Test",
items: ["one", "two", "three"]
};
res.render("data", { model: test });
});
<%- include("_header") -%><h1><%= model.title %>></h1><ul>  <% for (let i = 0; i < model.items.length; i++) { %>
<li><%= model.items[i] %></li>
<% } %>
</ul><%- include("_footer") -%>

The updated “index.js” file

const express = require("express");
const path = require("path");
// Creating the Express server
const app = express();
// Server configuration
app.set("view engine", "ejs");
app.set("views", path.join(__dirname, "views"));
app.use(express.static(path.join(__dirname, "public")));
// Starting the server
app.listen(3000, () => {
console.log("Server started (http://localhost:3000/) !");
});
// GET /
app.get("/", (req, res) => {
// res.send("Hello world...");
res.render("index");
});
// GET /about
app.get("/about", (req, res) => {
res.render("about");
});
// GET /data
app.get("/data", (req, res) => {
const test = {
titre: "Test",
items: ["one", "two", "three"]
};
res.render("data", { model: test });
});

6. First steps with the node-postgres module

Access a PostgreSQL database

  • Installing PostgreSQL => it’s a no (this is not the purpose of this tutorial).
  • Having a PostgreSQL server on hand already => it’s perfect (so, you know enough about it).
  • Using a server in the cloud => it looks great (but it’s a little longer).
  • Go to the website https://www.elephantsql.com/
  • Click on the “Get a managed database today” button
  • Choose the “Tiny Turtle” instance which is free
  • Once on the login screen, click on the “Sign Up” link at the bottom
  • Enter your email address and click on the new “Sign Up” button
  • In the confirmation email received, click on the “Confirm Email” button
  • When you arrive on the “Create an account” screen, you must:
  • Enter a password (and confirm it)
  • Accept their conditions
  • Accept or refuse emails from them
  • Click on the “Submit” button
  • Once on the list of your instances (empty), click on the “+ Create New Instance” button and follow the 4 steps:
  • 1 : “Select a plan and name” => stay on “Tiny Turtle” and give a name
  • 2 : “Select a region and data center” => choose the one closest to your home (but not all are available with the free offer)
  • 3 : “Configure” => grayed out because it’s reserved for dedicated plans
  • 4 : “Confirm new instance” => here we go!
  • server : xxxxxxx.elephantsql.com
  • user & default database: mystery
  • password: asecretthingthatnoonehastosee

Declare the node-postgres module

const { Pool } = require("pg");

Connection to the PostgreSQL database

const pool = new Pool({
user: "mystery",
host: "xxxxx.elephantsql.com",
database: "mystery",
password: "asecretthingthatnoonehastosee",
port: 5432
});
console.log("Successful connection to the database");

Creating a “Books” table

  • Book_ID: the automatic identifier
  • Title: the title of the book
  • Author: the author of the book
  • Comments: a memo field with some notes about the book
CREATE TABLE IF NOT EXISTS Books (
Book_ID SERIAL PRIMARY KEY,
Title VARCHAR(100) NOT NULL,
Author VARCHAR(100) NOT NULL,
Comments TEXT
);
const sql_create = `CREATE TABLE IF NOT EXISTS Books (
Book_ID SERIAL PRIMARY KEY,
Title VARCHAR(100) NOT NULL,
Author VARCHAR(100) NOT NULL,
Comments TEXT
);`;
pool.query(sql_create, [], (err, result) => {
if (err) {
return console.error(err.message);
}
console.log("Successful creation of the 'Books' table");
});

Seeding the “Books” table

INSERT INTO Books (Book_ID, Title, Author, Comments) VALUES
(1, 'Mrs. Bridge', 'Evan S. Connell', 'First in the serie'),
(2, 'Mr. Bridge', 'Evan S. Connell', 'Second in the serie'),
(3, 'L''ingénue libertine', 'Colette', 'Minne + Les égarements de Minne')
ON CONFLICT DO NOTHING;
...
console.log("Successful creation of the 'Books' table");
// Database seeding
const sql_insert = `INSERT INTO Books (Book_ID, Title, Author, Comments) VALUES
(1, 'Mrs. Bridge', 'Evan S. Connell', 'First in the serie'),
(2, 'Mr. Bridge', 'Evan S. Connell', 'Second in the serie'),
(3, 'L''ingénue libertine', 'Colette', 'Minne + Les égarements de Minne')
ON CONFLICT DO NOTHING;`;
pool.query(sql_insert, [], (err, result) => {
if (err) {
return console.error(err.message);
}
const sql_sequence = "SELECT SETVAL('Books_Book_ID_Seq', MAX(Book_ID)) FROM Books;";
pool.query(sql_sequence, [], (err, result) => {
if (err) {
return console.error(err.message);
}
console.log("Successful creation of 3 books");
});
});

Display the list of books

app.get("/books", (req, res) => {
const sql = "SELECT * FROM Books ORDER BY Title"
pool.query(sql, [], (err, result) => {
if (err) {
return console.error(err.message);
}
res.render("books", { model: result.rows });
});
});
  • The 1st parameter is the SQL query to execute
  • The 2nd parameter is an array with the variables necessary for the query. Here, the value “[]” is used because the query does not need a variable.
  • The 3rd parameter is a callback function called after the execution of the SQL query.
  • (err, result)” corresponds to the parameters passed to the callback function. “err” may contain an error object and “result” is an object containing information about the result of the execution of the query, including a “rows” array corresponding to the list of lines returned by the SELECT.
<%- include("_header") -%><h1>List of books</h1><ul>  <% for (const book of model) { %>
<li>
<%= book.title %>
<em>(<%= book.author %>)</em>
</li>
<% } %>
</ul><%- include("_footer") -%>

Display books in tabular form

<%- include("_header") -%><h1>List of books (<%= model.length %>)</h1><div class="table-responsive-sm">
<table class="table table-hover">
<thead>
<tr>
<th>Title</th>
<th>Author</th>
<th>Comments</th>
<th class="d-print-none">
<a class="btn btn-sm btn-success" href="/create">Add</a>
</th>
</tr>
</thead>
<tbody>
<% for (const book of model) { %>
<tr>
<td><%= book.title %></td>
<td><%= book.author %></td>
<td><%= book.comments %></td>
<td class="d-print-none">
<a class="btn btn-sm btn-warning" href="/edit/<%= book.book_id %>">Edit</a>
<a class="btn btn-sm btn-danger" href="/delete/<%= book.book_id %>">Delete</a>
</td>
</tr>
<% } %>
</tbody>
</table>
</div>
<%- include("_footer") -%>

7. Modify an existing row

The “views/edit.ejs” and “views/_editor.ejs” views

<%- include("_header") -%><h1>Update a record</h1><form action="/edit/<%= model.book_id %>" method="post">
<div class="form-horizontal">
<%- include("_editor") -%> <div class="form-group row">
<label class="col-form-label col-sm-2"></label>
<div class="col-sm-10">
<input type="submit" value="Update" class="btn btn-default btn-warning" />
<a class="btn btn-outline-dark cancel" href="/books">Cancel</a>
</div>
</div>
</div>
</form>
<%- include("_footer") -%>
<div class="form-group row">
<label class="col-form-label col-sm-2" for="Title">Title</label>
<div class="col-sm-8">
<input autofocus class="form-control" name="Title" value="<%= model.title %>" />
</div>
</div>
<div class="form-group row">
<label class="col-form-label col-sm-2" for="Author">Author</label>
<div class="col-sm-7">
<input class="form-control" name="Author" value="<%= model.author %>" />
</div>
</div>
<div class="form-group row">
<label class="col-form-label col-sm-2" for="Comments">Comments</label>
<div class="col-sm-10">
<textarea class="form-control" cols="20" name="Comments" maxlength="32000" rows="7"><%= model.comments %></textarea>
</div>
</div>

The GET /edit/xxx route

// GET /edit/5
app.get("/edit/:id", (req, res) => {
const id = req.params.id;
const sql = "SELECT * FROM Books WHERE Book_ID = $1";
pool.query(sql, [id], (err, result) => {
// if (err) ...
res.render("edit", { model: result.rows[0] });
});
});

The POST /edit/xxx route

// POST /edit/5
app.post("/edit/:id", (req, res) => {
const id = req.params.id;
const book = [req.body.title, req.body.author, req.body.comments, id];
const sql = "UPDATE Books SET Title = $1, Author = $2, Comments = $3 WHERE (Book_ID = $4)";
pool.query(sql, book, (err, result) => {
// if (err) ...
res.redirect("/books");
});
});

The middleware “express.urlencoded()”

// Server configuration
app.set("view engine", "ejs");
app.set("views", path.join(__dirname, "views"));
app.use(express.static("public"));
app.use(express.urlencoded({ extended: false })); // <--- middleware configuration

8. Create a new row

The “views/create.ejs” view

<%- include("_header") -%><h1>Create a record</h1><form action="/create" method="post">
<div class="form-horizontal">
<%- include("_editor") -%> <div class="form-group row">
<label class="col-form-label col-sm-2"></label>
<div class="col-sm-10">
<input type="submit" value="Save" class="btn btn-default btn-success" />
<a class="btn btn-outline-dark cancel" href="/books">Cancel</a>
</div>
</div>
</div>
</form>
<%- include("_footer") -%>

The GET /create route

// GET /create
app.get("/create", (req, res) => {
res.render("create", { model: {} });
});
// GET /create
app.get("/create", (req, res) => {
const book = {
Author: "Victor Hugo"
}
res.render("create", { model: book });
});

The POST /create route

  • It retrieves the data entered via the body property of the Request object from the Express framework.
  • The pool.query() method of node-postgres is used to execute an "INSERT INTO ..." query.
  • The callback function redirects the user to the book list.
// POST /create
app.post("/create", (req, res) => {
const sql = "INSERT INTO Books (Title, Author, Comments) VALUES ($1, $2, $3)";
const book = [req.body.title, req.body.author, req.body.comments];
pool.query(sql, book, (err, result) => {
// if (err) ...
res.redirect("/books");
});
});

9. Delete a row

The “views/delete.ejs” and “views/_diplay.ejs” views

<%- include("_header") -%><h1>Delete a record?</h1><form action="/delete/<%= model.book_id %>" method="post">
<div class="form-horizontal">
<%- include("_display") -%> <div class="form-group row">
<label class="col-form-label col-sm-2"></label>
<div class="col-sm-10">
<input type="submit" value="Delete" class="btn btn-default btn-danger" />
<a class="btn btn-outline-dark cancel" href="/books">Cancel</a>
</div>
</div>
</div>
</form>
<%- include("_footer") -%>
<div class="form-group row">
<label class="col-form-label col-sm-2" for="Title">Title</label>
<div class="col-sm-8">
<input readonly class="form-control" id="Title" value="<%= model.title %>" />
</div>
</div>
<div class="form-group row">
<label class="col-form-label col-sm-2" for="Author">Author</label>
<div class="col-sm-7">
<input readonly class="form-control" id="Author" value="<%= model.author %>" />
</div>
</div>
<div class="form-group row">
<label class="col-form-label col-sm-2" for="Comments">Comments</label>
<div class="col-sm-10">
<textarea readonly class="form-control" cols="20" id="Comments" maxlength="32000" rows="7"><%= model.comments %></textarea>
</div>
</div>

The GET /delete/xxx route

// GET /delete/5
app.get("/delete/:id", (req, res) => {
const id = req.params.id;
const sql = "SELECT * FROM Books WHERE Book_ID = $1";
pool.query(sql, [id], (err, result) => {
// if (err) ...
res.render("delete", { model: result.rows[0] });
});
});

The POST /delete/xxx route

  • It finds the identifier of the book to be deleted via req.params.id.
  • The pool.query() method of node-postgres executes a "DELETE ..." query for this identifier.
  • The callback function redirects the user to the book list.
// POST /delete/5
app.post("/delete/:id", (req, res) => {
const id = req.params.id;
const sql = "DELETE FROM Books WHERE Book_ID = $1";
pool.query(sql, [id], (err, result) => {
// if (err) ...
res.redirect("/books");
});
});

10. Conclusion

  • npm init and npm init -y to initialize a project
  • npm install… (without — save) to install modules
  • npm start to launch the project
  • app.set(…) and app.use(…) to configure the server and middleware
  • app.listen(port, callback) to start the server
  • app.get(url, callback) to respond to GET requests
  • app.post(url, callback) for POST from the input forms
  • req.params.* to retrieve the named parameters from the URL (the route)
  • req.body.* to access the data posted by the input form
  • res.send(“text”) to return a text
  • res.render(view_name, model) to return a view
  • res.redirect(url) to redirect the user
  • use of partial views to simplify work
  • and EJS looks a lot like ASP or Sinatra’s ERB views
  • new Pool() to connect to the database
  • pool.query(sql, [params], callback) to execute all types of queries (data management, SELECT returning several lines, SELECT by ID…)
  • use arrows functions for callbacks
  • declare constants whenever possible (i.e. always in the developed program)
  • use loops for…. of simpler than classic loops for (let i = 0; i < list.length; i++)

Appendix — The complete code for “index.js”

  • 156 lines of code
  • 3 NPM dependencies (ejs, express and pg)
  • 3 imported modules (express, path and pg)
const express = require("express");
const path = require("path");
const { Pool } = require("pg");
// Creating the Express server
const app = express();
// Server configuration
app.set("view engine", "ejs");
app.set("views", path.join(__dirname, "views"));
app.use(express.static(path.join(__dirname, "public")));
app.use(express.urlencoded({ extended: false }));
// Connection to the PostgreSQL database
const pool = new Pool({
user: "mystery",
host: "xxxxx.elephantsql.com",
database: "mystery",
password: "asecretthingthatnoonehastosee",
port: 5432
});
console.log("Successful connection to the database");
// Creating the Books table (Book_ID, Title, Author, Comments)
const sql_create = `CREATE TABLE IF NOT EXISTS Books (
Book_ID SERIAL PRIMARY KEY,
Title VARCHAR(100) NOT NULL,
Author VARCHAR(100) NOT NULL,
Comments TEXT
);`;
db.run(sql_create, err => {
if (err) {
return console.error(err.message);
}
console.log("Successful creation of the 'Books' table");
// Database seeding
const sql_insert = `INSERT INTO Books (Book_ID, Title, Author, Comments) VALUES
(1, 'Mrs. Bridge', 'Evan S. Connell', 'First in the serie'),
(2, 'Mr. Bridge', 'Evan S. Connell', 'Second in the serie'),
(3, 'L''ingénue libertine', 'Colette', 'Minne + Les égarements de Minne');
ON CONFLICT DO NOTHING;`;
pool.query(sql_insert, [], (err, result) => {
if (err) {
return console.error(err.message);
}
const sql_sequence = "SELECT SETVAL('Books_Book_ID_Seq', MAX(Book_ID)) FROM Books;";
pool.query(sql_sequence, [], (err, result) => {
if (err) {
return console.error(err.message);
}
console.log("Successful creation of 3 books");
});
});
});
// Starting the server
app.listen(3000, () => {
console.log("Server started (http://localhost:3000/) !");
});
// GET /
app.get("/", (req, res) => {
// res.send("Hello world...");
res.render("index");
});
// GET /about
app.get("/about", (req, res) => {
res.render("about");
});
// GET /data
app.get("/data", (req, res) => {
const test = {
titre: "Test",
items: ["one", "two", "three"]
};
res.render("data", { model: test });
});
// GET /books
app.get("/books", (req, res) => {
const sql = "SELECT * FROM Books ORDER BY Title";
pool.query(sql, [], (err, result) => {
if (err) {
return console.error(err.message);
}
res.render("books", { model: result.rows });
});
});
// GET /create
app.get("/create", (req, res) => {
res.render("create", { model: {} });
});
// POST /create
app.post("/create", (req, res) => {
const sql = "INSERT INTO Books (Title, Author, Comments) VALUES ($1, $2, $3)";
const book = [req.body.title, req.body.author, req.body.comments];
pool.query(sql, book, (err, result) => {
if (err) {
return console.error(err.message);
}
res.redirect("/books");
});
});
// GET /edit/5
app.get("/edit/:id", (req, res) => {
const id = req.params.id;
const sql = "SELECT * FROM Books WHERE Book_ID = $1";
pool.query(sql, [id], (err, result) => {
if (err) {
return console.error(err.message);
}
res.render("edit", { model: result.rows[0] });
});
});
// POST /edit/5
app.post("/edit/:id", (req, res) => {
const id = req.params.id;
const book = [req.body.title, req.body.author, req.body.comments, id];
const sql = "UPDATE Books SET Title = $1, Author = $2, Comments = $3 WHERE (Book_ID = $4)";
pool.query(sql, book, (err, result) => {
if (err) {
return console.error(err.message);
}
res.redirect("/books");
});
});
// GET /delete/5
app.get("/delete/:id", (req, res) => {
const id = req.params.id;
const sql = "SELECT * FROM Books WHERE Book_ID = $1";
pool.query(sql, [id], (err, result) => {
if (err) {
return console.error(err.message);
}
res.render("delete", { model: result.rows[0] });
});
});
// POST /delete/5
app.post("/delete/:id", (req, res) => {
const id = req.params.id;
const sql = "DELETE FROM Books WHERE Book_ID = $1";
pool.query(sql, [id], (err, result) => {
if (err) {
return console.error(err.message);
}
res.redirect("/books");
});
});

--

--

Database engineer going rogue on cloud architect

Love podcasts or audiobooks? Learn on the go with our new app.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Luc chan

Luc chan

Database engineer going rogue on cloud architect