Javascript event sequence, pg, postgresql, why?

Question

I am trying to write a javascript file in express to talk to a postgresql database. More precisely, I want to write a function that takes SQL as an input parameter and returns the stringified json. I can assume memory is not an issue given these table sizes. This is paid work making an internal use tool for a private business.

My most recent attempt involved the query callback putting the value into a global variable, but even that still fails because the outermost function returns before the json string is defined. Here is the relevant code:

var dbjson;

function callDB(q) {
    pg.connect(connectionString, function(err, client, done) {
        if (err) {
            console.error('error fetching client from pool', err);
        } else {
            client.query(q, [], function(err, result) {
                client.query('COMMIT');
                done();
                if (err) {
                    console.error('error calling query ' + q, err);
                } else {
                    dbjson = JSON.stringify(result.rows);
                    console.log('1 ' + dbjson);
                }
                console.log('2 ' + dbjson);
            });
            console.log('3 ' + dbjson);
        }
        console.log('4 ' + dbjson);
    });
    console.log('5 ' + dbjson);
}

The SQL in my test is "select id from users". The relevant console output is:

5 undefined
GET /db/readTable?table=users 500 405.691 ms - 1671
3 undefined
4 undefined
1 [{"id":1},{"id":2},{"id":3},{"id":4}]
2 [{"id":1},{"id":2},{"id":3},{"id":4}]

Why do the console logs occur in the order that they do? They are consistent in the order.

I attempted to write a polling loop to wait for the global variable to be set using setTimeout in the caller and then clearing the timeout within the callback but that failed, I think, because javascript is single threaded and my loop did not allow other activity to proceed. Perhaps I was doing that wrong.

While I know I could have each function handle its own database connection and error logging, I really hate repeating the same code.

What is a better way to do this?

I am relatively new to express and javascript but considerably more experienced with other languages.

Answer 1

Presence of the following line will break everything for you:

client.query('COMMIT');

You are trying to execute an asynchronous command in a synchronous manner, and you are calling done(), releasing the connection, before that query gets a chance to execute. The result of such invalid disconnection would be unpredictable, especially since you are not handling any error in that case.

And why are you calling a COMMIT there in the first place? That in itself looks completely invalid. COMMIT is used for closing the current transaction, that which you do not even open there, so it doesn't exist.

There is a bit of misunderstanding there in terms of asynchronous code usage and the database also. If you want to have a good start at both, I would suggest to have a look at pg-promise.