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

Does your code output ''todo list RESTful API server started on: 3001' before 'connected successfully to DB.'? — Alexander
– Alexander, Commented Mar 30, 2019 at 7:24
Can u log what is connection in api.js file? It seems connection object is not yet exported before you are registering your routes and handlers. — Suresh Prajapati
– Suresh Prajapati, Commented Mar 30, 2019 at 7:35

Suresh Prajapati · Accepted Answer · 2019-03-31 08:06:30Z

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.

@vikranth I have updated the answer to export other functions as well.
Thanks a lot for the edit, its working now. But i had a query: why wouldn't the exporting of connection function work??

Narendra Chouhan · Accepted Answer · 2019-04-01 05:12:55Z

4

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

edited Apr 1, 2019 at 5:12

answered Mar 30, 2019 at 9:57

Narendra Chouhan

2,3091 gold badge17 silver badges25 bronze badges

3 Comments

vikranth Over a year ago

@narenda: I tried your approach too, but in database.js file i am getting "ReferenceError: connection is not defined"

Narendra Chouhan Over a year ago

@vikranth missed the one line before the connection.connect, sry for that but now i have added the line and edited the code you can have a look, have a good day

Narendra Chouhan Over a year ago

your welcome @vikranth , you can mark the answer as correct if you feel that work for you so that other while find it easy

Collectives™ on Stack Overflow

How to import mysql connection in another file in nodejs?

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related