Where should I do database queries and why am I not getting the results of the queries?

I'm new to node.js and the express framework.

Can anyone tell me if I'm doing this correctly?

I created a database.js as a module, and the code contains:

var mysql = require('mysql'),
dateFormat = require('dateformat'),
db = require('./dashboard');

var connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '',
  database: 'selenium',
  timezone: '-07:00'
});

exports.selectalldate = function() {
  connection.query('SELECT * FROM date', function (err, rows, fields) {
    if (err) {
      console.log(err);
    }
    if(rows.length > 0) {
      for(i = 0; i < rows.length; i ++) {
        rows[i].date = dateFormat(rows[i].date, "yyyy-mm-dd")
      }
      return rows;
    } else {
      return false;
    }
  });
}

I required it in my app.js, and when I call the selectalldate() to get all the dates from the database in app.js and see what's the results. I get undefined. What am I doing wrong here?

var express = require('express')
  , routes = require('./routes')
  , user = require('./routes/user')
  , http = require('http')
  , path = require('path')
  , fs = require('fs')
  , file = __dirname + '/test2'
  , get = require('./routes/get')
  , db = require('./routes/database')
  ;

app.get('/dashboard', function(req, res) {
  var datee = db.selectalldate();
  console.log(datee);
  res.render('dashboard', {title: 'Selenium Dashboard', date: datee});
});

That's because the asynchronous nature of Node. Everything that has networking involved (DB queries, webservices, etc) is async.

Because of this, you should refactor your selectalldate() method to accept an callback. From that callback you'll be able to render your template successfully with the data fetched from the DB.

In the end, it'll be something like this:

exports.selectalldate = function(callback) {
  connection.query('SELECT * FROM date', function (err, rows, fields) {
    if(rows.length > 0) {
      for(i = 0; i < rows.length; i ++) {
        rows[i].date = dateFormat(rows[i].date, "yyyy-mm-dd")
      }
    }

    callback(err, rows);
  });
}

app.get('/dashboard', function(req, res) {
  db.selectalldate(function(err, datee) {
    if (err) {
      // Handle the error in some way!
      return;
    }

    res.render('dashboard', {title: 'Selenium Dashboard', date: datee});
  });
});