Guide on popular Object-relational mapping (ORM)

In this versatile world, database systems are also not 100% alike—the way of accessing data differs. When it comes to migration between databases, Object-Relational Mapping (ORM) could be an option if you want to avoid wasting time and effort.

Here are some advantages of ORM over the traditional query approach:

• Developers can only focus on business logic rather than writing interfaces between code and DB.
• Reduces development time and costs by avoiding redundant codes
• Capable of connecting to different databases, which comes in handy during switching from one DB to the other.
• Helps to effectively query from multiple tables similar to SQL JOIN—ORM takes the responsibility of converting the object-oriented query approach to SQL queries.

In this article, we will learn how to make an effective object-relational mapping with Sequelize in Node.js.

There are a couple of other alternatives but this module is my favorite. Sequelize is easy to learn and has dozens of cool features like synchronization, association, validation, etc. It also has support for PostgreSQL, MySQL, MariaDB, SQLite, and MSSQL.

What is an ORM?

Object-Relational Mapping (ORM) is the process of mapping between objects and relational database systems. So it acts like an interface between two systems hiding details about an underlying mechanism.

What is object relational mapping

Starter app

So our objective here is to understand its functionality by creating a sample application that will perform some basic operations with CRUD. I assume you have Node.js and PostgresSQL installed.

Let’s use “express application generator” to create a starter application.

npm install express-generator -g
express testapp

To install sequelize, postgres and ejs (template engine), navigate to root of your generated folder.

npm install --save sequelize
npm install --save pg pg-hstore
npm install --save ejs

We will use EJS for templating instead of Jade. So we can remove Jade related dependencies and files from the default Express-generated project.

DB connection and models

Connecting to postgres is as easy as a single line

var sequelize = new Sequelize('postgres://username:[email protected]:5432/db_name');

Models are the objects which represent tables in a database. They are the heart of ORM and we can define them with sequelize.define. Our User model looks like this:

var User = sequelize.define('user', {
  firstName: {
    type: DataTypes.STRING,
    allowNull: false,
    unique: 'compositeIndex'
  },
  lastName: {
    type: DataTypes.STRING,
    unique: 'compositeIndex'
  },
    .........
    .........
    .........
  dateJoined: {
    type: DataTypes.DATE,
    defaultValue: DataTypes.NOW
  }
}, {
  getterMethods   : {
    address: function()  { return this.state + ', ' + this.country }
  },
  setterMethods   : {
    address: function(value) {
      var names = value.split(', ');
      this.setDataValue('country', names[0]);
      this.setDataValue('state', names[1]);
    },
  }
});

You just need to define the columns and their data types and Sequelize will automatically add createdAt and updatedAt to your model. There are a few other settings you can also configure for your columns.

• allowNull — Set false if null is not allowed
• defaultValue — Set a default value for a column.
  Example: DataTypes.NOW for dateJoined column.
• autoIncrement — Automatically increments the column by one every time a new row is inserted
• comment — Mention a comment for your column
• unique — Mention a boolean true to define a column as unique. For a composite key, a string can be mentioned for multiple columns.

Example

userId: {type: Sequelize.STRING, unique: true},
fullName: { type: Sequelize.STRING,  unique: 'compositeIndex'},
dob: { type: Sequelize.DATE, unique: 'compositeIndex'}

Getter and setter methods

In Sequelize, we can define pseudo properties on a model. These properties are not an actual part of the database schema, they are just for the models. In the example above, “address” is a pseudo-property, and its value is initialized through getter and setter methods. When state and country are fetched from db, Sequelize merges them with a comma to populate the “address” property (getterMethods). Similarly, when we set the address, it gets split into state and country (setterMethods). Thus, the “address” seems like a column in db but actually it’s not.

Preparing sample data

There are different approaches to create a new entry in DB. The first approach is to build a non persistent object and then call save() to persist the data.

var newUser = user.build({
  firstName: 'John', 
  lastName: 'Doe', 
  age: 28, 
  country: 'US', 
  state: 'Indiana', 
  email: '[email protected]'
});
newUser.save().then(function() {
  // Do stuffs after data persists
})

Another alternative is to do both the steps in a single line using user.create({.....}).then(function(user) {}). If we need to create bulk instances, here’s how we do it.

user.bulkCreate([USERS],{ validate: true }).then(function() {
  // Congratulate user!
}).catch(function(errors) {
  // Catch  if validation failed
  // Print errors
});

Check ./routes/createData.js in the sample project for the bulk creation logic. Go with the steps on localhost:3000 to see the operations live as we discuss further.

Querying data

We can query data using findAll and findOne which takes additional parameters like attributes, where condition, ordering, and pagination. Let’s learn these through examples.

Get first 100 user instances. The highlighted column in the image above comes from the “group” table.

user.findAll({limit: 100}).then(function(users) {
  // Send array to view
});

Get user by email

user.findOne({where : {email: '[email protected]'}}).then(function(user) {
  // Send user to view
});

A little more complex example. Find all users of California and Arizona whose age is in between 20 and 40 and last name contains ‘user’.

var query = {};
query.where = {
  $or: [
    {state: "California"},
    {state: "Arizona"}
  ],
  age: {
    $between: [20, 40]
  },
  lastName: {
    $ilike: '%user%'
  }
};
user.findAll(query).then(function(users) {
  // Do something awesome here
});

SELECT "id", "firstName", "lastName", "email", "age", "country", "state", "dateJoined", "createdAt", "updatedAt"
FROM "user" AS "user"
WHERE ("user"."state" = 'California' OR "user"."state" = 'Arizona')
  AND "user"."age" BETWEEN 20 AND 40
  AND "user"."lastName" ILIKE '%user%';

If you need more operators for querying data, you can find the list on the net.

Sequelize also offers an option to directly provide SQL query, like:

sequelize.query(QUERY, { model: user }).then(function(users) {
  // Array of instance of user
})

Updates and destroy

Updating an instance can take two parameters, the updated values and where condition.

user.update({
  firstName: "John",
  lastName: "Doe",
  address: "Nevada, US"
},{
  where: { email : "[email protected]" }
})
.then(function () { 

});

“Destroy” also takes a where condition (Check below). You can also follow the callback syntax (just like update) to do some logic after destroying.

user.destroy({
  where: {
    email: '[email protected]'
  }
});
// DELETE FROM user WHERE email="[email protected]";

Relation and associations

In this section, we will learn about one-to-one association. When two models are linked to each other by a single foreign key, we say it as a one-to-one association. In our case, we have two models: user and group. Each user is associated with a single group. So the user model has a foreign key group_id which points to the groupIdof the group model. Defining this association is very easy in Sequelize.

user.belongsTo(group, {foreignKey: 'group_id', targetKey: 'groupId'});

Once the line above is executed, it creates a foreign key “group_id” for the group model. To read the data from both tables (like join in SQL), simply include the target model as follows:

user.findAll({
  limit: 100,
  include: [{
    model: group
  }]
}).then(function(users) {
  // Send users to view
});

Wrapping up

There are more features in Sequelize, such as transaction management, hooks, scopes, etc. Combining all these features Sequelize becomes a strong ORM module for Node.js.

But when it comes to complex relations and associations, it seems a little dimmed and maintainability could be a concern. But other than that it is a bullet-proof module with well-described documentation.

Lastly, if ever you feel the need to learn more about technologies, frameworks, languages, and more – InApps will always have you updated.