ORM research for TypeScript Node.js applications

Introduction

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.

Case study

Libraries

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.

Database schema

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]
}

Implementation

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.

Results

Community

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

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.

TypeScript integration

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.

Sequelize 👎

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.

Knex 👍

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 🏆

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.

Objection 🏆

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.

Performance

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

-c The number of concurrent connections to use.
-d The number of pipelined requests to use.
-m The method of the requests.
-i File path for request body.
-H Headers 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.

GET simple

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

GET nested object

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

POST nested object

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

Developing

Database configuration

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_name
CREATE 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

Migrations

You need to run migrations before start app. To do it simply run npm run migrate.

Starting app

Run in your terminal: npm start.

Debugging

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"]
    }
  ]
}

License

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.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

README.md

README.md

ORM research for TypeScript Node.js applications

Table of contents

Introduction

Case study

Libraries

Database schema

Implementation

Results

Community

Documentation

TypeScript integration

Sequelize 👎

Knex 👍

TypeORM 🏆

Objection 🏆

Performance

GET simple

GET nested object

POST nested object

Developing

Database configuration

Migrations

Starting app

Debugging

License

Files

README.md

Latest commit

History

README.md

File metadata and controls

ORM research for TypeScript Node.js applications

Table of contents

Introduction

Case study

Libraries

Database schema

Implementation

Results

Community

Documentation

TypeScript integration

Sequelize 👎

Knex 👍

TypeORM 🏆

Objection 🏆

Performance

GET simple

GET nested object

POST nested object

Developing

Database configuration

Migrations

Starting app

Debugging

License