jarednielsen.com

What is Object-Relational Mapping? How to Roll-Your-Own JavaScript ORM

December 31, 2018

Object-Relational Mapping

Understanding object-relational mapping can be challenging when you’re first learning web development. In this tutorial, you will roll-your-own ORM with JavaScript to help you better understand how full-featured object-relational mapping, such as Sequelize, works under-the-hood.

You will learn:

  • What is Object-Relational Mapping?
  • How to use Promises to handle asynchronous database queries
  • Models and how to implement them
  • How to implement a simple, Promise-based ORM with JavaScript

What is Object-Relational Mapping?

According to Wikipedia, Object-Relational Mapping is:

… a programming technique for converting data between incompatible type systems using object-oriented programming languages.

Martin Fowler gives us a more nuanced answer in OrmHate:

Essentially what you are doing is synchronizing between two quite different representations of data, one in the relational database, and the other in-memory.

What does this mean for us as Node.js developers?

The first problem for us is that SQL database management systems only store scalar values. In JavaScript, with the exception of primitive data types, everything is an object. Unlike objects, scalar variables can only hold one value at a time. So in the case of using a SQL Database Management System (DBMS), that means strings or integers. Lucky for us, there are npm packages such as mysql and mysql2 that return our queries in JSON format. But that’s only half the battle.

The other half of the problem is writing methods that allow us to query our database in our native language (JavaScript) and not that of the SQL DBMS. Object Relational Mapping is useful as it allows us to separate concerns in our application with reusable methods for database queries. The added, and perhaps most important, benefit of this separation of concerns is that we can easily use other SQL databases without needing to rewrite the entire code-base and instead only make minor changes to the ORM.

War is Never a Lasting Solution

While researching this article, I encountered many references to ORMs as the ‘Vietnam of Computer Science’. This phrase can be traced back to a conference presentation by Ted Neward who describes it in detail on his blog:

Object/Relational Mapping […] represents a quagmire which starts well, gets more complicated as time passes, and before long entraps its users in a commitment that has no clear demarcation point, no clear win conditions, and no clear exit strategy.

As on the battlefield, there are two fierce, opinionated sides of the object-relational mapping debate: those in favor of, and those against, ORMs. In a 2016 article, Should I Or Should I Not Use ORM?, Mithun Sasidharan describes a different middle-ground between the two camps: choose the approach that is most appropriate for your application. He lists several questions to ask when making this decision, but we can distill it down to two:

  • Are your data access patterns going to be simple? Use an ORM
  • Is speed your priority? Don’t use an ORM

In a recent article, Why you should avoid ORMs (with examples in Node.js), Thomas Hunter II makes the case for a middle-ground between hand-written database drivers and third-party ORMs: query builders, such as knex. The reasons he outlines are:

  • ORMs are very complex.
  • ORMs are inefficient.
  • ORMs don’t do everything.

While the above is true for a full-featured ORM, Sasidharan outlines several counterpointing benefits to using an ORM:

  • ORMs facilitate model implementation
  • ORMs result in a smaller code-base
  • ORMs enable faster start-up time

Enough history and debate. Let’s get building!

Promises, Promises

Sequelize touts itself as a Promise-based ORM. In order to understand object-relational mapping, we need a baseline understanding of Promises.

Asynchronous

Let’s declare a function, asynchronousEmulator, to emulate asynchronous database queries. The function generates a random number and passes it to setTimeout.

function asynchronousEmulator(num){
  const rand = Math.floor(Math.random() * 1000)
  setTimeout(function(){
      console.log(num);
  }, rand);
}

Next, let’s declare a second function, ordinalNumbers, and make three calls to asynchronousEmulator.

function ordinalNumbers(){
  asynchronousEmulator("First");
  asynchronousEmulator("Second");
  asynchronousEmulator("Third");
}

ordinalNumbers();

We would expect ordinalNumbers to log each number as it is written, but when we run this from the command line, the ordinal numbers are logged out of sequence. (Unless they are in sequence! 🤯)

Callbacks

