module export for mysql connection

Question

I've been playing with Node.js and i was writing some test applications with node-mysql. I am trying to write a module that automatically establishes connections with the database so that i don't have to write the code for connection again and again. Here is what i have written:

var mysql = require('mysql');

module.exports = function(mysql) {
  var client = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '12345'
  });
  return client;
}

But when i try to import this file into my another *.js file, i get an error:

~/work : $ node queryInfo.js
/Users/socomo22/work/queryInfo.js:3
client.connect();
       ^
TypeError: undefined is not a function
    at Object.<anonymous> (/Users/socomo22/work/queryInfo.js:3:8)
    at Module._compile (module.js:460:26)
    at Object.Module._extensions..js (module.js:478:10)
    at Module.load (module.js:355:32)
    at Function.Module._load (module.js:310:12)
    at Function.Module.runMain (module.js:501:10)
    at startup (node.js:129:16)
    at node.js:814:3

Since i'm new to Node.js i'm not sure what i'm doing wrong. Please help!

queryInfo.js // Code that requires above stated module

var client = require('./connectMysql');

client.query("USE node");

client.query("INSERT INTO test(content) VALUES(?)", ['the content'],
  function(err, info) {
    if(err)
      return handle_error(err);
    console.log(info.insertId);
});


client.query('UPDATE test SET content = ?', ['new content'], function(err, info) {
  if(err)
    return handle_error(err);
  console.log(info.insertId);
});

client.end();

Solution 1

Opening a database connection in node.js is asynchronous usually. I don't know about that module, but look for a connected event or callback from connecting to the db. Then you let your MySQL function take a callback function as a parameter. Here u pass the connection.

edit:

MySQL package handles asynchronous connection by temporary queuing the queries if the connection is not yet open. However, the recommended way to open a MySQL connection is this:

var connection = mysql.createConnection({
  host     : 'example.org',
  user     : 'bob',
  password : 'secret'
});

connection.connect(function(err) {
  if (err) {
    console.error('error connecting: ' + err.stack);
    return;
  }

  console.log('connected as id ' + connection.threadId);
});

The connection is open for real once the callback is executed. This is one of the core concepts of node.

To use this, import the connection module and have an interface like this:

var db = require("./connectMysql");
db.getConnection(function(err, connection) {
    // here u can run queries 
});

Then make sure the MySQL createClient is just run once, and save the connection for the next time some module wants to use it.

Solution 2

You can use this:

Config.js

var mysql      = require('mysql');
var config;
config = {
    mysql_pool : mysql.createPool({
        host     : 'hede',
        user     : 'hede',
        password : 'hede',
        database : 'hede'
    })
};
module.exports = config;

When you use this js. You can use like that;

var mysqlConf = require('config.js').mysql_pool;

    mysqlConf.getConnection(function (err, connection) {
        connection.query('{YOUR QUERY}' ,[{FIELDS}], function (err, rows) {
            connection.release();   //---> don't forget the connection release.
        });
    });

Solution 3

connectMySQL.js

const mysql = require('mysql');

const con = mysql.createConnection({
    host: "{YOUR_HOST}",
    user: "{YOUR_USER}",
    password: "{YOUR_PASSWORD}",
    database: "{YOUR_DATABASE_NAME}"
});

con.connect(function(err) {
    if (err) throw err;
    console.log("Connected!");
});

module.exports = con

index.js

const con = require('./connectMySQL');

con.query("{YOUR_QUERY}", function (err, result, fields) {
    if (err) throw err;
    console.log(result);
});

con.end();