如何正确地通过mysql连接到具有express.js的路由 [英] How to properly pass mysql connection to routes with express.js

问题描述

我试图找出在我的express.js路由之间传递mysql连接（使用node-mysql）的最佳方式。我动态添加每个路由（在路由中为每个文件循环使用一个），这意味着我不能仅仅将连接传递给需要它的路由。我要么把它传递给每一条路线，要么没有。我不喜欢将它传递给那些不需要它的想法，所以我创建了一个dbConnection.js，路由可以单独导入，如果他们需要。问题是我不认为我正在做这件事。到目前为止，我的dbConnection.js包含：

  var mysql = require（'mysql'）; 
 var db = null; 
 module.exports = function（）{
 if（！db）{
 db = mysql.createConnection（{
 socketPath：'/tmp/mysql.sock'，
用户：'*********'，
密码：'*********'，
数据库：'********* *'
}）; 
} 
 return db; 
}; 
  

我使用以下方式将其导入每个路线：

  var db = require（'../ dbConnection.js'）; 
 var connection = new db（）; 
  

但是我想这样做：

  var connection = require（'../ dbConnection.js'）; 
  

然而，当我尝试这样，我收到一个错误，说连接没有方法'查询'当我尝试查询时。

解决方案

我发现使用node-mysql的池对象更可靠。这是我如何设置我的。我使用环境变量来获取数据库信息。

database.js

  var mysql = require（'mysql'）; 
 
 var pool = mysql.createPool（{
 host：process.env.MYSQL_HOST，
 user：process.env.MYSQL_USER，
 password：process.env。 MYSQL_PASS，
数据库：process.env.MYSQL_DB，
 connectionLimit：10，
 supportBigNumbers：true 
}）; 
 
 //从城市获取记录
 exports.getRecords = function（city，callback）{
 var sql =SELECT name FROM users WHERE city =？; 
 //从池中获取一个连接
 pool.getConnection（function（err，connection）{
 if（err）{console.log（err）; callback（true）; return; } 
 //使查询
 connection.query（sql，[city]，function（err，results）{
 connection.release（）; 
 if（err）{ console.log（err）; callback（true）; return;} 
 callback（false，results）; 
}）; 
}）; 
}; 
  

路线

  var db = require（'../ database'）; 
 
 exports.GET = function（req，res）{
 db.getRecords（San Francisco，function（err，results）{
 if（err）{res。发送（500，服务器错误）; return; 
 //响应结果为JSON 
 res.send（results）; 
}）; 
}; 
  

I am trying to figure out the best way to pass a mysql connection (using node-mysql) between my routes for express.js. I am dynamically adding each route (using a for each file loop in routes), meaning I can't just pass in the connection to routes that need it. I either need to pass it to every route or none at all. I didn't like the idea of passing it to ones that dont need it so I created a dbConnection.js that the routes can individually import if they need. The problem is that I dont think I am doing it correctly. As of now, my dbConnection.js contains:

var mysql = require('mysql');
var db = null;
module.exports = function () {
    if(!db) {
            db = mysql.createConnection({
                socketPath: '/tmp/mysql.sock',
            user: '*********',
            password: '*********',
            database: '**********'
        });
    }
    return db;
};

And I am importing it into each route using:

var db = require('../dbConnection.js');
var connection = new db();

But I would like to do it like this:

var connection = require('../dbConnection.js');

When I try it like this, however, I get an error saying connection has no method 'query' when I try to make a query.

解决方案

I find it more reliable to use node-mysql's pool object. Here's how I set mine up. I use environment variable for database information. Keeps it out of the repo.

database.js

var mysql = require('mysql');

var pool = mysql.createPool({
  host: process.env.MYSQL_HOST,
  user: process.env.MYSQL_USER,
  password: process.env.MYSQL_PASS,
  database: process.env.MYSQL_DB,
  connectionLimit: 10,
  supportBigNumbers: true
});

// Get records from a city
exports.getRecords = function(city, callback) {
  var sql = "SELECT name FROM users WHERE city=?";
  // get a connection from the pool
  pool.getConnection(function(err, connection) {
    if(err) { console.log(err); callback(true); return; }
    // make the query
    connection.query(sql, [city], function(err, results) {
      connection.release();
      if(err) { console.log(err); callback(true); return; }
      callback(false, results);
    });
  });
};

Route

var db = require('../database');

exports.GET = function(req, res) {
  db.getRecords("San Francisco", function(err, results) {
    if(err) { res.send(500,"Server Error"); return;
    // Respond with results as JSON
    res.send(results);
  });
};

这篇关于如何正确地通过mysql连接到具有express.js的路由的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！