TypeORM Tips (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.

Comments

[Amin]

As @jorge rightly said, listeners are called by default when using the save method (this can be disabled) whereas the insert method only execute a clean and fast INSERT INTO SQL query.

Also, if you have multiple relationships to create, because a user has many articles to save, insert might not be the best fit because again it uses a clean and simple INSERT INTO SQL query, whereas the save method will actually use a transaction in order to perform a slower but safer query, leading to data not being inserted if ever one of the queries has an error. This can be useful for data integrity where the situation requires it.

Actually, the save and remove options both supports listeners like @BeforeInsert which makes it easy to implements a user entity for instance.

import { BeforeInsert, Column, PrimaryGeneratedColumn } from "typeorm";
import { hash, compare } from "bcryptjs";

@Entity("users")
export class User {
  @PrimaryGeneratedColumn("uuid")
  public id!: string;

  @Column({
    type: "varchar",
    length: 50,
    unique: true,
    nullable: false
  })
  public email!: string;

  @Column({
    type: "varchar",
    length: 60,
    nullable: false
  })
  public password!: string;

  @BeforeInsert()
  public async hashPassword() {
    this.password = await hash(this.password, 10);
  }
}

And you can create a user as simple as that.

const user = this.userRepository.create({
  email: "[e-mail]",
  password: "plainPassword"
});

await userRepository.save(user);

const storedUser = await this.userRepository.findOneBy({
  email: "[e-mail]"
});

console.log(storedUser.password);
// $2b$10$3euPcmQFCib...

[Rishit Bansal]

Hi, thank you for this information, always super good to learn more about type orm.

As I covered in this reply, save() does have its applications (dev.to/rishit/comment/27c1d), and I completely agree with that!

About the point on the transaction, I think the "context" of where you are executing an update mattes a lot here. For example, is the save() is just to update a single row on a table, executing

BEGIN transaction
UPDATE ...
COMMIT

has no difference as compared to

UPDATE...

I think the reason typeorm wraps it in a transaction is because when you use cascade, it may execute more than one UPDATE command. We actually faced an issue on production where this was causing issues as well, until we discovered this issue (github.com/typeorm/typeorm/issues/...), where you need to do some workarounds to make sure the library knows what to update in what context.

Overall, I think the objective of an ORM is maybe to compromise on a bit of performance to make it super easy to use API wise, and save() is a great example of this ideology. Of course this kind of opinionated design will have performance repercussions, which is what I was trying to point to in the article.

[Jorge]

If you don't use save, then you cannot use Entity Listeners and Subscribers.

[Rishit Bansal]

Hi, thank you for pointing this out!
I definitely agree save() has its use cases, and Entity Listeners and Subscribers are great! However, I also feel that for users that do not use these features, and for performance critical applications where tables have millions of rows, it is sometimes more efficient to execute an insert() or update() directly.

If you don't mind, I would like to include this information (about entity listeners and subscribers) as a note in the article, with credit to you. Let me know if this is okay!

[Peter Hostačný]

Hi guys, @rishit thanks for the article.
@ramirezsandin & @rishit: what would you suggest if we want to retain the performance of executing the simple query (INSERT/UPDATE) by executing insert()/update() methods but also want to use Entity Listeners and Subscribers? Do you by any chance know about a workaround?

We have milions of rows and are syncing the database with external systems so updates/inserts are happening pretty often. We are doing the evaluation "whether to update the entity or not" inside our code so we have access to the old record in the time of evaluation but then if we call update() method, the event listener does not (obviously) have the access to it. I would like to solve it by providing the old record to the listener somehow but avoid using the save() function to refetch the same record we already have.

[Jorge]

If you don't want to use save method of typeorm, then I would suggest to write listeners/subscribers at database level, creating triggers, stored procedures, etc.

[Jorge]

Sure, include it

[nxquan]

I think, the Entity Listener & Subscribers can be trigger by any method insert, update, or save but not for raw sql.

[TechTitans0101]

Thanks @rishit for the tips.

Open source is essential for application developers. It is unfortunate that Open Base has shut down. While searching for alternate, came across kandi from Open Weaver. It helps developers find code snippets, packages, libraries and solutions from millions of assets.
Thanks to such tools for support to the open source community. Happy coding!

[Joaquin Angelino Corona]

Nice

[Dileepa Mabulage]

In the typeorm save method, if we enter the same data that already in the database, wont it update that record, in save method

@Entity()
export class Contact {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @Column({ unique: true })
  googleId: string;

  @Column()
  email: string;
}

it will throw a unique constraint error, Is there a way to bulk update the same data records in typeorm. Thank you

[José Neto]

I had big problems using .save() inside transactions, because after every save, the transactions was COMMITed, and the ROLLBACK not undo commited changes.

[Alexander6]

nice post

[Miguel]

cool stuff, thanks!

there is a typo on the TypeORM documentation link, as an undefined in there.
link is just typeorm.io/#updating-in-the-database