javascriptsqlnode.jspostgresqlnode-postgres

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


A single row can be inserted like this:

client.query("insert into tableName (name, email) values ($1, $2) ", ['john', 'john@gmail.com'], callBack)

This approach automatically comments out any special characters.

How do i insert multiple rows at once?

I need to implement this:

"insert into tableName (name, email) values ('john', 'john@gmail.com'), ('jane', 'jane@gmail.com')"

I can just use js string operators to compile such rows manually, but then i need to add special characters escape somehow.


Solution

  • 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.rawType = true;
        this.toPostgres = 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.