7
\$\begingroup\$

Here are the app.js, users.js and user.js routes of a node application I am building. As you can see I am connecting to the SQL DB in each route, and this is not ideal. I have tried passing the connection to different routes at least 3 different ways based on examples online. I have failed multiple times.

Please advise as to the best way to pass this connection. I am sure there are other errors in my code, so anywhere I can improve I would appreciate feedback. It is currently working, kinda. You can sign up login with auth and submit a bid working version here. But the server times out and the app crashes a lot.

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 exphbs = require('express-handlebars');
var expressValidator = require('express-validator');
var flash = require('connect-flash');
var session = require('express-session');
var passport = require('passport');
var LocalStrategy = require('passport-local'),Strategy;
var mongo = require('mongodb');
async = require('async');
SchemaObject = require('node-schema-object');
var app = express();

// This should make our db available in all views.. 
/*  wrapped our database object into every request .*/
app.use(function(req,res,next){
    //req.conn = conn;
    req.app = app;
    //req.db = db;
    // handle_database(req,res);
    next();
});

var routes = require('./routes/index');
var users = require('./routes/users');
var classes = require('./routes/classes');
var bids = require('./routes/bids');
//var students = require('./routes/students');
var clients = require('./routes/clients');
//var reviews = require('./routes/reviews');
var instructors = require('./routes/instructors');
var companys = require('./routes/companys');
//var instructors = require('./routes/instructors');

// view engine setup
app.set('views', path.join(__dirname, 'views'));
app.engine('handlebars', exphbs({defaultLayout: 'layout'}));
app.set('view engine', 'handlebars');

// uncomment after placing your favicon in /public
//app.use(favicon(__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')));

// Express Session
app.use(session({
    secret: 'secret',
    saveUninitialized: true,
    resave: true
}));

// Passport
app.use(passport.initialize());
app.use(passport.session());

// Express Validator
app.use(expressValidator({
  errorFormatter: function(param, msg, value) {
      var namespace = param.split('.')
      , root    = namespace.shift()
      , formParam = root;

    while(namespace.length) {
      formParam += '[' + namespace.shift() + ']';
    }
    return {
      param : formParam,
      msg   : msg,
      value : value
    };
  }
}));

// Connect-Flash
app.use(flash());

// Global Vars
app.use(function (req, res, next) {
  res.locals.messages = require('express-messages')(req, res);

  if(req.url == '/'){
    res.locals.isHome = true;
  }
  next();
});





// Makes the user object global in all views
app.get('*', function(req, res, next) {
  app.use(flash());
  // put user into res.locals for easy access from templates
  res.locals.user = req.user || null;

    if(req.user){
      res.locals.type = req.user.type;


    if(req.user.type == 'company'){
      res.locals.isCompany = true;
    } 

    if(req.user.type == 'client'){
      res.locals.isClient = true;
    } 
  }
  next();
});

app.use('/', routes);
app.use('/users', users);
app.use('/classes', classes);
app.use('/bids', bids);
//app.use('/students', students);
app.use('/clients', clients);
//app.use('/reviews', reviews);
app.use('/instructors', instructors);
app.use('/companys', companys);

// 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;

users.js route

var express = require('express');
var router = express.Router();
var passport = require('passport');
var LocalStrategy = require('passport-local').Strategy;
var db = require('../database');
var returnedPw;
var flash = require('connect-flash');
var session = require('express-session');


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


// Include User Model
var User = require('../models/user');
// Include Student Model
var Client = require('../models/client');

// Include Instructor Model
var Company = require('../models/company');

router.get('/signup', function(req, res, next) {
  res.render('users/signup');
});

