Alexander Nnakwue Software engineer. React, Node.js, Python, and other developer tools and libraries.

The best TypeScript ORMs

11 min read 3340

The best TypeScript ORMs

Introduction

In building or designing software systems, most design decisions come with tradeoffs. Whether or not to use object-relational mappers (ORMs) has been a controversial subject amongst many developers, since there has always been a debate as to whether they’re really worth it or not.

In some cases, while poorly-written ORMs may lead to performance bottlenecks when compared with raw queries, in some other cases, ORMs can come in handy when you need quick and easy access to database methods.

ORMs also help accelerate development times, which of course leads to better developer productivity. The maintainability of your code base will likely also see an overall improvement, since there is an easier interface in terms of database APIs to interact with it.

ORMs also serve as an important tool to have when starting out an application from scratch: developers can quickly bootstrap and iterate on the initial working functionalities of a backend application in a shorter amount of time.

In this post, we are going to explore a couple of popular ORMs used in the TypeScript landscape, and also discuss the following:

How to define ORMs practically

ORMs help us deal with the complexities associated with the data layer or models in our applications. With ORMs, developers can easily perform database read and write operations by calling specific methods directly on the instances of model classes. This, in a way, is a more convenient approach and comes closer to the mental model developers are used to when thinking about data manipulation.

With ORMs, developers can manage models or database schema by mapping rows in database tables to classes (or instances of these classes). Database logic, which handles all the required methods for interacting with the database, are abstracted away into objects or classes, in a way that is, most times, highly reusable. Therefore, we can enforce a kind of separation of concerns, which can be quite useful.

But before we proceed, let us ask ourselves these very pertinent questions: Is it okay to use ORMs with TypeScript? What would necessitate an ORM approach in accessing our data models or querying our database?

We’ll answer these questions and cover all the pros and cons of using ORMs in general.

Should we use ORMs in our TypeScript codebases?

ORMs are not a silver bullet. They are not all perfect for all use cases. It remains our duty as developers to pick the right tradeoffs for the job when needs arise.

We made a custom demo for .
No really. Click here to check it out.

In my own experience, ORMs should not be the immediate go-to solution for every database access layer problem. In general, it depends on the specific problem a developer is trying to solve, the tradeoffs they can afford to make, and the scope of their data layer.

While ORMs may make it seem easier to build out queries because of the higher level of abstraction involved, they are usually harder to migrate away from and therefore not feasible in the long-term. Also, they cannot be relied upon for all use cases because not all problems can be fixed by the kind of ORM queries or APIs that are supported or available.

Additionally, with poorly-designed ORMs, expensive SQL queries are usually generated under the hood. This has the potential to drastically slow down an application and take a huge hit on the overall performance.

Benefits of using ORMs

On the other hand, ORMs have their place in spite of the arguments against using them. One of the greatest arguments for using ORMs is that the layer of database abstraction they do provide makes switching databases easier, by helping to create a consistent pattern of accessing the data layer of our application. This means that we can easily manipulate our application’s data layer in a predictable manner. And with query builders (improving raw queries by providing additional inbuilt methods), developers can more easily and quickly ramp up to speed in writing queries, therefore leading to a greater productivity boost when compared with writing raw database queries.

As an application gets bigger, writing large, raw queries gets more complex and complicated, and the queries themselves become difficult to comprehend — never mind that there exist multiple ways of writing same raw queries based on the developer, which can even make it more difficult to extend, most especially for new team members. This is where ORMs come to our rescue!

The Prisma docs highlight an interesting argument on the use of ORMs, query builders, and raw queries in terms of the productivity level and the level of control it allows developers. With raw queries, developers have total control over the quality and complexity of the queries they write.

However, a question arises. How secure is it to run those queries against database schemas? Are these queries secure enough to prevent the popular SQL injection attacks, for example? What is the maintenance burden like to construct large, complex queries, like multiple joins? Well, that depends on the experience and expertise of the programmer implementing that solution.

Therefore, with raw queries, developers get a lower productivity count, but more control over how they interact with their data layer. They also have total control over how performant the kind of queries they write are.

However, with ORMs, the idea is that developers need not care about figuring out complicated SQL queries are or needing to massage query results to fit their needs. Instead, that attention should be paid to refining the data they need to implement features.

Picking the best TypeScript ORMs

Choosing an ORM for your TS projects can be challenging because there are many available options. They vary in their design and their level of abstractions. Some query builders and ORMs also offer additional features, like sanitization and type safety, or they can abstract away a lot of things, giving developers less to worry about.

Using ORMs is more popular among developers than not these days, and now there are several libraries to choose from as well. In choosing an ORM to use for our application, our aim is to look at several key factors and examine them broadly. We are going to cover their features, the extent of their documentation, how well they perform, their level of community support, and maintenance metrics.

