How can I efficiently perform multi-row inserts in a PostgreSQL database using pg-promise?

Multi-Row Insert with pg-promise

Inserting multiple rows into a database table with a single query can significantly improve performance, especially when dealing with large datasets. Pg-promise, a powerful SQL query builder and executor library, provides a convenient and efficient way to perform multi-row inserts.

Multi-Row Insert with Helpers

To insert multiple rows with pg-promise, you can use the helpers namespace. This namespace provides a set of utility functions that make it easy to construct parameterizable SQL queries.

<code class="javascript">const pgp = require('pg-promise')({
  capSQL: true // capitalize all generated SQL
});
const db = pgp(/*connection*/);
const {ColumnSet, insert} = pgp.helpers;

// our set of columns, to be created only once (statically), and then reused,
// to let it cache up its formatting templates for high performance:
const cs = new ColumnSet(['col_a', 'col_b'], {table: 'tmp'});
    
// data input values:
const values = [{col_a: 'a1', col_b: 'b1'}, {col_a: 'a2', col_b: 'b2'}];
    
// generating a multi-row insert query:
const query = insert(values, cs);
//=> INSERT INTO &quot;tmp&quot;(&quot;col_a&quot;,&quot;col_b&quot;) VALUES('a1','b1'),('a2','b2')
    
// executing the query:
await db.none(query);</code>

The ColumnSet class defines the columns to be inserted, while the insert function takes the data values to be inserted and generates the SQL query. This approach is highly performant and allows for both single-row and multi-row inserts.

SQL Injection Protection

Regarding the side question of SQL injection protection, it is crucial to understand that using placeholders ($1, $2, etc.) does not guarantee protection on its own. It is essential to combine placeholders with proper input validation and sanitization to prevent malicious SQL commands from being executed. Pg-promise employs this approach by default, providing a secure way to insert data into your database.

Additional Considerations and Extras

• Returning IDs: To retrieve the IDs of newly inserted records, append RETURNING id to your query and execute it with the map method to convert the result into an array of integers.
• Huge Data Imports: For importing exceptionally large datasets, consider using the Data Imports feature provided by pg-promise.
• Optimizing for Speed: The query generation can be wrapped into a function to ensure that it is performed only once, further improving execution speed.

In conclusion, pg-promise's helpers namespace makes it easy to perform multi-row inserts, protecting against SQL injection while optimizing performance.

The above is the detailed content of How can I efficiently perform multi-row inserts in a PostgreSQL database using pg-promise?. For more information, please follow other related articles on the PHP Chinese website!