router.post('/signup', function(req, res, next){
    // Get Form Values
    console.log('starting post and making new user');

    var first_name      = req.body.first_name;
    var last_name       = req.body.last_name;
    var street_address  = req.body.street_address;
    var city            = req.body.city;
    var state           = req.body.state;
    var zip             = req.body.zip;
    var email           = req.body.email;
    var username        = req.body.username;
    var password        = req.body.password;
    var password2       = req.body.password2;
    var type            = req.body.type;

    // Form Field Validation
    req.checkBody('first_name', 'First name field is required').notEmpty();
    req.checkBody('last_name', 'Last name field is required').notEmpty();
    req.checkBody('email', 'Email field is required').notEmpty();
    req.checkBody('email', 'Email must be a valid email address').isEmail();
    req.checkBody('username', 'Username field is required').notEmpty();
    req.checkBody('password', 'Password field is required').notEmpty();
    req.checkBody('password2', 'Passwords do not match').equals(req.body.password);

    var errors = req.validationErrors();

    if(errors){
        res.render('users/signup', {
            errors: errors,
            first_name: first_name,
            last_name: last_name,
            street_address: street_address,
            city: city,
            state: state,
            zip: zip,
            email: email,
            username: username,
            password: password,
            password2: password2
        });
    } else {
        var newUser = new User({
            email: email,
            username:username,
            password: password,
            type: type
        });
        console.log('calling post to database file to receive new user:' + newUser)

        // THIS IS WHERE WE ARE POSTING THE NEW USER TO THE DATABASE!!!
        //db.registerUsers(newUser);

        User.saveUser(newUser);

        var newClient = new Client({
            first_name: first_name,
            last_name: last_name,
            address: [{
                street_address: street_address,
                city: city,
                state: state,
                zip: zip
            }],
            email: email,
            username:username
        });
        if(type == 'client'){
            //User.saveClient(newUser, newClient, function(err, user){
            //  console.log('Client created');
            ///}); works but replacing w sql
        } else {

        var newCompany = new Company({
            first_name: first_name,
            last_name: last_name,
            address: [{
                street_address: street_address,
                city: city,
                state: state,
                zip: zip
            }],
            email: email,
            username:username
        });  

            console.log('calling sql save..');
            //db.postUsers(newUser, newClient);

        }

        req.flash('success','User added');
        res.redirect('/');
    }
});


<!--//// -USER AUTH SECTION- \\\\--!><!--//// -USER AUTH SECTION- \\\\--!><!--//// -USER AUTH SECTION- \\\\--!>

passport.serializeUser(function(user, done) {
  done(null, user);
});

passport.deserializeUser(function(user, done) {
  done(null, user);
});


router.post('/login',passport.authenticate('local',{failureRedirect:'/', failureFlash:'Wrong Username or Password'}), function(req, res){
    req.flash('success','You are now logged in');
    // it appears that the pipes re crossed and the type is being set as the username. need to check our model and post
    var usertype = req.user.type;
   // res.redirect('/'/*+usertype+'s/classes' */);
    console.log(user + 'hail Mary');
    res.redirect('/' /*, { "results": user } */ );
});

passport.use(new LocalStrategy(
    function(username, password, done ) {
            console.log('in users the username is ' + username)
            connection.query('SELECT password FROM t_user WHERE username = ?', username, function(err, user) {
                console.log(user +'got user')
                parseResults(user, done);
            });// end query

        function parseResults(user, done) {   
            Object.keys(user)[0];
            var key = Object.keys(user)[0];
            user[key]; 
            var storedPw = user[key];
            for(var i in storedPw){
                returnedPw = storedPw[i];
            }

            console.log('returnedPw is defined here ' +returnedPw);
            movePw(returnedPw,  done);
        }// end function

        var candidatePassword = password;

        function movePw (returnedPw, done) {
            if (returnedPw ) {
                User.comparePassword(candidatePassword, returnedPw, function(err, isMatch) {
                    if (err) return done(err);
                    if(isMatch) {
                       user = username;
                       return done(null, user);

                    } else {
                        console.log('Invalid Password');
                        // Success Message
                        req.flash('failureFlash','User Access Denied. False Password');
                        return done(null, false, { message: 'Invalid password' });
                    }
                });  
            }
            else {console.log('return PW not defined')}
        }

    }// end outer function ?

));//end passport

// Log User Out
router.get('/logout', function(req, res){
    req.logout();
    // Success Message
    req.flash('success', "You have logged out");
    res.redirect('/');
});

function ensureAuthenticated(req, res, next) {
  if (req.isAuthenticated()) { 
      return next(); 
    }
  res.redirect('/')
}

module.exports = router;

User.js

var SchemaObject = require('node-schema-object');
var db = require('../database');
var connection  = require('express-myconnection');
var mysql = require('mysql');
var bcrypt = require('bcryptjs');