We can fix this by passing our asynchronousEmulator a function, cb, in addition to a string. When setTimout finishes executing, it will then call our function cb(). We refactor ordinalNumbers so that each call to asynchronousEmulator takes a second argument, an anonymous function. That’s our callback! Each anonymous function (except for the last) calls asynchronousEmulator.

function asynchronousEmulator(num, cb){
  const rand = Math.floor(Math.random() * 1000)
  setTimeout(function(){
      console.log(num);
      cb();
  }, rand);
}

function ordinalNumbers(){
  asynchronousEmulator("First", function(){
    asynchronousEmulator("Second", function(){
      asynchronousEmulator("Third", function(){
        return;
      });
    });
  });
}

ordinalNumbers();

But we are now on the brink of callback hell!

Promises

Using the Promise object, we can omit the callback(s) and instead refactor asynchronousEmulator to return a new Promise object:

function asynchronousEmulator(num){
  return new Promise(function(resolve, reject){
    const rand = Math.floor(Math.random() * 1000);
    setTimeout(function(){
       console.log(num);
       resolve();
       reject();
     }, rand);
  });
}

A Promise is an object (which is why we use the new keyword) that manages the eventual resolution (or failure) of an asynchronous task. A Promise only promises to end.

We pass our Promise a function (the executor) with two parameters, resolve and reject. The resolve will be called if the Promise is fulfilled (i.e., the execution is successful). The reject function will be called if the Promise fails (i.e., there is an error).

We then need to refactor ordinalNumbers to use .then() and .catch() rather than callbacks:

function ordinalNumbers(){
  asynchronousEmulator("First")
  .then(function(){
    return asynchronousEmulator("Second")
  })
  .then(function(){
    return asynchronousEmulator("Third")
  })
  .catch(function(){
    return "Something went wrong...";
  });
}

ordinalNumbers();

We chain .then() and .catch() functions to our Promise invocation. We can chain as many as we want, effectively adding multiple callbacks to our Promise object. .then() handles the successful resolution of our Promise. .catch() handles the failure, or rejection, of our Promise.

For a deeper-dive, read my related article to Learn JavaScript Promises and Promise Methods.

Setup The MySQL Database

Throughout this tutorial we will work with MySQL and the mysql npm package to build a simple Node app for a vet to track pets and owners. We’ll use the following schema and seeds:

DROP DATABASE IF EXISTS vet_db;
CREATE DATABASE vet_db;
USE vet_db;

CREATE TABLE cats
(
	id INT NOT NULL AUTO_INCREMENT,
	pet_name VARCHAR(30) NOT NULL,
	pet_age INTEGER(2),
	pet_sex VARCHAR(6) NOT NULL,
	desexed BOOLEAN DEFAULT false,
	PRIMARY KEY (id)
);

INSERT INTO cats (pet_name, pet_age, pet_sex, desexed)
VALUES  ('Tommy', 12, "Male", false),
        ('Whiskers', 2, "Female", true),
        ('Felix', 1, "Male", true)
;

All The Cats Are Undefined

Let’s build a simple Node application for querying our database. Make a new directory and install our only dependency:

npm install mysql

Then create a server.js file and setup a connection to our database:

const mysql = require("mysql");

const connection = mysql.createConnection({
  host: "localhost",
  port: 3306,
  user: "root",
  password: "",
  database: "vet_db"
});

connection.connect(function(err) {
  if (err) {
    console.error('error connecting: ' + err.stack);
    return;
  }

  console.log('connected as id ' + connection.threadId);
});

Let’s write our first database query and select all the cats from our cats table:

function all(){
  connection.query("SELECT * FROM cats;", function(err, data) {
    if (err) throw err;
    return data;
  });
}

let allTheCats = all();

console.log(`All the cats are ${allTheCats}!`);

All the cats are undefined! Why? We are attempting to query our database before we have connected to it. We can resolve (pun intended) this situation with a Promise.

function all(){
  const sql = "SELECT * FROM cats";

  return new Promise(function(resolve, reject){
    connection.query(sql, function(err, data) {
      if (err) reject(err);
      resolve(data);
    });
  })
}

Now that we are using a Promise, we chain .then() and .catch() to our call to all().

all()
  .then(function(data){
    console.log(data);
  })
  .catch(function(err){
    console.log(err);
  });

