How to Achieve Efficient Multi-Row Inserts with pg-promise?

Multi-Row Insert with pg-promise

Inserting multiple rows into a database with a single INSERT query can greatly improve performance. In pg-promise, there are several methods you can utilize for multi-row inserts.

Using the ColumnSet and insert helpers from the helpers namespace is the recommended approach for optimized performance. First, create a ColumnSet object, which defines the columns you wish to insert data into. Then, use the insert helper to generate the INSERT query with the provided values. The query will be generated with the appropriate number of value placeholders and the VALUES clause will contain the provided data. Here's an example:

<code class="javascript">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);</code>

This will generate the following INSERT query:

<code class="sql">INSERT INTO "tmp"("col_a","col_b") VALUES('a1','b1'),('a2','b2')</code>

You can execute the query using db.none(query).

SQL Injection Protection

When using the insert helper, the values you provide are automatically parameterized, offering protection against SQL injections. However, if you are dynamically inserting schema/table/column names, it is crucial to use SQL Names to prevent against potential SQL injection attacks.

Inserting with Return Values

If you want to retrieve the generated IDs or any other values from a multi-row insert, you can append RETURNING to your query and execute it using db.many. This will return an array of objects containing the requested data.

Conclusion

Using the ColumnSet and insert helpers along with SQL Names provides a secure and efficient way to perform multi-row inserts with pg-promise.

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