Express routing with a login action using SQLite

I'm new to Express and SQL, so I don't know the conventional ways of combining the two. Right now I have done it the following way:

app.get('/login', function (req, res) {
    res.render('login');
});

app.get('/home', function (req, res) {
    res.render('home');
});

app.post('/login', function (req, res) {
    db = new sqlite3.Database(file);
    db.serialize(function () {
        [...]
        db.all(query, function (err, rows) {
            if(rows.length == 1) {
                [...]
                res.render('home', {
                    username: rows[0].username
                });
            }
            else {
                res.render('login', {
                    message: "Login not successful!"
                });
            }
        });
    });
    db.close();
});

However, I feel like the routing should be separated from the database stuff. What should I do different? Or is this normal?

For larger projects this can get very complicated and nasty. You can refactor the db access into functions that take a callback as a parameter. Example:

// db_helper.js
module.exports.isUserAuthenticated = function(query, callback) {
  [db code here]
  callback(err, rows);
};

And then in your routes file:

// app.js
var db_helper = require('./path/to/db_helper.js');

....

app.post('/login', function(req, res) {
  var query = [parse from request object]
  db_helper.isUserAuthenticated(query, function(err, rows) {
    if (rows.length == 1) {
      [...]
      res.render(...);
    } else {
      [...]
      res.render(...);
    }
  });
});

Node-style callbacks typically have an error as the first parameter (null if there was no error), and associated data in other parameters.

Nesting callbacks can be a pain if this gets more complicated, so I suggest you look into Promises or async

StackExchange Code Review Q#160252, answer score: 2