Thanks! We'll be in touch in the next 12 hours
Oops! Something went wrong while submitting the form.

A Step Towards Simplified Querying in NodeJS

Prakash Thete

Full-stack Development
Tags:

Recently, I came across a question on StackOverflow regarding querying the data on relationship table using sequalize and I went into flashback with the same situation and hence decided to write a blog over a better alternative Objection.js. When we choose ORM’s without looking into the use case we are tackling we usually end up with a mess.

The question on StackOverflow was about converting the below query into sequalize query.

CODE: https://gist.github.com/velotiotech/9db15da501318b19cbfb07291e316cbb.js

(Pardon me for naming in the query, it was asked by novice programmer and I wanted to keep it as it is for purity sake).

Seems pretty straightforward right? So the solution is like below:

CODE: https://gist.github.com/velotiotech/d67bc427063601d9618bece60335959f.js

If you look at this it’s much complex solution for simple querying and this grows with added relationships. And also for simple queries like this, the sequalize documentation is not sufficient. Now if you ask me how it can be done in a better way with Objection.js below is the same query in objection.

CODE: https://gist.github.com/velotiotech/40c44f3020906ff84710054737221e67.js

Note: It’s assumed that relationship is defined (in model classes) in both examples.

Now you guys can see the difference this is just one example I came across there are others on the internet for better understanding. So are you guys ready for diving into Objection.js?

But before we dive in, I wanted to let you guys know whenever we check online for Node.js ORM, we always find some people saying “don’t use an ORM, just write plain SQL” and they are correct in their perception. If your app is small enough that you can write a bunch of query helper functions and carry out all the needed functionality, then don't go with ORM approach, instead just use plain SQL.

But when your app has an ample amount of tables and relationships between them that need to be defined and multiple-joint queries need to done, there comes the power of ORM.

So when we search for the ORM's (For relational DB) available in NodeJS arena we usually get the list below:

1. Sequelize

2. Objection.js

3. typeORM

There are others, I have just mentioned more popular ones.

Well, I have personally used both Sequelize and Objection.js as they are the most popular ORM available today. So if you are a person who is deciding on which ORM you should be using for your next project or got frustrated with the relationship query complexity of `Sequelize` then you have landed on the correct place.

I am going to be honest here, I am using Objection.js currently doesn't make it the facto or best ORM for NodeJS. If you don't love to write the SQL resembling queries and prefer the fully abstracted query syntax then I think `Sequelize` is the right option for you (though you might struggle with relationship queries as I did and land up with Objection.js later on) but if you want your queries to resemble the SQL one then you should read out this blog.

What Makes Objection So Special?

1. Objection under the hood uses KNEX.JS a   powerful SQL query builder

2. Let’s you create models for tables with ES6 / ES7 classes and define the relationships between them

3. Make queries with async / await

4. Add validation to your models using JSON schema

5. Perform graph inserts and upserts

to name a few.

The Learning Curve

I have exclusively relied upon the documentation. The Knex.js and objection.js documentation is great and there are simple (One of them, I am going to use below for explanation) examples on the Objection GitHub. So if you have previously worked with any NodeJS ORM or you are a newbie, this will help you get started without any struggles.

So let’s get started with some of the important topics while I explain to you the advantages over other ORM and usage along the way.

For setup (package installation, configuration, etc.) and full code you can check out Github

Creating and Managing DB Schema

Migration is a good pattern to manage your changes database schema. Objection.js uses knex.js migration for this purpose.

So what is Migration : Migrations are changes to a database’s schema specified within your ORM, so we will be defining the tables and columns of our database straight in JavaScript rather than using SQL.

One of the best features of Knex is its robust migration support. To create a new migration simply use the knex cli:

CODE: https://gist.github.com/velotiotech/1d42ac9abfc44bd98228715e8973cf5e.js

After running this command you'll notice that a new file is created within your migrations directory. This file will include a current timestamp as well as the name that you gave to your migration. The file will look like this:

CODE: https://gist.github.com/velotiotech/9c2bc81f85d8d1789fbc575394dab8f6.js

As you can notice the first is `exports.up`, which specifies the commands that should be run to make the database change that you'd like to make.e.g creating database tables, adding or removing a column from a table, changing indexes, etc.

The second function within your migration file is `exports.down`. This functions goal is to do the opposite of what exports.up did. If `exports.up` created a table, then `exports.down` will drop that table. The reason to include `exports.down` is so that you can quickly undo a migration should you need to.

