javascript - Node MySQL - 可重用连接模块

Question

问题已更新:

<小时/>

该解决方案应详细说明 Node 连接模块的简化、正确模型，允许需要连接到数据库的 Node 应用程序的任何模块重复使用该连接。这样，这个问题对于任何在 Node.js 中遇到连接模块问题的人来说可能都是有用的。

答案甚至可能包括一种传递不同凭据的方法，以便通过从应用程序中的任何位置调用单个函数来连接到不同的表或数据库。

<小时/>

我编写了一个脚本，该脚本利用许多简单的模块来允许用户发布登录数据，在服务器上验证该数据，如果正确，则接收成功响应。一个非常基本的登录功能。

问题: 一个用户可以登录，但在重新启动服务器之前再尝试登录都无法连接到数据库。

看来，因为我在模块 db_connect 的变量中声明连接并需要该模块，所以无法重新使用该连接。它是在所需的模块中声明的，我错误地认为在每次连接尝试中调用该变量都会重新创建连接。事实并非如此。

解决方案: 正如 barry 在评论中所建议的，在 db_connect 模块中，我需要将连接功能设置为函数而不是变量，这样我就可以从我的内部创建连接验证脚本。

我该如何执行此操作？我尝试在调用 createConnection() 时输出连接对象function，它是db_connect的导出方法。

db_connect:

console.log('db_connect module initialized');
var mysql      = require('mysql');

function createConnection(){
    var connection = mysql.createConnection({
        host     : 'localhost',
        user     : 'root',
        password : '',
        database : 'officeball'
    });
}

exports.createConnection = createConnection();
exports.mysql = mysql;

验证器:

console.log('validator module initialized');
var connect = require("./db_connect");

function validate(username, password, callback){

    var createConnection = connect.createConnection();
    //the idea is for this to return the object, connection, 
    //which opens a new connection

    connection.connect(function (err){
        if (err) return callback(new Error('Failed to connect'), null);
        console.log('Connection with the Officeball MySQL database openned...');

        connection.query('select username,password,fname,lname,rank,active from users where username=?',
                username,
                function(err,rows,fields) {
                    connection.destroy();
                    console.log('...Connection with the Officeball MySQL database closed.');
                    if (err)
                        return callback(new Error ('Error while performing query'), null);
                    if (rows.length !== 1)
                        return callback(new Error ('- [Anomaly] - Failed to find exactly one user'), null);

                    if (rows[0].password === password & rows[0].active === "yes") {

                        var result = new Object;

                        result.username = rows[0].username;
                        result.password = rows[0].password;
                        result.fname = rows[0].fname;
                        result.lname = rows[0].lname;
                        result.rank = rows[0].rank;

                        return callback(null, result);

                    } if(rows[0].active !== "yes"){

                        return callback(new Error ('User account not active.'), null);

                    }else {

                        return callback(new Error ('Login credentials did not match.'), null);

                    }

                });


    });
};

exports.validate = validate;

控制台日志 (本来是连接错误，但经过我尝试修复后，错误是关于方法的)<强>:

C:\xampp\htdocs\officeball\node_scripts>node index.js
application initialized
server module initialized
login module initialized
validator module initialized
db_connect module initialized
sale module initialized
Server running at http://127.0.0.1:8080/
User username1 is attempting login...
TypeError: Property 'createConnection' of object #<Object> is not a function
    at Object.validate (C:\xampp\htdocs\officeball\node_scripts\custom_modules\v
alidator.js:6:33)
    at C:\xampp\htdocs\officeball\node_scripts\custom_modules\login.js:61:13
    at callbacks (C:\xampp\htdocs\officeball\node_scripts\node_modules\express\l
ib\router\index.js:164:37)
    at param (C:\xampp\htdocs\officeball\node_scripts\node_modules\express\lib\r
outer\index.js:138:11)
    at pass (C:\xampp\htdocs\officeball\node_scripts\node_modules\express\lib\ro
uter\index.js:145:5)
    at Router._dispatch (C:\xampp\htdocs\officeball\node_scripts\node_modules\ex
press\lib\router\index.js:173:5)
    at Object.router (C:\xampp\htdocs\officeball\node_scripts\node_modules\expre
ss\lib\router\index.js:33:10)
    at next (C:\xampp\htdocs\officeball\node_scripts\node_modules\express\node_m
odules\connect\lib\proto.js:193:15)
    at multipart (C:\xampp\htdocs\officeball\node_scripts\node_modules\express\n
ode_modules\connect\lib\middleware\multipart.js:93:27)
    at C:\xampp\htdocs\officeball\node_scripts\node_modules\express\node_modules
\connect\lib\middleware\bodyParser.js:64:9

Answer 1

您的问题主要是我在评论中提到的，所以我会回答它。不要拍打额头弄伤自己。 :-) 请参阅下面我的内嵌评论。您的一个方法调用次数太多，结果未返回，并且变量命名错误 - 否则，它工作得很好。

db_connect.js

console.log('db_connect module initialized');
var mysql      = require('mysql');

function createConnection(){
    var connection = mysql.createConnection({
        host     : 'localhost',
        user     : 'root',
        password : '',
        database : 'officeball'
    });
    // ************ NOTE BELOW FOR CHANGE **************
    // You didn't return anything from this function. You need to return the connection
    return connection;
}
// ************ NOTE BELOW FOR CHANGE **************
// You are exporting a single connection by invoking createConnection();
// exports.createConnection = createConnection();
// what you want is:
exports.createConnection = createConnection;
exports.mysql = mysql;

validator.js

function validate(username, password, callback){
    // ************ NOTE BELOW FOR CHANGE **************
    // You had:
    // var createConnection = connect.createConnection();
    // but based on your code, you wanted to write this instead:
    var connection = connect.createConnection();

    /// ... REMAINDER OMITTED, because it was A-OK and this is already a long page
};

如果您进行了 两个 三个更改，那么您应该可以开始了。与以往一样，如果有帮助，请随时要求任何澄清。

更新这就是我对它的称呼 - 正如你所看到的，我让它每 2 秒尝试执行一次。

jt-test.js

var v = require('./validator');
var timers = require('timers');
var connections = 0;

timers.setInterval(function(){
    v.validate('bagehot','foo',function(err,result){
        if (err)
            console.log('failed', err);
        else
            console.log('success! ',result);
    });
},2000);

结果

Connection with the Officeball MySQL database openned...
...Connection with the Officeball MySQL database closed.
success!  { username: 'bagehot',
  password: 'foo',
  fname: 'walter',
  lname: 'bagehot',
  rank: 12 }
Connection with the Officeball MySQL database openned...
...Connection with the Officeball MySQL database closed.
success!  { username: 'bagehot',
  password: 'foo',
  fname: 'walter',
  lname: 'bagehot',
  rank: 12 }
Connection with the Officeball MySQL database openned...
...Connection with the Officeball MySQL database closed.
success!  { username: 'bagehot',
  password: 'foo',
  fname: 'walter',
  lname: 'bagehot',
  rank: 12 }
Connection with the Officeball MySQL database openned...
...Connection with the Officeball MySQL database closed.
success!  { username: 'bagehot',
  password: 'foo',
  fname: 'walter',
  lname: 'bagehot',
  rank: 12 }

等等。它无限期地运行。这三个文件的完整代码位于 this gist