How to query mySql table with node js

Question

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

ardritkrasniqi · Accepted Answer · 2022-05-19 22:36:48Z

1

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);
  }

answered May 19, 2022 at 22:36

ardritkrasniqi

8596 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ChillAndCode Over a year ago

Thank you for your answer, I modified my initial post and posted the entire solution, could you explain to me why I have to retrieve my information in the callback function ?

Matt Welke Over a year ago

It's just the way Node.js works. Sync and async will have the same end result in terms of your program achieving its goals. But they will use the underlying hardware differently. Async enables concurrency. See stackoverflow.com/questions/27659116/node-js-sync-vs-async for more info.

Collectives™ on Stack Overflow

How to query mySql table with node js

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related