Object-Relational Mapping (ORM) is a technique that consists in encapsulate the code needed to manipulate the data in your database, so you don't use SQL anymore; you interact directly with an interface in the same language you're using.
When people talk about an ORM, they usually make reference to a library that implements this technique. You can find a great explanation in the following link from StackOverflow.
On the other hand, as it name claims, a query builder is an interface that allows you to write SQL in your prefered language. Main difference with ORMs is that you don't have to define models structure for a query builder because you are not working with objects that represents your data.
This research objective isn't to claim which is the best ORM out there but to provide an objective vision about these libraries most important features, performance, community, documentation, and more.
- Sequelize: It features solid transaction support, relations, eager and lazy loading, read replication and more. It is one of the most complete ORMs for Node.js. It has support for Postgres, MySQL, MariaDB, SQLite and Microsoft SQL Server.
- Knex: It's a very powerful query builder with transactions support. It hasn't all features an ORM may has but its performance it's quite better. Postgres, MSSQL, MySQL, MariaDB, SQLite3, Oracle, and Amazon Redshift.
- TypeORM: Its goal is to always support the latest JavaScript features and provide additional features that help you to develop any kind of application that uses databases. It supports MySQL, MariaDB, Postgres, CockroachDB, SQLite, Microsoft SQL Server, Oracle and MongoDB NoSQL.
- Objection: It's build on Knex, thus supports the same databases. It has all the benefits of an SQL query builder but also a powerful set of tools for working with relations, for this it can be considered an ORM.
Other libraries were considered for this research but we discarded them, I’m going to do a special mention for the following:
- Bookshelf: Another query builder based on Knex, like Objection. After comparing both, we opted for Objection because we considered that its API implementation is better and a preliminary performance tests showed has the best performance.
- Waterline: It’s Sails.js ORM library. Although it seems very interesting, there is a lack of documentation about standalone implementation. Also we couldn’t find any good examples out there about a project implementing it outside Sails environment.
For research purposes, we had to define a database schema. We wanted to keep it simple but also explore models relations API of each library. For that reason we decided to implement a simple ManyToMany relation. We used Postgres as database engine.
Above you can see database schema graph and its definition.
Table orders {
id int [pk]
user string
date timestamp
}
Table items {
id int [pk]
name string
value float
}
Table order_items {
order_id int [ref: > orders.id]
item_id int [ref: > items.id]
}
In order to have where implement our study libraries we developed a simple Express.js application with two endpoints. Main idea is to contrast how these libraries behave in most common use cases, check performance, compare implementation complexity, etc.
# ⬇️ Get all orders
#
# If query string 'simple' is set to "true", then app will return
# just the records from "orders" table. Otherwise, we are going to
# receive orders array with its nested relations.
GET /<orm_name>/orders?simple=<boolean>
# ⬆️ Create orders
#
# This endpoint receives an array of orders and create them.
# Orders could include nested relations, in that cases app should
# hanlde it and create them too.
POST /<orm_name>/orders # Create many orders with their items
The key on this implementation is that we are managing nested resources: an order has one or many items. Payloads we receive has orders and its nested items, and we have to handle them.
After all this setup we are finally ready to implement our libraries and start having some results.
First, we can look at NPM packages and their metrics to have a clear image about how much they are being used.
| Library | Creation date | Weekly downloads | Dependents |
|---|---|---|---|
| Sequelize | Jul 22, 2010 | 446,312 | 3,139 |
| Knex | Dec 29, 2012 | 410,065 | 1,953 |
| TypeORM | Feb 29, 2016 | 168,470 | 917 |
| Objection | Apr 14, 2015 | 49,842 | 220 |
With this information, we can take some assumptions. First of all, clearly Sequelize is the most used library. It has been in the game for the longest time and its community haven’t stopped growth.
It’s interesting to see that Knex is almost as used as Sequelize despite being a less friendly solution. Besides both packages duplicated their weekly downloads this year. Objection, on the other hand, shows practically an almost null increase in their weekly downloads, keeping it around 50K downloads per week.
Finally, in the last year TypeORM has triplicated its downloads. Despite staying still well below Sequelize and Knex it seems to be positioning itself as an interesting competitor.
You can find an updated analisys about npm packages on NPM Trends 🔗.
Documentation is a really important element when you have to choose a library. On this depends how much easy or painful could be to implement it. For that reason, we analysed the next points:
- Implementation: Provides a "Getting started" guide, with examples and steps explanations about how to implement the library in my project.
- Recipes: Provides examples about how to implement solutions for different case uses (ie: relations).
- Real-world examples: Provides real-world examples, with implementations in differents frameworks.
- Straightforward: Easy to find needed information and read, updated and clear.
- TypeScript implementation: Provides documentation about TypeScript support, with examples and explanations about best practices.
| Library | Implementation | Recipes | Real-world examples | Straightforward | TypeScript |
|---|---|---|---|---|---|
| Sequelize | ✅ | ✅ | ❌ | ❌ | |
| Knex | ✅ | ❌ | ❌ | ✅ | |
| TypeORM | ✅ | ✅ | ✅ | ✅ | ✅ |
| Objection | ✅ | ✅ | ✅ | ✅ | ✅ |
Given all these libraries are used for thousands of people around the globe everyday is not strange that they document the basics on how to implement and start using they. But, for an advanced use, we need more information.
Based in our research TypeORM and Objection are the best-documented ORMs. Both has an easy to understand, straightforward and user-friendly documentation, they provide real-world examples and recipes for the most common use cases. Besides, they both provide information about TypeScript support, with examples and best practices.
Either Sequelize and Knex provide TypeScript documentation, it is not enough to implement a clean solution in our projects and requires more research.
Knex documentation is not bad, but it lacks in official recipes or real-world examples.
Although Sequelize has a very large documentation, it is difficult to find something in there specially for edge cases.
In the section above, we talk about TypeScript documentation these libraries provide
All analized libraries expose their own types we can use, but not all has a great integration with TypeScript and developing experience could be a little bit rough with some of them.
Quoting its own documentation: "As Sequelize heavily relies on runtime property assignments, TypeScript won't be very useful out of the box. A decent amount of manual type declarations are needed to make models workable.". Working with Sequelize and TypeScript could be a really bad experience when you are starting with typing in JavaScript. You have to do a lot of manual work to define your models types and made them work in strict mode.
Relationships are hard with Sequelize and TypeScript. You’ll need to add a set of mixin functions for every single association you create and on both the models involved in the association. If you want to know more about this, you can read this article which explains how to setup Sequelize with TypeScript.
Moreover, when you start deep inside sequelize functionality you will notice many optional properties you can pass some functions are not typed! For example, when you use bulkCreate optional property include (which should be defined on BulkCreateOptions interface) is not created so you have to extend Sequelize type definitions yourself in order to use it.
Remember Knex is just a query builder, so we don't define objects that represents our database tables. For this reason, we need to create interfaces for our inputs and outputs. Besides that, integration between TypeScript and Knex is acceptable.
TypeORM, name already gives us a hint. It is a perfect partner for TypeScript and is the one that exploits the most its capabilities. It allows you to write only one TypeScript Class and automatically generates all structure for your entity.
Code complexity and quantity are greatly reduced, thus our entities definitions are much more cleaner than, for example, Sequelize.
Decorators can seem strange at first sight, especially if you've never implement this concept before, but once you get used to them they are very easy to use.
Integration with TypeScript is surprisingly simple and intuitive. You define your models and Objection automatically generates all structure you need to use them. Models definition are very clean and use them is very straightforward.
Documentation is very simple and useful and you have real-world examples as guide.
Besides, it could be a good alternative if you want to avoid using TypeORM decorators syntax, and keep a more conservative sintax.
Probably one of the most controversial topics around ORMs is their performance, thus before start let me do a little disclaimer. This research doesn’t intent to convince you, or not, to use an ORM in your project, there are already a bunch of publications that talk about that. We are going to focus on just in a comparison between our selected libraries uses the use cases we presented before. That said, let's start.
To perform load tests against our application we used autocannon. It is a fast HTTP/1.1 benchmarking tool written in Node.js. All tests were benchmarked using these options:
# Load script for GET (simple)
autocannon "localhost:8080/<orm>/orders?simple=true" \
-m "GET" \
-c 100 -d 20
# Load script for GET (nested)
autocannon "localhost:8080/<orm>/orders" \
-m "GET" \
-c 100 -d 20
# Load script for POST
autocannon "localhost:8080/<orm>/orders" \
-m "POST" \
-c 100 -d 20 \
-i "./data.json" \
-H "Content-Type: application/json"
# where <orm> = knex|typeorm|sequelize|objection-cThe number of concurrent connections to use.-dThe number of pipelined requests to use.-mThe method of the requests.-iFile path for request body.-HHeaders definitions.
Test Bench Configuration:
- OS: macOs Catalina 10.15.1.
- CPU: 2,2 GHz Quad-Core Intel Core i7.
- RAM: 16 GB 1600 MHz DDR3.
- Node version: v10.17.0.
| Library | Latency avg | Latency max | Req/Sec avg | Req/Sec min | Bytes/Sec avg | Bytes/Sec min |
|---|---|---|---|---|---|---|
| Sequelize | 2201.91 ms | 2647.18 ms | 42.9 | 29 | 5.83 MB | 3.94 MB |
| Knex 🥇 | 1276.52 ms ms | 1647.52 ms | 75.91 | 58 | 10.3 MB | 7.88 MB |
| TypeORM 🥈 | 1423.56 ms | 1685.29 ms | 67.8 | 53 | 9.21 MB | 7.2 MB |
| Objection 🥉 | 1466.76 ms | 1808.73 ms | 65.66 | 47 | 8.92 MB | 6.39 MB |
| Library | Latency avg | Latency max | Req/Sec avg | Req/Sec min | Bytes/Sec avg | Bytes/Sec min |
|---|---|---|---|---|---|---|
| Sequelize | 5697.71 ms | 7124.39 ms | 14.75 | 6 | 5.1 MB | 2.07 MB |
| Knex 🥇 | 2156.48 ms | 2471.32 ms | 43.9 | 35 | 11 MB | 8.8 MB |
| TypeORM 🥉 | 2917.68 ms | 4586.61 ms | 31.9 | 14 | 8.02 MB | 3.52 MB |
| Objection 🥈 | 2902.18 ms | 3672.11 ms | 32.1 | 15 | 8.07 MB | 3.77 MB |
| Library | Latency avg | Latency max | Req/Sec avg | Req/Sec min | Bytes/Sec avg | Bytes/Sec min |
|---|---|---|---|---|---|---|
| Sequelize 🥉 | 882.75 ms | 1304.18 ms | 111.2 | 70 | 38.9 kB | 24.3 kB |
| Knex 🥈 | 693.45 ms | 1166.97 ms | 141.95 | 91 | 50.4 kB | 32.2 kB |
| TypeORM 🥇 | 477.26 ms | 2507.41 ms | 205.95 | 36 | 65.2 kB | 11.2 kB |
| Objection | 906.97 ms | 1322.14 ms | 108 | 70 | 34.1 kB | 21.9 kB |
Before running the app, make sure you have Postgresql installed installed.
You need to create project database manually, to create it run the following steps inside a psql terminal:
CREATE DATABASE db_project_name;\c db_project_nameCREATE ROLE "project_name" LOGIN CREATEDB PASSWORD 'project_name';
Don't forget to create a dotenv file for environment variables. Dotenv is used for managing environment variables. They must be stored in a /.env file. File structure is described below:
DB_HOST=localhost
DB_PORT=5432
DB_USERNAME=project_name
DB_PASSWORD=project_name
DB_NAME=db_project_name
You need to run migrations before start app. To do it simply run npm run migrate.
Run in your terminal: npm start.
In order to debug our Node.js application, we enable 'sourceMap' in tsconfig.json, this compiler option generates corresponding .map files from original Javascipt counterpart. This change is mandatory to attach a debugger, otherwise it wouldn't be able to match transpiled files with their originals.
In VSCode, you will need to add an ./.vscode/launch.json file in order to launch the debugger. You can use the following:
{
// Use IntelliSense to learn about possible attributes.
// Hover to view descriptions of existing attributes.
// For more information, visit: https://go.microsoft.com/fwlink/?linkid=830387
"version": "0.2.0",
"configurations": [
{
"type": "node",
"request": "launch",
"name": "Launch Program",
"program": "${workspaceFolder}/server.ts",
"preLaunchTask": "tsc: build - tsconfig.json",
"internalConsoleOptions": "neverOpen",
"console": "integratedTerminal",
"disableOptimisticBPs": true,
"outFiles": ["${workspaceFolder}/dist/**/*.js"]
}
]
}This project is written and maintained by Emanuel Casco and is available under the MIT license.
Copyright (c) 2019 Emanuel Casco
Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in
all copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
THE SOFTWARE.