NodeJs - Rendering same page after multiple SELECT queries

Question

I have below codes in my index.js file. I can print data of profile table. And i need to print data of resume table also in same (index.njk) page. But i couldn't. I also found a similar question but i am new and couldn't modify those codes according to my project. Can you please help?

var express = require('express'),
path = require('path'),
bodyParser = require('body-parser'),
router = express.Router(),
app = express();
var pg =require('pg');

// DB Connect string
var connect = {
user: 'arslan', 
database: 'resumedb', 
password: '1984',
host: 'localhost', 
port: 5432, 
max: 10, 
idleTimeoutMillis: 30000, 
};

router.get('/', function(req, res){
pg.connect(connect, function(err, client, done, skills){
  if(err){
    return console.error('errrr', err)
  }
  //Get Profile Informations
client.query('select id,fname,lname,title,description,profileimage from profile', function(err, result){

    if(err){
      return console.error('error running query', err);
    }
   if(result.rows.length > 0) {
          res.render('index.njk', { 
                profileName: result.rows[0].fname,
                profileLName: result.rows[0].lname , profileTitle: result.rows[0].title
              , profileDesc: result.rows[0].description 
              , profileImage: result.rows[0].profileimage
          });

       console.log(result.rows[0].profileimage);
    }else {
        console.log('No rows found in DB');
    }
    done() 
});
}); 
});

Answer 1

The best solution for all async stuff is using Promises. Your code uses the config for a connection pool, but later you dont use a pool, but its often a good idea to use one. You create a new module db.js to query the db

const pg = require('pg')

const connect = { // Normaly you would use an config file to store this information
  user: 'arslan',
  database: 'resumedb',
  password: '1984',
  host: 'localhost',
  port: 5432,
  max: 10,
  idleTimeoutMillis: 30000
}

let pool = new pg.Pool(config)

exports.query = (query, values) => {
  return new Promise((resolve, reject) => {
    pool.connect(function(err, client, done) {
      if (err)
        return reject(err)
      client.query(query, values, (err, result) => {
        done()
        if (err)
          return reject(err)
        resolve(result)
      })
    })
  })
}

exports.queryOne = (query, values) => {
  return new Promise((resolve, reject) => {
    this.query(query, values).then(rows => {
      if (rows.length === 1) {
        resolve(rows[0])
      } else if (rows.length === 0) {
        resolve()
      } else {
        reject(new Error('More than one row in queryOne'))
      }
    }).catch(err => {
      reject(err)
    })
  })
}

pool.on('error', function (err, client) {
  console.error('idle client error', err.message, err.stack)
})

and then in your route

// ...
const db = require('./db')

router.get('/', function(req, res, next) {

  let profileQuery = db.queryOne('select id,fname,lname,title,description,profileimage from profile')
  let resumeQuery = db.query('???')
  Promise.all([profileQuery, resumeQuery]).then(([profile, resume]) => {
    if (!profile) {
        return res.status(404).send('Profile not found') // error page
    res.render('index.njk', { 
            profileName: profile.fname,
            profileLName: profile.lname,
            profileTitle: profile.title,
            profileDesc: profile.description,
            profileImage: profile.profileimage
      })
  }).catch(err => {
    next(err)
  })

})

If you want to make a single query you can use db.query('select 1 + 1').then(rows => { /* your code */}).catch(err => { next(err) }). Because you often only want one row you can use queryOne. It is returning undefined with no rows, the row you want, or an error for multiple rows

The next() with an error as argument will call the express error handlers. There you can log the error and return 500. You should create your own for that

Please ask if you dont understand something, because it could be complicated for the first time :)