Node.js node-postgres get JSON objects from API call

Question

I have followed the node-postgres.org instruction at https://node-postgres.com/guides/async-express to connect via async/await to my postgres table users.

Navigating to localhost:3000/users/1 will return the JSON string for user 1 in the browser. I have extended this a bit to return all users at localhost:3000/users. My routes/user.js script is:

const Router = require('express-promise-router')

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

// create a new express-promise-router
// this has the same API as the normal express router except
// it allows you to use async functions as route handlers
const router = new Router()

// export our router to be mounted by the parent application
module.exports = router

router.get('/:id', async (req, res) => {
  console.log('Where id = ');
  const { id } = req.params
  const { rows } = await db.query('SELECT * FROM users WHERE id = $1', [id])
  res.send(rows[0])
})

router.get('/', async (req, res) => {
  console.log('*');
  const { rows } = await db.all('SELECT * FROM users')
  res.send(rows)
})

the index for this route at routes/index.js is simply:

const users = require('./user')

module.exports = (app) => {
  app.use('/users', users)
} 

and the db.query() and db.all() functions that I am awaiting are in db/index.js:

const { Pool } = require('pg')
const pool = new Pool()
module.exports = {
    query: (text, params) => pool.query(text, params),
    all: (text) => pool.query(text)
}

The routes are required in my main app.js file:

// ./app.js
const express = require('express')
const mountRoutes = require('./routes')
const cons = require('consolidate')
const path = require('path')
const bodyParser = require('body-parser')
const app = express()
mountRoutes(app)

// Assign Dust Engine to .dust files
app.engine('dust', cons.dust);

// Set .dust as the default extension
app.set('view engine', 'dust');
app.set('views', __dirname + '/views');

// Set Public Folder
app.use(express.static(path.join(__dirname, 'public')));

//Body parser and Middleware
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({
  extended: false
}));

app.get('/', function(reg, res) {
  console.log('Root');
  res.render('index', {hallo:'test'})
});

//Server 
app.listen(3000, function() {
  console.log('Server Started on Port 3000');
});

So far this works beautifully! I get the JSON strings that I want and I can build upon this sort of API by extending my routes and queries.

Question: How can I return my JSON object rows back to app.js to res.render() it there?

or

Can I do something like this anywhere in my app:

jsonVar = GetMyJson('/users/1');
console.log(jsonVar);

returns:

[
  {
    "id": 1,
    "usr_name": "Michael"
  },
  {
    "id": 2,
    "usr_name": "Thomas"
  },
  {
    "id": 3,
    "usr_name": "Paul"
  }
]

I could then pass whatever route and parameters I want into GetMyJson() and deal with the resulting JSON.

This may be a trivial question for javascript devs ...

Thanks!

EDIT 21/12/2017

I have created a frontend script called graphs.js that simply logs my result when i call the fuction api('/user/1').

var el = document.getElementById("clickMe");
if (el.addEventListener)
    el.addEventListener("click", api, false);
else if (el.attachEvent)
    el.attachEvent('onclick', api);

var api = function(what){
    // Action
    sendRequest(what, function(result){
        if(result){
           log(result);
         }
    })

}

var apiEndpoint = 'http://localhost:3000/'
function sendRequest(_path, cb) {
    var oReq = new XMLHttpRequest();
    oReq.open('GET', apiEndpoint+_path);
    oReq.onreadystatechange = function() {
        if (this.readyState === 4) {
            cb(JSON.parse(this.response));
        }
        else{
            cb(null);
        }
    }
    oReq.send();
}

function log(msg){
    console.log(msg);
}

BUT Is that a proper way of doing it in javascript?

Answer 1

The way to go about it would be something like this:

router.get('/', async (req, res) => {
  console.log('*');
  const { rows } = await db.all('SELECT * FROM users')
  res.render('user', rows)
});

Instead of res.send you do res.render

Answer 2

You have to get it from database. Making self call will not be an ideal solution. So you can do following

const db = require('../db') // fix the location of db.js

app.get('/', function(req, res) {
  db.all('SELECT * FROM users').then(function({ rows }) {
    res.render('index', rows)
  })
})

Best would be to make an userRepository.js and add code related to db over there. Then use that repository at both places.