9 Best JavaScript and TypeScript ORMs for 2021 – SitePoint

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:
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

$ 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(‘users’).select(‘id’, ‘age’);
} catch (err) {

}

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:
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

$ npm i pg pg-hstore
$ npm i mysql2
$ npm i mariadb
$ npm i sqlite3
$ npm i tedious

Below is an example of the setup code along with examples of CRUD and basic query statements:
const { Sequelize } = require(‘sequelize’);

const sequelize = new Sequelize(‘database’, ‘username’, ‘password’, {
host: ‘localhost’,
dialect:
});

const User = sequelize.define(‘User’, {

firstName: {
type: DataTypes.STRING,
allowNull: false
},
lastName: {
type: DataTypes.STRING

}
}, {

});

const jane = User.build({ firstName: “Jane”, lastName: “Doe” });
await jane.save();

const jane = await User.create({ firstName: “Jane”, lastName: “Doe” });

const users = await User.findAll();
console.log(users.every(user => user instanceof User));
console.log(“All users:”, JSON.stringify(users, null, 2));

The following is an example of how a complex query is written:

Post.findAll({
where: sequelize.where(sequelize.fn(‘char_length’, sequelize.col(‘content’)), 7)
});

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:

const projects = await sequelize.query(‘SELECT * FROM projects’, {
model: Projects,
mapToModel: true
});

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

$ npm install pg
$ npm install mysql
$ npm install sqlite3

Setup code looks like this:

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)

const User = bookshelf.model(‘User’, {
tableName: ‘users’,
posts() {
return this.hasMany(Posts)
}
})

const Post = bookshelf.model(‘Post’, {
tableName: ‘posts’,
tags() {
return this.belongsToMany(Tag)
}
})

const Tag = bookshelf.model(‘Tag’, {
tableName: ‘tags’
})

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

$ 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’},

pets: {
collection: ‘pet’,
via: ‘owner’
}
}
});

waterline.registerModel(userCollection);

