Unable to get rows from SELECT statement-CodePudding

I am using MySQL NPM package to connect to a MySQL database and trying to select a column from a table by simply invoking a query from my application. But the issue is that, I am not able to get the rows from the table at my repository level code.

First thing I have done is created a connection pool. The implementation looks like this. I create a connection pool and set connectionPoolInitialized as true when connetion is tested successfully.

const mysql = require("mysql");

export class ConnectionPool {
    private connectionPool: any;
    private connectionPoolInitialized: boolean = false;

    public async initializeConnection() {
        try {
            await this.createConnectionPool();
        } catch (err) {
            console.log(err);
        }
    }

    public async query(sql, args) {
        return new Promise((resolve, reject) => {
            this.connectionPool.query(sql, args, (err, rows) => {
                if (err){
                    console.log(err);
                    return reject(err);
                }
                resolve(rows);
            });
        });
    }

    private async createConnectionPool(attempt: number = 0): Promise<void> {
        if (this.connectionPoolInitialized) return;
    
        try {
            this.connectionPool = mysql.createPool({
                connectionLimit: 100,
                host: mysqlHost,
                port: mysqlPort,
                user: mysqlUser,
                password: mysqlPassword
            });
            await this.testConnection();
            this.connectionPoolInitialized = true;
            console.log("Created Connection Pool.");
        } catch (err) {
            console.log(err);
        }
    }

    private testConnection(): Promise<void> {
        return new Promise<void>((resolve, reject) => {
            this.connectionPool.query("SHOW DATABASES;", null, (err, rows) => {
                if (err) {
                    reject(err);
                } else { 
                    console.log("Success.");
                    resolve();
                }
            });
        });
    }
}

I have a BaseRepository from where this connection pool is initialized and queries are executed.

export class BaseRepository {
    private readonly connectionPool: ConnectionPool;

    public async executeQuery(sql: string, values: any) {
        await this.connectionPool.initializeConnection();
        await this.connectionPool.query(sql, null)
            .then((rows) => {
                console.log(JSON.stringify(rows));
                return JSON.parse(JSON.stringify(rows));
            })
            .catch((err) => {
                console.log(err);
                throw err;
            });
    }
}

And then there is actual invocation of the query through BaseRepository.

export class PersonRepository{
    private tableName: string = 'person';
    private readonly baseRepo: BaseRepository;

    public async getActivePersons() {
        const query = `SELECT 
            id 
            FROM dbName.${this.tableName} 
            WHERE isActive = 1;`;

        const rows = await this.baseRepo.executeQuery(query, null);
        console.log('Active Persons');
        console.log(JSON.stringify(rows));
    }
}

When getActivePersons() method is executed the console log in BaseRepository is printing all the rows as string on the console. But When it is getting returned from there to getActivePersons() method the console log to print the rows is printing as undefined.

I also tried returning the rows from BaseRepository without parsing it as JSON but still the result is undefined.

Is there any issue with the code? Am I missing or doing something wrong here?

CodePudding user response：

executeQuery doesn't return anything.

Either use the .then() structure and return the Promise:

return this.connectionPool.query(sql, null)
        .then((rows) => {
            console.log(JSON.stringify(rows));
            return JSON.parse(JSON.stringify(rows));
        })
        .catch((err) => {
            console.log(err);
            throw err;
        });

Or use the await syntax and return the result:

try {
  let rows = await this.connectionPool.query(sql, null);
  console.log(JSON.stringify(rows));
  return JSON.parse(JSON.stringify(rows));
} catch (err) {
  console.log(err);
  throw err;
};

But not both. By using both you've created a structure where you await the Promise, but never return the result to which it resolves.