如何在 nodejs 中的单个文件中提供 mysql 数据库连接

How to provide a mysql database connection in single file in nodejs

提问by user3836476

I need to provide the mysql connection for modules. I have a code like this.

我需要为模块提供 mysql 连接。我有一个这样的代码。

var express = require('express'),
app = express(),
server = require('http').createServer(app);

var mysql      = require('mysql');
var connection = mysql.createConnection({
    host     : '127.0.0.1',
    user     : 'root',
    password : '',
    database    : 'chat'
});

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

app.get('/save', function(req,res){
    var post  = {from:'me', to:'you', msg:'hi'};
    var query = connection.query('INSERT INTO messages SET ?', post, function(err, result) {
        if (err) throw err;
    });
});

server.listen(3000);

But how we provide one time mysql connection for all the modules.

但是我们如何为所有模块提供一次 mysql 连接。

回答by Sean3z

You could create a db wrapper then require it. node's require returns the same instance of a module every time, so you can perform your connection and return a handler. From the Node.js docs:

您可以创建一个 db 包装器然后需要它。node 的 require 每次都返回相同的模块实例，因此您可以执行连接并返回处理程序。来自Node.js 文档：

every call to require('foo') will get exactly the same object returned, if it would resolve to the same file.

每次调用 require('foo') 都会得到完全相同的对象返回，如果它解析为同一个文件。

You could create db.js:

你可以创建db.js：

var mysql = require('mysql');
var connection = mysql.createConnection({
    host     : '127.0.0.1',
    user     : 'root',
    password : '',
    database : 'chat'
});

connection.connect(function(err) {
    if (err) throw err;
});

module.exports = connection;

Then in your app.js, you would simply require it.

然后在您的app.js，您只需需要它。

var express = require('express');
var app = express();
var db = require('./db');

app.get('/save',function(req,res){
    var post  = {from:'me', to:'you', msg:'hi'};
    db.query('INSERT INTO messages SET ?', post, function(err, result) {
      if (err) throw err;
    });
});

server.listen(3000);

This approach allows you to abstract any connection details, wrap anything else you want to expose and require dbthroughout your application while maintaining one connection to your db thanks to how node require works :)

db由于 node require 的工作方式，这种方法允许您抽象任何连接细节，在整个应用程序中包装您想要公开和需要的任何其他内容，同时保持与数据库的一个连接:)

回答by Rafael

I took a similar approach as Sean3z but instead I have the connection closed everytime i make a query.

我采用了与 Sean3z 类似的方法，但每次进行查询时我都会关闭连接。

His way works if it's only executed on the entry point of your app, but let's say you have controllers that you want to do a var db = require('./db'). You can't because otherwise everytime you access that controller you will be creating a new connection.

如果它仅在您的应用程序的入口点上执行，那么他的方式有效，但假设您有控制器想要执行var db = require('./db'). 您不能这样做，否则每次访问该控制器时，您都会创建一个新连接。

To avoid that, i think it's safer, in my opinion, to open and close the connection everytime.

为了避免这种情况，我认为每次都打开和关闭连接更安全。

here is a snippet of my code.

这是我的代码片段。

mysq_query.js

mysq_query.js

// Dependencies
var mysql   = require('mysql'),
    config  = require("../config");

/*
 * @sqlConnection
 * Creates the connection, makes the query and close it to avoid concurrency conflicts.
 */
var sqlConnection = function sqlConnection(sql, values, next) {

    // It means that the values hasnt been passed
    if (arguments.length === 2) {
        next = values;
        values = null;
    }

    var connection = mysql.createConnection(config.db);
    connection.connect(function(err) {
        if (err !== null) {
            console.log("[MYSQL] Error connecting to mysql:" + err+'\n');
        }
    });

    connection.query(sql, values, function(err) {

        connection.end(); // close the connection

        if (err) {
            throw err;
        }

        // Execute the callback
        next.apply(this, arguments);
    });
}

module.exports = sqlConnection;

Than you can use it anywhere just doing like

比你可以在任何地方使用它就像

var mysql_query = require('path/to/your/mysql_query');
mysql_query('SELECT * from your_table where ?', {id: '1'}, function(err, rows)   {
    console.log(rows);
});

UPDATED:config.json looks like

更新：config.json 看起来像

{
        "db": {
        "user"     : "USERNAME",
        "password" : "PASSWORD",
        "database" : "DATABASE_NAME",
        "socketPath": "/tmp/mysql.sock"
    }
}

Hope this helps.

希望这可以帮助。

回答by Pankaj katiyar

try this

尝试这个

var express = require('express');

var mysql     =    require('mysql');

var path = require('path');
var favicon = require('serve-favicon');
var logger = require('morgan');
var cookieParser = require('cookie-parser');
var bodyParser = require('body-parser');