Here’s a partial sample of some CRUD code:
(async ()=>{

var user = await User.create({
firstName: ‘Neil’,
lastName: ‘Armstrong’
});

var pet = await Pet.create({
breed: ‘beagle’,
type: ‘dog’,
name: ‘Astro’,
owner: user.id
});

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.
Objection.js
Launch: April 2015
Website
GitHub: Used by 5.7k
Plugins
Databases : SQLite3, Postgres and MySQL (including all Knex.js supported databases)
Objection.js is a minimal Node.js ORM library designed to stay out of your way and make is easy to access SQL databases. In this category, Objection.js is the youngest, and it appears to defeat many arguments that have been raised against the use of ORM libraries.
The Objection.js documentation is excellent. It’s well written, as you can easily find clear instructions for building your application’s data layer. The syntax is clean and easy to understand. It’s built on top of Knex.js, and has official built-in support for TypeScript. It has about everything you need in an ORM.
Looking at the numbers, it’s quite surprising Objection.js isn’t as popular at it should be. ORM libraries such as Sequelize and TypeORM do offer many more features, which may explain their popularity. However, I think the set of features the Objection.js team decided to go with is perfect for an open-source library. It means fewer bugs occur over time, and the small team are able to resolve them in good time. You can see evidence of this by looking at the issues tab, which had about 50 unresolved issues at the time of writing.
In contrast, Sequelize and TypeORM, which are bigger in terms features, have unfortunately generated a massive backlog for their maintainers. Currently, each have 1,000+ issues that haven’t been resolved and there doesn’t seem to be an increase in the number of maintainers contributing to the project.
If you’re having any doubt about picking this library, check out this testimonals link.
Let’s take a look at the installation steps and some sample code. To get started, you’ll need to install Objection.js, Knex.js and one of the database adapters:
npm install objection knex

npm install pg
npm install sqlite3
npm install mysql
npm install mysql2

The setup code is so simple it hardly needs any explanation:
const { Model } = require(‘objection’);
const Knex = require(‘knex’);

const knex = Knex({
client: ‘sqlite3’,
useNullAsDefault: true,
connection: {
filename: ‘example.db’
}
});

Model.knex(knex);

class Person extends Model {
static get tableName() {
return ‘persons’;
}

static get relationMappings() {
return {
children: {
relation: Model.HasManyRelation,
modelClass: Person,
join: {
from: ‘persons.id’,
to: ‘persons.parentId’
}
}
};
}
}

async function createSchema() {
if (await knex.schema.hasTable(‘persons’)) {
return;
}

await knex.schema.createTable(‘persons’, table => {
table.increments(‘id’).primary();
table.integer(‘parentId’).references(‘persons.id’);
table.string(‘firstName’);
});
}

async function main() {

const sylvester = await Person.query().insertGraph({
firstName: ‘Sylvester’,

children: [
{
firstName: ‘Sage’
},
{
firstName: ‘Sophia’
}
]
});

console.log(‘created:’, sylvester);

const sylvesters = await Person.query()
.where(‘firstName’, ‘Sylvester’)
.withGraphFetched(‘children’)
.orderBy(‘id’);

console.log(‘sylvesters:’, sylvesters);
}

createSchema()
.then(() => main())
.then(() => knex.destroy())
.catch(err => {
console.error(err);
return knex.destroy();
});

Here’s an example of basic query:

const person = await Person.query().findById(1);

const middleAgedJennifers = await Person.query()
.select(‘age’, ‘firstName’, ‘lastName’)
.where(‘age’, ‘>’, 40)
.where(‘age’, ‘<', 60) .where('firstName', 'Jennifer') .orderBy('lastName'); The SQL output for the basic query: select "persons".* from "persons" where "persons"."id" = 1 select "age", "firstName", "lastName" from "persons" where "age" > 40
and “age” < 60 and "firstName" = 'Jennifer' order by "lastName" asc Here’s an example of a complex query: const people = await Person.query() .select('persons.*', 'parent.firstName as parentFirstName') .innerJoin('persons as parent', 'persons.parentId', 'parent.id') .where('persons.age', '<', Person.query().avg('persons.age')) .whereExists( Animal.query() .select(1) .whereColumn('persons.id', 'animals.ownerId') ) .orderBy('persons.lastName'); console.log(people[0].parentFirstName); The SQL output for the complex query: select "persons".*, "parent"."firstName" as "parentFirstName" from "persons" inner join "persons" as "parent" on "persons"."parentId" = "parent"."id" where "persons"."age" < ( select avg("persons"."age") from "persons" ) and exists ( select 1 from "animals" where "persons"."id" = "animals"."ownerId" ) order by "persons"."lastName" asc In addition to the features Knex.js already provides, Objection.js has: official TypeScript support support for lifecycle hooks built-in validation support using JSON Schema syntax plugins The library is very well maintained. For SQL databases, Objection.js appears to be the best ORM library for your JavaScript application. Unfortunately, it doesn’t support NoSQL databases. But the next library we feature does support NoSQL databases. Mongoose If you plan on using MongoDB as your database, then Mongoose is likely going to be your ORM of choice. It’s currently the most popular ORM library in the Node.js world. Mongoose uses schema syntax to define models. Its feature list includes: built-in type casting validation query building hooks via middleware Mongoose only supports MongoDB, so installation only requires one package: npm install mongoose Below is an example of the setup code: const mongoose = require('mongoose'); mongoose.connect('mongodb://localhost/test', {useNewUrlParser: true, useUnifiedTopology: true}); const kittySchema = new mongoose.Schema({ name: { type: String, required: true } }); const Kitten = mongoose.model('Kitten', kittySchema); const fluffy = new Kitten({ name: 'fluffy' }); fluffy.save(function (err, fluffy) { if (err) return console.error(err); console.log(fluffy.name, 'saved!') }); There are two ways of defining queries in Mongoose. Below are both examples: Person. find({ occupation: /host/, 'name.last': 'Ghost', age: { $gt: 17, $lt: 66 }, likes: { $in: ['vaporizing', 'talking'] } }). limit(10). sort({ occupation: -1 }). select({ name: 1, occupation: 1 }). exec(callback); Person. find({ occupation: /host/ }). where('name.last').equals('Ghost'). where('age').gt(17).lt(66). where('likes').in(['vaporizing', 'talking']). limit(10). sort('-occupation'). select('name occupation'). exec(callback); Of course, there’s no raw SQL option, since MongoDB is a NoSQL database. MongoDB also doesn’t support transactions. If that’s important for your project, you’ll need to stick with SQL databases. One key advantage of Mongoose over all other open-source ORM libraries listed here is that it’s development is sponsored by the Tidelift platform. This means security issues are identified and patched early. One downside is that Mongoose doesn’t officially support TypeScript. Unofficially, you can use TypeScript, but it will take a bit of extra work to maintain your models. Fortunately, the next ORM library we’ll look at addresses this issue. TypeScript ORM Libraries In this category, all libraries listed here provide first-class support for TypeScript projects. You can use them in JavaScript projects, but documentation is mostly written for TypeScript code. Typegoose Typegoose is a “wrapper” for easily writing Mongoose models with TypeScript. This library solves the problem of having to maintain a separate Mongoose model and a TypeScript interface. With Typegoose, you only need to define your model schema using the Typegoose interface. Under the hood, it uses the Reflect and reflect-metadata API to retrieve the types of the properties, so redundancy can be significantly reduced. Installing Typegoose in your projects requires several packages: npm i -s @typegoose/typegoose npm i -s mongoose npm i -D @types/mongoose Below is an example of a Mongoose model written in JavaScript: const kittenSchema = new mongoose.Schema({ name: String }); const Kitten = mongoose.model('Kitten', kittenSchema); let document = await Kitten.create({ name: 'Kitty' }); Below is the same model written in TypeScript using Typegoose library: class KittenClass { @prop() public name?: string; } const Kitten = getModelForClass(KittenClass); let document = await Kitten.create({ name: 'Kitty' }); The following code sample shows the setup process and how to execute CRUD commands: import { prop, getModelForClass } from '@typegoose/typegoose'; import * as mongoose from 'mongoose'; class User { @prop() public name?: string; @prop({ type: () => [String] })
public jobs?: string[];
}

const UserModel = getModelForClass(User);

(async () => {
await mongoose.connect(‘mongodb://localhost:27017/’, { useNewUrlParser: true, useUnifiedTopology: true, dbName: “test” });

const { _id: id } = await UserModel.create({ name: ‘JohnDoe’, jobs: [‘Cleaner’] } as User);
const user = await UserModel.findById(id).exec();

console.log(user);
})();

Since Typegoose is simply a TypeScript wrapper for an ORM library, you’ll have to look at the Mongoose documentation to see how to perform CRUD tasks.
TypeORM
Launch : Feb 21, 2016
Website
GitHub: Used by 71.8k
Slack
Databases : MySQL, MariaDB, Postgres, CockroachDB, SQLite, Microsoft SQL Server, Oracle, SAP Hana, sql.js and MongoDB
TypeORM is currently the most popular ORM library built for TypeScript projects. It can run on many platforms, including:
Node.js
the browser
on mobile — Cordova, PhoneGap, Ionic, React Native and NativeScript
Electron
The library also supports both Active Record and Data Mapper patterns, allowing developers to build high-quality, scalable and maintainable database-driven applications. It’s highly influenced by other ORMs such as Hibernate, Doctrine and Entity Framework. This means developers with Java and Ruby backgrounds will feel right at home.
TypeORM is an ORM that can run in Node.js, the browser, Cordova, PhoneGap, Ionic, React Native, NativeScript, Expo, and Electron platforms, and can be used with TypeScript and JavaScript. 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 — from small applications with a few tables to large-scale enterprise applications with multiple databases.
Installing TypeORM requires installing multiple packages, including database adapters and additional TypeScript packages:
npm install typeorm –save

npm install reflect-metadata –save

npm install @types/node –save-dev

npm install mysql –save (you can install mysql2 instead as well)
npm install pg –save
npm install sqlite3 –save
npm install mssql –save
npm install sql.js –save

npm install oracledb –save

npm i @sap/hana-client
npm i hdb-pool

npm install mongodb –save

Next, you’ll need to enable the following settings in tsconfig.json:
“emitDecoratorMetadata”: true,
“experimentalDecorators”: true,

You may also need to enable es6 in the lib section of compiler options, or install es6-shim from @types.
Alternatively, instead of manually setting up a TypeORM project, you can simply use the TypeORM CLI tool to scaffold the project for you:
npm install typeorm -g
typeorm init –name MyProject –database mysql

Models can be defined using the DataMapper implementation:

import {Entity, PrimaryGeneratedColumn, Column} from “typeorm”;

@Entity()
export class User {

@PrimaryGeneratedColumn()
id: number;

@Column()
firstName: string;

@Column()
lastName: string;

@Column()
age: number;

}

const repository = connection.getRepository(User);

const user = new User();
user.firstName = “Timber”;
user.lastName = “Saw”;
user.age = 25;
await repository.save(user);

const allUsers = await repository.find();
const firstUser = await repository.findOne(1);
const timber = await repository.findOne({ firstName: “Timber”, lastName: “Saw” });

await repository.remove(timber);

Alternatively, you can use an Active Record pattern to define your models:
import {Entity, PrimaryGeneratedColumn, Column, BaseEntity} from “typeorm”;

@Entity()
export class User extends BaseEntity {

@PrimaryGeneratedColumn()
id: number;

@Column()
firstName: string;

@Column()
lastName: string;

@Column()
age: number;

}

const user = new User();
user.firstName = “Timber”;
user.lastName = “Saw”;
user.age = 25;
await user.save();

const allUsers = await User.find();
const firstUser = await User.findOne(1);
const timber = await User.findOne({ firstName: “Timber”, lastName: “Saw” });

await timber.remove();

TypeORM provides multiple ways of building queries using its own Query Builder. Here’s one of its examples:
const firstUser = await connection
.getRepository(User)
.createQueryBuilder(“user”)
.where(“user.id = :id”, { id: 1 })
.getOne();

Below is the SQL output:
SELECT
user.id as userId,
user.firstName as userFirstName,
user.lastName as userLastName
FROM users user
WHERE user.id = 1

Here’s an example of a complex query:
const posts = await connection.getRepository(Post)
.createQueryBuilder(“post”)
.where(qb => {
const subQuery = qb.subQuery()
.select(“user.name”)
.from(User, “user”)
.where(“user.registered = :registered”)
.getQuery();
return “post.title IN ” + subQuery;
})
.setParameter(“registered”, true)
.getMany();

While TypeORM seems to cover all the features required to build the data layer for your application, there are a few thorny issues you should be aware of. The most notable one is regarding performance, which has been reported and documented in this unresolved issue.
Due to the vast number of features that the library supports, the backlog of unresolved issues has piled up to significant levels, placing a heavy burden on the core maintainers. This issue has been addressed by the maintainers here, where they discuss the future of TypeORM.
Regardless, TypeORM is currently the most popular TypeScript ORM. This means finding developers familiar with the library will be easier when it comes to supporting your project in the long run. Hopefully, more contributors will join the core maintenance team and help stabilize the ORM.
MikroORM
Launch: Mar 11, 2018
Website
GitHub: Used by 206
Slack
Databases : MongoDB, MySQL, MariaDB, PostgreSQL and SQLite
MikroORM is one of the youngest Node.js TypeScript ORM entrants in this list. It supports both SQL and NoSQL databases, which is an amazing feat that not many ORMs have accomplished. It’s heavily inspired by Doctrine and Nextras ORM. Anyone familiar with these should feel right at home with MikroORM.
The library is optimized for transactions and performance through Identity Map patterns. It also supports the Data Mapper pattern. The documentation is excellent, with easy navigation to specific topics. One of the key advantages with using newer ORM libraries is that they’re designed to overcome many of the architectural issues faced by older and larger libraries.
As you go through the code samples I’ve provided, you’ll notice the syntax is much simpler to understand. This is key for building large-scale projects that will remain maintainable in the long run. Let’s now go through the installation process:
npm i -s @mikro-orm/core @mikro-orm/mongodb
npm i -s @mikro-orm/core @mikro-orm/mysql
npm i -s @mikro-orm/core @mikro-orm/mariadb
npm i -s @mikro-orm/core @mikro-orm/postgresql
npm i -s @mikro-orm/core @mikro-orm/sqlite

Next, you need to enable support for decorators and esModuleInterop in tsconfig.json:
“experimentalDecorators”: true,
“emitDecoratorMetadata”: true,
“esModuleInterop”: true,

Then call MikroORM.init as part of bootstrapping your app:
const orm = await MikroORM.init({
entities: [Author, Book, BookTag],
dbName: ‘my-db-name’,
type: ‘mongo’,
clientUrl: ‘…’,
});
console.log(orm.em);

MikroORM provides a command-line tool, @mikro-orm/cli, which you access using npx or by installing locally and accessing it like this:

$ node node_modules/.bin/mikro-orm

$ npx mikro-orm

$ yarn mikro-orm

The command-line tool helps with the development process and can help you with performing tasks such as:
schema management
importing SQL file to database
generating entities
database migration
MikroORM provides three ways of defining entity classes. Here’s one example using the reflect metadata syntax:
@Entity()
export class Book extends BaseEntity {

@Property()
title!: string;

@ManyToOne(() => Author)
author!: Author;

@ManyToOne(() => Publisher, { wrappedReference: true, nullable: true })
publisher?: IdentifiedReference;

@ManyToMany({ entity: ‘BookTag’, fixedOrder: true })
tags = new Collection(this);

}

Once you’ve defined your entities, you can use the entity manager to persist and query your data:

const author = new Author(‘Jon Snow’, ‘[email protected]’);
author.born = new Date();

const publisher = new Publisher(‘7K publisher’);

const book1 = new Book(‘My Life on The Wall, part 1’, author);
book1.publisher = publisher;
const book2 = new Book(‘My Life on The Wall, part 2’, author);
book2.publisher = publisher;
const book3 = new Book(‘My Life on The Wall, part 3’, author);
book3.publisher = publisher;

await orm.em.persistAndFlush([book1, book2, book3]);

orm.em.persist(book1);
orm.em.persist(book2);
orm.em.persist(book3);
await orm.em.flush();

const book = await orm.em.findOne(Book, 1);
book.title = ‘How to persist things…’;

await orm.em.flush();

const books = await orm.em.find(Book, {});
for (const book of books) {
console.log(book.title);
}

Querying of entities can be done via a conditions object known as FilterQuery. Here are different examples:

const users = await orm.em.find(User, { firstName: ‘John’ });

const id = 1;
const users = await orm.em.find(User, { organization: id });

const ref = await orm.em.getReference(Organization, id);
const users = await orm.em.find(User, { organization: ref });

const ent = await orm.em.findOne(Organization, id);
const users = await orm.em.find(User, { organization: ent });

const users = await orm.em.find(User, { $and: [{ id: { $nin: [3, 4] } }, { id: { $gt: 2 } }] });

const users = await orm.em.find(User, [1, 2, 3, 4, 5]);

const user1 = await orm.em.findOne(User, 1);

The library also supports:
fetching partial entities
fetching paginated results
using custom SQL fragments
To perform even more complex queries, you can use the Query Builder. Here’s some example code:
const qb = orm.em.createQueryBuilder(Author);
qb.update({ name: ‘test 123’, type: PublisherType.GLOBAL }).where({ id: 123, type: PublisherType.LOCAL });

console.log(qb.getQuery());

console.log(qb.getParams());

const res1 = await qb.execute();

Under the hood, MikroORM’s query builder uses Knex.js, which you can get access to via the qb.getKnexQuery() function. This means all the complex and raw SQL queries you want to construct and run can be performed. Hence, you get the flexibility and performance benefits of choosing MikroORM in your tech stack. The documentation on its Query Builder has many examples of query building — including different types of joins — which are too many to list here. You’ll be pleased to learn that the Query Builder provides a function for displaying its SQL output during development without enabling a debug option. Here’s an example:
const qb = orm.em.createQueryBuilder(BookTag, ‘t’);
qb.select([‘b.*’, ‘t.*’])
.leftJoin(‘t.books’, ‘b’)
.where(‘b.title = ? or b.title = ?’, [‘test 123’, ‘lol 321’])
.andWhere(‘1 = 1’)
.orWhere(‘1 = 2’)
.limit(2, 1);

console.log(qb.getQuery());

One issue of concern is that the library is quite young and the number of users are quite low. However, the library’s founder has enabled the GitHub sponsor feature, which allows them to raise funds so that they can work full-time on the project. I believe this is a better approach to open-source development than having to work part-time on a different project. By having full-time developers working on an open-source project, they can focus on maintaining the quality of the library and ensuring the backlog is kept to a minimum. I do hope that they get a major sponsor soon.
Prisma
Launch: April 2019
Website
GitHub: Used by 5.7k
Databases : PostgreSQL, MySQL, SQLite, SQL Server
Prisma is the most recent TypeScript ORM in this article. It describes itself as a “next-generation ORM” that makes working with databases easy for application developers. It provides the following tools:
Prisma Client: a client library that provides type-safe access to the database
Prisma Migrate (preview): a migration tool that autogenerates when you make changes to the schema file
Prisma Studio: a modern GUI for browsing and managing data in your database
Prisma is very different from all the other ORMs we’ve looked at. It doesn’t use object models (entity classes), but rather a schema file to map all the tables and columns. This file is used by the migration tool to generate an SQL migration file and the client library to generate type definitions. All generated type definitions are stored in a .prisma/client/index.d.ts folder. Here’s an example of the generated representation for User type:
export declare type User = {
id: string
email: string
name: string | null
}

You may have noticed that the posts reference in the model isn’t present in the TypeScript definition. The recommended solution is to create a variation of the User type like this:
import { Prisma } from ‘@prisma/client’

type UserWithPosts = Prisma.UserGetPayload<{ include: { posts: true } }>

When you write a query, your code will be checked to ensure you don’t reference a property that doesn’t exist and that you assign the correct data type for each property. When you execute the query, all results will be returned in plain JavaScript objects.
Traditional ORMs provide an object-oriented way for working with relational databases by mapping tables to model classes in your programming language. This approach leads to many problems that are caused by the object-relational impedance mismatch.
Setting up a Prisma project is a bit of a process, which you can find the full instructions here. For now, we’re simply just evaluating. Here are the basic installation steps:
npm install prisma typescript ts-node @types/node –save-dev

You’ll need to update tsconfig.json as follows:
{
“compilerOptions”: {
“sourceMap”: true,
“outDir”: “dist”,
“strict”: true,
“lib”: [“esnext”],
“esModuleInterop”: true
}
}

Start by creating your application’s data model in the schema file located at prisma/schema.prisma:
datasource db {
provider = “postgresql”
url = env(“DATABASE_UR
}

model Post {
id Int @default(autoincrement()) @id
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
title String @db.VarChar(255)
content String?
published Boolean @default(false)
author User @relation(fields: [authorId], references: [id])
authorId Int
}

model Profile {
id Int @default(autoincrement()) @id
bio String?
user User @relation(fields: [userId], references: [id])
userId Int @unique
}

model User {
id Int @default(autoincrement()) @id
email String @unique
name String?
posts Post[]
profile Profile?
}

Next, you’ll need to map your data model to the database schema using prisma migrate CLI tool:
npx prisma migrate dev –name init –preview-feature

We’ll skip ahead the installation process and look at our setup code in index.ts:
import { PrismaClient } from ‘@prisma/client’

const prisma = new PrismaClient()

async function main() {
const allUsers = await prisma.user.findMany()
console.log(allUsers)
}

main()
.catch(e => {
throw e
})
.finally(async () => {
await prisma.$disconnect()
})

Below is a sample demonstrating how to persist data and query records:
async function main() {
await prisma.user.create({
data: {
name: ‘Alice’,
email: ‘[email protected]’,
posts: {
create: { title: ‘Hello World’ },
},
profile: {
create: { bio: ‘I like turtles’ },
},
},
})

const allUsers = await prisma.user.findMany({
include: {
posts: true,
profile: true,
},
})
console.dir(allUsers, { depth: null })
}

When you run the above code, the results will be returned as JavaScript objects like this:
[
{
email: ‘[email protected]’,
id: 1,
name: ‘Alice’,
posts: [
{
content: null,
createdAt: 2020-03-21T16:45:01.246Z,
id: 1,
published: false,
title: ‘Hello World’,
authorId: 1,
}
],
profile: {
bio: ‘I like turtles’,
id: 1,
userId: 1,
}
}
]

Prisma’s documentation looks pretty, and it appears to have a lot of content. Unfortunately, I’ve found it difficult finding the information you need. Either it’s due to an over-complicated navigation system, or that specific content is missing. Information is spread over multiple sections, including:
concepts
guides
reference
support/help articles
Prisma is a newer library that follows a different philosophy on data layer building. It also appears to be growing faster than MikroORM, especially since it was launched a year later.
Conclusion
As we conclude, I’d like to briefly discuss the case against using ORM libraries in your project. The main arguments include:
bulky, inefficient queries
frustrations using a library
migration issues: keeping entity classes and the database scheme in sync
loss of type safety when using the raw SQL option
You can read all the arguments against using ORM libraries here and here.
Having looked at all current JavaScript and TypeScript ORM libraries, you should be aware that each one differs in its implementation. Most of the arguments against ORM libraries have been resolved by the newer ones, such as Object.js and Prisma. If you decide not to use an ORM library, you’ll have to decide the individual tools and libraries that make up your data layer stack.
The way I see it, choosing an ORM for your project is the best solution because of this one reason: documentation.
As developers, we’re pretty bad at documenting our own code. If we were to implement a custom solution, or implement a library that’s not well known, future maintainers would have a hard time keeping your application up to date with its business needs.
However, if you use a well-documented ORM library, it becomes much easier for them to work on your application long after you’ve left the project. This is because ORMs instill good code practices, such as architecture and patterns such as Data Mapper. And while that may introduce a learning curve, it’s better in the long run.
I hope I’ve provided useful information that can help you evaluate an ORM library for your project. If you’d like a recommendation, choose a TypeScript ORM library that’s is most suited for an enterprise-class project.

Coded at

Share your love

Leave a Reply