Export MySQL connection for use with other modules in Node/Express using promise-mysql?

Question

Attempting to import and use a database module in one of my controllers. The initial connection is logged, however, I'm receiving the following error when hitting any of my routes from the browser:

"Cannot read property 'query' of undefined"

The connection variable in the database.js file is obviously not being set, but for the life of me I cannot figure out why.

database.js

const mysql = require("promise-mysql");
const config = require("../config");

let connection;

mysql.createConnection(config.MYSQL)
.then(conn => {
  connection = conn
  console.log('Connected to ', config.MYSQL.database)
})
.catch(function (error) {
  console.log(error)
})

module.exports = connection;

schools.js

const router = require('express').Router()
const Schools = require('../controllers/schools-controller')

const schools = new Schools

router.get('/', schools.getAllSchools)
...

module.exports = router

schools-controller.js

const db = require("../lib/database");


module.exports = class Schools {

 async getAllSchools (req, res, next) {

  const queryString = 'SELECT * FROM schools ORDER BY school_id ASC LIMIT 5'

  try {
    const results = await db.query(queryString);
    if (results == "") {
     res.status(404).json({ message: "No schools found" });
    } else {
      res.status(200).json(results);
    }
  } catch(error) {
    next(error)
  }
 };
 ...

}

Below is the pool function I ended up using based on the answer from @Sven

database.js

const pool = mysql.createPool(config.MYSQL);
pool.getConnection()
  .then(function(connection) {
    console.log(`Connected to database: ${config.MYSQL.database}`);
    pool.releaseConnection(connection)
  })
  .catch(function(error) {
    console.error(error.message);
  });

module.exports = pool;

Sven · Accepted Answer · 2018-11-04 20:36:07Z

4

You can't export a value that is acquired asynchronously like that. Just like everywhere else in Node, you have to use a callback, a promise or an async function. However, since this is for a MySQL connection, you should use a connection pool.

In your database.js-file:

const mysql = require("promise-mysql");
const config = require("../config");

module.exports = mysql.createPool(config.MYSQL);

You need no code changes at all in the rest of your code.

Alternatively, you can use mysql2 instead of promise-mysql (based on mysql) as it offers many welcome features, listed in their readme.

Your code would require very little changes, an npm i -S mysql2 or yarn add mysql2, followed by changing the line require("promise-mysql") to require("mysql2/promise") in your database.js-file. Possibly updating configuration in your config.js to match additional options you can use for the connection pool. Otherwise mysql2 is largely API-compatible with mysql.

answered Nov 4, 2018 at 20:36

Sven

5,27532 silver badges53 bronze badges

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

1 Comment

romeplow Over a year ago

I was able to get this working with a connection pool as you suggested, thanks.

Collectives™ on Stack Overflow

Export MySQL connection for use with other modules in Node/Express using promise-mysql?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related