mysql - 如何在nodejs中的另一个文件中导入mysql连接？

Question

我无法将 mysql 连接文件从父文件导入到另一个文件以建立数据库连接。

如果查询写在同一个文件中，我可以连接到数据库，如果我尝试在另一个文件中命中相同的查询，那么我会收到“TypeError: connection.query is not a function”错误。无法访问连接对象的任何功能。

这是我的 server.js 文件:

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;

这是我的 api.js 文件:

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

Answer 1

创建一个文件来处理连接并导出与 mysql 相关的所需实用函数，然后您可以在启动文件中初始化连接并在任何处理程序中导入该连接对象。

你的 mysql 库:

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

然后在server.js文件中创建连接:

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

在你的 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!')
  })
};

希望对你有帮助。