The best TypeScript ORMs: Prisma

Prisma is an auto-generated and type safe query builder for both TypeScript and Node.js applications. It is an open source, next-generation ORM that allows developers to easily manage and interact with their database. It has a huge, supportive community of many open-source contributors and maintainers.

Features

Prisma is both a SQL/relational and NoSQL-oriented type of ORM with current support for PostgreSQL, MYSQL, MicrosoftSQL server, SQLite, CockroachDB and MongoDB.

Prisma also supports most technologies in the JavaScript ecosystem, including REST API patterns, GraphQL, gRPC, and most backend frameworks in the JS and TS landscape, like Express.

When it comes to features, Prisma is quite impressive. It offers:

  • A Prisma client
  • A type safe ORM that allows developers to think about how they work with their data (rather than how to write raw queries)
  • A migration tool that’s useful for running database migrations as the schema evolves, which happens over time during development and production
  • A GUI to play with data in the database, useful also for development

Prisma is batteries-included in that it allows developers to define their application models in a data-modeling language. It also contains an effortless way to connect to your database of choice and defines a generator. An example of a Prisma schema file from the Prisma docs is shown below:

datasource db { 
provider = "postgresql" 
  url      = env("DATABASE_URL") 
} 

generator client { 
  provider = "prisma-client-js" 
} 

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

With the above, we can configure three things, including:

  1. The data source, which specifies the database connection
  2. A generator, which indicates that we intend to generate a Prisma Client
  3. A data model, which defines the application model

The Prisma data model

To get a data model in Prisma, there are two major workflows. They include:

  1. Writing the data model and mapping it to the database with the Prisma Migrate command
  2. Generating the data model by introspecting a database

Once the data model is defined, the Prisma Client can be generated, which will expose the CRUD operations and queries for the defined models. With TypeScript, we can also get the full type-safe benefits for all queries.

To install @prisma/client with npm, we can run the following command:

npm install @prisma/client 

After a change is made to the data model, you’ll also need to regenerate the Prisma Client manually. We can do so by running:

prisma generate

We can import the client code as shown below.

import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient() 

Now, we can send queries via the generated Prisma Client API. Here is a sample query below:

// Run inside async function 
const allUsers = await prisma.user.findMany() 

More details on the available operations can be found in the Prisma Client API reference. The Prisma documentation is also mature, with extensive details on the roadmap, the limitations, the API reference, example project, FAQs, and so on.

TypeScript configuration

To make use of Prisma with TypeScript, make sure you are using TypeScript version ≥ 3.3. The tsconfig.json file should look like this below:

{
  "compilerOptions": {
    "sourceMap": true,
    "outDir": "dist",
    "target": "ES2018",
    "module": "commonjs",
    "strict": true,
    "lib": ["esnext"],
    "esModuleInterop": true
  },
  "exclude": ["dist", "prisma", "tests"]
}

Finally, go ahead and install the following npm packages as development dependencies for Prisma to work with Typescript:

npm install ts-node ts-node-dev typescript --save-dev

The best TypeScript ORMs: TypeORM

TypeORM is an open source ORM that runs in Node.js and in the browser. It supports TypeScript and all latest JavaScript applications. The goal of the project is to provide additional features for small applications to large scale enterprise applications.

According to the documentation, it supports both the Active Record and Data Mapper patterns, which is unlike other JavaScript ORMs. This support means developers can write high quality, loosely coupled, scalable, maintainable, production-ready applications.

Features

TypeORM has a rich feature list in their documentation. Some of the popular ones include the CLI, query caching, connection pooling, and support for Hooks. It also comes with a decorator API with an extensive reference.

TypeORM also supports MySQL, MariaDB, PostgreSQL, CockroachDB, SQLite, Microsoft SQL Server, SQL.js, and Oracle, in addition to basic MongoDB support. It also supports migrations, relations, and indices; with TypeORM, we can migrate from using Sequelize directly. Finally, it has a huge, supportive community with many open-source contributors and maintainers.

TypeScript configuration

To make use of TypeORM with TS, make sure you are using TypeScript version ≥ 3.3 and have enabled the following settings in the tsconfig.json file:

