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
Does IE9 Support Console.Log, and Is It a Real Function
How to Access Svg Elements with JavaScript
How to Check a Radio Button with Jquery
How to Get Element by Xpath Using JavaScript in Selenium Webdriver
Differencebetween a User and a Guildmember in Discord.Js
How to Debug JavaScript/Jquery Event Bindings with Firebug or Similar Tools
Can't Bind to 'Ngmodel' Since It Isn't a Known Property of 'Input'
How to Remove All Line Breaks from a String
How to Determine User's Locale Within Browser
Extract Hostname Name from String
JavaScript Before Leaving the Page
How to Escape Regular Expression Special Characters Using JavaScript
Differencebetween String Primitives and String Objects in JavaScript
How Does JavaScript's Sort() Work
Number Prime Test in JavaScript