using postgres with nodejs for connection pool

Question

i am using nodejs as my REST api host. The overall functionality is pretty basic. that is, make a REST call which should do a postgres db query and return data. The problem I am having is the overall code organization.

I have the server.js which has basic initialization. Now, where should i place the postgres connection and call it to query db whenever i need it. current structure is as below. so i think i should be moving the postgres connect code to server.js? what should be the right structure

server.js

"use strict";

 var express = require('express'),
  app = express(),
  port = process.env.PORT || 4001,
  bodyParser = require('body-parser');

  app.use(bodyParser.urlencoded({ extended: true }));
  app.use(bodyParser.json());
  app.use(function (req, res, next) {

    // Website you wish to allow to connect
    res.setHeader('Access-Control-Allow-Origin', '*')
    // Request methods you wish to allow
    res.setHeader('Access-Control-Allow-Methods', 'GET, POST, OPTIONS, PUT, PATCH, DELETE');
    // Request headers you wish to allow
    res.setHeader('Access-Control-Allow-Headers', 'X-Requested-With,content-type');
    // Set to true if you need the website to include cookies in the requests sent
    // to the API (e.g. in case you use sessions)
    res.setHeader('Access-Control-Allow-Credentials', true);
    // Pass to next layer of middleware
    next();
});
var routes = require('./api/routes/pushRoutes');
routes(app);

app.listen(port);
console.log('push notification server started on: ' + port);

pushController.js

'use strict';

exports.send_notification = function(req, res) {
    console.log("start of send_notification ");

     if (req.method == 'POST') {
        var jsonString = '';

        req.on('data', function (data) {
            jsonString += data;
        });

        req.on('end', function () {
            console.log(JSON.parse(jsonString));
            var json = JSON.parse(jsonString);
            var timeNow = json.message;

            //get device token from db
            var deviceToken = '';
            var pg = require('pg')
            var format = require('pg-format')
            var PGUSER = 'deploy'
            var PGDATABASE = 'oscpushserver'
            var config = {
              user: PGUSER, // name of the user account
              database: PGDATABASE, // name of the database
              max: 10, // max number of clients in the pool
              idleTimeoutMillis: 30000 
            }

            var pool = new pg.Pool(config)
            var myClient

            pool.connect(function (err, client, done) {
              if (err) console.log(err)
              app.listen(3000, function () {
                console.log('listening on 3000')
              })
              myClient = client
              var ageQuery = format('SELECT * from push_table WHERE seq_id = 1')
              myClient.query(ageQuery, function (err, result) {
                if (err) {
                  console.log(err)
                }
                console.log("row data:" + result.rows[0])
                console.log("device id from db:" + result.rows[0].device_id)
                deviceToken =result.rows[0].device_id;


              })
            });


            res.json(JSON.parse(jsonString));
        });
    }

};

Looks about right, if you want to have shorter code, you might want to try node-postgres.com — Mμ.
– Mμ., Commented Jun 20, 2017 at 4:13
well the app inside pool.connect is not resolved in my case as it is defined in the server.js — Vik
– Vik, Commented Jun 20, 2017 at 4:16
Oh wait my bad you are already using node-postgres. May I ask why do you put app inside pool. You already have the app listening on server.js — Mμ.
– Mμ., Commented Jun 20, 2017 at 4:26
well i copy pasted code from 2 different locations and i m super new to nodejs. so didnt know how to resolve it — Vik
– Vik, Commented Jun 20, 2017 at 4:33
pg-promise-demo offers the best code structure for the database that you can find for use with pg-promise. — vitaly-t
– vitaly-t, Commented Jun 20, 2017 at 21:17

Mμ. · Accepted Answer · 2018-01-22 13:19:10Z

9

I assume that you want to make a REST api that just gets data from db.

server.js

Looks fine in most parts, except that you are not using the routes correctly. To use the routes you have defined you use app.use:

app.use('/', routes);

database.js I would create another file where you can put the database credentials in one place and create a new pool.

var pg = require('pg')
var PGUSER = 'deploy'
var PGDATABASE = 'oscpushserver'
var config = {
  user: PGUSER, // name of the user account
  database: PGDATABASE, // name of the database
  max: 10, // max number of clients in the pool
  idleTimeoutMillis: 30000 
}

var pool = new pg.Pool(config);

module.exports = pool;

pushController

I assume that you want to require this file in server.js. So make sure that the path points to '.../pushController' and not './api/routes/pushRoutes'.

const express = require('express');
const router = express.Router();
const format = require('pg-format');
const pool = require('../path/to/database.js');

router.get(function(req, res, next) {
  pool.connect(function (err, client, done) {
    if (err) throw new Error(err);
    var ageQuery = format('SELECT * from push_table WHERE seq_id = 1')
    client.query(ageQuery, function (err, result) {
      if (err) throw new Error(err);
      res.json(result.rows[0]); 
    })
  }); 
});

module.exports = router;

So now, a GET request to your API server should return a json object from your postgres database.

There are loads of examples online on how to make REST APIs with node and postgres. One of them is this: https://github.com/mjhea0/node-postgres-todo.

edited Jan 22, 2018 at 13:19

answered Jun 20, 2017 at 5:01

Mμ.

8,5723 gold badges28 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

chovy Over a year ago

how would i share the pool across multiple route files in my router? I have a separate file for each endpoint in ./routes directory and need to instantiate the pool in one spot in the app and expose it to all the routes. In koa I can stick the connection pool instance on ctx.pool, is it possible with express?

SAURABH · Accepted Answer · 2024-07-30 10:33:04Z

I think we can short the size of the code by using the way we are using pool, first intialise it in db.js file and then export it and then you can use it whenever you want.

Also using async await can really reduce the size of code and can enhance readability of code.

And don't forget to use .env file to store your database credentials.

db.js

import pg from 'pg';
const Pool = pg.Pool;

const dbPort = Number(5432);

  const pool = new Pool({
  user:  'localhost',
  host:  '127.0.0.1',
  database: 'database name',
  password:  'password',
  port: dbPort  ,
  idleTimeoutMillis: 30000, // 30 seconds
  min:2,
  max:20,
  connectionTimeoutMillis: 15000

  }); 
  
    pool.on('error', (err, client) => {
    console.error('Unexpected error on idle client', err)
    process.exit(-1)
  })

  pool.connect( (err : any, connection:any) => {
      if (err) throw err;
      console.log('Database is connected successfully !');
      connection.release();
    }); 
    
  export default pool;

And now you can import this pool into any file where you want to use database pool

Controller file

  import { Request, Response } from "express";
  import pool from '../../db' ;
  
  export const someApi =async (req, res) => {
  
    try{
      const pinSql = `SELECT * FROM table where uid = $1 limit 1`;
      const pinValues = [uid];
      const data = await pool.query(pinSql, pinValues);
                       
      if(data.rowCount === 0) return res.sendStatus(404).json({ "msg": "No data exist !")
     }
    catch(e){
        res.sendStatus(500).json({msg:"Something went wrong !"});
    } }

Collectives™ on Stack Overflow

using postgres with nodejs for connection pool

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related