Optimizing TypeORM: Tips from Experience! (Part 1: Don't use save())

Introduction to the series

As of January 21st 2022 (source), TypeORM is the 3rd most popular Javascript ORM library and certainly the most popular if we're talking about Typescript.

I have been extensively working with this library for the past two years, using it to serve millions of database queries every single day. In this series, I will list a few tips and pitfalls I learned while working with the project, which helped me catch bugs in production and optimize API calls. I'll try to keep each post short, informative and straightforward so you can quickly use these optimizations in your code.

In each post, we will go over:

1. What is the problem?
2. Why is it wrong?
3. How can you fix it?

So let's get started!

save() vs insert(), update()

Repositories have the .save() method, which has a dual function:

1. It inserts the entity if it doesn't already exist.
2. If the entity exists, it attempts to update the existing one.

Let's observe two example usages of the method:

Here is a code snippet taken from an API endpoint which registers a new user:

const user = this.userRepository.create({    name: "Rishit",    password: "test123",});await this.userRepository.save(user);

And here is a snippet from another endpoint which updates the name of an existing user:

const user = this.userRepository.findOne({    name: "Rishit",});user.name = "John";await this.userRepository.save(user);

Pretty handy, right? Well, not so much as this comes at the cost of a performance penalty. The implementation of save() executes 2 queries instead of a single one:

1. First, it uses a SELECT query to search for an existing entity.
2. If step 1 returns a record, it uses UPDATE to update the record. Otherwise, it uses INSERT to insert a new record.

Why is it bad?

1. These queries need two round trips to the database, meaning that you have to suffer the network latency due to each of the trips.
2. There are specific contexts in your code where you know for a fact that you are certainly inserting / updating and do not require to use save()'s dual functionality.
3. The SELECT query generated by TypeORM includes a subquery, which is highly inefficient for tables with millions of rows.

How do you fix this?

Look at the context you use your query in, and that should usually let you decide whether you meant to do an insert() or an update(). For example, you can refactor the above two snippets to:

registering a new user:

const user = this.userRepository.create({    name: "Rishit",    password: "test123",});await this.userRepository.insert(user);

updating an existing user:

await this.userRepository.update({    name: "Rishit",},{    name: "John",});

And just like that, you reduced the database load due to these queries by half: a 2x improvement!. The usage of save() might seem pretty obvious. Still, it's a prevalent practice to use .save() everywhere in code due to the documentation of TypeORM itself recommending it as the primary mode to update entities.

That said, save() does indeed pose a valid use case for code that actually requires the functionality to insert OR update depending on if the entity exists or not. However, I am sure that most use cases don't need this feature and intend to either insert or update a record, not both.