How to create html table dynamically from database with node.js and express?

Question

I am totally new in node.js and I want to create a simple phonebook app with express and postgresql. I want to have two pages, one to add a new contact and another one for contacts to be shown in an html table with the ability to update or delete rows. Until now I have implemented the insert but I don't know how to create the "contacts.html" page dynamically from database. Thank you in advance!

index.html

<header>
    <ul>
        <li><h2>Phonebook</h2></li>
        <li><a href="index.html" id="index">New Contact</a></li>
        <li><a href="contacts.html" id="contacts">Contacts</a></li>
    </ul>
</header>

<section>
    <form action="insertContact">
        <p>Full Name</p>
        <input type="text" name="fullname" required>

        <p>Phone</p>
        <input type="text" name="phone1" required>

        <p>Mobile</p>
        <input type="text" name="phone2">

        <p>Address</p>
        <input type="text" name="address" required> <br><br>

        <input type="submit" name="submitBtn" id="submitBtn" value="Submit">
    </form>
</section>

server.js

var express = require('express');
var path = require('path');
var db = require('pg');
var http = require('http');

var app = express();

app.use(express.static(path.join(__dirname,'/')));

var dbConnection = "postgres://postgres:root@localhost:5432/Phonebook";

app.get('/insertContact',function(req,res){
    var dbClient = new db.Client(dbConnection);

    dbClient.connect(function(err){
        if(err)
            throw err;

        var query = "insert into Contacts (fullname,phone,mobile,address) values ($1,$2,$3,$4)";
        var fullname = req.query.fullname;
        var phone = req.query.phone1;
        var mobile = req.query.phone2;
        var address = req.query.address;

        var contact = [fullname , phone , mobile , address];

        dbClient.query(query , contact , function(err){
            if(err)
                throw err;
            else {
                console.log('Success!') ;
                res.redirect('/');      
                res.end();
            }               
        });
    });
});

app.get('????',function(req,res) {
    var dbClient = new db.Client(dbConnection);

    dbClient.connect(function(err){
        if(err)
            throw err;

        var query = "select * from Contacts";

        dbClient.query(query,function(err,result){
            if(err)
                throw err;
            else {

                   ??????????

                res.end();
            }
        });
    });
});

app.listen(8080,function(){
    console.log('Server started');
});

sample image

Solution 1

Thanks to Amr Labib's help

server.js

var express = require('express');
var path = require('path');
var db = require('pg');
var app = express();

app.use(express.static(path.join(__dirname,'/')));
app.set('view engine', 'ejs');

var dbConnection = "postgres://postgres:root@localhost:5432/Phonebook";


// Insert Contact

app.get('/insertContact',function(req,res){
    var dbClient = new db.Client(dbConnection);

    dbClient.connect(function(err){
        if(err)
            throw err;

        var query = "insert into Contacts (fullname,phone,mobile,address) values ($1,$2,$3,$4)";
        var fullname = req.query.fullname;
        var phone = req.query.phone;
        var mobile = req.query.mobile;
        var address = req.query.address;

        var contact = [fullname , phone , mobile , address];

        dbClient.query(query , contact , function(err){
            if(err)
                throw err;
            else {
                console.log('Contact Inserted!')    ;
                res.redirect('/');      
                res.end();
            }               
        });
    });
});


// Form Handling - Update Row / Delete Row

app.get('/handleForm',function(req,res){
    var dbClient = new db.Client(dbConnection);

    dbClient.connect(function(err){
        if(err)
            throw err;

        if(req.query.deleteBtn != null){

            var query = "delete from Contacts where id = ($1)";
            var id = [req.query.id];

            dbClient.query(query , id , function(err){
                if(err)
                    throw err;
                else {
                    console.log('Contact Deleted!') ;
                    res.redirect('/contacts.html');     
                    res.end();
                }               
            });
        } else if(req.query.updateBtn != null) {
            var query = "update Contacts set fullname=($1),phone=($2),mobile=($3),address=($4) where phone=($5)";
            var fullname = req.query.fullname;
            var phone = req.query.phone;
            var phoneHidden = req.query.phoneHidden;
            var mobile = req.query.mobile;
            var address = req.query.address;            

            dbClient.query(query , [fullname,phone,mobile,address,phoneHidden], function(err){
                if(err)
                    throw err;
                else {
                    console.log('Contact Updated!') ;
                    res.redirect('/contacts.html');     
                    res.end();
                }               
            });         
        }

    });
});


// Search contact by phone

