Prisma, Drizzle, TypeORM or Sequalize — When Your Focus Is Scale, Which One To Choose?

Node.js ecosystem is experiencing an interesting development of toolings in the ORM fields. Few years ago, we had the veteran ORMs , Sequalize and TypeORM. In the last year and a half, 2 more libraries are gaining popularity — Prisma and Drizzle. each one of those 4 libraries is part of different family of tools. So what are they? what are their strength ? and if I start a new project — which one of them should I choose ?

First , lets talk about each ORM, and what are its characteristics.

TypeORM and Sequalize are traditional ORMs. Traditional ORM provide an object-oriented way for working with relational databases by mapping tables to model classes in your programming language. You create a model and maps it to a DB table, and you perform most of your Db work through your model, as you can see in the code below:

Drizzle is more lightweight and actually it is defined as a “Headless ORM” . In Drizzle, the DB driver is decoupled from the ORM itself, and you can choose between multiple DB drivers :

And Prisma presents a different approach. When writing code in Prisma, you use a client , which is auto generated and manage all the schemas that are defined within the same service .

Choosing An ORM By Considering The Complexity Of Your Data Scenarios

From my personal experience, when the scale becomes bigger , the data scenarios become more complex. At this point, you need to implement more query optimizations in the DB that you are using, and you need more control over your queries. So in order to come to a conclusion about which ORM to choose, I looked at several query optimizations that I used in the past. The goal was to understand whether I can implement them with the different ORM tools.

Basic — Don’t use select *

Why using select * is not recommended in production ?

There are several reasons, but let me note 2 main reasons here :

First , select * will result in fetching all columns from the DB, even columns that you don’t need. It happen to me several times that a large field was added to a large table, which results in slowness in all select * queries. All those queries started to consume more memory and resources, because more data (that I did not need) was fetched from the DB and moved across the network to my application server.

Second is the index usage. Query optimization and efficient use of indexes can be hampered by using SELECT *. By specifying the necessary columns in the query, the database optimizer can utilize indexes more effectively to speed up query execution.

Can I create an application code in Drizzle, Prisma , TypeORM and Sequalize , that will not create DB queries with select * ?

Drizzle —

When using Drizzle, even if you don’t specify explicitly which fields you would like to include in your query , Drizzle will create select queries that list all the fields in your model. In addition, Drizzle gives you the possibility to select specific fields for your queries. You can see Drizzle code snippets below :

Prisma —

Prisma by default will generate select * queries, but will give you the possibility to select specific fields on your queries. You can see an example in the code below:

Sequalize and TypeORM will also let you the possibility to select specific fields, although by default , if not field is chosen, those libraries will generate select * queries.

So, to summarize, all libraries support creating select queries with specifying specific fields.

Advanced — Use DB Cursor For Efficient Pagination

There are 2 ways to implement pagination :

Offset based pagination — a technique used to paginate datasets with a database query by specifying the number of items to skip (offset) and the number of items to retrieve (limit) in each page of results. You can see an example below.

Cursor pagination — pagination technique used to retrieve a page in a large dataset with a database query by using markers or cursors to track the current position in the dataset. Instead of using an offset value, cursor-based pagination uses a cursor or key as a reference point to fetch the next page of results. The cursor is usually a unique identifier that represents the position in the dataset, and must be indexed and sortable.

Why cursor pagination is more efficient then offset based pagination ?

The cursor is unique ,indexed and sortable. The database jumps directly to the record without iterating through the unwanted data. Hence make it more efficient.

Can I Implement Cursor Based Pagination In Drizzle, Prisma, TypeORM and Sequalize ?

Prisma —

In prisma , you are able to implement cursor based pagination., You can see an example below :

Drizzle -

In Drizzle, you are also able to implement cursor based pagination, as you can see in the code below :

However, in TypeORM and Sequalize, I did not find an efficient way to implement cursor pagination. There are several libraries that implement cursor based pagination with TypeORM and Sequalize , but they don’t have vast amount of downloads.