{
  "compilerOptions": {
    "sourceMap": true,
    "outDir": "dist",
    "target": "ES2018",
    "module": "commonjs",
    "strict": true,
    "lib": ["esnext"],
    "esModuleInterop": true,
    "emitDecoratorMetadata": true, 
    "experimentalDecorators": true, 
  },

Then, go ahead and install the npm package:

npm install typeorm --save 

Install the reflect-metadata shim:

npm install reflect-metadata --save 

Make sure to import it somewhere in the global namespace of your app, for example in the app.ts or database connection file.

You may also need to install Node typings, which you can do by running:

npm install @types/node --save-dev 

The ormconfig.json file with the database connection configuration is shown below.

{ 
   "type": "mysql", 
   "host": "localhost", 
   "port": 3306, 
   "username": "test", 
   "password": "test", 
   "database": "test", 
   "synchronize": true, 
   "logging": false, 
   "entities": [ 
      "src/entity/**/*.ts" 
   ], 
   "migrations": [ 
      "src/migration/**/*.ts" 
   ], 
   "subscribers": [ 
      "src/subscriber/**/*.ts" 
   ] 
} 

Next, we can go ahead to set up the connection to the database.

import "reflect-metadata"; 
import { createConnection } from "typeorm"; 

createConnection({ 
    type: "mysql", 
    host: "localhost", 
    port: 3306, 
    username: "root", 
    password: "admin", 
    database: "test", 
    entities: [ 
        __dirname + "/entity/*.js" 
    ], 
    synchronize: true, 
    logging: false 
}).then(connection => { 
    // here you can start to work with your entities 
}).catch(error => console.log(error)); 

With TypeORM, we need to decorate the model with the @Entity decorator, which means that an equivalent database table would be created for the model. We can work with entities everywhere with TypeORM, and that allows us to query our database layer.

The TypeORM data model

TypeORM models look like this:

@Entity() 
export class User { 
    @PrimaryGeneratedColumn() 
    id: number;  
    @Column() 
    firstName: string;  
    @Column() 
    lastName: string; 
    @Column() 
    age: number; 
} 

Here’s some sample domain logic, which looks like this:

const repository = connection.getRepository(User); 

const user = new User(); 
user.firstName = "Alex"; 
user.lastName = "Cage"; 
user.age = 26; 
await repository.save(user); 

const allUsers = await repository.find(); 
const firstUser = await repository.findOne(1); 
const name = await repository.findOne({ firstName: "Alex" }); 
await repository.remove(name); 

Bonus: QueryBuilder

TypeORM comes with an inbuilt QueryBuilder, which is one of its most powerful features. This allows developers to build SQL queries using an elegant and convenient syntax, execute them, and receive automatically transformed entities.

A simple example of a QueryBuilder in TypeORM:

const firstUser = await connection 
    .getRepository(User) 
    .createQueryBuilder("user") 
    .where("user.id = :id", { id: 1 }) 
    .getOne(); 

It builds the following SQL query shown below, which translates to a great raw SQL query.

SELECT 
    user.id as userId, 
    user.firstName as userFirstName, 
    user.lastName as userLastName 
FROM users user 
WHERE user.id = 1 

The best TypeScript ORMs: MikroORM

MikroORM is an open-source TypeScript ORM for Node.js based on the Data Mapper, Unit of Work, and Identity Map patterns.

Features

MikroORM has support for both SQL and NoSQL databases, including MongoDB, MySQL, MariaDB, PostgreSQL and SQLite databases. More databases can be supported via custom drivers.

It also supports a query builder for when we need to execute an SQL query without all the ORM stuff involved. In doing so, we can either compose the query ourselves, or use the query builder helper to construct the query for us.

MikroORM comes with lots of advanced features, including events and Hooks support, a schema generator, migrations, and propagation.

Installation and configuration

To install, all we need to run is the driver package for each provider of our choice, as shown below.

npm i -s @mikro-orm/core @mikro-orm/mongodb     # for mongo 
npm i -s @mikro-orm/core @mikro-orm/mysql       # for mysql/mariadb 
npm i -s @mikro-orm/core @mikro-orm/mariadb     # for mysql/mariadb 
npm i -s @mikro-orm/core @mikro-orm/postgresql  # for postgresql 
npm i -s @mikro-orm/core @mikro-orm/sqlite      # for sqlite 

TypeScript configuration

Next, we will need to enable support for decorators and esModuleInterop in our tsconfig.json, like before. See below:

{
  "compilerOptions": {
    "sourceMap": true,
    "outDir": "dist",
    "target": "ES2018",
    "module": "commonjs",
    "strict": true,
    "lib": ["esnext"],
    "esModuleInterop": true,
    "emitDecoratorMetadata": true, 
    "experimentalDecorators": true, 
}

MikroORM also comes with several command line tools that are extremely helpful during development, like SchemaGenerator and EntityGenerator.

To work with the CLI, we need to install the @mikro-orm/cli package locally. Note that the version needs to be aligned with the @mikro-orm/core package.

$ yarn add @mikro-orm/cli 

The best TypeScript ORMs: Sequelize

Sequelize is a well-known, Promise-based Node.js ORM that works with MySQL, MariaDB, SQLite, Microsoft SQL Server, and PostgreSQL. It has a large set of features, which means that developers love it.

Features

Sequelize comes with solid documentation, supporting lots of excellent features like database seeding, migrations, model validations, raw queries, and transaction support. Sequelize also provides its own TypeScript definitions.

Note that only TypeScript versions ≥ 4.1 are supported for now. Unfortunately, TypeScript support for Sequelize does not follow SemVer. Sequelize also heavily relies on runtime property assignments for manual type declarations to work with models, which is another con.

Although the setup with TypeScript is similar to our previous configuration guide for other ORMs, to learn about more about how to setup your typescript projects with Sequelize, have a look at our previous post on this topic.

To avoid clashes with different Node versions, the typings for Node are not included in our demo. You must install @types/node manually yourself if you’d like to try it out. More details can be found here.

Installation and configuration

Sequelize is available via npm and Yarn. To install, we can run:

# using npm 
npm i sequelize 
npm i @sequelize/core 

# using yarn 
yarn add sequelize 
yarn add @sequelize/core

We will also have to manually install the driver for our database of choice. To connect to the database, we must create a Sequelize instance. We can either pass the connection parameters to the Sequelize constructor, or pass a single connection URI. Both options are outlined below:

const { Sequelize } = require('@sequelize/core'); 

// Option 1: Passing a connection URI 
const sequelize = new Sequelize('sqlite::memory:') // Example for sqlite 
const sequelize = new Sequelize('postgres://user:[email protected]:5432/dbname') // Example for postgres 

// Option 2: Passing parameters separately (sqlite) 
const sequelize = new Sequelize({ 
  dialect: 'sqlite', 
  storage: 'path/to/database.sqlite' 
}); 

// Option 3: Passing parameters separately (other dialects) 
const sequelize = new Sequelize('database', 'username', 'password', { 
  host: 'localhost', 
  dialect: /* one of 'mysql' | 'mariadb' | 'postgres' | 'mssql' */ 
}); 

The best TypeScript ORMs: Objection.js

Objection.js is an SQL-friendly ORM for Node.js applications. It provides all the benefits of a SQL query builder and a powerful set of APIs for also working with relational databases.

Indeed, Objection can be said to be a relational query builder. Objection is built on top of a SQL query builder, knex. Due to this, all databases supported by Knex are equally supported by Objection.js. They include SQLite3, PostgreSQL, and MySQL.

Objection can be installed via npm or Yarn as usual. Since it uses Knex as its database access layer, we need to also install it.

npm install objection knex 
yarn add objection knex 

We also need to install one of the following, depending on the database dialect we plan to use:

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

Objection supports document-based databases, transactions, Hooks, validation, and has a growing plugin ecosystem. It also supports raw SQL queries. More details can be found on the documentation guide.

TypeScript configuration

With Objection.js we can extend the query builder with TypeScript, although it’s not fully a supported feature yet. We need to add some extra typings to the custom query builder. To do so, all we need to do is define a BaseModel — just once. See below from the documentation.

import { Model } from 'objection';

class YourQueryBuilder<M extends Model, R = M[]> extends QueryBuilder<M, R> {
  // Make sure to change the name of the query builder classes. 
  ArrayQueryBuilderType!: MyQueryBuilder<M, M[]>;
  SingleQueryBuilderType!: MyQueryBuilder<M, M>;
  NumberQueryBuilderType!: MyQueryBuilder<M, number>;

  customMethod(something: number): this {
    //use function;
  }
}

class BaseModel extends Model {
  QueryBuilderType!: YourQueryBuilder<this>;
  static QueryBuilder = YourQueryBuilder;
}

After this, we can then inherit from the BaseModel with the defined query builder. More details in the documentation.

Conclusion

In TypeScript, while we have a diversity of ORMs to choose from, some are easier to interact with via a nicer API interface layer, while others offer better performance and more highly optimized and intuitive queries.

A compelling argument for anyone using ORMs would be that, overall, they save lots of development time and time performing maintenance tasks, which are usually boring and repetitive. Also, the ability to easily switch database schemas and access them seamlessly in minutes — without needing to learn all the data models or database internals — is indeed a big win for ORMs.

Developers should be able to ask for the data they need instead of having to worry about writing queries, and an abstraction that makes the right decisions for them can be a powerful help. In some cases, however, this can mean that the abstraction imposes certain constraints that can make our queries slow and cumbersome, which is where many of the problems with ORMs arise.

In summary, ORMs are more useful for applications that have simple data access patterns, for example, applications without complex queries. For simple CRUD applications and those requiring simple queries, ORMs might be a handy tool for the job. On the other hand, if we desire speed as a priority in terms of query performance, then using ORMs would not be beneficial in that regard.

Writing a lot of TypeScript? Watch the recording of our recent TypeScript meetup to learn about writing more readable code.

TypeScript brings type safety to JavaScript. There can be a tension between type safety and readable code. Watch the recording for a deep dive on some new features of TypeScript 4.4.

Alexander Nnakwue Software engineer. React, Node.js, Python, and other developer tools and libraries.

Leave a Reply