The results should be something like:

Connected as id 6
[ RowDataPacket {
    id: 1,
    pet_name: 'Tommy',
    pet_age: 12,
    pet_sex: 'Male',
    desexed: 0 },
  RowDataPacket {
    id: 2,
    pet_name: 'Whiskers',
    pet_age: 2,
    pet_sex: 'Female',
    desexed: 1 },
  RowDataPacket {
    id: 3,
    pet_name: 'Felix',
    pet_age: 1,
    pet_sex: 'Male',
    desexed: 1 } ]

CRUD: Cat Read Update Delete

Implementing our remaining CRUD methods is straightforward:

// create new cat with name and sleepy values
function create(name, age, sex, fixed){
  const sql = `INSERT INTO cats (pet_name, pet_age, pet_sex, desexed) VALUES (?, ?, ?, ?)`;

  return new Promise(function(resolve, reject){
    connection.query(sql, [name, age, sex, fixed], function(err, data) {
      if (err) reject(err);
      resolve(data);
    });
  })
}

We call create:

create("Keyboard", 7, "female", false)
  .then(function(data){
    console.log(data);
  })
  .catch(function(err){
    console.log(err);
  });

The results will be something similar to:

Connected as id 7
OkPacket {
  fieldCount: 0,
  affectedRows: 1,
  insertId: 4,
  serverStatus: 2,
  warningCount: 0,
  message: '',
  protocol41: true,
  changedRows: 0 }
[ RowDataPacket {
    id: 1,
    pet_name: 'Tommy',
    pet_age: 12,
    pet_sex: 'Male',
    desexed: 0 },
  RowDataPacket {
    id: 2,
    pet_name: 'Whiskers',
    pet_age: 2,
    pet_sex: 'Female',
    desexed: 1 },
  RowDataPacket {
    id: 3,
    pet_name: 'Felix',
    pet_age: 1,
    pet_sex: 'Male',
    desexed: 1 },
  RowDataPacket {
    id: 4,
    pet_name: 'Keyboard',
    pet_age: 7,
    pet_sex: 'female',
    desexed: 0 } ]

Our update method:

// update cat sleepy value by id
function update(desexed, id){
  const sql = `UPDATE cats SET desexed = ? WHERE id = ?`;

  return new Promise(function(resolve, reject){
    connection.query(sql, [desexed, id], function(err, data) {
      if (err) reject(err);
      resolve(data);
    });
  })
}

We call update like so:

update(true, 1)
  .then(function(data){
    console.log(data);
  })
  .catch(function(err){
    console.log(err);
  });

The results of our query will be something like:

Connected as id 8
OkPacket {
  fieldCount: 0,
  affectedRows: 1,
  insertId: 5,
  serverStatus: 2,
  warningCount: 0,
  message: '',
  protocol41: true,
  changedRows: 0 }
OkPacket {
  fieldCount: 0,
  affectedRows: 1,
  insertId: 0,
  serverStatus: 2,
  warningCount: 0,
  message: '(Rows matched: 1  Changed: 1  Warnings: 0',
  protocol41: true,
  changedRows: 1 }
[ RowDataPacket {
    id: 1,
    pet_name: 'Tommy',
    pet_age: 12,
    pet_sex: 'Male',
    desexed: 1 },
  RowDataPacket {
    id: 2,
    pet_name: 'Whiskers',
    pet_age: 2,
    pet_sex: 'Female',
    desexed: 1 },
  RowDataPacket {
    id: 3,
    pet_name: 'Felix',
    pet_age: 1,
    pet_sex: 'Male',
    desexed: 1 },
  RowDataPacket {
    id: 4,
    pet_name: 'Keyboard',
    pet_age: 7,
    pet_sex: 'female',
    desexed: 0 },
  RowDataPacket {
    id: 5,
    pet_name: 'Keyboard',
    pet_age: 7,
    pet_sex: 'female',
    desexed: 0 } ]

Notice that Tommy is now fixed. No longer the stud bull!

Also notice that we have two Keyboard cats because we ran the create method again. Let’s address that with a destroy method. (“Destroy” sounds extreme, but delete is a reserved keyword in JavaScript.)

