You probably encounter such discussion in the wild often, mostly probably if you are a backend developer. Which ORM to use? There are options and you cannot decide?

Well, let me help! But first...

What is an ORM?

To make this article accessible for most developers, I will explain very quickly why you would want to have the ORM:

• Mostly a simplification, but truth nonetheless: It writes SQL for you!
• Maps objects in the code to what and how is saved in your relational database (hence the name, object <-> relational mapping)
• Most of libraries also expose some useful tools to keep the database in sync with the code, or other way around.

Why would you want to use one, you ask? Why not use raw connection with let's say PostgreSQL and manage data this way?
Well, you certainly could, and most people did that decade or two decades ago, and shock-you-not - it worked! But there were some disadvantages of doing this:

• You need to keep the database up to date with the changes manually or spin up some tool to do that
• If migrations mechanics are not employed, database state is not versioned or may be versioned in less than ideal way
• You need to write code that maps raw stuff from the database into your code objects and vice-versa, map your code objects to database raw data.

So in this case, you write mapping by yourself, migrations by yourself, and then only SQL is left for you to do manually and you are quickly approaching the inevitable implict outcome - you will build your own ORM this way.

There is a reason there are so many new ORM appearing for the node ecosystem recently, many probably came into life this way, someone was writing some db connectivity convenience layer and it turned into an ORM. Fun activity, but don't do that when you code for the money in your current project.

Right, with this out of our way...

Tell me about TypeORM!

I'm glad you asked. I used this library for years now professionally. Which is the main reason I started looking into Prisma, but let's not get ahead of ourselves..

TypeORM key concept is that usually uses the code-first paradigm.

What does that mean? It means you write your code first, entity classes to be precise, then add decorators to those classes and fields inside that serve as a description of this class layout to the ORM, and ORM then uses that data, provided by the decorators, to map your classes into database data.

This is similar to what Doctrine (PHP and Symfony world) and Hibernate (Java and Spring world) usually do as well, and TypeORM also can do the mapping in separate files if needed, but in general using decorators is encouraged by its docs.

So summarizing, your write code, decorate it, and that's how you declare your mapping.

And now, Prisma

I'm experimenting with Prisma for some time now, but not yet switched in professional jobs, but this is on my horizon. But when it comes to my personal hobby projects - I use Prisma all the way.

Prisma has a very different approach to defining your mapping, and you do that in just one file, the schema file. This file is called usually schema.prisma and can be anywhere in your repo.

Now comes the brilliant part: Once you have your mapping written in that schema.prisma file, Prisma will generate all entity and repository classes and functions with all possible typing variations for you.

This means Prisma is schema-first, you write your schema, and code is generated for you. Along with the types!

TypeORM Setup

After installing the library, on npm called typeorm, let's create a file with the connection. This is fairly standard and is needed for typeorm cli to work, but we will also use it later in the code. Call the file data-source.ts:

As you can see, all of the database credentials are loaded from the environment. There could also be some bit of validation for those values, but let's keep things simple now (looking at you zod, later).

Now let's create 2 very simple entities

So this is it. This is how you do the mapping in the TypeORM. You can see there is a top level decorator making the class as an entity, and decorators for fields telling how to manage them. We can also see how relations are mapped, and the autogenerated uuid id field.

Now that we have our entities, we need to create a migration that will apply our changes to the database. There is a CLI tool typeorm exposes to do that, so let's use it, this way:

yarn typeorm-ts-node-commonjs migration:generate -d ./data-source.ts "./src/migrations/CreateUserAndAgency"

And if it works: we will be presented with following code:

You can see there are up and down methods, and that the code is in fact a class. You can write any code there, which is a blessing but also a curse. More on that later.

And finally, apply the migration!
yarn typeorm-ts-node-commonjs migration:run -d ./data-source.ts

If all went well, we can now use the ORM to do something:

Prisma setup

When it comes to installing prisma, we need 2 libraries:
@prisma/client in production dependencies and prisma in dev dependencies
Once installed, let's go straight to writing our schema!

And that's it! This is how you define your mapping in Prisma. There are several pros of this we will talk on later, but notice now that there is just 1 type of the field, not "type from code" and "type from db" dualism.

Once we have the schema ready, now it's time for the biggest trick, client code generation. Use this to do it:
prisma generate --schema=./schema.prisma
And generated! By default, the client code is saved to ./node_modules/.prisma

Now, just as in TypeORM, we will create a migration, using the following command:
prisma migrate dev --schema=./schema.prisma --create-only --name UserAndAgency

We can now see a big different - Prisma migrations are just SQL files! This can be a curse or a blessing, more on that later.

Now apply migrations, but now be careful:

• on your dev environments where you don't care that much of data but care a lot about bug catching: prisma migrate dev --schema=./schema.prisma
• on your prod environment where you care a lot of your data: prisma migrate deploy --schema=./schema.prisma

