William Dawson Full Stack Developer Share





Background

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. In this versatile world, database systems are also not 100% alike—the way of accessing data differs. When it comes to migration between databases, ORM could be an option if you want to avoid wasting time and effort. Here are some advantages of ORM over 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 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.

Introduction

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.

How to start ?

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.

Starter app

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.

Example: for 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 })

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

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

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 groupId of 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 a well-described documentation.

Also Try Reading Javascript Frameworks you should definitely learn in 2020

How useful was this post? How useful was this post? Click on a star to rate it!







Submit Rating Average rating 3 / 5. Vote count: 1 No votes so far! Be the first to rate this post. Please do Rate Us and Share!





Related Blogs Kela Casey Angular Best Angular Projects for Beginners 2020 Presenting the best angular projects for beginners list that will prepare you well with the basics and practical needs in angular development. Mentioning your experience in Angular projects can make your resume stand apart from other candidates. Angular Projects for Beginners Soundnode Notepad application Data binding in forms Customer service manager Angular Bare bones project Angular... Continue Reading

Kela Casey Programming , Python Best Python IDEs & Code Editors for 2020 In this post, we’ll discuss what is an IDE/ Code editor, the difference between IDE & Code editors, and some of the best Python IDEs & code editors, along with their best features. Python is a multi-faceted programming language that has been embraced globally with open arms. Python comes with innumerable useful features of... Continue Reading

Adam Davidson Technologies Stripe vs Braintree vs PayPal: Best Payment Gateway for E-commerce In this post, we will throw some light on some of the most essential online payment systems to make it easier for you to select among Stripe vs Braintree vs Paypal, the one that best fits your business objectives and the needs of your customers. Did you know that around 50% of online shoppers... Continue Reading



About The Author William is a CTO and a full-stack engineer with 10 years of experience. He has spent the past seven years doing web and mobile apps. He’s good at designing architecture and implementing agile development process. The technologies he’s worked with include: Node.js, Elixir, Rails, AngularJS, React, React Native, Objective-C, iOS, Java, Android. He’s also familiar with C++, Haskell, C#/.NET. He is an enthusiastic programmer and a great guy to know

Feedback Please leave a comment here and share your valuable feedback with us!

Try our One-Week Risk Free Trial for Hiring a Coder Know more Hire a Coder