CODEWITHSUNDEEP
node.jstypescriptnode-mysql
ChillAndCode
ChillAndCode

Reputation: 308

How to query mySql table with node js

I built a singleton to connect to my database in which I created a query function :

import IDataBase from "../interfaces/i-database";
import mySql from 'mysql';

export default class DatabaseService implements IDataBase {
  private static _instance: DatabaseService;
  private static _connection: mySql.Connection;

  private constructor() {

  }

  public static getInstance(): DatabaseService {
    if (!DatabaseService._instance) {
      DatabaseService._instance = new DatabaseService();
      DatabaseService._connection = DatabaseService._instance._connect();
    }
    return DatabaseService._instance;
  }

  private _connect() {
   return mySql.createConnection({
      host: process.env.DB_HOST,
      user: process.env.DB_USER,
      password: process.env.DB_PASSWORD,
      database: 'chess'
    });
  }

  public query(query: string, params?: any): any {
    DatabaseService._connection.query(query, params, (err: any, results: any) => {
      if (err) {
        console.log(err);
      }
      return results;
    });
  }

}

I have a controller in which I call my singleton in order to call the query method of it :

import * as express from 'express';
import DatabaseService from '../services/database';

export default class UserController {
  public path = 'users';
  public router = express.Router();
  private _db: DatabaseService = DatabaseService.getInstance();

  constructor() {
    this._initializeRoutes();
  }

  private _initializeRoutes() {
    this.router.get('/', this.getAllUsers);
  }

  getAllUsers = (req: express.Request, res: express.Response) => {
    const results = this._db.query('SELECT * FROM player');
    console.log(results);
    res.send('look at server logs').status(200);
  }
}

I don't know why the console.log(results) of the getAllUsers() function returns undefined when I actually have 2 rows in my "player" table. My database connection is OK, but not the query

Thanks in advance

EDIT: The solution is

getAllUsers = async (req: express.Request, res: express.Response) => {
    const results = await this._db.query('SELECT * FROM player', function (err : any, result: any) {
      if (err) {
        console.log(err);
      }
      console.log(result);
    } );
    res.send('look at server logs').status(200);
  }

I am forced to retrieve my data in the callback of the sql.connection.query function

Upvotes: 0

Views: 104

Answers (1)

ardritkrasniqi
ardritkrasniqi

Reputation: 839

Accessing a database is an example of an operation which is asynchronous by nature. It means that you have to wait for the results of a query, but while waiting, your program will continue to execute. In your case, you are making a database query and expecting that the result will be instantly delivered which cannot be the case.
Make sure that you query the db asynchronously through callbacks, promises (optionally async await)

getAllUsers =  async (req: express.Request, res: express.Response) => {
    const results = await this._db.query('SELECT * FROM player');
    console.log(results);
    res.send('look at server logs').status(200);
  }

Upvotes: 1

Related Questions