Sequelize adjustments – ORM in Express
As I stated in my previous posts ([1], [2]), I decided to use Sequelize as an ORM for my project. I don’t want to deal with the database connections and queries directly for as long as possible. I want to be able to switch from one database engine to another if needed. For now, SQLite is good enough, I can even use an in-memory database for testing purposes, but there is a chance that I will need something more robust in the future.
First steps
I started simple. In configuration file I simply placed few lines of SQLite based configuration:
config.db = {
database : 'null',
username : 'null',
password : 'null',
sequelizeParams : {
dialect : 'sqlite',
storage : './sqliteDB/tracker.sqlite'
}
}
Please remember to create sqliteDB directory in your project. I’m now ready to create the first table and insert the first row. Just as a test (and this can be useful in the future) I decided to create DB Version related table. I have chosen one of my already implemented routes, and I simply modified contents of the middleware to test database connection and basic operations. Let’s take a look at the code first:
var Sequelize = require('sequelize');
// ...
router.get('/dbtest', function(req, res, next) {
var cfg = req.app.get('config');
var con = new Sequelize(
cfg.db.database,
cfg.db.username,
cfg.db.password,
cfg.db.sequelizeParams);
var dbVersion = con.define('dbVersion', {
version: Sequelize.STRING
});
con.sync().then(function() {
dbVersion.create({
version: '2018040601'
})
}
);
res.render('dbtestView');
});
// ...
As you can see, I imported Sequelize, and I used config variables to initialize the connection. There are two important concepts introduced; the first one is here:
var dbVersion = con.define('dbVersion', {
version: Sequelize.STRING
});
The dbVersion variable holds the model of the database table named dbVersion. It contains one field: version of type String. Later I will use dbVersion variable to access, create and remove records from this table.
Second important thing is this piece of code:
con.sync().then(function() {
dbVersion.create({
version: '2018040601'
})
}
);
The sync is taking care of the database table creation if the table does not exists yet. The sync is then calling the anonymous function which is creating the new record in dbVersion table.
I can now retrieve the data stored in my database this way:
dbVersion.findAll().then(function(data) {
console.log(data);
});
There are various find related functions and options you can use, including where statements, pagination, limiting and so on – you may want to take a look at the Sequelize documentation in this matter.
Moving on with complexity
I don’t want to initialize my database connection everywhere I will need to use it. My goal is also to take care of all my models in one place. When looking at the authentication tutorials, one caught my eye – it was not exactly what I needed, but I found pieces of code from it useful. You may want to take a look at it by yourself – it is worth reading. I decided to move my core database layer functions to the separate module. First I created the directory named models, and I placed my database objects models in there. For example /models/dbVersion.js file contains:
module.exports = function(sequelize, Sequelize) {
var dbVersion = sequelize.define('dbVersion', {
version: Sequelize.STRING
});
return dbVersion;
}
My newly created /modules/dbLayer.js file contains:
var fs = require("fs");
var path = require("path");
var Sequelize = require('sequelize');
var env = process.env.NODE_ENV || 'development',
config = require('./../config/config.' + env);
var db = {};
var sequelize = new Sequelize(
config.db.database,
config.db.username,
config.db.password,
config.db.sequelizeParams);
// load models
fs
.readdirSync(__dirname + '/../models')
.filter(function(file) {
return (file.indexOf(".") !== 0);
})
.forEach(function(file) {
var model = sequelize.import(
path.join(__dirname + '/../models', file));
db[model.name] = model;
});
Object.keys(db).forEach(function(modelName) {
if ("associate" in db[modelName]) {
db[modelName].associate(db);
}
});
//Sync Database
sequelize.sync().then(function() {
console.log('Nice! Database looks fine');
db.dbVersion.findAndCountAll().then(function(data) {
if (data.count == 0) {
console.log('No data in dbVersion, initializing...');
db.dbVersion.create({
version: '2018040601'
})
} else {
console.log('Current database version: ' +
data.rows[0].version);
if (data.count > 1) {
console.log('Too many records in the dbVersion table!');
}
}
});
}).catch(function(err) {
console.log(err, "Something went wrong!");
});
db.Sequelize = Sequelize;
db.sequelize = sequelize;
module.exports = db;
As you can see, between lines 14 and 30 the module is taking care of all models I created. Each one is assigned as the property of db object which is exported at the end. I also expanded and adjusted database initialization part. It is not only creating the tables related to my models but also makes sure that there is only one record in the dbVersion table.
Usage of database layer module
Now with such a useful module, the usage is straightforward. I created the additional model for users table (I will need it for authentication), and I decided to display all my users in the Admin part of the site. The changes I made to my admin middleware are listed below:
var dbLayer = require('../modules/dbLayer');
// ...
router.get('/', function(req, res, next) {
var User = dbLayer.user;
User.findAll().then(function(users) {
var viewData = {
title: 'Main admin page',
adminContents: 'This is main admin page',
users: users,
}
res.render('adminMain', viewData);
});
});
As you can see, I don’t have to take care of the database synchronization or connection in each place I want to use it – I just retrieve the proper model from the dbLayer, and I can query for the data I need.
I know that my solution may not be ideal but it is simple enough for me, and it works – which is the best part – you know “done is better than perfect.”
You can find this project source on GitHub.