Show database content in browser with node.js, MySQL and Jade

Question

I'm currently working with Node.js, Express.js and Jade. My database is MySQL. I'm new to node.js, so I thought I try something very easy: Displaying some data from the database in a table in the browser. Unfortunately it still doesn't work. I can display data on an free port but not where I need it - on port 3000. And I also can't work with the response itself. This is one of the "solutions" or ideas I had. Maybe there is a problem with the asynchronous call? I simply have no idea.

Here is my code:

routes.js

var express = require('express'); 
var controller = express.Router();
var dataModel2 = require('../models/rooms');

controller.get('/rooms', function(req, res, next) {
  var rooms = dataModel2();
  res.render('rooms', {
    items: rooms
  });
});

module.exports = controller;

models/rooms.js

var rooms;
var connection = require('./databaseConnection');
var http = require('http');

rooms = function() {
  http.createServer(function (request, response) 
  { 
    console.log('Creating the http server');
    connection.query('SELECT * FROM rooms', function(err, rows, fields)
    {
      response.writeHead(200, { 'Content-Type': 'application/json'});
      var room = response.end(JSON.stringify(rows));
      return room;
    }); 
   });

module.exports = rooms();

models/databaseConnection.js

var mysql = require('mysql');
module.exports = mysql.createConnection({
  host     : 'localhost',
  user     : 'root',
  password : '',
  database : 'raspi_key_royal'
});

rooms.jade

extends layout

block content
  div(id="bodyRoyal")
   table(border='1')
    thead
     tr
      th ID
      th Name
   tbody
    each item in items
     tr
      td=item.rid
      td=item.name

I splitted the functions a bit because there are some other sections like "persons" etc. I tried to insert console.logs in the rooms.js but that doesn't seem to work. I also thought I could save the response into a variable so that I can work with it somewhere else.

Thank you for every help and hints! Steffi

Solution

Something like this should do it:

var express = require('express'),
    app = express(),
    connection = require('./databaseConnection');

app.get('/rooms', function (req, res) {
    connection.query('SELECT * FROM rooms', function(err, rows, fields)
    {
        res.render('rooms', {
          items: rows
        });
    });
});

var server = app.listen(3000, function () {

  var host = server.address().address;
  var port = server.address().port;

  console.log('Example app listening at http://%s:%s', host, port);

});

Comments

[Stefanie Weber]

Thank you very much for the quick answer! The problem is that port 3000 is already in use - I was already able to put up a layout there and I wanted to include the table in one of the subpages (rooms.jade). So I don't really need to create a new port...I need to get the actual port I'm working on.

[michelem]

Change the port to 3001 or a free one.

[Stefanie Weber]

Sorry for the stupid question but how will I be ever able to display several data in the same port or one page? Or do I only need a new port to "save" the variable?

[michelem]

Your Node.js app (server) will listen for that port and only that, all the requests you need will be done through that port. Add as many routes app.get|post|put|delete as you want to do different calls. And I strongly suggest you read something expressjs.com/guide/routing.html

[Stefanie Weber]

Now it works! Thank you so much for your help! I just put in the connection part in between the app.get.