Скачать или смотреть Performing Efficient Bulk Inserts with mikro-orm in PostgreSQL

Learn how to efficiently duplicate records in PostgreSQL using `mikro-orm` with raw SQL queries instead of loading all entities into memory.
---
This video is based on the question https://stackoverflow.com/q/76359869/ asked by the user 'isqua' ( https://stackoverflow.com/u/1318780/ ) and on the answer https://stackoverflow.com/a/76379637/ provided by the user 'Martin Adámek' ( https://stackoverflow.com/u/3665878/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How to insert into a table the result of a select query with mikro-orm

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Efficient Data Duplication with mikro-orm

When working with databases, optimizing performance is crucial, especially with operations that involve large data sets. One common scenario is duplicating records based on specific criteria. In this guide, we will explore how to effectively duplicate rows in a PostgreSQL database using mikro-orm, a popular Node.js ORM that simplifies database interactions.

The Problem

Imagine you have a table in your PostgreSQL database structured as follows:

[[See Video to Reveal this Text or Code Snippet]]

Here, foreign_id can have multiple entries, and you want to duplicate every row that has a foreign_id of 31 but set the new foreign_id to 42 and update the created_at timestamp to the current date.

While using an ORM provides benefits like type safety and abstraction, it can also lead to inefficiencies when dealing with bulk operations. Using a traditional SELECT followed by a loop to duplicate entities can lead to high memory usage due to loading all entities into memory. A more efficient approach would be to utilize raw SQL.

The Solution: Utilizing Raw SQL in mikro-orm

When faced with a situation where ORM functionalities can fall short of your needs, it's perfectly acceptable to use raw SQL queries. With mikro-orm, you have the option to execute raw SQL directly through the EntityManager. Here’s how you can achieve this:

Using exec to Run Raw SQL Query

Instead of fetching all entities into memory, you can run a single query that handles the duplication efficiently. Here’s how you can do it:

[[See Video to Reveal this Text or Code Snippet]]

Explanation of the Query

INSERT INTO my_table (id, foreign_id, created_at): This begins the insertion statement where you're specifying the fields that will be populated.

SELECT id, 42, now(): Here, you're selecting the id from the original records but changing the foreign_id to 42 and setting created_at to the current timestamp using now().

FROM my_table WHERE foreign_id = 31;: Only rows with a foreign_id of 31 are pulled from the table, ensuring that only the relevant data is duplicated.

By using this method, you effectively avoid loading large datasets into your application's memory, thus enhancing performance and efficiency.

Alternative: Building the Query with Knex

If you're interested in a more programmatic approach, you can also use Knex.js to build this query in a more dynamic way. This allows you to keep your query modular and easier to manage. Here’s how you would set it up:

[[See Video to Reveal this Text or Code Snippet]]

Conclusion

Optimizing database operations is essential for scalability and performance, especially when working with large datasets. By leveraging raw SQL in mikro-orm, you can efficiently duplicate records without compromising memory usage. Whether you choose to write raw SQL directly or use an ORM-compatible querying tool like Knex, the key takeaway is to choose the best tool for the task at hand.

Now you have the knowledge to handle bulk inserts effectively in your mikro-orm applications. Happy coding!