var routes = require('./routes/index');
var users = require('./routes/users');

var app = express();

// view engine setup
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'jade');

// uncomment after placing your favicon in /public
//app.use(favicon(path.join(__dirname, 'public', 'favicon.ico')));
app.use(logger('dev'));
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: false }));
app.use(cookieParser());
app.use(express.static(path.join(__dirname, 'public')));

app.use('/', routes);
app.use('/users', users);

// catch 404 and forward to error handler
app.use(function(req, res, next) {
  var err = new Error('Not Found');
  err.status = 404;
  next(err);
});

// error handlers

// development error handler
// will print stacktrace
console.log(app);
if (app.get('env') === 'development') {
  app.use(function(err, req, res, next) {
    res.status(err.status || 500);
    res.render('error', {
      message: err.message,
      error: err
    });
  });
}

// production error handler
// no stacktraces leaked to user
app.use(function(err, req, res, next) {
  res.status(err.status || 500);
  res.render('error', {
    message: err.message,
    error: {}
  });
});

var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "admin123",
  database: "sitepoint"
});

con.connect(function(err){
  if(err){
    console.log('Error connecting to Db');
    return;
  }
  console.log('Connection established');
});



module.exports = app;

回答by Towerss

From the node.js documentation, "To have a module execute code multiple times, export a function, and call that function", you could use node.js module.export and have a single file to manage the db connections.You can find more at Node.js documentation. Let's say db.js file be like:

从 node.js 文档中，“要让一个模块多次执行代码，导出一个函数，然后调用该函数”，您可以使用 node.js module.export 并有一个文件来管理数据库连接。您可以找到更多见Node.js 文档。假设 db.js 文件是这样的：

    const mysql = require('mysql');

    var connection;

    module.exports = {

    dbConnection: function () {

        connection = mysql.createConnection({
            host: "127.0.0.1",
            user: "Your_user",
            password: "Your_password",
            database: 'Your_bd'
        });
        connection.connect();
        return connection;
    }

    };

Then, the file where you are going to use the connection could be like useDb.js:

然后，您要使用连接的文件可能类似于 useDb.js：

const dbConnection = require('./db');

var connection;

function callDb() {

    try {

        connection = dbConnectionManager.dbConnection();

        connection.query('SELECT 1 + 1 AS solution', function (error, results, fields) {
            if (!error) {

                let response = "The solution is: " + results[0].solution;
                console.log(response);

            } else {
                console.log(error);
            }
        });
        connection.end();


    } catch (err) {
        console.log(err);
    }
}

回答by Surendra Kumar Laggisetty

var mysql = require('mysql');

var pool  = mysql.createPool({
    host     : 'yourip',
    port    : 'yourport',
    user     : 'dbusername',
    password : 'dbpwd',
    database : 'database schema name',
    dateStrings: true,
    multipleStatements: true
});


// TODO - if any pool issues need to try this link for connection management
// https://stackoverflow.com/questions/18496540/node-js-mysql-connection-pooling

module.exports = function(qry, qrytype, msg, callback) {

if(qrytype != 'S') {
    console.log(qry);
}

pool.getConnection(function(err, connection) {
    if(err) {
        if(connection)
            connection.release();
        throw err;
    } 

    // Use the connection
    connection.query(qry, function (err, results, fields) {
        connection.release();

        if(err) {
            callback('E#connection.query-Error occurred.#'+ err.sqlMessage);    
            return;         
        }

        if(qrytype==='S') {
            //for Select statement
            // setTimeout(function() {
                callback(results);      
            // }, 500);
        } else if(qrytype==='N'){
            let resarr = results[results.length-1];
            let newid= '';
            if(resarr.length)               
                newid = resarr[0]['@eid'];
            callback(msg + newid);
        } else if(qrytype==='U'){
            //let ret = 'I#' + entity + ' updated#Updated rows count: ' + results[1].changedRows;
            callback(msg);                      
        } else if(qrytype==='D'){
            //let resarr = results[1].affectedRows;
            callback(msg);
        }
    });

    connection.on('error', function (err) {
        connection.release();
        callback('E#connection.on-Error occurred.#'+ err.sqlMessage);   
        return;         
    });
});

}

}

回答by Thiago Bustamante

I think that you should use a connection pool instead of share a single connection. A connection pool would provide a much better performance, as you can check here.

我认为您应该使用连接池而不是共享单个连接。连接池会提供更好的性能，您可以在此处查看。

As stated in the library documentation, it occurs because the MySQL protocol is sequential (this means that you need multiple connections to execute queries in parallel).

正如库文档中所述，发生这种情况是因为 MySQL 协议是顺序的（这意味着您需要多个连接来并行执行查询）。

Connection Pool Docs

连接池文档