Database query Promise not resolving and blocking the request

Question

I'm trying to implement an H2 database connection (the database is running on server mode so I can make multiple connections via TCP/IP) in a web server and I found myself in the same state every time, the function is called and it returns the promise in a pending status which I leave with an await that ends up just going and going, without ever getting the answer from the query.

The stack I'm using is

• Node (12.11.0, don't blame me, it was the client's requirement)
• Express.js, since the client only needs 3 endpoints
• H2 database already existing on my client's server for authentication
• SQL Server, which has the core data of the business and I'll be using after getting the auth done.

Call of the function

userModel.getUsers();

The function getUsers

    try {
        const connection = await h2.getInstance().getConnection();
        console.log('Connection established');

        return connection.query('SELECT * FROM USERS');
    } catch (error) {
        console.error(error);
        throw error;
    }

H2 connection class as you will see I'm using the pg library for handling the connection since it can be done via TCP/IP, but have also tried using jdbc with the same result.

    class H2Connection {
        // Use a private static field to store the instance
        static #instance = null;

        // Use a private constructor to prevent direct instantiation
        constructor() {
            this.connection = new pg.Client(h2Conf);
            this.connection.connect();
        }

        static getInstance() {
            // If the instance does not exist, create a new one
            if (H2Connection.#instance == null) {
                H2Connection.#instance = new H2Connection();
            }
            // Return the instance
            return H2Connection.#instance;
        }

        // Use a method to get the connection
        getConnection() {
           return this.connection;
        }
    }

Tried:

• Changed the handling of callbacks to promises and vice versa.
• Tried to use await on the call of the function to receive the promise resolved but it never is.
• Debugged the code to check for any errors that might not be catching, but it ends up going back and forth in the waiting for request internal process of the web server.
• Tried to resolve the promise and return the values needed in a local variable inside the function but outside the scope of the promise/callback.

Expected:

• Logging of any kind after the call of the service which I got when not using await on the call of the function but it always assigned undefined values to the variable that later I checked in the logic.
• Tried to get logging from the usage of then-catch, but also never got any inside or outside the function.
• The result from the query in an array or JSON format.

Answer 1

Looks like your connection factory could be simplified, I couldn't reproduce the full scenario but connection.query() needs to have a connected instance of the database with this.connection.connect(); first, which is a Promise but you're not waiting for it to finish before calling return connection.query('SELECT * FROM USERS');.

What you can do is to use the Singleton Design Pattern to build a reusable connection instance, and use it globally across the project. One important thing after instantiating the singleton, is to call a method to setup it/wait for a connection establishment before running queries, like waitConnection(), because there's a limitation by design of forbidden async constructors, I recommend taking a look. The solution I propose is something like that:

database-driver.js:

const pg = require("pg");

const connectionString = "..."

class H2ConnectionFactory {
    constructor(h2ConnectionString) {
        this.connection = new pg.Client(h2ConnectionString);
    }

    async waitConnection() {
        await this.connection.connect();
    }

    getConnection() {
        return this.connection;
    }
}

export const dbSingleton = new H2ConnectionFactory(connectionString);

main.js:

import { dbSingleton } from "./database-driver";

async function getAnyData() {
    try {
        const connection = dbSingleton.getConnection();
        console.warn('query rows result:', (await connection.query('SELECT NOW()')).rows);
    } catch (error) {
        console.error(error);
        throw error;
    }
};

async function main() {
    await dbSingleton.waitConnection();
    console.log('Connection established');

    getAnyData();
}

main()

So the order of the code is:

• 1. Ensure DB is connected successfully;
• 2. Execute any query reusing the connection afterwards;

Hope that helps.