I’ve spent just about my entire adult life working in the Microsoft Universe. Professionally, I’m a .NET developer and have been so for as long as .NET has been a thing. Aside from that, I also spent a few years in the 00’s, on the Java EE side. More recently however, I’ve been dabbling in the ultra-hip open source JavaScript world. I don’t want to feel left out or un-cool, so I’m at least trying to keep up with this alternate universe and its many rapid innovations. Who can ignore the hype anymore?
My first observation upon landing on planet Node was; there sure are a lot of developers using MongoDB on the back-end. After a bit of evaluation and confirmation bias, I handily dismissed Mongo as an option. I can see the use-case and for most things, I don’t think it’s a good fit. Once I decided to stick with the time-tested RDBMS approach, I went about evaluating which database to use and how best to perform data-access. In .NETLand, the modern de-facto standard for data-access is the venerable Entity Framework, an Object-Relational Mapping API to rival the likes of Hibernate, JPA, RoR, etc. While there are a few options available for Node, I decided to stick with as low-level of a library as possible. The equivalent in .NET would be ADO.NET, where one must manually create and destroy connections, transactions, and write the actual SQL code to perform CRUD operations against the tables. This is how our primitive ancestors did data-access and achieved great speeds. It works great…and we liked it that way!
While this might sound daunting to those who have become accustomed to ORMs, it’s really not that complicated. Some might say it’s even easier, as there is no API to learn, with all its many nuances and often…frustrations. I won’t debate the pros and cons of ORM tools here, but I do have a love/hate relationship with them, personally. Anyhow, it’s even easier to deal with manual data-access when using the time-tested Unit of Work pattern to abstract the connection and transaction details, providing the developer with a simple interface to work with.
This article will demonstrate the Unit of Work pattern using the mysql npm package, using ES6 syntax. The goal is to wrap as many data-access and business logic calls as we need in a single request, with a single connection and transaction, per-request.
This article assumes you’re familiar with MySQL and at least, some basic Node.js and JavaScript. I will also assume you have these things setup in your environment and you’re ready to write some code. At the time this article was written, Node.js 7.5.0, MySQL 5.7.10, and ES6 were used.
To follow along, the sample project used in this article can be found here.
Please keep in mind that this project is a WIP (work in-progress) and doesn’t do all things, entirely correctly, or entirely in the “Node.js Way”. It’s essentially my first attempt at proper, abstracted data-access, from a .NET developer’s point of view. I welcome all suggestions, improvements, and thoughtful pull requests.
In the sample project we’ll first visit the “sql” folder. You’ll find the following code in the two files located there.
First, let’s create a MySQL database called “uow”.
CREATE DATABASE `uow`Next, we’ll create a single table called “movie”.
CREATE TABLE `movie` (
`movie_id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(100) NOT NULL,
`description` varchar(500) NOT NULL,
`year_released` int(11) NOT NULL,
PRIMARY KEY (`movie_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;Now, let’s seed the “movie” table with some sample data.
insert into `movie`
(title, description, year_released)
values
(‘Resident Evil 4’, ‘The final sequel. Super scary!’, 2017),
(‘Deadpool’, ‘Wise-cracking superhero gets the girl.’, 2016),
(‘The Martian’, ‘Stranded on the Red Planet!’, 2015),
(‘Whiplash’, ‘Jazz snob drum instructor makes student crazy.’, 2014),
(‘The Wolf of Wall Street’, ‘Leo tries crack with Jonah Hill.’, 2013),
(‘The Avengers’, ‘Marvel superhero potpourri.’, 2012),
(‘Limitless’, ‘A secret, magic pill sends your IQ off the charts.’, 2011),
(‘Inception’, ‘Dream inside a dream, inside a dream, and so on…’, 2010)Excellent! We have actual, real data to play with! Let’s get on with the Node nitty-gritties. Using the sample project, let’s install and get all dependencies via npm. In the project root folder, open a terminal and execute the following command:
npm installThe sample project includes some unit tests as well, so go ahead and install nodeunit globally, while you’re at it. Be sure to include the “-g” flag, which stands for “global”. This gives you command-line access to the “nodeunit” command:
npm install -g nodeunitI’ll get back to the unit tests shortly. Let’s first carry on with the sample project.
Looking at the sample project, notice the three files in the “lib” folder. The UnitOfWorkFactory.js file is just that; a factory class that determines which database you’re using and returns a connection to the caller.
NOTE: In the root of the sample project, notice the “config.json” file. I’ve externalized my database connection settings here so that connection changes would not require code changes. Remember to change these settings to suit your environment. Also make special notice of the “DbType” value.
You can see we load the config file and initialize some values globally, at the top of UnitOfWorkFactory.js
const config = require("../config.json");
const _config = config.Database.development;
let _dbConnection = undefined;Also, a switch statement determines which type of database connection to load:
switch (config.DbType) {
case "mock":
console.log("Mock data source selected");
break;
case "mysql":
let mysql = require('mysql');
_dbConnection = mysql.createPool({
connectionLimit: _config.connectionLimit,
host: _config.host,
user: _config.user,
password: _config.password,
database: _config.database
});
break;
default:
throw "ERROR: config.Database.type not defined!";
}This is handy for supporting multiple databases. In the future I intend to add support for PostgreSQL and MS SQL Server. However, since there isn’t a standard interface for data-access and we’re dealing with multiple, independent libraries in Node, each of them work differently. This poses a bit of a challenge code-wise and will likely require further abstraction to “standardize” in this setup.
Moving on…where all the magic happens; the static create() method in UnitOfWorkFactory.js
class UnitOfWorkFactory {
static create(callback) {
let uow = undefined;
switch (config.DbType) {
case "mock":
let MockUnitOfWork = require("./MockUnitOfWork");
uow = new MockUnitOfWork();
return callback(uow);
break;
case "mysql":
let MySqlUnitOfWork = require("./MySqlUnitOfWork");
_dbConnection.getConnection((err, connection) => {
uow = new MySqlUnitOfWork(connection);
return callback(uow);
});
break;
}
}
}Depending on which database type you’ve configured, a connection is created and opened. Also, a Unit of Work class is created and returned in a callback. For the purposes of this article, we’ll focus on MySqlUnitOfWork.js:
class MySqlUnitOfWork {
constructor(connection) {
this.connection = connection;
}
query(query, params, callback) {
this.connection.beginTransaction((err) => {
this.connection.query(query, params, (err, result) => {
if (err) this.connection.rollback();
return callback(result);
});
});
}
complete() {
this.connection.commit((err) => {
this.connection.release();
});
}
}This is pretty straightforward and how you’d model other data sources, as you add them. As you can see, the connection is passed into the constructor and utilized in the remaining two methods, query() and complete(). The query() method opens a transaction and executes a SQL query against your data source. The complete() method commits the transaction created in query() and also closes the database connection that was created there.
Let’s examine this in action, in a client. For our purposes, the client will be the app.js file, which is the entry-point for this Node application.
First, I make reference to my UoW class, and also a new class I’ve created which acts as a repository for movie-related data, called MovieData.js. This file is located in the “service” folder of the project:
const UnitOfWork = require("./lib/UnitOfWorkFactory");
const MovieData = require("./service/MovieData");I suppose “data” or “dataaccess” might have been a more apt name for this folder, but who am I to judge how you might architect the layers of your own application? It matters not! Only the usage is important here.
The MovieData data-access class is a very straightforward, simple way of interacting with the data source. You’ve likely seen similar code in other technologies:
class MovieData {
constructor(uow) {
this.uow = uow;
}
getAll(callback) {
this.uow.query("select * from movie", [], (result) => {
return callback(result);
});
}
...
}Notice that the active, open, connected Unit of Work is passed into the constructor, which gives access to it from all other methods in this class. This provides a simple way to query the database and pass the result back to the calling code.
Now, the moment of truth…using the Unit of Work in app.js:
let db = UnitOfWork.create((uow) => {
let data = new MovieData(uow);
data.getAll((result) => {
result.forEach(movie => console.log(movie.title));
});
uow.complete();
});This should result in a list of movie titles from the movie table, printed out to the terminal.
The basic chain-of-events here is; open a connection and transaction, and retrieve the active Unit of Work in a callback. Use the UoW in your data-access, service, or whichever type of abstraction you prefer when separating concerns. When finished, before the request completes, call the complete() method to commit the transaction and close the connection. All of this should occur once per-request. In an Express.js application, for example, this would occur once for a single HTTP request. The database connection is returned to the pool so it can be reused, which is the optimal approach, performance-wise. Should any operation against the database fail during this request, the entire transaction is rolled back and your data integrity remains intact.
As you might have already guessed, you would be able to utilize several service/data-access classes here and all would share the same active Unit of Work object, across a single request.
Earlier, I mentioned that there some unit tests. I’ve included this in the project as a way to test the full set of CRUD operations against the data. You’ll find the test methods in the UoWTests.js file in the “tests” folder. To run them, open a terminal in the folder and execute the following command:
nodeunit UoWTests.jsHopefully, this results in a readout of successful test results. I didn’t put a lot of effort into these unit tests and they mostly serve as a guide on how to do full CRUD.
In a later post, I might outline how the Unit of Work can be injected into the HTTP request pipeline in Express, allowing it to be passed into an endpoint in a simpler way. This would achieve the same effect with less code in your controllers.
Until then, code on!
