How to import mysql connection in another file in nodejs?

Question

I am not able to import the mysql connection file from the parent file to another file for making a DB connection.

I can connect to DB if the query is written in same file, if i try to hit the same query in another file then i get "TypeError: connection.query is not a function" error. Not able to access any functions of the connection object.

this is my server.js file:

var express = require('express');
var cors = require('cors');
var mysql = require('mysql');
app = express(),
app.use(cors()),
port = process.env.PORT || 3001;
bodyParser = require('body-parser');
app.use(bodyParser.urlencoded({ extended: true }));
app.use(bodyParser.json());
config = {
    host: 'localhost',
    user: 'root',
    password: '',
    database: '<DB name>'
}
var connection = mysql.createConnection(config);

connection.connect(function(err){
    if (err){
        console.log('error connecting:' + err.stack);
    }
    console.log('connected successfully to DB.');
});


var routes = require('./api/routes/routes'); //importing route
routes(app);
console.log('todo list RESTful API server started on: ' + port);
app.listen(port);

module.exports= connection;

This is my api.js file:

'use strict';
var connection = require('../../server.js');

connection.query ('select * from <table name>', function(error, results){
    if (results){
        console.log(results);
    }
    else{
        console.log(error);
    }
});
or
exports.get=function(req,res){
        console.log(req.query.printerModel);
        console.log('inside get function');
        connection.query('select * from inkdetails', function(error, results){
            if (results){
                res.status(200).send(inkdetails)
                console.log(results);
            }
            else{
                res.status(400).send(error)
                console.log(error);
            }
        });
    };

Answer 1

create a config folder and create a file databaseConfig.js

var mysql = require('mysql');

config = {
   host: 'localhost',
   user: 'root',
   password: '',
   database: '<DB name>'
}
var connection =mysql.createConnection(config); //added the line
connection.connect(function(err){
  if (err){
    console.log('error connecting:' + err.stack);
  }
  console.log('connected successfully to DB.');
});

module.exports ={
     connection : mysql.createConnection(config) 
} 

app.js

var express = require('express');
var cors = require('cors');
app = express(),
app.use(cors()),
port = process.env.PORT || 3001;
bodyParser = require('body-parser');
app.use(bodyParser.urlencoded({ extended: true }));
app.use(bodyParser.json());
var routes = require('./api/routes/routes'); //importing route
routes(app);
console.log('todo list RESTful API server started on: ' + port);
app.listen(port);

controller.js

var config = require('../../databaseConfig.js');
var connection= config.connection
connection.query ('select * from <table name>', function(error, results){
   if (results){
     console.log(results);
   }
   else{
     console.log(error);
   }
});

Answer 2

Create a file which will handle connection and export required utility functions related to mysql and then you can initialize connection in your startup file and import that connection object in any handler.

your mysql lib:

const mysql = require('mysql');
const logger = require('your-looger-path');
const mysqlConfig = {
  host: 'localhost',
  user: 'root',
  password: '',
  database: '<DB name>'
}

var pool = mysql.createPool(mysqlConfig);

module.exports.connect = function (cb) {
  return new Promise((resolve, reject) => {
    pool.on('connection', function (connection) {
      connection.on('error', function (err) {
        logger.error('MySQL error event', err)
      });
      connection.on('close', function (err) {
        logger.warn('MySQL close event', err)
      });
    });
    resolve()
  })
}

async function executeQuery (query) {
  logger.debug(`query: `, query)
  return new Promise((resolve, reject) => {
    try{
      pool.query(query, (e, r, f) => {
        if(e){
          reject(e)
        }
        else{
          logger.debug(r,f)
          resolve(r[0])
        }
      });
    }
    catch(ex){
      reject(ex)
    }
  })  
}

async function execSP(spName, params){
  return new Promise((resolve, reject) => {
    try{
      var paramPlaceHolder = ''
      if(params && params.length){
        for(var i = 0; i < params.length; i++){
          paramPlaceHolder += '?,'
        }
      }
      if(paramPlaceHolder.length){
        paramPlaceHolder = paramPlaceHolder.slice(0, -1)
      }
      logger.debug('final SP call', `CALL ${spName}(${params})`)
      pool.query(`CALL ${spName}(${paramPlaceHolder})`, params, (e, r, f) => {
        if(e){
          reject(e)
        }
        else{
          resolve(r[0])
        }
      });
    }
    catch(ex){
      reject(ex)
    }
  })
}
module.exports.executeQuery = executeQuery
module.exports.execSP = execSP

and then create the connection in server.js file:

var express = require('express');
var cors = require('cors');
var mysqllib = require('require to your mysql wrapper created above');
app = express(),
app.use(cors()),
port = process.env.PORT || 3001;
bodyParser = require('body-parser');
app.use(bodyParser.urlencoded({ extended: true }));
app.use(bodyParser.json());

mysqllib.connect().then(() => {
  console.log('Connected to mysql...')
  var routes = require('./api/routes/routes'); //importing route
  routes(app);
  console.log('todo list RESTful API server started on: ' + port);
  app.listen(port);

}).catch(e => {
  console.error('Error connecting mysql...')
  process.exit()
})

In your controller

'use strict';
const mysqlLib = require('your mysql lib path')

exports.get = function(req, res){
  console.log(req.query.printerModel);
  console.log('inside get function');
  mysqlLib.executeQuery('select * from inkdetails').then((d) => {
    console.log(d);
    res.status(200).send(d)
  }).catch(e => {
    console.log(e);
    res.status(500).send('Sorry, something went wrong!')
  })
};

Hopes this help you.