Call function after getting DB data

Question

I'm a newbie in Node.js and I'm trying to call a function after a select query with the following code:

private getUsersList(): any {

    let con = this.connect();

    con.connect(function(err: any) {
        if (err) throw err;

        let sql = 'SELECT * FROM my_table WHERE 1';

        return con.query(sql, function(this: any, error: any, result: any) {
            if(err) throw error;
            return result;
        });
    });
}


private showUsers()
{
    let usersList = this.getUsersList();
    user.prompt("GetUserList: " + usersList);
}

When I call showUsers it displays an empty array. I don't know why, and how could I fix it.

Answer 1

Firstly I would suggest using a connection pool instead of creating a new client for every request. See: https://node-postgres.com/features/pooling

In your example you're not releasing/closing the client. In fact you're connecting and not using the client the connection gives you.

Check out the node-postgres docs.

Now on to your problem. You're mixing together two ways of getting data, callbacks and promises. You can't return the query since you're using callback form and you can't return the results. You have to either use promises and return the promise or make a callback for your function.

So in the end you're code might look something like this:

import { myPool } from './mypool.ts'

private getTicketsList(cb) {
  const sql = 'SELECT * FROM my_table WHERE 1';

  myPool.query(sql, cb)
}

private showUsers()
{
    this.getUsersList((err, userlist) => {
     if (err) {
       console.log(err)
     }
     
      user.prompt("GetUserList: " + userlist.rows);
    };
}

Or if you want to use promises

import { myPool } from './mypool.ts'

private getTicketsList(cb) {
  const sql = 'SELECT * FROM my_table WHERE 1';

  return myPool.query(sql)
}

private showUsers()
{
    return this.getUsersList.then(userlist => {
      user.prompt("GetUserList: " + userlist.rows);
    }.catch(err => {
      console.log(err)
    }

}

Answer 2

You are trying to receibe data from getTicketsList, but that function hasn't return.

In this case, you should use a Promise:

private getTicketsList(): Promise<any> {
    return new Promise((resolve, reject) => {
        let con = this.connect();
        con.connect(function (err: any) {
            if (err) reject(err);
            let sql = 'SELECT * FROM my_table WHERE 1';
            con.query(sql, function (this: any, error: any, result: any) {
                if (err) reject(error);
                resolve(result);
            });
        });
    });
}

private async showUsers() {
    let usersList = await this.getUsersList();
    user.prompt("GetUserList: " + usersList);
}

Answer 3

There's no magic bullet here to make this faster. Deleting documents requires about the same number of physical writes as initially creating them, so it takes a similar amount of time. Consequently, deleting a lot of documents takes a lot of time.

You can typically speed it up by deleting the document in batches, which is the approach the Firebase CLI takes, and is also the approach taken by the documentation on Delete data with a Callable Cloud Function. But even then it'll be a O(n) operation, taking an amount of time linearly proportional to the number of documents.