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

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:

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 is a nice query builder with good TS support, but MikroORM is using 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:

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

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:

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), 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) looks more complicated and includes inevitable dangerous type casts.

The limited interface of MikroORM and TypeORM would not be enough for this query, so 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 take 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:

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, and other benchmarks in my other article.

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.

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:

npx orchid-orm

Or you can clone this examples repo 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?