var mysql      = require('mysql');
var connection = mysql.createConnection({
    host : 'sm',
    user : 'an',
    password : '',
    database : 't'
});

// User Schema
var user = new SchemaObject({
    email: {
        type: String
    },
    username: {
        type: String
    },
    password:{
        type:String,
        bcrypt: true
    },
    type:{
        type:String
    }
});

var User = module.exports = user;

// Fetch All Classes
module.exports.getUserById = function(id, callback){
    User.findById(id, callback);
}

// #1 IN THE LOGIN PROCESS THIS IS FIRST FUNCTIO CALLED
module.exports.getUserByUsername = function(username){
    console.log('in our user model, attempting to fetch user...')

    console.log('connected as id ' + connection.threadId);
    console.log('returning username function...')
    console.log('making sure we got the username ' + username)
    var query = {username: username};
    connection.query('SELECT ? FROM t_user',query, function(err, results, query) {
        if (err) throw err;
        if(!err) {
            console.log('um ok, heres your query results i guess.. ' + results)
            indexPage.receiveResults(results);
        }
    });
};

// Recieve Results
exports.useReceiveResults = function (results) {
        res.render('index', { "results": results });
};

// Save Client
module.exports.saveClient = function(newUser, newClient, callback){
    bcrypt.hash(newUser.password, 10, function(err, hash){
        if(err) throw errl
        // Set hash
        newUser.password = hash;
        console.log('Client is being saved looking for db');
        async.parallel([newUser.save, newClient.save], callback);
        return;
    });
}

// Save User
module.exports.saveUser = function(newUser, callback){
    bcrypt.hash(newUser.password, 10, function(err, hash){
        if(err) throw errl
        // Set hash
        newUser.password = hash;
        console.log('Client is being saved looking for db');
        //async.parallel([newUser.save, newClient.save], callback);

        connection.query('INSERT INTO t_user SET ?',newUser, function(err, results, newUser) {
        //connection.release();
            if(err) console.log(err + 'darnit');
            if(!err) console.log('data recieved by database...');
        });
        return;
    });
}


// Save Company
module.exports.saveCompany= function(newUser, newCompany, callback){
    bcrypt.hash(newUser.password, 10, function(err, hash){
        if(err) throw errl
        // Set hash
        newUser.password = hash;
        console.log('Company is being saved');
        async.parallel([newUser.save, newCompany.save], callback);
    });
}

// Compare password   
module.exports.comparePassword = function(candidatePassword, returnedPw, callback){
    bcrypt.compare(candidatePassword, returnedPw, function(err, isMatch){
        if(err) throw err;
        callback(null, isMatch);
    });
}
\$\endgroup\$
12
\$\begingroup\$

You can create a file

db.js

var mysql = require('mysql');
var settings = require('./settings.json');
var db;

function connectDatabase() {
    if (!db) {
        db = mysql.createConnection(settings);

        db.connect(function(err){
            if(!err) {
                console.log('Database is connected!');
            } else {
                console.log('Error connecting database!');
            }
        });
    }
    return db;
}

module.exports = connectDatabase();`

This will create a singleton db connection; Also as you can see, you could have your mysql settings in another file

settings.json

{
    host : 'sm',
    user : 'an',
    password : '',
    database : 't'
}

That way you have everything more organized.

Then on each of your files where you want to make a query you just have to require the db.js file

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

and with that var call the query function. For instance in your User.js file, the query to get the user by name would be something like:

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

db.query('SELECT ? FROM t_user',query, function(err, results, query) {
    if (err) throw err;
    if(!err) {
        indexPage.receiveResults(results);
    }
});
\$\endgroup\$
  • 1
    \$\begingroup\$ The overall pattern is solid, but I'd use connection pooling instead of a single connection (github.com/felixge/node-mysql#pooling-connections). As a small side note, I've also found that it's more flexible to create simple JS files for config than JSON files (which lack comments and are not dynamic) \$\endgroup\$ – Max Feb 21 '16 at 5:07
  • 1
    \$\begingroup\$ I have been working on this app using these patterns. github is up and tutorial on my website www.seanquinndesign.com/blog/index.html \$\endgroup\$ – SQUALLIE Feb 29 '16 at 2:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.