How can pg-promise efficiently handle multi-row inserts in PostgreSQL?

Multi-row Inserts with pg-promise

Inserting multiple rows within a single query is an efficient approach for bulk operations. Pg-promise provides seamless support for multi-row inserts through its array-based mechanism.

Multi-row Insert Syntax

To insert multiple rows, you can define them as an array of objects, each object representing a row:

const values = [{col_a: 'a1', col_b: 'b1'}, {col_a: 'a2', col_b: 'b2'}];

To generate the multi-row insert query, utilize the insert helper from the pgp.helpers namespace:

const cs = new ColumnSet(['col_a', 'col_b'], {table: 'tmp'});
const query = insert(values, cs);

This generates the following query:

INSERT INTO "tmp"("col_a","col_b") VALUES('a1','b1'),('a2','b2')

Query Execution

You can execute the generated query using the none method:

await db.none(query);

Dynamic SQL Names and SQL Injection Protection

Pg-promise employs placeholders ($1, $2, etc.) for query parameters, instead of string interpolation. This approach prevents SQL injection vulnerabilities as the library sanitizes the parameters before constructing the query.

Additional Notes

• Returning New Record IDs: To retrieve the IDs of newly inserted records, append RETURNING id to the query and use the many method to execute it.
• Error Handling: To handle potential errors, wrap the query generation within a function that can throw exceptions if the generation fails.

In summary, pg-promise offers a robust solution for performing multi-row inserts, ensuring data integrity and performance efficiency. Utilize the insert helper and dynamic SQL names for optimal results.

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