Migrating a Large Production App From TypeORM To Prisma

Migrating a Large Production App From TypeORM To Prisma

One of the last projects we worked on at Myothis is a property management system for a big client.

It's a pretty large codebase, and we built the backend with the NestJS framework, which is database agnostic but provides strong integration with TypeORM, which is why I've always decided to use it when I'm working with NestJS.

Since the Prisma team announced V2 I've kept an eye open on their Twitter profile, and when they announced the beta version of Prisma Client I've started to play around and got pretty excited about it. I almost immediately felt like I was looking at a truly next-gen approach to data access in NodeJS, which I always felt could be more than improved.

Prisma became generally available as stable in June 2020, and since there they constantly released new versions with bug fixes and lots of improvements and new features. After only six months it felt utopian to use it in production, especially for our big project, as I was pretty sure there were a lot of edge cases it couldn't be ready to handle. However I was too curious to give it a serious try, and one day, as an experiment, I decided to migrate a single part of the backend and see where it would take me.

The results impressed me so much, the next day we started to migrate the whole project.

First steps

Please note: the migration process is explained in detail here in the Prisma docs. For this reason, I'll focus on my experience rather than the exact steps and the code.

The very first step to introduce Prisma to an existing codebase is schema introspection. It takes a single command for Prisma to analyze your database schema and fill out the schema.prisma file with model definitions, each one corresponding to a table in your schema. There were a couple of things to fix, mostly related to naming and relations handling, but since we're talking about a thirty-two tables schema with every possible form of relation involved, it did a pretty good job.

Now, I decided to make a couple of updates to our database schema to better fit Prisma conventions, to take the most advantage of code generation and avoid current limitations, in particular:

  • Join tables need to follow some conventions to represent implicit relations, which means that Prisma will handle the relation completely behind the scenes, reducing complexity a lot. At the same time we can also have explicit many-to-many relations in case we need to add extra data to the join table.

  • JSON columns are supported, but query capabilities are pretty limited at the time of writing ( this might change soon ). That's why I decided to get rid of every JSON column, simply changing them to scalar columns or adding new tables. This might be a problem for some cases, but fortunately not for ours.

I also want to spend some words on the editing experience for the Prisma schema. Initially, I didn't like the idea of a separate schema with a new syntax, but I must say the VSCode extension does an impressive job at making things fast and easy to edit, other than giving you hints and warnings about any issue and even fixing those for you.

NestJS integration

If you are not interested specifically in NestJS you can safely skip this paragraph. NestJS basic knowledge is required to understand what I'm about to describe.

Thanks to the NestJS dependency injection system Prisma integration is smooth, and it turns out to be less verbose than TypeORM.

With TypeORM we need to:

  1. Register TypeOrmModule in a global Nest module, with the configuration object.
  2. Every time a new entity is introduced we need to register it in the entities array ( this may be skipped with entities auto-loading ).
  3. Every time we need to use TypeORM repositories in a Nest module we need to add TypeOrmModule.forFeature([EntityClass]) to the imports array for that module.
  4. For every TypeORM repository we need to use in a provider class we need to add this to the class constructor:
    constructor(
     @InjectRepository(User)
     private usersRepository: Repository<User>,
    ) {}
    
    Prisma integration, on the other hand, is pretty straightforward. We just need an injectable PrismaService extending the generated PrismaClient, add it to a global module, and it can now be used anywhere we need.
import { Injectable, OnModuleInit, OnModuleDestroy } from '@nestjs/common';
import { PrismaClient } from '@prisma/client';

@Injectable()
export class PrismaService extends PrismaClient implements OnModuleInit, OnModuleDestroy {

  async onModuleInit() {
    await this.$connect();
  }

  async onModuleDestroy() {
    await this.$disconnect();
  }
}
// any provider we need to access prisma from 
constructor(private prisma: PrismaService) {}

More on the NestJS - Prisma combo can be found on the Prisma website.

Migrations

Prisma also provides Prisma Migrate, which is a very promising tool to handle migrations, but it is not considered production-ready at the time of writing. That's why we decided to rely on an external tool for migrations, which is out of the scope of this article.

At the time of writing, they've just announced a release preview, and I can't wait to try it out and write down my first impressions.

I've also had some not so nice experiences with TypeORM migrations generation, which is why I've always ended up using the CLI to generate empty files and then filling them up myself with raw SQL. Because of that, giving it up wasn't difficult at all.

Developer Experience

The developer experience working with Prisma was definitely above my team's expectations.

Code readability

With TypeORM, most of the queries end up being split into multiple parts, with a lot of the query builder methods scattered all over the function's body. This really hurts code readability, and it takes some time for anyone to fully understand what the query is doing. With Prisma, even the most complex query fits in an extremely well-structured object parameter, making it possible even for developers not really experienced with SQL to easily understand the purpose of the query.

Consistency and predictability

It is pretty common with TypeORM to start with a particular approach on a single query and then switch to another one because of some limitation or unpredictable behavior. There are lots of ways to do the same thing, and it may be confusing for newcomers to figure out which one is the best. With Prisma, there is a single, predictable, and intuitive way to perform any possible operation.

Type safety

