In Search of the Perfect TypeScript ORM

Jun 15, 2024

Roger Padilla

Originally published on Medium.

What is an ORM?

An ORM provides a simpler way to interact with databases in an app: it lets developers work with data as objects.

What is a Perfect ORM for TypeScript?

Below are the ideal features a TypeScript ORM should have, why such features are the most important ones, and how the existing ORMs fall short in most of these areas.

The top 5 features a perfect TypeScript ORM should have are:

1. Serializable Queries

Queries that can travel between the layers of a system keep the design simple. If the client can send a query to the server over HTTP or websockets using the ORM’s own syntax, you no longer need an extra query language on top: the GraphQL => ORM => Database flow collapses to ORM => Database. Concretely:

• No pseudo-language for queries and no context switching: the syntax is standard JSON, entirely declarative and serializable.
• No additional servers and no extra steps in the build process.
• Editors and IDEs understand the queries natively, without custom plugins or extensions.

2. Native TypeScript

Use TypeScript itself for everything: JSON queries, classes, and decorators.

• Type-safe queries and models that are natively validated by the same language that you use to write your app.
• Context-aware queries allow auto-completion of the appropriate operators and fields according to the different parts of a query.
• Entity definition with standard classes and decorators to avoid the need for proprietary DSLs (as happens with Prisma), extra steps in the build process, and custom extensions for the editors.

3. Multi-level Operators

Operations such as filter, sort, limit, project, and others work on any level of the queries (including relations and their fields).

4. Consistent API Across Databases

Write the queries for any database in a consistent way and then transparently optimize these queries for the configured database dialect.

5. Universal Syntax (Language Agnostic)

Because the queries are standard JSON, they can be produced or consumed from any language: JSON is a first-class format in Python, Rust, Go, and virtually everything else. That also makes UQL implementations in other languages feasible.

---

Why Do the Current Top 3 TypeScript ORMs Fall Short?

What does TypeORM lack to be a perfect ORM?

1. Lack of 100% serializable queries:

Notice how the LessThan operator is a function that has to be imported and called; that alone makes the query impossible to serialize.

import { LessThan } from 'typeorm';

const loadedPosts = await dataSource.getRepository(Post).findBy({
  likes: LessThan(10),
});

2. Lack of Native TypeScript:

The query dissolves into strings because relations and where accept any string, so any invalid string can go there.

const posts = await connection.manager.find(Post, {
  select: ['id'],
  relations: ['< anything can go here >'],
});

3. Lack of consistent API across databases:

In the section that TypeORM has for MongoDB, there is a self-explanatory warning about this.

4. Lack of Universal Syntax (language agnostic):

It relies on closures to support advanced queries (and not every language out there supports closures).

---

What does Prisma lack to be a perfect ORM?

1. Lack of Native TypeScript:

• Context-switching between the custom DSL and TypeScript makes this process obtrusive.
• Extra steps in the build process to generate the corresponding files from the custom DSL.
• It is required to install a custom extension for VS Code to get (basic) autocompletion from the editor for the DSL, which is far from being as good and reliable as with TypeScript.

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

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

model User {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  email     String   @unique
  name      String?
  role      Role     @default(USER)
  posts     Post[]
}

2. Lack of consistent API across databases:

Prisma exposes low-level details about MongoDB that could be encapsulated by the ORM.

model User {
  id String @id @default(auto()) @map("_id") @db.ObjectId
  // Other fields
}

3. Lack of Universal Syntax (language agnostic):

It relies on its own proprietary DSL to define the models.

---

What does MikroORM lack to be a perfect ORM?

1. Lack of 100% serializable queries:

Notice how that query uses two separate methods, one for the update and another for the where; that structure cannot be serialized.

const qb = orm.em.createQueryBuilder(Author);

qb.update({ name: 'test 123', type: PublisherType.GLOBAL }).where({
  id: 123,
  type: PublisherType.LOCAL,
});

2. Lack of Native TypeScript:

Dissolves into strings: any string can go inside the fields array, so the query loses the possibility of being type-safe.

const author = await em.findOne(
  Author,
  {},
  {
    fields: ['name', 'books.title', 'books.author', 'books.price'],
  }
);

3. Lack of Multi-level operators:

What if you need to filter the records of a relation or sort them? This seems unachievable in a type-safe way from what can be seen in the MikroORM docs.

4. Lack of consistent API across databases:

import { EntityManager } from '@mikro-orm/mongodb';
const em = orm.em as EntityManager;
const qb = em.aggregate(/* ... */);

MikroORM exposes low-level details about MongoDB that could be encapsulated by the ORM.

---

Why is UQL the Closest to a Perfect ORM?

In short, because it was designed from the beginning with all of the above foundations. Let’s see how.

1. 100% Serializable Queries

Even the insert and update operations have a fully serializable API.

const lastUsers = await querier.findMany(User, {
  $select: { id: true, name: true, email: true },
  $sort: { createdAt: -1 },
  $limit: 20,
});

2. Native TypeScript with Truly Type-safe Queries

Every operator and field is validated according to the context. For example, the possible values for the $select operator will automatically depend on the level.

const lastUsersWithProfiles = await querier.findMany(User, {
  $select: { id: true, name: true },
  $populate: {
    profile: { $select: { id: true, picture: true }, $required: true }
  },
  $sort: { createdAt: -1 },
  $limit: 20,
});

3. Multi-level Operators

The operators work on any level. For example, $sort and $where can be applied to the relations and their fields in a type-safe and context-aware way.

const items = await querier.findMany(Item, {
  $select: {
    id: true,
    name: true,
    measureUnit: {
      $select: { id: true, name: true },
      $where: { name: { $ne: 'unidad' } },
      $required: true,
    },
    tax: { $select: { id: true, name: true } },
  },
  $where: {
    salePrice: { $gte: 1000 },
    name: { $istartsWith: 'A' },
  },
  $sort: {
    tax: { name: 1 },
    measureUnit: { name: 1 },
    createdAt: -1,
  },
  $limit: 100,
});

4. Consistent API Across Databases

Its API is unified across different databases: the same entities and queries transparently work on any supported database, with the dialect-specific SQL (or MongoDB commands) generated under the hood. This makes it easier to switch from a Document to a Relational database (or vice-versa); the migration guide walks through what that move looks like coming from Mongoose.

5. Universal Syntax Across Languages

Its syntax is standard JSON, so queries can be produced or consumed from other languages, enabling interoperation or even implementations of UQL in languages such as Python or Rust.

---

See more at uql-orm.dev. Please star it ⭐ on GitHub if you like the idea!

---

Tags:

• orm
• typescript
• architecture

In Search of the Fastest TypeScript ORM