Multi-Row Insert with Pg-Promise

Inserting multiple records with pg-promise

I am the author of pg-promise.

There are two ways to insert multiple records. The first, and most typical way is via a transaction, to make sure all records are inserted correctly, or none of them.

With pg-promise it is done in the following way:

db.tx(t => {
const queries = lst.map(l => {
return t.none('INSERT INTO table(id, key, value) VALUES(${id}, ${key}, ${value})', l);
});
return t.batch(queries);
})
.then(data => {
// SUCCESS
// data = array of null-s
})
.catch(error => {
// ERROR
});

You initiate a transaction with method tx, then create all INSERT query promises, and then resolve them all as a batch.

The second approach is by concatenating all insert values into a single INSERT query, which I explain in detail in Performance Boost. See also: Multi-row insert with pg-promise.

For more examples see Tasks and Transactions.

Addition

It is worth pointing out that in most cases we do not insert a record id, rather have it generated automatically. Sometimes we want to get the new id-s back, and in other cases we don't care.

The examples above resolve with an array of null-s, because batch resolves with an array of individual results, and method none resolves with null, according to its API.

Let's assume that we want to generate the new id-s, and that we want to get them all back. To accomplish this we would change the code to the following:

db.tx(t => {
const queries = lst.map(l => {
return t.one('INSERT INTO table(key, value) VALUES(${key}, ${value}) RETURNING id',
l, a => +a.id);
});
return t.batch(queries);
})
.then(data => {
// SUCCESS
// data = array of new id-s;
})
.catch(error => {
// ERROR
});

i.e. the changes are:

  • we do not insert the id values
  • we replace method none with one, to get one row/object from each insert
  • we append RETURNING id to the query to get the value
  • we add a => +a.id to do the automatic row transformation. See also pg-promise returns integers as strings to understand what that + is for.

UPDATE-1

For a high-performance approach via a single INSERT query see Multi-row insert with pg-promise.

UPDATE-2

A must-read article: Data Imports.

Multi-row insert with pg-promise

I'm the author of pg-promise.

In older versions of the library this was covered by simplified examples within the Performance Boost article, which is still a good read when writing high-performance database applications.

The newer approach is to rely on the helpers namespace, which is ultimately flexible, and optimised for performance.

const pgp = require('pg-promise')({
/* initialization options */
capSQL: true // capitalize all generated SQL
});
const db = pgp(/*connection*/);

// 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 pgp.helpers.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 = pgp.helpers.insert(values, cs);
//=> INSERT INTO "tmp"("col_a","col_b") VALUES('a1','b1'),('a2','b2')

// executing the query:
await db.none(query);

See API: ColumnSet, insert.

Such an insert doesn't even require a transaction, because if one set of values fails to insert, none will insert.

And you can use the same approach to generate any of the following queries:

  • single-row INSERT
  • multi-row INSERT
  • single-row UPDATE
  • multi-row UPDATE

Are insertions using ${} notation protected against sql injection?

Yes, but not alone. If you are inserting schema/table/column names dynamically, it is important to use SQL Names, which in combination will protect your code from SQL injection.


Related question: PostgreSQL multi-row updates in Node.js



extras

Q: How to get id of each new record at the same time?

A: Simply by appending RETURNING id to your query, and executing it with method many:

const query = pgp.helpers.insert(values, cs) + ' RETURNING id';

const res = await db.many(query);
//=> [{id: 1}, {id: 2}, ...]

or even better, get the id-s, and convert the result into array of integers, using method map:

const res = await db.map(query, undefined, a => +a.id);
//=> [1, 2, ...]

To understand why we used + there, see: pg-promise returns integers as strings.

UPDATE-1

For inserting huge number of records, see Data Imports.

UPDATE-2

Using v8.2.1 and later, you can wrap the static query-generation into a function, so it can be generated within the query method, to reject when the query generation fails:

// generating a multi-row insert query inside a function:
const query = () => pgp.helpers.insert(values, cs);
//=> INSERT INTO "tmp"("col_a","col_b") VALUES('a1','b1'),('a2','b2')

// executing the query as a function that generates the query:
await db.none(query);

INSERT ON CONFLICT DO UPDATE using pg-promise helpers for multi row insert/update

Create your static variables somewhere:

const cs = new pgp.helpers.ColumnSet(['first', 'second', 'third', 'fourth'], 
{table: 'my-table'});

// let's assume columns 'first' and 'second' produce conflict when exist:
const onConflict = ' ON CONFLICT(first, second) DO UPDATE SET ' +
cs.assignColumns({from: 'EXCLUDED', skip: ['first', 'second']});
  • See ColumnSet and assignColumns.

In the example below we assume your data is an array of valid objects:

const upsert = pgp.helpers.insert(data, cs) + onConflict; // generates upsert