For example:

CODE: https://gist.github.com/velotiotech/2c4832d4a5322e9d8c522c409f169f09.js

It’s that simple to create the migration. Now you can run your migration like below.

CODE: https://gist.github.com/velotiotech/f9deefa8a7111acb10636c60e8e39dad.js

You can also pass the `--env` flag or set `NODE_ENV` to select an alternative environment:

CODE: https://gist.github.com/velotiotech/4a5a7db7790842ef9bad3834cd9fb788.js

To rollback the last batch of migrations:

CODE: https://gist.github.com/velotiotech/13699105fffd1dc1ae77983f85066b1c.js

Models

Models are wrappers around the database tables, they help to encapsulate the business logic within those tables.

Objection.js allows to create model using ES classes.

Before diving into the example you guys need to clear your thoughts regarding model little bit as Objection.js Model does not create any table in DB. Yes! the only thing Models are used for are adding the validations and relationship mapping.  

For example:

CODE: https://gist.github.com/velotiotech/df9c6900d73fb0340fd6acd42d67dbfb.js

  • Now let’s break it down, that static getter `tableName` return the table name.
  • We also have a second static getter method that defines the validations of each field and this is an optional thing to do. We can specify the required properties, type of the field i.e. number, string, object, etc and other validations as you can see in the example.
  • Third static getter function we see is `relationMappings` which defines this models relationship to other models. In this case, the key of the outside object `pets` is how we will refer to the child class. The join property in addition to the relation type defines how the models are related to one another. The from and to properties of the join object define the database columns through which the models are associated. The modelClass passed to the relation mappings is the class of the related model.

So here `Person` has `HasManyRelation` with `Animal` model class and join is performed on persons `id` column and Animals `ownerId` column. So one person can have multiple pets.

Queries

Let’s start with simple SELECT queries:

CODE: https://gist.github.com/velotiotech/192026fee914a2f1a63f743f39ddb081.js

Can be done like:

CODE: https://gist.github.com/velotiotech/338fc8de90f2e37aa905b5386e7fb609.js

Little advanced or should I say typical select query:

CODE: https://gist.github.com/velotiotech/d8cc224cfa591b81ee90386e2632595b.js

Can be done like:

CODE: https://gist.github.com/velotiotech/248c4eb19da22e88b8e0926d9a0932b8.js

So we can look how much objection queries resemble to the actual SQL queries so it’s always easy to transform SQL query easily into Objection.js one which is quite difficult with other ORMs.

INSERT Queries:

CODE: https://gist.github.com/velotiotech/a03747504c6c1b0de468b700abf1b576.js

Can be done like:

CODE: https://gist.github.com/velotiotech/c35845e7171a580f13af208ac8ed4f58.js

UPDATE Queries:

CODE: https://gist.github.com/velotiotech/fa72df4661ae505ab8095256f02b9491.js

Can be done like:

CODE: https://gist.github.com/velotiotech/5a13d138886f21336f7d6f74104af08d.js

DELETE Queries:

CODE: https://gist.github.com/velotiotech/705a17532b0fb9e239fc578665d9e65f.js

Can be done like:

CODE: https://gist.github.com/velotiotech/bffa5ea5c63f984f6a1433808dfd23ef.js

Relationship Queries:

Suppose we want to fetch all the pets of Person whose first name is Ben.

CODE: https://gist.github.com/velotiotech/0cf8420aecb9a3687faf7b0cd4f955ac.js

Now suppose you want to insert person along with his pets. In this case we can use the graph queries.

CODE: https://gist.github.com/velotiotech/ce11cb75fb628e562efc4a0fd35549a6.js

So here we can see the power of Objection queries and if try to compare these queries with other ORM queries you will find out the difference yourself which is better.

Plugin Availability

objection-password: This plugin automatically adds automatic password hashing to your Objection.js models. This makes it super-easy to secure passwords and other sensitive data.

objection-graphql: Automatic GraphQL API generator for objection.js models.

Verdict

I am having fun time working with Objection and Knex currently! If you ask me to choose between sequalize and objection.js I would definitely go with objection.js to avoid all the relationship queries pain. It’s worth noting that Objection.js is unlike your other ORM’s, it’s just a wrapper over the KNEX.js query builder so its like using query builder with additional features.

Get the latest engineering blogs delivered straight to your inbox.
No spam. Only expert insights.
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.

Did you like the blog? If yes, we're sure you'll also like to work with the people who write them - our best-in-class engineering team.