So, to summarize cursor based pagination in the various tools :

Advanced — Implement Multiple Join Types In Nested Reads

When working with nested entities, you can have entities which are saved across more then one table and has relations of one-to-one, one-to-many, many-to-one and many-to-many. Here is an example of a one-to-many entity of users and their posts — The DB representation and the API representation .

In order to query those entities , the queries are written with joins, as multiple join types exists : left join, right join, inner join and more.

Why It is importent to have the possibility to implement multiple join types ?

Choosing appropriate join techniques and conditions to minimize the number of rows involved in joins can significantly enhance query performance. Understanding data relationships is crucial for optimizing join operations.

Can I Implement Different Join Types In Drizzle, Prisma, TypeORM and Sequalize ?

Drizzle —

Drizzle gives you the ability to implement all join types .

Prisma -

The default configuration of the prisma client does not use joins for nested reads. The following code will generate the following queries :

There is a configuration for Prisma client called “relationJoins” . This configuration will create on PostgreSQL a Lateral Join query combined with JSON aggregations which generates the following query .

However, there is not explicit control on more join types. In Prisma’s documentation it is stated that there might be cases where the query can be more performant depending on the characteristics of the dataset and query.

In TypeORM you are able to implement multiple join types. You are also able to implement multiple Join types in Sequalize, but you need to get into the internals of the ORM and understand which query it will generate in each situation. Here is an example of a code that implements inner join in TypeORM :

Nested Reads Summary

For implementing multiple join types in Nested reads, here is the summary of the abilities of each tool :

Nested Writes — Single Entity And Bulk Write

There are 2 important points when implementing nested writes .

For inserting single entity — when inserting data across tables, you need to use a transaction. Otherwise — in error scenarios, your DB can get to an inconsistent state.

For inserting multiple entities, especially in large scale — It is importent to work with bulk writes from multiple reasons.Some of the reasons are that they reduce the overhead of executing multiple insert statements, enable the DB to optimize inserts more efficiently and reduce DB round trips.

Nested Writes In Drizzle, Prisma, TypeORM And Sequalize

Drizzle —

For inserting a single nested entity — Drizzle does not executes transaction out of the box. Below is an example for Drizzle code to insert single nested entity. This code does not execute transaction on the DB, and to execute transaction, you need to use Drizzle’s transaction API.

For bulk insert , Drizzle has support for bulk insert of nested entities — but it only support bulk insert for several DB drivers and not for all of them. So at the end — it is dependent on the DB driver that you chose.

Prisma -

For single nested entity insert— Prisma supports transaction out of the box. The following code will generate a DB transaction to insert user, the posts it has created and its profiles.

For bulk insert — Prisma support bulk operations, but does not support bulk operations with nested entities .

To write bulk operations with Prisma, you can try use Prisma’s raw query API.

TypeORM and Sequalize also support transaction out of the box on inserting single nested entity, and also do not support bulk insert for nested entities.

Here is the summary for inserting single neted entities for the various tools :

And here is the summary for bulk insert abilities for the various tools. As you can see, except Drizzle , no tool supports bulk inserts for nested entities. The solution for that will be to use the raw query API that those tools expose.

After Comparing The Abilities Of The Various Tools — Which ORM To Choose ?

The answer which ORM to choose for your data access layer — is dependent on your data scenarios.

Data intensive work and complicated scenarios

In those situations, you need as much query control as possible, to be able to work in high scale. For those situation the most suitable of all is Drizzle, and you should also consider working with the native driver and implement the queries yourself.

Medium Complexity

TypeORM and Sequalize will serve you the best in those cases, as they give solid query control and solid development experience.

Simple CRUD Scenarios, Full Stack Teams

For those cases , Prisma is a great fit. It has very good development experience, it is easy to learn, and for those cases gives great abilities.