Connecting PostgreSQL to a Javascript server

The full context of the code shown here can be viewed here

|-- package.json
|-- config.js
|-- db
    |-- pgHelper.js
    |-- dbQueryHelper.js
|-- controllers
    |-- index.js
    |-- locations_controller.js`

First steps first, lets make sure that you have the following in your package.json:

package.json

"dependencies": {
    "body-parser": "^1.17.2",
    "express": "^4.15.3",
    "pg": "^6.0.2"
  }

And that you have used createdb database_name and psql -d database_name -f database_setup.sql to create your database, and that you know it's local address.

Next, make a config.js file. This will store constants in one place, so if it changes you only have to change them in one location. The only one we need for now is:

config.js

module.exports = {
  'database': 'postgres://user@localhost/react_table_tennis'  
}

This then ties to a pgHelper held in a db folder, to handle all forms of requests:

pgHelper.js

"use strict"
const config = require('../config')


const pg = require('pg'),
    databaseURL = config.database

    exports.query = function (sql, values, dontLog) {

    if (!dontLog) {
console.log(sql, values)
    }

    return new Promise((resolve, reject) => {

        pg.connect(databaseURL, function (err, conn, done) {
            if (err) return reject(err)
            try {
                conn.query(sql, values, function (err, result) {
                    done()
                    if (err) {
                        reject(err)
                    } else {
                        resolve(result.rows)
                    }
                })
            }
            catch (e) {
                done()
                reject(e)
            }
        })
    })
}

In the same db folder a dbQueryHelper is made to handle specific request types:

dbQueryHelper.js

"use strict"
const db = require('./pgHelper')

//LOCATIONS
  findAllLocations(req, res, next){
    const sql = "SELECT * FROM locations ORDER BY l_name ASC"

    db.query(sql, [])
        .then(locations => res.json(locations))
        .catch(next)
  }
  findLocationById(req, res, next){
    const sql = "SELECT * FROM locations WHERE id = $1"

    const id = req.params.id

    db.query(sql, [id])
        .then(location => res.json(location[0]))
        .catch(next)
  }
  addLocation(req, res, next){
    const sql = "INSERT INTO locations (l_name) VALUES ($1) RETURNING *"

    const l_name = req.body.l_name

    db.query(sql, [l_name])
        .then(location => res.json(location[0]))
        .catch(next)    
  }
  deleteLocation(req, res, next){
    const sql = "DELETE FROM locations WHERE id = $1 RETURNING *"

    const id = req.params.id

    db.query(sql, [id])
        .then(location => res.json(location))
        .catch(next)
  }
  updateLocation(req, res, next){
    const sql = "UPDATE locations SET (l_name) = ($1) WHERE id = $2 RETURNING *"

    const l_name = req.body.l_name
    const id = req.params.id

    db.query(sql, [l_name, id])
        .then(location => res.json(location))
        .catch(next)
  }

Finally this can tie to a controller in a controllers folder, itself tied to an index.js master controller, as below:

locations_controller.js

const express = require('express')
const locationsRouter = new express.Router()
const dbQueryHelper = require('../db/dbQueryHelper.js')
const query = new dbQueryHelper()

locationsRouter.get('/', query.findAllLocations)

locationsRouter.post('/', query.addLocation)

locationsRouter.delete('/:id', query.deleteLocation)

locationsRouter.get('/:id', query.findLocationById)

locationsRouter.patch('/:id', query.updateLocation)


module.exports = locationsRouter

dominicfraser/postgres_server_connection_setup.md

Connecting PostgreSQL to a Javascript server