Node and Express

Node allows you to start servers written in JavaScript. For executing the JavaScript it uses Chrome’s V8 JavaScript engine.

Express is a framework that is based on Node and allows you to easily implement an API. You can define a API using the routing features of Express. Routing is Expresses term for the definition of how a server answers web requests.

Generating a project skeleton

Express has a tool that initializes a basic project structure. First install then use that tool:

npm install express-generator -g
express --view=pug <Application Name>
cd <Application Name>
npm install

Then you can start the server.

DEBUG=<Application Name>:* npm start

You can visit http://localhost:3000/ to see the servers routing for a GET to the root URL.

Adding a router

A router defines the servers reaction to calls to a URL. A router is registered or bound to a URL in the app.js file

var todosRouter = require('./routes/todos');
app.use('/todos', todosRouter);

In order to add the router itself, create a new file in the routes folder called todos.js for the todosRouter.

var express = require('express');
var router = express.Router();

router.get('/', function (req, res, next) {
  res.setHeader('Content-Type', 'application/json');
  res.send(JSON.stringify({ a: 1 }));
});

module.exports = router;

Note that this router never refers to the /todos URL! Instead it defines a routing for GET requests to / (root). There is one important thing to understand here. All URLs inside a router definition are relative.

A router does not care which URL it is bound to, instead it only defines relative URLs inside its own local context. The absolute URL of a router is constructed from appending the local URLs to the URL that the router is currently bound to.

In the context of the current example, this means that the root of the todosRouter router actually resolves to the absolute URL http://localhost:3000/todos/, because the router was bound to http://localhost:3000/todos.

Connect to An SQL Database

Install the node sql driver

npm install mssql -s

Connect to the Database in a Router (MySQL as an example)

Create a new schema:

CREATE SCHEMA `cash` ;

Create a table in the schema:

CREATE TABLE `cash`.`account_entry` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `amount` INT NULL COMMENT 'Amount in least significant currency units of the currency of the entries account. E.g. Cent for EUR if the account has EUR set as a currency.',
  PRIMARY KEY (`id`));

Define a connection settings object in one of the router files of the express project and use that connection settings object to connect to the schema using a user on the database server.

var express = require('express');
const sql = require('mssql');
var router = express.Router();

const sqlConfig = {
    user: 'root',
    password: 'test',
    server: '127.0.0.1:3306',
    database: 'cash'
}

/* GET home page. */
router.get('/', function (req, res, next) {

    const connection = sql.connect(sqlConfig, (err) => {
        if (err) {
            console.log('error');
            res.send('error');
        } else {
            res.send('DB connected');
        }
    });

    //res.render('index', { title: 'Express' });

});

module.exports = router;

Using an ORM Mapper (Sequelize)

An ORM mapper abstracts away all SQL interface code for translating between your programming language of choice and the SQL server. Instead of dealing with SQL queries, the ORM mapper lets you store and load data using objects used in your application.

When I started writing applications that used SQL databases as datastores, I wanted to write all the SQL code myself and I did not familiarize myself with ORM mappers at all. It is fun to write your own DAO and DTO class hierarchy to map from and to the SQL database but only for the first handfull of classes. As projects grow and more requirements and features and therefore objects to manage get added to the project, you quickly find yourself in a situation where you replicate your more or less dumb boilerplate code over and over for every small little insignificant new object. In reality the only thing you should care about is writing code for business logic. You should not spend time on SQL storage code if ever possible.

Another scenario is that of a high performance application. If you are required to squeeze out the last millisecond of your application, then maybe manually use SQL. In all other cases, I will recommend at least looking into the idea of using an ORM mapper. It will pay off quickly.

I really do not know which ORM mapper is suited best for your needs but as an example, lets use Sequelize. Sequelize internally connects to the SQL server, so it is possible to remove the SQL driver dependency from the example above.

Add sequelize to the project

npm install sequelize

As MySQL is beeing used in this example, install the MySQL dependencies.

npm install --save pg pg-hstore
npm install --save mysql
npm install --save mysql2

Add code to a router that inserts a new row into a table and returns the inserted element.

var express = require('express');
var Sequelize = require('sequelize');
var router = express.Router();

const sqlConfig = {
    user: 'root',
    password: 'test',
    server: '127.0.0.1:3306',
    database: 'cash'
}

var sequelize = new Sequelize(sqlConfig.database, sqlConfig.user, sqlConfig.password, {
    host: 'localhost',
    port: 3306,
    dialect: 'mysql',

    pool: {
        max: 5,
        min: 0,
        idle: 10000
    }
});

var account_entry = sequelize.define('account_entry', {
    'id': {
        type: Sequelize.INTEGER(11),
        allowNull: false,
        primaryKey: true,
        autoIncrement: true
    },
    'amount': { type: Sequelize.INTEGER }
});

autoIncrement: true,

    /* GET home page. */
    router.get('/', function (req, res, next) {

        sequelize.sync().then(function () {
            return account_entry.create({
                amount: 123
            });
        }).then(function (new_account_entry) {

            const msg = new_account_entry.get({
                plain: true
            });
            console.log(msg);
            res.send(msg);
        });

    });

module.exports = router;

Add the router to the application configuration (app.js)

var createError = require('http-errors');
var express = require('express');
var path = require('path');
var cookieParser = require('cookie-parser');
var logger = require('morgan');

var indexRouter = require('./routes/index');
var usersRouter = require('./routes/users');
var accountEntriesRouter = require('./routes/account_entries');

var app = express();

// view engine setup
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'pug');

app.use(logger('dev'));
app.use(express.json());
app.use(express.urlencoded({ extended: false }));
app.use(cookieParser());
app.use(express.static(path.join(__dirname, 'public')));

app.use('/', indexRouter);
app.use('/users', usersRouter);
app.use('/account_entries', accountEntriesRouter);

// catch 404 and forward to error handler
app.use(function (req, res, next) {
    next(createError(404));
});

// error handler
app.use(function (err, req, res, next) {
    // set locals, only providing error in development
    res.locals.message = err.message;
    res.locals.error = req.app.get('env') === 'development' ? err : {};

    // render the error page
    res.status(err.status || 500);
    res.render('error');
});

module.exports = app;

Delete the table account_entry table created manually earlier. The ORM mapper will automatically generate a table creation SQL statement from the type definition and execute that statement agains the MySQL server.

When you call http://localhost:3000/account_entries the table is created in the MySQL schema as it did not exist already and an account_entry row is inserted into the database. All that is possible without writing a single line of SQL thanks to the ORM mapper which intersects your request to store an object and does all the required SQL boilerplate for you!