We’ll look at each of the following tools:
- Knex.js: SQL Query Builder
Object Relational Mapping
Object relational mapping might seem complex, but its purpose is to make your life as a programmer easier. To get data out of a database, you need to write a query. Does that mean you have to learn SQL? Well, no. Object relational mapping makes it possible for you to write queries in the language of your choice.
Object relational mapping is a technique for converting a database query result into entity class instances. An entity is simply an object wrapper for a database table. It contains attributes that are mapped to columns of a database table. Entity instances have ways of performing CRUD operations and have support for additional functions that contain custom logic such as validation and data encryption.
If you’re building a small project, installing an ORM library isn’t required. Using SQL statements to drive your application should be sufficient. An ORM is quite beneficial for medium- to large-scale projects that source data from hundreds of database tables. In such a situation, you need a framework that allows you to operate and maintain your application’s data layer in a consistent and predictable way.
Entity classes are the building blocks of business applications, as they’re designed to encapsulate logic for implementing business rules. A business rule is defined to ensure that an automated process only executes within the boundaries of a business policy. Examples of business rules include:
- customer discounts
- loan approvals
- sales commissions
- shipping and tax calculations
Object relational mapping is commonly undertaken with help of a library. The term ORM most commonly refers to an actual ORM library — an object relational mapper — that carries out the work of object relational mapping for you.
Often business rules require the execution of multiple SQL statements that need to run in batches. If a single SQL statement fails, it can leave the database in an inconsistent state. Most ORM libraries support a feature known as Transactions, which prevents such an incidents from happening. If an SQL statement fails to run within the context of a transaction, all other SQL statements that had successfully executed within that batch are reversed through an operation known as roll back.
Hence, using an ORM library to build your data layer helps ensure that the database will always remain in a consistent state. ORM libraries often contain many more essential features, such as:
- query builders
- migration scripts
- a CLI tool for generating boilerplate code
- a seeding feature for pre-populating tables with test data
In this article, I’ll provide snippets on how each ORM library does:
- initial setup and configuration
- basic CRUD operations
- advance query operations
I’ve also included important information such as the launch dates, number of users and links to documentation, and support channels if available. I’ll also be discussing important issues relating to query performance, library maintenance and architecture philosophy that you should weigh heavily when making your decision.
Before we begin our evaluation, let’s first take a look at Knex.js, a popular SQL Query Builder that comes already integrated with a number of ORM libraries listed here. Knex.js is very flexible and often performs better than some of the ORM libraries that have their own built-in implementation of a Query Builder. Consider this an advantage when choosing an ORM library that uses Knex.js as its foundation.
Knex.js: SQL Query Builder
- Launch: Dec, 2012
- GitHub: Used by 158.6k
- Databases: Postgres, MSSQL, MySQL, MariaDB, SQLite3, Oracle, and Amazon Redshift
So what’s a Query Builder?
It’s simply an API that provides a set of functions that can be chained together to form a query. Here’s an example:
knex( a: 'table', b: 'table' ) .select( aTitle: 'a.title', bTitle: 'b.title' ) .whereRaw('?? = ??', ['a.column_1', 'b.column_2']) SQL Output: select `a`.`title` as `aTitle`, `b`.`title` as `bTitle` from `table` as `a`, `table` as `b` where `a`.`column_1` = `b`.`column_2`
This begs the question of why should one use a Query Builder instead of writing raw SQL statements. I’ll give you four reasons:
- It helps you abstract your code from your database’s SQL dialect, making switching easier.
- It eliminates, or greatly reduces, the chances of SQL injection attacks on your application.
- It allows easy building of queries with dynamic conditions.
- It comes with additional features and CLI tools for performing database development operations.
Those features include:
- connection pooling
- callback and Promise interfaces
- stream interface
- transaction support
- schema support
Installing it in your application requires you to install the Knex.js package, along with the driver of the database you’re using:
$ npm install knex --save # Then add one of the following (adding a --save) flag: $ npm install pg $ npm install sqlite3 $ npm install mysql $ npm install mysql2 $ npm install oracledb $ npm install mssql
Here’s an example of setup code:
const knex = require('knex')( client: 'mysql', connection: host : '127.0.0.1', user : 'your_database_user', password : 'your_database_password', database : 'myapp_test' ); knex.schema.createTable('users', function (table) table.increments(); table.string('name'); table.timestamps(); ) Outputs: create table `users` (`id` int unsigned not null auto_increment primary key, `name` varchar(255), `created_at` datetime, `updated_at` datetime)
Here’s an example of a basic query:
knex('users').where( first_name: 'Test', last_name: 'User' ).select('id') Outputs: select `id` from `users` where `first_name` = 'Test' and `last_name` = 'User'
Raw SQL statements are also supported. Here’s an example of a complex query:
const subcolumn = knex.raw('select avg(salary) from employee where dept_no = e.dept_no') .wrap('(', ') avg_sal_dept'); knex.select('e.lastname', 'e.salary', subcolumn) .from('employee as e') .whereRaw('dept_no = e.dept_no') Outputs: select `e`.`lastname`, `e`.`salary`, (select avg(salary) from employee where dept_no = e.dept_no) avg_sal_dept from `employee` as `e` where dept_no = e.dept_no
Knex.js also supports TypeScript, which is great, as it allows you to write code like this:
import Knex, knex from 'knex' interface User id: number; age: number; name: string; active: boolean; departmentId: number; const config: Knex.Config = client: 'sqlite3', connection: filename: './data.db', , ); const knexInstance = knex(config); try const users = await knex<User>('users').select('id', 'age'); catch (err) // error handling
Do note a number of ORM libraries listed in this article use Knex.js under the hood. These include:
ORM libraries often provide additional features on top of Knex.js. Let’s have a look at them in the next section.
In the data access layer, there are two popular architectural patterns that are used:
- Data Mapper
- Active Record
With the Data Mapper pattern, entity classes are pure and only contain attributes. CRUD operations and business rules are implemented in containers known as repositories. Here’s an example:
const repository = connection.getRepository(User);. const user = new User(); user.firstName = "Timber"; await repository.save(user); const allUsers = await repository.find();
With Active record pattern, logic for CRUD operations and business rules are implemented within entity classes. Here’s a similar example implementation of the above:
const user = new User(); user.firstName = "Timber"; await user.save(); const allUsers = await User.find();
There are pros and cons of using either pattern. These patterns were named by Martin Fowler in his 2003 book Patterns of Enterprise Application Architecture. You should check the book out if you want more detailed information on the subject. Most ORM libraries listed in this article support one or both patterns.
Let’s start looking at them now.
- Launch: July 2010
- GitHub: used by 271k
- Databases: Postgres, MySQL, MariaDB, SQLite and Microsoft SQL Server
Sequelize is a very mature and popular Node.js ORM library with excellent documentation containing well explained code examples. It supports many of the data layer features that we’ve already mentioned before in previous libraries. Unlike Bookshelf, it has its own Query Builder which performs just as well as Knex.js
Installing the library is quite simple, and the database driver is quite straightforward:
$ npm i sequelize # This will install v6 # And one of the following: $ npm i pg pg-hstore # Postgres $ npm i mysql2 $ npm i mariadb $ npm i sqlite3 $ npm i tedious # Microsoft SQL Server
Below is an example of the setup code along with examples of CRUD and basic query statements:
const Sequelize = require('sequelize'); // Connect to database const sequelize = new Sequelize('database', 'username', 'password', 'postgres' ); // Create Model const User = sequelize.define('User', // Model attributes are defined here firstName: type: DataTypes.STRING, allowNull: false , lastName: type: DataTypes.STRING // allowNull defaults to true , // Other model options go here ); // Create instance const jane = User.build( firstName: "Jane", lastName: "Doe" ); await jane.save(); // save to database // Shortcut for creating instance and saving to database at once const jane = await User.create( firstName: "Jane", lastName: "Doe" ); // Find all users const users = await User.findAll(); console.log(users.every(user => user instanceof User)); // true console.log("All users:", JSON.stringify(users, null, 2));
The following is an example of how a complex query is written:
// What if you wanted to obtain something like WHERE char_length("content") = 7? Post.findAll( where: sequelize.where(sequelize.fn('char_length', sequelize.col('content')), 7) ); // SELECT ... FROM "posts" AS "post" WHERE char_length("content") = 7 // A more complex example Post.findAll( where: [Op.or]: [ sequelize.where(sequelize.fn('char_length', sequelize.col('content')), 7), content: [Op.like]: 'Hello%' , [Op.and]: [ status: 'draft' , sequelize.where(sequelize.fn('char_length', sequelize.col('content')), [Op.gt]: 10 ) ] ] );
In the last complex query example, the SQL output was:
SELECT ... FROM "posts" AS "post" WHERE ( char_length("content") = 7 OR "post"."content" LIKE 'Hello%' OR ( "post"."status" = 'draft' AND char_length("content") > 10 ) )
Sequelize supports raw SQL statements, which gives developers the flexibility to write complex and highly performant SQL statements. The results can also be mapped to object entity instances. Here’s an example:
// Callee is the model definition. This allows you to easily map a query to a predefined model const projects = await sequelize.query('SELECT * FROM projects', model: Projects, mapToModel: true // pass true here if you have any mapped fields ); // Each element of `projects` is now an instance of Project
The main downside of Sequelize is that development has slowed down and that issues have piled up without being resolved. Fortunately, one of the maintainers has announced that the library will get the attention it deserves from 2021. Do note that all ORM library projects in this article are open source and that they do need help from developers to make them better.
- Launch: March, 2013
- GitHub: Used by 22.4k
- Databases : PostgreSQL, MySQL, and SQLite3
- eager and nested-eager relation loading
- polymorphic associations
- support for one-to-one, one-to-many, and many-to-many relations.
It’s unfortunate there’s no built-in support for validation. However, it can be implemented in code via a third-party library such as
Installing Bookshelf in your project is as follows:
$ npm install knex $ npm install bookshelf # Then add one of the following: $ npm install pg $ npm install mysql $ npm install sqlite3
Setup code looks like this:
// Setting up the database connection const knex = require('knex')( client: 'mysql', connection: host : '127.0.0.1', user : 'your_database_user', password : 'your_database_password', database : 'myapp_test', charset : 'utf8' ) const bookshelf = require('bookshelf')(knex) // Define User model const User = bookshelf.model('User', tableName: 'users', posts() return this.hasMany(Posts) ) // Define Post model const Post = bookshelf.model('Post', tableName: 'posts', tags() return this.belongsToMany(Tag) ) // Define Tag model const Tag = bookshelf.model('Tag', tableName: 'tags' ) // Unfortunate example of unreadable code new User(id: 1).fetch(withRelated: ['posts.tags']).then((user) => console.log(user.related('posts').toJSON()) ).catch((error) => console.error(error) )
You’ll need to look up the Knex.js documentation to see how to perform queries and CRUD transactions. Bookshelf’s documentation doesn’t cover this.
Interestingly, Strapi, a headless CMS, uses Bookshelf as its default database connector. However, it’s worth noting the following issues:
- the documentation is not particularly helpful
- at the time of writing, the library hadn’t been updated for five months
- Launch: May 2013
- GitHub: Used by 8.5k
- Databases : Local disk/memory, MySQL, MongoDB, and Postgres(official adapters)
- Community Database Adapters: Oracle, SAP, Cassandra, IBM, Apache Derby, Redis, Solr and more
Waterline is the default ORM used by Sails.js, a Node.js framework. When using Sails.js to develop your project, the amount of code you need to write to build your own database API is greatly reduced. This is achieved using convention-over-configuration philosophy and the Blueprints API that contains boilerplate code for accessing the database and performing CRUD functions. In addition, Sails.js provides a command-line interface that helps developers generate API routes, perform migrations and other data layer functions. Typescript support is available via the Typed definitions package.
In this article, we’re going to assume you’d want to use the Waterline ORM as a standalone, which is possible. Let’s look at how to install and set it up.
Installation requires you to install the Waterline library, then one of the database adapters:
$ npm install --save waterline # Install database adapters $ npm install --save sails-mysql $ npm install --save-dev sails-disk
Here’s a partial sample of the setup code:
const Waterline = require('waterline'); const sailsDiskAdapter = require('sails-disk'); const waterline = new Waterline(); const userCollection = Waterline.Collection.extend( identity: 'user', datastore: 'default', primaryKey: 'id', attributes: id: type: 'number', autoMigrations: autoIncrement: true , firstName: type:'string', lastName: type:'string', // Add a reference to Pets pets: collection: 'pet', via: 'owner' ); waterline.registerModel(userCollection);
Here’s a partial sample of some CRUD code:
(async ()=> // First we create a user var user = await User.create( firstName: 'Neil', lastName: 'Armstrong' ); // Then we create the pet var pet = await Pet.create( breed: 'beagle', type: 'dog', name: 'Astro', owner: user.id ); // Then we grab all users and their pets var users = await User.find().populate('pets'); )()
Here’s a sample of a basic query code:
var thirdPageOfRecentPeopleNamedMary = await Model.find( where: name: 'mary' , skip: 20, limit: 10, sort: 'createdAt DESC' );
When it comes to handling complex queries, the documentation seems to be missing that part. If you plan on using Sails.js, using Waterline ORM is a no brainer. But as a standalone, the ORM library faces the following issues:
- Documentation is mixed in with Sails.js documentation.
- At the time of writing, the library package hadn’t been updated in nine months.