9 Best JavaScript and TypeScript ORMs for 2021

This article will briefly explain what object relational mapping (ORM) is, what an ORM library is, and why you should consider using one with your next JavaScript project. We’ll also help you evaluate the best JavaScript and TypeScript ORM libraries based on your needs as a project developer and maintainer.

We’ll look at each of the following tools:

• Knex.js: SQL Query Builder
• Sequelize
• Bookshelf
• Waterline
• Objection.js
• Mongoose
• Typegoose
• TypeORM
• MikroORM
• Prisma

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

ORM Libraries

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.

I’ve ordered the list based on launch date from the earliest to the newest. I’ve split the list into two sections based on the primary supported language: JavaScript and TypeScript.

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
• Website
• GitHub: Used by 158.6k
• Databases: Postgres, MSSQL, MySQL, MariaDB, SQLite3, Oracle, and Amazon Redshift

Knex.js is currently the most mature JavaScript SQL Query builder that can run in both Node.js and the browser (via webpack or Browserify). It’s capable of generating highly performant SQL queries that are on par with manually written SQL statements.

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
• migration
• seeding

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 } 

In the above TypeScript example, Knex.js is almost acting like an ORM. However, entity object instances aren’t being created. Instead, the interface definition is being used to create JavaScript objects with type-safe properties.

Do note a number of ORM libraries listed in this article use Knex.js under the hood. These include:

• Bookshelf
• Objection.js
• MikroORM

ORM libraries often provide additional features on top of Knex.js. Let’s have a look at them in the next section.

JavaScript ORM Libraries

In this category, all libraries listed here are written in JavaScript and can run directly in Node.js. TypeScript support is provided either through built-in types or through @types/node definitions package. If you want first-class support for TypeScript projects, you should skip to the TypeScript ORM Libraries 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.

Sequelize

• Launch: July 2010
• Website
• GitHub: used by 271k
• Slack
• 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', { host: 'localhost', dialect: /* one of 'mysql' | 'mariadb' | 'postgres' | 'mssql' */ }); // 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.

Bookshelf

• Launch: March, 2013
• Website
• GitHub: Used by 22.4k
• Plugins
• Databases : PostgreSQL, MySQL, and SQLite3

Bookshelf is one of the oldest and most basic ORM JavaScript library we have available. It’s built on top of Knex.js SQL Query Builder, and it takes a lot of ideas from the Data Mapper pattern. It provides additional features, such as:

• 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 checkit.

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

Waterline

• Launch: May 2013
• Website
• GitHub: Used by 8.5k
• Documentation
• 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.

Continue reading 9 Best JavaScript and TypeScript ORMs for 2021 on SitePoint.