app.get('/searchContact',function(req,res) {
    var dbClient = new db.Client(dbConnection);

    dbClient.connect(function(err){
        if(err)
            throw err;

        var query = "select * from Contacts where phone=($1)";
        var searchBoxValue = req.query.searchBoxValue;

        dbClient.query(query , [searchBoxValue], function(err,result){
            if(err)
                throw err;
            else {
                res.render('searchedContact.ejs' , {contacts: result});
                res.end();
            }               
        }); 
    });
});

// Show Contact's Table

app.get('/contacts.html',function(req,res) {
    var dbClient = new db.Client(dbConnection);

    dbClient.connect(function(err){
        if(err)
            throw err;

        var query = "select * from Contacts";

        dbClient.query(query,function(err,result){
            if(err)
                throw err;
            else {

                res.render('contacts.ejs', { contacts: result });
                res.end();
            }
        });
    });
});

app.listen(8080,function(){
    console.log('Server started');
});

contacts.ejs

    <section id="table">
        <div class="table">

            <div id="headers">
                <span id="id">id</span>
                <span id="fullname">Name</span>
                <span id="phone">Phone</span>
                <span id="mobile">Mobile</span>
                <span id="address">Address</span>
            </div>

            <% for(var i = 0; i < contacts.rows.length; i++) { %>
                    <form class="tr" action="handleForm">
                        <input type="text" id="id" name="id" class="td" readonly value= <%= contacts.rows[i].id %>>
                        <input type="text" name="fullname" class="td" value= <%= contacts.rows[i].fullname %>>
                        <input type="text" name="phone" class="td" value= <%= contacts.rows[i].phone %>>
                        <input type="text" name="mobile" class="td" value= <%= contacts.rows[i].mobile %>>
                        <input type="text" name="address" class="td" value= <%= contacts.rows[i].address %>>
                        <input type="submit" name="updateBtn" id="updateBtn" value="update" class="td">
                        <input type="submit" name="deleteBtn" id="deleteBtn" value="delete" class="td">
                        <input type="hidden" name="phoneHidden" id="phoneHidden" class="td" value=<%= contacts.rows[i].phone %> >
                    </form>
            <% } %>     
        </div>
    </section>

Solution 2

You can do that by using any javascript template language one of the most popular is EJS "embedded javascript" its very easy to integrate and use with node js

You simply create your template and pass any variable like an array.

Check the code below this is how you add a template in EJS

<html >

<head>
    <meta charset="utf-8">
</head>

<body>
    <section class="home">
    <h1>Contacts list</h1>
    <ul class="list-group">
        <% for(var i=0; i<contacts.length; i++) {%>
            <li class="list-group-item">
                <span>Name: </span><%= contacts[i].name %>
                <br/>
                <span>Phone: </span><%= contacts[i].phone %>
            </li>
        <% } %>
    </ul>
    </section>
</body>

</html>

Then in your node js route handler will just render that template and pass the required data.

app.get('????',function(req,res) {
    var dbClient = new db.Client(dbConnection);

    dbClient.connect(function(err){
        if(err)
            throw err;

        var query = "select * from Contacts";

        dbClient.query(query,function(err,result){
            if(err)
                throw err;
            else {
                 res.render('contacts.ejs', { contacts: result });  
            }
        });
    });
});

One final step is to tell node that it will use ejs as template language.

app.set('view engine', 'ejs');

And don't forget to npm install --save ejs

Comments

[Αntonis Papadakis]

I did all these but I'm getting the error: Cannot find module 'ejs'

[Amr Labib]

did you run npm install --save ejs ?

[Amr Labib]

Can you see "ejs" package listed in your package.json file ?

[Αntonis Papadakis]

Yea... "dependencies": { "body-parser": "^1.18.2", "ejs": "^2.5.7", "pg": "^7.3.0" }

[Amr Labib]

Ok can you check if you have a folder named ejs inside node_modules or not just to make sure if its installed ... also i can't find express in your dependencies can you try running npm install express --save

[Αntonis Papadakis]

Yes the file exists. If I try npm install express --save I'm getting this error "Refusing to install express as a dependency of itself ". But the app is working to insert contact.

[Amr Labib]

I am not sure why ejs cannot be found .... i googled and found this answer stackoverflow.com/questions/7754799/… , it might help

[Αntonis Papadakis]

Finally I created a new project and I installed all modules from the beginning and ejs worked. However I changed some thing in your code to get the values. I will post the answer, but now I want to be able to update or delete rows in the table.

[Amr Labib]

Good to know its working fine :) ... to be able to delete contact you can just add a button with contact id <a class="btn btn-default" href="/contacts/delete/<%=contacts[i]._id%>">Delete</a> and handle this route in your node code

[Martynas]

Thanks for the tip, but to be fair, you had to accept Amr Labib answer and not yours...