Although TypeORM is written in TypeScript, type safety is not really guaranteed. The type of a record in a query result set will always be the defined model class, regardless of any column selection or joined relation. Moreover, the query builder requires passing tables and column names as strings, which to me feels like the complete opposite of type safety.

Prisma generates advanced types based on the schema.prisma model, and thanks to that it manages to provide type safety at its best.

For a query like this:

const users = await this.db.user.findMany({
  select: {
    email: true,
    active: true,
    zones: {
      select: {
        name: true,
        properties: {
          select: {
            name: true,
            address: true,
          },
        },
      },
    },
  },
});

The result type would be:

const users: {
    email: string;
    active: boolean;
    zones: {
      name: string;
      properties: {
        name: string;
        address: string;
      }[];
    }[];
}[]

As you can see, the result type is extremely precise, and it perfectly mirrors the query selection. I highly recommend taking a look at the generated types to better understand how the powerful TypeScript types system has been exploited.

I can confidently say this is the first time I feel like I'm working with a truly type-safe ORM.

More on this and the following paragraphs can be found here in the Prisma docs comparison.

Relations handling

Managing relations has always been painful even with ORMs, and TypeORM makes no exception. For deeply nested and complex queries we end up writing a lot of .leftJoinAndSelect() methods because of the limitations of the ORM capabilities.

This is what a select query with multiple JOINs looks like with Prisma:

const users = await prisma.user.findMany({
  include: {
    posts: {
      include: {
        categories: {
          include: {
            posts: true,
          },
        },
      },
    },
  },
});

Of course, we can add filters and custom selection on every level of the query.

Another example from the Prisma docs: this is a query to retrieve all users records where all posts are published and at least one related post mentions "Prisma":

const result = await prisma.user.findMany({
  where: {
    post: {
      every: {
        published: true
      },
      some: {
        content: {
          contains: "Prisma"
        },
      },
    },
  },
});

Basically, developers don't even need to know how a JOIN works to handle complex queries with relations involved.

Raw queries

Honestly, I expected I'd need to fall back to raw SQL queries all the time. It turned out I was very wrong. While with TypeORM I always needed to give up on the ORM and leverage the query builder, with half of the clauses as non-typed raw strings, with Prisma I only needed to write raw queries a couple of times for complex aggregation queries. I'm talking about really complex aggregations, since simpler ones are nicely supported.

Even for those cases where raw SQL is required, Prisma provides some nice helpers and hints to help us write raw queries, such as tagged templates helpers and utility functions and constants.

One thing that I miss from TypeORM is the possibility to mix type-safe query functions and raw SQL clauses, but I understand this is something the team is trying to avoid because of the consequences it might have on the developer experience and the wrong behavior it might lead to.

Transactions

Prisma lacks a way to handle long-running transactions the way we are used to writing them, such as with multiple operations encapsulated within a callback or batched within a single promise.

This looked like a severe limitation, but we realized any time we need a transaction is because of a sequence of write operations performed atomically, which is something Prisma has always supported with a query like this:

await prisma.post.create({
  data: {
    title: 'Post Title',
    tags: {
      create: [
        { title: 'Tag #1' },
        { title: 'Tag #2' },
        { title: 'Tag #3' },
      ],
    },
  },
});

At the time of writing, they are also working on a new transaction api which might cover every other use case, available right now as a preview feature.

I highly recommend reading this article by the Prisma team to go deeper into this topic.

Documentation

One of the pain points of TypeORM is documentation. I understand that is probably because of the time this library has been available and the number of features. Nonetheless, it lacks lots of things and it is very confusing. Every time you need to look for something you always end up giving up on the docs and search somewhere else.

Prisma is much newer, but the documentation has also been very well curated. Thanks to the intuitive structure and the powerful search tool, is always easy to find what you need.

Current limitations

There are also some things we had to workaround. Please note that most of these issues are being worked on at the time of writing, and they might be solved when you read this.

  • Advanced query capabilities for JSON columns, as already mentioned, are not yet supported.
  • Cascade deletes present some problems. Even when the foreign key has been declared with an ON DELETE CASCADE clause, Prisma prevents you from deleting the record. This can be solved with a simple raw query:
    prisma.$executeRaw`DELETE FROM "Post" WHERE "id" = ${id}`.
    
  • We needed to implement soft delete for some of our entities, and although they provide an example middleware it has some flaws we needed to deal with. I mentioned that on Twitter and the team reached to me to ask for feedback and help me tackle down the issues, which is something I really appreciate and value.

Conclusions

We are really happy with the decision we made. We reduced our codebase by thousands of lines of code, and those of us who struggled with data access are now confidently working even on complex queries.

Even if Prisma is relatively new, it already provides almost all the features we need, and development is constantly active! They solve lots of issues for every release, some of the issues we had at the beginning of the migration were solved when we finished.

With the recent 2.13 release, we can also import generated types in the browser, which paves the way for exciting solutions to some issues we have with our current monorepo setup, but this is for another blog post...

TypeORM was a great companion in our journey and I really appreciate the work of the community on such a big and ambitious piece of software, but for us the time came to replace it with a next-gen solution.

I'm excited about the future of Prisma and thankful for what the team is doing for us, and I also can't wait to know what you think about all the things you just read.

Cheers!

Did you find this article valuable?

Support Dario Ielardi's Blog by becoming a sponsor. Any amount is appreciated!