This is very important! Because Prisma in the dev migrate utilizes a powerful mechanism called Shadow Schema.
Prisma will basically create a new database side by side, migrate it to the point where original db is migrated to, check for schema drift, and report on it. This will allow you to catch bugs. But you don't want this done on the production.

And now, let's use the Prisma in the simpliest way possible:

Side by Side comparison

Fetching by relations:

In this scenario we will see how to fetch something based on something related, particularly users belonging to an agency.

In Prisma:

In TypeORM:

Very similar, the repositories is a different approach between those ORMs. In TypeORM every entity has its own repository class configured to take care of that entity. In Prisma similar thing exists, but as subfields of the client object, like prismClient.user.

Fetching with relations:

In this scenario, we want to get and user but also its agency along with it.

Prisma:

TypeORM:

This looks very similar, but there are some differences under the hood:

• TypeORM will execute 1 query with a join clause to get the results
• Prisma will execute 1 query to get the user, and then 1 query to get the agency. If multiple users were fetched, there would still be 2 queries, 1 for users, and 2 for all agencies of fetched users. This is not n+1, rather 1+1 :)

TypeORM typing in this situation shows its biggest weakness. If we did not include agency in relations field, we would get an User field, which type suggest there is a non nullable Agency field in it. And compilator would not know any better that TypeORM will happily ignore that and return you an User with agency set to undefined, despite the types saying its not undefined. You can't rely on types to be sure what your entity really contains, you need to know its path from being fetched to being used to understand it's format. This is really painful.

Prisma, on the other hand, has types prepared for every situation, so in the example above it will return the result of type of a user with agency field, but if we removed the agency from include clause, the type returned would reflect that, and agency field wouldn't be there! This is perfect.

Inserting new data

In Prisma:

In TypeORM:

Some interesting points: look at this select clause in prisma api call. We put only 'id' there, and so the result will be typed to only have this one field and nothing else. This is how you get the ID back if it's generated by the database, usually.
In TypeORM example, pay attention that the create method doesn't write anything to database, it just creates an object instance, of type User, with fields set to that. This is needed so the repository knows that this object is in fact of the user entity type. The objects is then saved using the save method that works like an upsert - inserts if not exist, updates if exists. Confusing? Now tell me what if I put the id in the body? Will it update? Insert new? Override ID generation? How knows, and docs would not help.

Update existing entity

In Prisma:

In TypeORM:

Prisma and first TypeORM examples are simple and clear.
But the second TypeORM example shows something different, if you already have the entity in your code, you can change it directly and save it, the result will be the same.

Running code in transaction

In Prisma:

In TypeORM:

This type of transactions are called interactive because arbirtary code can be put inside. Beware there are timeouts, in prisma, the defualt timeout in this transaction block is 5000ms but can be changed easily.
Be sure to use the parameter of the callback method you pass to the transaction method instead!

Dockerization

When it comes to TypeORM, it's fairly easy because no code generation so I won't go into details here.

But when it comes to Prisma, here is an example of a Dockerfile how to approach it:

Let me explain.
In the first step, production node modules are downloaded
In next step, application is build, and prisma client code is generated
In last step, production node modules are copied back but also autogenerated prisma client code from build stage
And that should be enough to get it up and running.

Conclusion!

TypeORM

Pros:

• Similar to other well known tooks - Doctrine, Hibernate
• Can be more modular fitting in more complex project architectures - for example giving one module access to only some entities
• I think integration with NestJS is very good, but Prisma integrates just as well in my opinion
• More optimal queries at times
• Migrations are code - Yay! Custom logic!

Cons:

• Very weird bugs happening at times
• Most usually your bug solving will result in you reading tons of github issues of the typeorm repo, instead of docs
• Types are commonly invalid at runtime
• Data-source file is not documented anywhere well while being crucial part of cli
• No idea why, but it looks like schema drift happens very quick
• Migrations are code - Oh no, people will use real entities in there and break migrations - happens too often and best is to disallow imports from app into migrations - you don't want a junior import and Enum and iterate over it in the migration file...
• Migrations are less readable, especially keys names
• At times error handling is very not helpful

Prisma

Pros:

• So fresh approach
• Very good type safety
• Much better error reporting
• Nice documentation
• Looks like the community around is more helpful
• Handy schema language - I recommend a JetBrains plugin for that, makes working with the file so easy
• Code generation saves a lot of time
• Can create and update multiple and nested entities in one api call in a migration - see nested writes

Cons:

• At times relations are not obvious but the JetBrains plugin helps
• No query builder at all, either high level management or low level queies
• At times type errors are very verbose but at least are clear
• Especially at the beginning - easy to forget to rebuild client - best to have it as part of the build and dev commands

So we got it.

I hope this article will help to make some educated decision on your next project! I will personally switch to Prisma.

Happy mapping!