Reusing postgresql pool in other node javascript files

Question

I am creating nodejs backend app with postgresql database. What I want is when once I create connection to database in my db.js file, that I can reuse it in other files to execute queries.

This is my db.js file

const pool = new Pool({
    user: 'us',
    host: 'localhost',
    database: 'db',
    password: 'pass',
    port: 5432,
})
pool.on('connect', () => {
    console.log('connected to the Database');
});
module.exports = () => { return pool; }

And this is how I tried to use it in index.js file

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

app.get('/', (request, response) => {

    db().query('SELECT * FROM country'), (error, results) => {
        if (error) {
            response.send(error)
        }
        console.log(results)
        response.status(201).send(results)
    }
})

There aren't any errors, and when I go to this specific page, it's keep loading. Nothing in console also.

But, if I write a function in my db.js file and do something like pool.query(...), export it, and in my index.js I write app.get('/', exportedFunction), everything is working fine.

Is there any way not to write all my (like 50) queries in just one (db.js) file, because I want to organise my project a little bit?

Can you share the function that you wrote in the db.js file, please? — Subbu, Jul 22 '19 at 16:11

whoami - fakeFaceTrueSoul · Accepted Answer · 2020-08-05T15:05:52.980

To streamline your project structure entirely, if you're starting from scratch maybe try this :

index.js

const express = require('express');
const app = express();

const PORT = 8080;
const bodyparser = require('body-parser');
const baseRouter = require('../your-router');

app.use(bodyparser.json());
app.use(express.json());
app.use('/', baseRouter);

app.listen(PORT, function () {
console.log('Server is running on PORT:', PORT);
});

your-router.js

const Router = require('express');
const router = Router();
const getCountries = require('../handlers/get');

router.get('/check-live', (req, res) => res.sendStatus(200));
// route for getCountries
router.get('/countries', getCountries);

src/handler/get.js

const YourService = require('./service/your-service');

function getCountries(request, response) {
    const yourService = new YourService();
    yourService.getCountries(request)
        .then((res) => { response.send(res); })
        .catch((error) => { response.status(400).send({ message: error.message }) })
}

module.exports = getCountries;

src/service/your-service.js

const connectionPool = require('../util/dbConnect');
class yourService {

    getCountries(req) {
        return new Promise(((resolve, reject) => {
            connectionPool.connect((err, db) => {
                if (err) reject(err);
                let query = format('SELECT * FROM country'); // get inputs from req
                db.query(query, (err, result) => {
                    if (err) reject(err);
                    resolve(result);
                })
            });
        }));
    }
}

module.exports = yourService;

dbConnect.js

const pgCon = require('pg')
const PGUSER = 'USER'
const PGDATABASE = 'localhost'
let config = {
    user: PGUSER,
    database: PGDATABASE,
    max: 10,
    idleTimeoutMillis: 30000
}

let connectionPool = new pgCon.Pool(config);

module.exports = connectionPool;

Please consider this as a basic example, refactor your code to use callbacks/async awaits (in the above example you can just use callbacks not needed to convert into promise), if needed - you can have DB-layer calls from the service layer in order to extract DB methods from the service layer.

Thank you, this was helpful, specially dbConnect.js and your-service.js where I figured out how to export created pool, and use it in another file, which was my primary question. And thank you for whole project structure example! — lexus_sa, Jul 22 '19 at 19:47
"const yourService = new YourService();" gives me "YourService is not a constructor" error, why is that? — vikrant, Aug 05 '20 at 09:58
you are also not using the resolve function in your promise, so how will the "then" work in promise? — vikrant, Aug 05 '20 at 10:54
@vikrant: that's a basic code; more kind of showing op the structure, not on actual coding! So once `.connect` is returned then the connection is released and can be re-used if you're looking into code give it a try with the updated answer. — whoami - fakeFaceTrueSoul, Aug 05 '20 at 15:08
I have not checked yet, but I assume I will get the error as the service class is not a constructor, isn't it? — vikrant, Aug 05 '20 at 15:25

Reusing postgresql pool in other node javascript files

1 Answers1