function destroy(id){
  const sql = `DELETE FROM cats WHERE id = ?`;

  return new Promise(function(resolve, reject){
    connection.query(sql, [id], function(err, data) {
      if (err) reject(err);
      resolve(data);
    });
  })
}

We call the destroy method like so:

destroy(5)
  .then(function(data){
    console.log(data);
  })
  .catch(function(err){
    console.log(err);
  });

ORM + Architectural Patterns

There are many established conventions when implementing an architectural pattern in a web application. MVC, or Model View Controller, for example, is (was?) very popular among web developers. Whatever pattern you choose, it will most likely implement a model layer.

Why is it called the Model? The Model is so named because it defines the shape of the database. You could say it “models” the data. The Model is also responsible for managing the data structure in your application.

As discussed above, one of the primary reasons for using an architectural pattern is the Separation of Concerns. We will implement and modularize the Model by separating the database queries from the controller logic in server.js.

At the root level of the directory, we create a folder db and move the two .sql files into it. This is common practice for keeping an application organized as it signals to other developers that in this folder lies files related to the database.

Again, at the root level of the directory, we create a folder, config, and inside config we create a file connection.js. This is also common practice for keeping an application organized and is the location where other configuration files will be stored.

From server.js, we cut the boilerplate MySQL configuration, including the NPM import, and paste it into connection.js. We use module.exports to export connection.

const mysql = require("mysql");

const connection = mysql.createConnection({
  host: "localhost",
  port: 3306,
  user: "root",
  password: "",
  database: "petshop_db"
});

connection.connect(function(err) {
  if (err) {
    console.error('error connecting: ' + err.stack);
    return;
  }

  console.log('connected as id ' + connection.threadId);
});

module.exports = connection;

Again, at the root level of the directory, we create a folder, models, and inside models we create a file cat.js. This is our first model.

At the top of cat.js, we require connection.js to import our database connection.

In cat.js, we create an object cat.

From server.js, cut the all, create, update and destroy functions and paste them inside the cat object in cat.js. Leave the calls to these functions in server.js.

Now convert the functions in the cat object to object methods and export cat. Don’t forget the commas!

const connection = require('../config/connection.js');

const cat = {
  all: function(){
    const sql = "SELECT * FROM cats";

    return new Promise(function(resolve, reject){
      connection.query(sql, function(err, data) {
        if (err) reject(err);
        resolve(data);
      });
    })
  },

  create: function(name, age, sex, fixed){
    const sql = `INSERT INTO cats (pet_name, pet_age, pet_sex, desexed) VALUES (?, ?, ?, ?)`;

    return new Promise(function(resolve, reject){
      connection.query(sql, [name, age, sex, fixed], function(err, data) {
        if (err) reject(err);
        resolve(data);
      });
    })
  },

  update: function(desexed, id){
    const sql = `UPDATE cats SET desexed = ? WHERE id = ?`;

    return new Promise(function(resolve, reject){
      connection.query(sql, [desexed, id], function(err, data) {
        if (err) reject(err);
        resolve(data);
      });
    })
  },

  destroy: function(id){
    const sql = `DELETE FROM cats WHERE id = ?`;

    return new Promise(function(resolve, reject){
      connection.query(sql, [id], function(err, data) {
        if (err) reject(err);
        resolve(data);
      });
    })
  }
}

module.exports = cat;

In server.js, require ./models/cat.js and modify the calls to all, create, and update to invoke the imported object methods.

const cat = require('./models/cat.js');

cat.all()  
  .then(function(data){
    console.log(data);
  })
  .catch(function(err){
    console.log(err);
  });

cat.create("Keyboard", 7, "female", false)
  .then(function(data){
    console.log(data);
  })
  .catch(function(err){
    console.log(err);
  });

cat.update(true, 1)
  .then(function(data){
    console.log(data);
  })
  .catch(function(err){
    console.log(err);
  });

cat.destroy(5)
  .then(function(data){
    console.log(data);
  })
  .catch(function(err){
    console.log(err);
  });

Your directory structure should now look like this:

/config
  |_ connection.js
.gitignore
/models
  |_ cat.js
/db
  |_ schema.sql
  |_ seeds.sql
