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:
- What is the problem?
- Why is it wrong?
- How can you fix it?
So let's get started!
save() vs insert(), update()
Repositories have the .save()
method, which has a dual function:
- It inserts the entity if it doesn't already exist.
- 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:
- First, it uses a
SELECT
query to search for an existing entity. - If step 1 returns a record, it uses
UPDATE
to update the record. Otherwise, it usesINSERT
to insert a new record.
Why is it bad?
- These queries need two round trips to the database, meaning that you have to suffer the network latency due to each of the trips.
- 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. - 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.
Top comments (14)
As @jorge rightly said, listeners are called by default when using the
save
method (this can be disabled) whereas theinsert
method only execute a clean and fastINSERT 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 thesave
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
andremove
options both supports listeners like@BeforeInsert
which makes it easy to implements a user entity for instance.And you can create a user as simple as that.
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
has no difference as compared to
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.
If you don't use save, then you cannot use Entity Listeners and Subscribers.
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!
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.
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.
Sure, include it
I think, the Entity Listener & Subscribers can be trigger by any method insert, update, or save but not for raw sql.
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!
Nice
In the typeorm save method, if we enter the same data that already in the database, wont it update that record, in save method
it will throw a unique constraint error, Is there a way to bulk update the same data records in typeorm. Thank you
nice post
I had big problems using .save() inside transactions, because after every save, the transactions was COMMITed, and the ROLLBACK not undo commited changes.
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