We're looking for talented developers who are passionate about new emerging technologies. If that's you, get in touch with us.

Explore current openings

A Step Towards Simplified Querying in NodeJS

Recently, I came across a question on StackOverflow regarding querying the data on relationship table using sequalize and I went into flashback with the same situation and hence decided to write a blog over a better alternative Objection.js. When we choose ORM’s without looking into the use case we are tackling we usually end up with a mess.

The question on StackOverflow was about converting the below query into sequalize query.

CODE: https://gist.github.com/velotiotech/9db15da501318b19cbfb07291e316cbb.js

(Pardon me for naming in the query, it was asked by novice programmer and I wanted to keep it as it is for purity sake).

Seems pretty straightforward right? So the solution is like below:

CODE: https://gist.github.com/velotiotech/d67bc427063601d9618bece60335959f.js

If you look at this it’s much complex solution for simple querying and this grows with added relationships. And also for simple queries like this, the sequalize documentation is not sufficient. Now if you ask me how it can be done in a better way with Objection.js below is the same query in objection.

CODE: https://gist.github.com/velotiotech/40c44f3020906ff84710054737221e67.js

Note: It’s assumed that relationship is defined (in model classes) in both examples.

Now you guys can see the difference this is just one example I came across there are others on the internet for better understanding. So are you guys ready for diving into Objection.js?

But before we dive in, I wanted to let you guys know whenever we check online for Node.js ORM, we always find some people saying “don’t use an ORM, just write plain SQL” and they are correct in their perception. If your app is small enough that you can write a bunch of query helper functions and carry out all the needed functionality, then don't go with ORM approach, instead just use plain SQL.

But when your app has an ample amount of tables and relationships between them that need to be defined and multiple-joint queries need to done, there comes the power of ORM.

So when we search for the ORM's (For relational DB) available in NodeJS arena we usually get the list below:

1. Sequelize

2. Objection.js

3. typeORM

There are others, I have just mentioned more popular ones.

Well, I have personally used both Sequelize and Objection.js as they are the most popular ORM available today. So if you are a person who is deciding on which ORM you should be using for your next project or got frustrated with the relationship query complexity of `Sequelize` then you have landed on the correct place.

I am going to be honest here, I am using Objection.js currently doesn't make it the facto or best ORM for NodeJS. If you don't love to write the SQL resembling queries and prefer the fully abstracted query syntax then I think `Sequelize` is the right option for you (though you might struggle with relationship queries as I did and land up with Objection.js later on) but if you want your queries to resemble the SQL one then you should read out this blog.

What Makes Objection So Special?

1. Objection under the hood uses KNEX.JS a   powerful SQL query builder

2. Let’s you create models for tables with ES6 / ES7 classes and define the relationships between them

3. Make queries with async / await

4. Add validation to your models using JSON schema

5. Perform graph inserts and upserts

to name a few.

The Learning Curve

I have exclusively relied upon the documentation. The Knex.js and objection.js documentation is great and there are simple (One of them, I am going to use below for explanation) examples on the Objection GitHub. So if you have previously worked with any NodeJS ORM or you are a newbie, this will help you get started without any struggles.

So let’s get started with some of the important topics while I explain to you the advantages over other ORM and usage along the way.

For setup (package installation, configuration, etc.) and full code you can check out Github

Creating and Managing DB Schema

Migration is a good pattern to manage your changes database schema. Objection.js uses knex.js migration for this purpose.

So what is Migration : Migrations are changes to a database’s schema specified within your ORM, so we will be defining the tables and columns of our database straight in JavaScript rather than using SQL.

One of the best features of Knex is its robust migration support. To create a new migration simply use the knex cli:

CODE: https://gist.github.com/velotiotech/1d42ac9abfc44bd98228715e8973cf5e.js

After running this command you'll notice that a new file is created within your migrations directory. This file will include a current timestamp as well as the name that you gave to your migration. The file will look like this:

CODE: https://gist.github.com/velotiotech/9c2bc81f85d8d1789fbc575394dab8f6.js

As you can notice the first is `exports.up`, which specifies the commands that should be run to make the database change that you'd like to make.e.g creating database tables, adding or removing a column from a table, changing indexes, etc.

The second function within your migration file is `exports.down`. This functions goal is to do the opposite of what exports.up did. If `exports.up` created a table, then `exports.down` will drop that table. The reason to include `exports.down` is so that you can quickly undo a migration should you need to.

For example:

