Home  >  Article  >  Web Front-end  >  How to Achieve Multi-Row Inserts with Efficiency Using pg-promise?

How to Achieve Multi-Row Inserts with Efficiency Using pg-promise?

Barbara Streisand
Barbara StreisandOriginal
2024-10-30 13:53:26924browse

How to Achieve Multi-Row Inserts with Efficiency Using pg-promise?

Multi-Row Inserts with pg-promise

Problem Statement:

How to efficiently insert multiple rows into a database using a single INSERT query in pg-promise?

Solution:

The preferred approach in pg-promise is to use the helpers namespace for high performance and flexibility.

<code class="javascript">const {ColumnSet, insert} = pgp.helpers;

const cs = new ColumnSet(['col_a', 'col_b'], {table: 'tmp'});
const values = [{col_a: 'a1', col_b: 'b1'}, {col_a: 'a2', col_b: 'b2'}];

const query = insert(values, cs);
// => INSERT INTO "tmp"("col_a","col_b") VALUES('a1','b1'),('a2','b2')

await db.none(query);</code>

Additional Considerations:

  • The ColumnSet object should be created statically to cache formatting templates for optimal performance.
  • This multi-row insert is transactionless, meaning all or none of the values will be inserted.
  • SQL injection protection is provided by using the helpers namespace in conjunction with SQL Names.

Extras:

  • To obtain the ID of each new record, append RETURNING id to the query and execute it with db.many().
  • For inserting large datasets, consider utilizing the Data Imports feature.
  • You can encapsulate query generation within a function for enhanced error handling.

The above is the detailed content of How to Achieve Multi-Row Inserts with Efficiency Using pg-promise?. For more information, please follow other related articles on the PHP Chinese website!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn