Announcing a new TypeScript ORM
Tags: orm, node, typescript, database
Published: 2023-02-03 20:58:29

There are already a lot of ORMs for node.js with different strong and weak sides, but still, this is not enough, there is still not a single tool to cover typical needs simultaneously:

- Type safety

- Flexibility

- Easiness of use for more complex cases

- Performance

This is why [Orchid ORM](https://orchid-orm.netlify.app/) was created - to answer all of these challenges!

Before creating a new ORM, I examined 5 top popular node.js ORMs and 2 query builders to find out if there is a "go to" solution I can safely pick for any project without worrying, and have concluded there is no such tool I was looking for.

In this post, I'll cover how different ORMs and query builders are responding to expectations, and what `Orchid` can offer in comparison.


## Raw SQL vs query builders vs ORMs

But why do we need ORMs at all, isn't raw SQL or a query builder all we need?

They operate on different levels of abstraction:

- Raw SQL means writing SQL manually, with no abstraction at all. This makes it hard to work with dynamic queries. Imagine you're building an endpoint for fetching a list of posts, and the client can send various parameters: how to order, filtering, pagination, and more. It is all possible with Raw SQL, but you'll end up with a much larger amount of code, that will look messy, and be vulnerable to SQL injections unless done very carefully.

- Query Builder solves the problem of dynamic queries, but it doesn't abstract away relations. When you do a join on two tables, and if one record in the left table has many records on the right, this will cause duplications of the left table data. This is solvable by using GROUP BY, or by using a subquery, or by de-duplicating data on the JS side, but this is cumbersome. In addition, if the relationship is complex (multiple tables are involved), you may want to use it in various places, it causes duplicating of quite complex code parts.

- ORMs solve the problems above, but they are often criticized for generating inefficient code and being not flexible, while Raw SQL and Query Builders are better at this. The goal of Orchid ORM is to prove that ORM can have all the benefits of Query Builder and ORMs combined.


## Type safety

I believe that TypeScript is the best thing that happened to JavaScript. If before it was normal to be extremely careful when changing something, we had to keep everything in our head and search for all places where some object is used to update the code accordingly, now we have the luxury of TS pointing to where we need to update the code.

Ideally, ORM must be capable of:

- Preserving a correct type when selecting specific columns

- Knowing which relations were included

- Different operations are available for different types: "lower than" and "greater than" are available for numeric types, "contains", and "starts with" are available for text types, etc.

- Mixing a raw statement into a query with the ability to specify its type

Most of the ORMs always have a full record as a returned type, ignoring selecting a subset of columns. This is done by design in OOP-inspired ORMs, where you have an "Entity" and it must be always fully loaded. So for some people, this point might be not a problem, but a valid and preferred approach. But for me, an ideal ORM allows you to select only what is needed and preserve the correct types. Among all popular ORMs in node.js, only Prisma serves this purpose well.

Knowing which relations were included: slowly but steadily things are improving, and some ORMs are gaining this ability. `MikroORM` gained this last year, and `Sequelize` has an actively developed alpha version where it probably can do this.

Mixing a raw statement into a query with the ability to specify its type: as far as I know, not a single ORM can do this.

Let me share a use case to demonstrate how Orchid ORM is solving the points from above:

Imagine two tables, posts, and likes. Users can like the post.

We want to load multiple posts with specific fields (point #1).

If this is an authorized request we want to load a boolean whether the post is liked by a current user (including data from the related table, point #2).

If this request is not authorized, we don't have a `currentUserId`, so we're selecting a constant `false` instead of a related table (point #4). `t.boolean()` here indicates the returning type of SQL expression.

Filter posts by title containing a word to show #3.

```ts
const result = await db.post.select(
  'title',
  'body',
  {
    liked: currentUserId
      ? (q) => q.likes.where({ userId: currentUserId }).exists()
      : db.article.raw((t) => t.boolean(), 'false'),
  },
).where({
  title: {
    contains: 'word',
  },
});
```

The resulting type is completely inferred and equals to this:

```ts
type Result = {
  title: string,
  body: string,
  liked: boolean
}[]
```


## Flexibility

Except they're not helping with relations, query builders are great at their flexibility, they are allowed to build complex queries with nested sub-queries.

ORMs are handling this differently:

- exposing only a limited interface (`Prisma`, `Sequelize`), so they become unusable when you need more control over a query.

- switch between a limited interface and a query builder (`MikroORM`, `TypeORM`). This feels like using two different libraries, switching between two different sets of limitations. [Kysely](https://github.com/koskimas/kysely) is a nice query builder with good TS support, but MikroORM is using [Knex](https://knexjs.org/guide/query-builder.html#knex) instead so you're losing TS, and `TypeORM` has a custom query builder, less user-friendly than `Knex`.

- Built on top of query builder (`Objection`, `OrchidORM`) - in such a way, querying feels natural and remains powerful.

Query builder-based ORM allows building the query step by step, adding pieces based on conditions. `Orchid ORM` example code:

```ts
let q = db.post.select('id', 'title')

if (params.search) {
  q = q.or([
    { title: { contains: params.search } },
    { body: { contains: params.search } },
  ]);
}

if (params.order === 'newer') {
  q = q.order({ createdAt: 'DESC' })
} else if (params.order === 'older') {
  q = q.order({ createdAt: 'ASC' })
}

const posts = await q
```

`Orchid ORM` has a custom query builder, specifically designed to be as TypeScript-friendly as possible. Query builder here is inspired by `Knex` and supports all the same query methods (and more).

To make things even cleaner, `Orchid ORM` has a [repository feature](https://orchid-orm.netlify.app/guide/orm-repo.html), that allows hiding complex or repeated parts under custom methods. In the following example, `search` and `customOrder` are custom methods defined somewhere else, and you can build nice looking easily readable queries:

```ts
const posts = await postRepo
  .search(params.search)
  .customOrder(params.order)
```


## Easiness of use for more complex cases

Let's say we want to load post records, include the post author, tags, and a few last comments, and comments should include the author. Here is how it looks with `Orchid`:

```ts
await db.post
  .select('id', 'title', 'description', {
    author: (q) => q.author.select('id', 'firstName', 'lastName'),
    tags: (q) => q.postTags.pluck('tagName'),
    lastComments: (q) =>
      q.comments
        .select('id', 'text', {
          author: (q) =>
            q.author.select('id', 'firstName', 'lastName'),
        })
        .order({ createdAt: 'DESC' })
        .limit(commentsPerPost),
  })
  .order({ createdAt: 'DESC' });
```

14 lines of code.

The same with `Prisma` took me 48 lines of code ([source](https://github.com/romeerez/orchid-orm-examples/blob/main/packages/benchmarks/src/benchmarks/nestedSelect.ts#L104)), and it looks clean and well, but requires mapping of result, as it doesn't support naming the fields as you need in the query, so we can't just load comments as "lastComments", but this may be required by our API spec.

`Sequelize` code for the same query ([source](https://github.com/romeerez/orchid-orm-examples/blob/main/packages/benchmarks/src/benchmarks/nestedSelect.ts#L170)) looks more complicated and includes inevitable dangerous type casts.

The limited interface of `MikroORM` and `TypeORM` would not be enough for this query, so if in case of using them, we would have to switch to a query builder.

But, how is it even possible with a query builder? Using `MikroORM`, `TypeORM`, `Knex`, `Kysely` for such a query would much more time and effort, resulting in something really scary to maintain. We would probably end up with one separate query per table, and then combining records on the JS side. And need to do this carefully to not introduce the `N + 1` problem. Let me know if I'm wrong, I'd love to be wrong at this and to see how it could be done with a query builder properly.


## Performance

Premature optimization is evil, so the performance doesn't matter as much as other characteristics, but still, in some cases, it can be critical.

The initial version of `OrchidORM` was built to test the idea: if Postgres can include nested resources on its end via sub-queries, and return them as JSON columns, why not a single ORM is using this? Would it be efficient to follow this way?

SQL example to demonstrate this approach: loading posts with JSON array of comments:

```sql
SELECT
  *,
  (
    SELECT json_agg(t.*)
    FROM (
      SELECT * FROM comments WHERE postId = posts.id
    ) AS t
  ) AS comments,
FROM posts
```

This is how `OrchidORM` handles relations under the hood, so all nested select queries are turned into a single SQL query.

`Prisma` loads each new relation as a new query, then combines results on the JS side.

`Sequelize` produces huge queries with joins.

`OrchidORM` is the fastest in comparison with other ORMs and even query builders, see benchmarks [here](https://orchid-orm.netlify.app/guide/benchmarks.html#benchmarks), and other benchmarks in my other [article](https://romeerez.hashnode.dev/nodejs-orms-overview-and-comparison#heading-benchmarks).


## Writing a model

Defining columns in `OrchidORM` may look familiar if you ever have used `Zod`, `Yup`, `Joi`, and similar. It is more compact than defining types separately (`Sequelize`) or using TypeScript decorators and ! signs as in `TypeORM` and `MikroORM`, and it doesn't require generating code on each schema change as in `Prisma`.

It's possible to convert the table schema to `Zod` to use for validations later.

All columns are required (not nullable) by default.

`text` type requires min and max arguments, so our table is protected from empty or billion chars long texts. All column types can be customized in `BaseTable`, this can be used to set a common min and max for all columns together.

Supported relation types are `belongsTo`, `hasOne`, `hasMany`, `hasAndBelongsToMany`.

`hasOne` and `hasMany` supports through the option for a table in the middle.

```ts
export class ArticleTable extends BaseTable {
  table = 'article';
  columns = this.setColumns((t) => ({
    id: t.serial().primaryKey(),
    userId: t.integer().foreignKey('user', 'id').index(),
    title: t.text(10, 200),
    body: t.text(100, 100000),
    favoritesCount: t.integer(),
    ...t.timestamps(),
  }));

  relations = {
    author: this.belongsTo(() => UserTable, {
      primaryKey: 'id',
      foreignKey: 'userId',
    }),
  };
}

// convert columns schema to Zod, use it later for validations
export const ArticleSchema = tableToZod(ArticleTable);
```


## What's the catch?

- only Postgres is supported at this point

- not possible to have a relation between tables in different databases (to be done in the future)

- though it works on demo projects, it is too green for production

- though it contains all methods from Knex and inserting/updating in the style of Prisma, many features are to be done yet

- it can generate migrations from the existing database, it can generate table files from running migrations. But generating migrations from table schemas (as in Prisma) or updating the database schema on the fly (as in many ORMs) is yet to be done.


## Summary of OrchidORM

- Type safety is a top priority

- Query-builder interface enables writing complex custom queries

- Creating and updating nested records is as powerful as in Prisma

- Zod-like defining columns makes it simpler than the others

- Cares about performance


## Try it out!

I hope you could appreciate `OrchidORM` and try it on non-critical personal projects and share feedback, so the ORM could grow and prosper.

To speed up the setup, I added a script to do all routine preparations automatically, simply run this in a new directory and check out [quickstart](https://orchid-orm.netlify.app/guide/quickstart.html):

```ts
npx orchid-orm
```

Or you can clone [this examples repo](https://github.com/romeerez/orchid-orm-examples/tree/main/packages/blog-api) and play with the Blog API code.

Don't forget to star the project if you liked it, and share feedback.

Does it look interesting to you? Do you agree existing tools are not good enough?