server.js

Run server.js to verify that everything works.

Unless our vet specializes in cats, we will want models for other species. We could copy the cat.js file, rename it, and all appropriate references to dog, but, that would not be DRY. What’s the solution? You guessed it. Object-relational mapping.

What is ORM?

If we read the Sequelize source code, we will see that it is class-based. Classes in JavaScript are syntactic sugar on constructors, so, for the time-being we will work with constructors then later translate our ORM to classes.

What does a constructor function do?

A constructor creates an object.

What is MySQL?

A relational database.

So what is Object Relational Mapping?

Mapping database functionality and structure to an object.

Let’s do it.

In the config folder create a new file, orm.js, and require connection.js.

Next, in orm.js create a constructor function, ORM, with a parameter table, and initialize it with this.table = table.

Cut the methods from cat.js and paste them inside the ORM constructor.

Refactor the object methods into constructor methods using this and replace all references to the cats table with this.table. Don’t forget to change the : to =!

Then export the ORM: module.exports = ORM;

The finished orm.js will look like:

const connection = require('./connection.js');

const ORM = function(table){
  this.table = table;

  this.all = function(){
    const sql = `SELECT * FROM ??`;

    return new Promise(function(resolve, reject){
      connection.query(sql, table, function(err, data) {
        if (err) reject(err);
        resolve(data);
      });
    })
  },

  this.create = function(name, age, sex, fixed){
    const sql = `INSERT INTO ?? (pet_name, pet_age, pet_sex, desexed) VALUES (?, ?, ?, ?)`;

    return new Promise(function(resolve, reject){
      connection.query(sql, [table, name, age, sex, fixed], function(err, data) {
        if (err) reject(err);
        resolve(data);
      });
    })
  },

  this.update = function(desexed, id){
    const sql = `UPDATE ?? SET desexed = ? WHERE id = ?`;

    return new Promise(function(resolve, reject){
      connection.query(sql, [table, desexed, id], function(err, data) {
        if (err) reject(err);
        resolve(data);
      });
    })
  },

  this.destroy = function(id){
    const sql = `DELETE FROM ?? WHERE id = ?`;

    return new Promise(function(resolve, reject){
      connection.query(sql, [table, id], function(err, data) {
        if (err) reject(err);
        resolve(data);
      });
    })
  }
}

module.exports = ORM;

Lastly, refactor the cat.js model to reflect the code below, respectively:

const ORM = require('../config/orm.js');

const cat = new ORM("cats");

module.exports = cat;

Note that we didn’t change anything in server.js. Run it now to verify that everything works.

Model Pets

We have a few options now for extending our ORM. If we wanted to keep it as is, we could rename cat.js to index.js and create a new ORM, dog, then export both of them in an object. That would look like this:

const ORM = require('../config/orm.js');

const Cat = new ORM("cats");
const Dog = new ORM("dogs");

module.exports = { Cat, Dog };

Now we can change the import in our server.js to import the models directory (Node will find the index.js) and update the method instances to use the nested model objects:

const db = require('./models');

db.Cat.all()
  .then(function(data){
    console.log(data);
  })
  .catch(function(err){
    console.log(err);
  });

db.Dog.all()
  .then(function(data){
    console.log(data);
  })
  .catch(function(err){
    console.log(err);
  });

Before we run our server, we will need to create a dogs table:

CREATE TABLE dogs
(
	id INT NOT NULL AUTO_INCREMENT,
	pet_name VARCHAR(30) NOT NULL,
	pet_age INTEGER(2),
	pet_sex VARCHAR(6) NOT NULL,
	desexed BOOLEAN DEFAULT false,
	PRIMARY KEY (id)
);

And seed it with dogs:

INSERT INTO dogs (pet_name, pet_age, pet_sex, desexed)
VALUES  ('Rex', 12, "Male", false),
        ('Fido', 2, "Female", true),
        ('Spot', 1, "Male", true)
;

The results should be all the cats and dogs, like so:

