Im having trouble connecting Node.js application with sql database. Error occurs is "req.getConnection is not a function". I am trying to get data from the database the moment I click on a certain page (eg.users).

My code is as below

app.js

var express = require('express');
var path = require('path');
var favicon = require('serve-favicon');
var logger = require('morgan');
var cookieParser = require('cookie-parser');
var bodyParser = require('body-parser');
var flash = require('express-flash');
var session = require('express-session');
var expressValidator = require('express-validator');
var methodOverride = require('method-override');

var connection  = require('express-myconnection');
var mysql = require('mysql');

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

// unment 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(session({secret:"mysecretpass"}));
app.use(flash());
app.use(expressValidator());
app.use(cookieParser());
app.use(require('less-middleware')(path.join(__dirname, 'public')));
app.use(express.static(path.join(__dirname, 'public')));

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

app.use(methodOverride(function(req, res){
 if (req.body && typeof req.body === 'object' && '_method' in req.body) 
  { 
      var method = req.body._method;
      delete req.body._method;
      return method;
    } 
  }));

/*-----------------------------------------
 * Connection peer, register as middleware
 * type koneksi:single,pool and request
 -----------------------------------------*/
app.use(
  connection(mysql,{
       host:'localhost',
       user:'root',
       password:'',
       port:3306,
       database:'stores'
   },'single')
   );


// 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
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: {}
  });
});
module.exports = app;

routes/index.js

var express = require('express');
var router = express.Router();

/* GET home page. */
router.get('/', function(req, res, next) {
  res.render('dashboard', { title: 'Dashboard' });
});

router.get('/admin_users', function(req, res, next) {
    req.getConnection(function(err,connection){
        var query = connection.query('SELECT * FROM users',function(err,rows)
        {
            if(err)
                var errornya  = ("Error Selecting : %s ",err );   
            req.flash('msg_error', errornya);   
            res.render('admin_users',{title:"Admin Users",data:rows});
        });
         //console.log(query.sql);
     });
});

module.exports = router;

Im having trouble connecting Node.js application with sql database. Error occurs is "req.getConnection is not a function". I am trying to get data from the database the moment I click on a certain page (eg.users).

My code is as below

app.js

var express = require('express');
var path = require('path');
var favicon = require('serve-favicon');
var logger = require('morgan');
var cookieParser = require('cookie-parser');
var bodyParser = require('body-parser');
var flash = require('express-flash');
var session = require('express-session');
var expressValidator = require('express-validator');
var methodOverride = require('method-override');

var connection  = require('express-myconnection');
var mysql = require('mysql');

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

// unment 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(session({secret:"mysecretpass"}));
app.use(flash());
app.use(expressValidator());
app.use(cookieParser());
app.use(require('less-middleware')(path.join(__dirname, 'public')));
app.use(express.static(path.join(__dirname, 'public')));

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

app.use(methodOverride(function(req, res){
 if (req.body && typeof req.body === 'object' && '_method' in req.body) 
  { 
      var method = req.body._method;
      delete req.body._method;
      return method;
    } 
  }));

/*-----------------------------------------
 * Connection peer, register as middleware
 * type koneksi:single,pool and request
 -----------------------------------------*/
app.use(
  connection(mysql,{
       host:'localhost',
       user:'root',
       password:'',
       port:3306,
       database:'stores'
   },'single')
   );


// 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
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: {}
  });
});
module.exports = app;

routes/index.js

var express = require('express');
var router = express.Router();

/* GET home page. */
router.get('/', function(req, res, next) {
  res.render('dashboard', { title: 'Dashboard' });
});

router.get('/admin_users', function(req, res, next) {
    req.getConnection(function(err,connection){
        var query = connection.query('SELECT * FROM users',function(err,rows)
        {
            if(err)
                var errornya  = ("Error Selecting : %s ",err );   
            req.flash('msg_error', errornya);   
            res.render('admin_users',{title:"Admin Users",data:rows});
        });
         //console.log(query.sql);
     });
});

module.exports = router;

• javascript
• node.js
• express

Share Improve this question Follow asked Feb 6, 2017 at 3:33 HelmiHelmi 3311 silver badge1010 bronze badges 0

Add a ment  | 

1 Answer 1

Sorted by: Reset to default 5

You are adding the express-myconnection middleware after your routes middleware. Middleware is executed in the order it is added, and as such the getConnection function is not defined on the req object until after your express-myconnection middleware is executed.

Simply move the connection middleware above the usage of routes, ie.

app.use(
  connection(mysql,{
       host:'localhost',
       user:'root',
       password:'',
       port:3306,
       database:'stores'
   },'single')
);

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