CODE: https://gist.github.com/velotiotech/2c4832d4a5322e9d8c522c409f169f09.js

It’s that simple to create the migration. Now you can run your migration like below.

CODE: https://gist.github.com/velotiotech/f9deefa8a7111acb10636c60e8e39dad.js

You can also pass the `--env` flag or set `NODE_ENV` to select an alternative environment:

CODE: https://gist.github.com/velotiotech/4a5a7db7790842ef9bad3834cd9fb788.js

To rollback the last batch of migrations:

CODE: https://gist.github.com/velotiotech/13699105fffd1dc1ae77983f85066b1c.js

Models

Models are wrappers around the database tables, they help to encapsulate the business logic within those tables.

Objection.js allows to create model using ES classes.

Before diving into the example you guys need to clear your thoughts regarding model little bit as Objection.js Model does not create any table in DB. Yes! the only thing Models are used for are adding the validations and relationship mapping.  

For example:

CODE: https://gist.github.com/velotiotech/df9c6900d73fb0340fd6acd42d67dbfb.js

  • Now let’s break it down, that static getter `tableName` return the table name.
  • We also have a second static getter method that defines the validations of each field and this is an optional thing to do. We can specify the required properties, type of the field i.e. number, string, object, etc and other validations as you can see in the example.
  • Third static getter function we see is `relationMappings` which defines this models relationship to other models. In this case, the key of the outside object `pets` is how we will refer to the child class. The join property in addition to the relation type defines how the models are related to one another. The from and to properties of the join object define the database columns through which the models are associated. The modelClass passed to the relation mappings is the class of the related model.

So here `Person` has `HasManyRelation` with `Animal` model class and join is performed on persons `id` column and Animals `ownerId` column. So one person can have multiple pets.

Queries

Let’s start with simple SELECT queries:

CODE: https://gist.github.com/velotiotech/192026fee914a2f1a63f743f39ddb081.js

Can be done like:

CODE: https://gist.github.com/velotiotech/338fc8de90f2e37aa905b5386e7fb609.js

Little advanced or should I say typical select query:

CODE: https://gist.github.com/velotiotech/d8cc224cfa591b81ee90386e2632595b.js

Can be done like:

CODE: https://gist.github.com/velotiotech/248c4eb19da22e88b8e0926d9a0932b8.js

So we can look how much objection queries resemble to the actual SQL queries so it’s always easy to transform SQL query easily into Objection.js one which is quite difficult with other ORMs.

INSERT Queries:

CODE: https://gist.github.com/velotiotech/a03747504c6c1b0de468b700abf1b576.js

Can be done like:

CODE: https://gist.github.com/velotiotech/c35845e7171a580f13af208ac8ed4f58.js

UPDATE Queries:

CODE: https://gist.github.com/velotiotech/fa72df4661ae505ab8095256f02b9491.js

Can be done like:

CODE: https://gist.github.com/velotiotech/5a13d138886f21336f7d6f74104af08d.js

DELETE Queries:

CODE: https://gist.github.com/velotiotech/705a17532b0fb9e239fc578665d9e65f.js

Can be done like:

CODE: https://gist.github.com/velotiotech/bffa5ea5c63f984f6a1433808dfd23ef.js

Relationship Queries:

Suppose we want to fetch all the pets of Person whose first name is Ben.

CODE: https://gist.github.com/velotiotech/0cf8420aecb9a3687faf7b0cd4f955ac.js

Now suppose you want to insert person along with his pets. In this case we can use the graph queries.

CODE: https://gist.github.com/velotiotech/ce11cb75fb628e562efc4a0fd35549a6.js

So here we can see the power of Objection queries and if try to compare these queries with other ORM queries you will find out the difference yourself which is better.

Plugin Availability

objection-password: This plugin automatically adds automatic password hashing to your Objection.js models. This makes it super-easy to secure passwords and other sensitive data.

objection-graphql: Automatic GraphQL API generator for objection.js models.

Verdict

I am having fun time working with Objection and Knex currently! If you ask me to choose between sequalize and objection.js I would definitely go with objection.js to avoid all the relationship queries pain. It’s worth noting that Objection.js is unlike your other ORM’s, it’s just a wrapper over the KNEX.js query builder so its like using query builder with additional features.

Did you like the blog? If yes, we're sure you'll also like to work with the people who write them - our best-in-class engineering team.

We're looking for talented developers who are passionate about new emerging technologies. If that's you, get in touch with us.

Explore current openings