connected as id 16
[ RowDataPacket {
    id: 1,
    pet_name: 'Tommy',
    pet_age: 12,
    pet_sex: 'Male',
    desexed: 0 },
  RowDataPacket {
    id: 2,
    pet_name: 'Whiskers',
    pet_age: 2,
    pet_sex: 'Female',
    desexed: 1 },
  RowDataPacket {
    id: 3,
    pet_name: 'Felix',
    pet_age: 1,
    pet_sex: 'Male',
    desexed: 1 } ]
[ RowDataPacket {
    id: 1,
    pet_name: 'Rex',
    pet_age: 12,
    pet_sex: 'Male',
    desexed: 0 },
  RowDataPacket {
    id: 2,
    pet_name: 'Fido',
    pet_age: 2,
    pet_sex: 'Female',
    desexed: 1 },
  RowDataPacket {
    id: 3,
    pet_name: 'Spot',
    pet_age: 1,
    pet_sex: 'Male',
    desexed: 1 } ]

Refactoring Custom Object-Relational Mapping for Variable Table Structures

This approach is all fine and good if our tables have similar structures. But what if our tables differ? Let’s create an owners table.

CREATE TABLE owners
(
	id INT NOT NULL AUTO_INCREMENT,
	first_name VARCHAR(30) NOT NULL,
	last_name VARCHAR(30) NOT NULL,
	PRIMARY KEY (id)
);

We need to refactor our ORM to use prepared statements and the format method:

this.create = function(cols, vals){
    let sql = `INSERT INTO ?? (??) VALUES (?)`;
    let inserts = [this.table, cols, vals];
    sql = connection.format(sql, inserts);

    return new Promise(function(resolve, reject){
      connection.query(sql, function(err, data) {
        if (err) reject(err);
        resolve(data);
      });
    })
  },

Then we need to modify models/index.js to create and export and Owner:

const ORM = require('../config/orm.js');

const Cat = new ORM("cats");
const Dog = new ORM("dogs");
const Owner = new ORM("owners");

module.exports = { Cat, Dog, Owner };

Lastly, in server.js, we need to create an owner and select it from the database:

db.Owner.create(
  ["first_name", "last_name"],
  ["Jared", "Nielsen"]
  )
  .then(function(data){
    console.log(data);
  })
  .catch(function(err){
    console.log(err);
  });

db.Owner.all()
  .then(function(data){
    console.log(data);
  })
  .catch(function(err){
    console.log(err);
  });

Refactoring Object-Relational Mapping to Create Tables via Model Instantiation

If we want our ORM to have functionality similar to Sequelize and other third-party object-relational mapping libraries, we need the ability to create our tables through our model instantiation. That’s going to require some refactoring, though. Let’s do it!

Let’s begin by refactoring orm.js:

const connection = require('../config/connection.js');

function ORM(table, cols){
  this.table = table;
  this.cols = cols;
}

ORM.prototype.sync = function(){
  const columns = (cols) => Object.keys(cols).map(key => `${key} ${cols[key]}`);

  const createTableSQL =`
    CREATE TABLE ${this.table} (
      id INT AUTO_INCREMENT NOT NULL,
      ${columns(this.cols).join(',')},
      PRIMARY KEY (id)
    )`;

  //@TODO: convert to Promises
  return new Promise(function(resolve, reject) {
      connection.query(createTableSQL, function(err, data){
        if (err) reject(err);
        resolve(data);
      });
  });
};

module.exports = ORM;

We can then refactor the Cat call (pun intended) in cat.js to take an additional argument, an object, where the properties are our column names and the values are the SQL data types:

const ORM = require('../config/orm.js');

const Cat = new ORM("cats", {
  pet_name: "VARCHAR(30) NOT NULL",
  pet_age: "INTEGER(2)",
  pet_sex: "VARCHAR(6)",
  desexed: "BOOLEAN DEFAULT false"
});

module.exports = Cat;

But when we run server.js we get an error:

TypeError: Cat.all is not a function

Why?

The Cat is Undefined

Wow! Are you still with me? This tutorial is clocking in near 1000 lines, so I will stop here and pick up in a subsequent post. Stay tuned for the gripping conclusion. I Promise it will be good.

Jared Nielsen

Want to level up your problem solving skills? I write a weekly newsletter about programming, problem solving and lifelong learning. Join now