await db.none(upsert); // executes the query:



Extras

If you want all SQL generated in upper case rather than low case, you can set option capSQL:

const pgp = require('pg-promise')({
capSQL: true
});

And the best way to see what's generated and executed is with the help of pg-monitor.

Multi row insert into multiple tables in a transaction using pg promise

You need to pass your queries to concat in a single array like so:

const concatAllQueries = pgp.helpers.concat([ customerDataQuery, campaignStatusQuery, messageStatusQuery ]); 

Massive inserts with pg-promise

UPDATE

Best is to read the following article: Data Imports.


As the author of pg-promise I was compelled to finally provide the right answer to the question, as the one published earlier didn't really do it justice.

In order to insert massive/infinite number of records, your approach should be based on method sequence, that's available within tasks and transactions.

var cs = new pgp.helpers.ColumnSet(['col_a', 'col_b'], {table: 'tableName'});

// returns a promise with the next array of data objects,
// while there is data, or an empty array when no more data left
function getData(index) {
if (/*still have data for the index*/) {
// - resolve with the next array of data
} else {
// - resolve with an empty array, if no more data left
// - reject, if something went wrong
}
}

function source(index) {
var t = this;
return getData(index)
.then(data => {
if (data.length) {
// while there is still data, insert the next bunch:
var insert = pgp.helpers.insert(data, cs);
return t.none(insert);
}
// returning nothing/undefined ends the sequence
});
}

db.tx(t => t.sequence(source))
.then(data => {
// success
})
.catch(error => {
// error
});

This is the best approach to inserting massive number of rows into the database, from both performance point of view and load throttling.

All you have to do is implement your function getData according to the logic of your app, i.e. where your large data is coming from, based on the index of the sequence, to return some 1,000 - 10,000 objects at a time, depending on the size of objects and data availability.

See also some API examples:

  • spex -> sequence
  • Linked and Detached Sequencing
  • Streaming and Paging

Related question: node-postgres with massive amount of queries.


And in cases where you need to acquire generated id-s of all the inserted records, you would change the two lines as follows:

// return t.none(insert);
return t.map(insert + 'RETURNING id', [], a => +a.id);

and

// db.tx(t => t.sequence(source))
db.tx(t => t.sequence(source, {track: true}))

just be careful, as keeping too many record id-s in memory can create an overload.

How do I properly insert multiple rows into PG with node-postgres?

Following this article: Performance Boost from pg-promise library, and its suggested approach:

// Concatenates an array of objects or arrays of values, according to the template,
// to use with insert queries. Can be used either as a class type or as a function.
//
// template = formatting template string
// data = array of either objects or arrays of values
function Inserts(template, data) {
if (!(this instanceof Inserts)) {
return new Inserts(template, data);
}
this._rawDBType = true;
this.formatDBType = function () {
return data.map(d=>'(' + pgp.as.format(template, d) + ')').join(',');
};
}

An example of using it, exactly as in your case:

var users = [['John', 23], ['Mike', 30], ['David', 18]];

db.none('INSERT INTO Users(name, age) VALUES $1', Inserts('$1, $2', users))
.then(data=> {
// OK, all records have been inserted
})
.catch(error=> {
// Error, no records inserted
});

And it will work with an array of objects as well:

var users = [{name: 'John', age: 23}, {name: 'Mike', age: 30}, {name: 'David', age: 18}];

db.none('INSERT INTO Users(name, age) VALUES $1', Inserts('${name}, ${age}', users))
.then(data=> {
// OK, all records have been inserted
})
.catch(error=> {
// Error, no records inserted
});

UPDATE-1

For a high-performance approach via a single INSERT query see Multi-row insert with pg-promise.

UPDATE-2

The information here is quite old now, see the latest syntax for Custom Type Formatting. What used to be _rawDBType is now rawType, and formatDBType was renamed into toPostgres.

How to insert an integer with nextval function in an multirow insert in pg-promise

Your column should be defined as this:

{
name: `object_id`,
init: () => `nextval('some_object_seq')`,
mod: `:raw`
}

As opposed to the answer by @baal, you do not need to use def, because you are not providing a default value, rather a complete override for the value, which is what init for.

And it can be used within upsert queries too.

how do I insert many, if it already exists, dont do anything with PG-promise

According to https://github.com/vitaly-t/pg-promise/issues/542,

simply append the ON CONFLICT part to your query, and that's it.

So in your case

const users = […];
const query = pgp.helpers.insert(users, cs) + ' ON DUPLICATE KEY UPDATE';

Where cs is your ColumnSet object. You also will typically use assignColumns method, as you can see from the same link.

Notice that the proper Postgres INSERT syntax uses ON CONFLICT DO UPDATE ….



Related Topics



Leave a reply



Submit