Web Development

Last time, we created a mostly static Express JS Sample Application. In that sample, we worked with Jade, a really nice templating engine for node.

In this second installment, we are now creating an ExpressJS that interacts with MySQL.

Express and MySQL Sample Code

var express = require('express');
var path = require('path');
var mysql = require('mysql');
var app = express();

var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'root',
  password : 'password'
});

connection.query('USE test_database');

app.set('port', 3000);
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'jade');
app.use(express.static(path.join(__dirname, 'public')));

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

app.listen(app.get('port'));
console.log('Express server listening on port ' + app.get('port'));

Code Explanation

Now, just like our Sample ExpressJS Application and Tutorial, we will attempt to explain what this does albeit focusing on the database portions.

Including Modules

We start off by including express, path and the mysql module.

var express = require('express');
var path = require('path');
var mysql = require('mysql');
var app = express();

The path module is used later on for including directories in our application. It is an important to note that the path module does not verify anything in the filesystem. The path module mostly does parsing of strings.

The next we notice is the inclusion of the mysql module. It is what we will use to interact with the MySQL database.

Connecting to the Database

var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'root',
  password : 'password'
});

connection.query('USE test_database');

This would be very familiar if you use MySQL. A connection is created and stored in the connection variable. The next line would query the database and set which database we will be using.

Same Old Same Old

app.set('port', 3000);
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'jade');
app.use(express.static(path.join(__dirname, 'public')));

Most of this will be familiar if you followed my last post about ExpressJS, but let’s make a recap.

  • app.set('port', 3000); — we will be setting 3000 as the port that we will be using.
  • app.set('views', path.join(__dirname, 'views')); — views will be found in the views folder
  • app.set('view engine', 'jade'); — we will of course be using jade as are templating engine.
  • app.use(express.static(path.join(__dirname, 'public'))); — we will use the public folder as the container for assets.

Where the Magic Begins

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

On the / route, using the connection variable we query the table, users. SELECT * FROM users. Errors would be found at err and results would be found in rows. We then render and pass the rows variable to the view.

Looping through the results

html
  head
    title Welcome
  body
    #container
      - each user in users
        p=user.first_names

We loop through the users and and output the properties of each record.

Conclusion

ExpressJS with MySQL is not something that is usually found and thought in tutorials. You mostly find MongoDB used. I was just obliged to at least write a script or tutorial using ExpressJS and MySQL