CODEWITHSUNDEEP
sqlnode.jspostgresql
Abdul Manaf
Abdul Manaf

Reputation: 5003

Execute multiple queries in a single statement using postgres and node js

I need to execute insert and delete query in a single statement like this

INSERT INTO COMPANY (ID,NAME) VALUES (1, 'Paul');DELETE FROM  COMPANY WHERE ID='12';

This is my node.js code for executing query

pg.connect(pgConString, function (err, client, done) {
        if (err) {
            callBack("DB connection failed. " + err, null);
            return;
        }
        var query = client.query({
            text: "INSERT INTO COMPANY (ID,NAME) VALUES (1, 'Paul');DELETE FROM  COMPANY WHERE ID='12';"
            values: [1, "Poul1"],
            name: "insertQuery"
        });
        query.on("error", function (err) {
            callBack("DB insertion failed. Error Message: " + err, null);
            return;
        });

        query.on('end', function (result) {

            done();
            return;
        });
    });

I got error message like this

error: cannot insert multiple commands into a prepared statement

is it possible to execute multiple queries in postgresql database using node.js ?

Upvotes: 23

Views: 29626

Answers (3)

n3ko
n3ko

Reputation: 415

Although there is an accepted answer, it's a bit obsolete. For now node-postgres handles multiple queries in one call and returns a neat little array to you, like:

const db.query('select 1; select 2; select 3;')
results.map(r => (r.rows[0]['?column?']))
// [ 1, 2, 3 ]

There is also an alternative 'opinionated' library, called pg-promise, which also accepts query chains in one call and works with sql files as well.

Upvotes: 10

vitaly-t
vitaly-t

Reputation: 25940

When using pg-promise...

First, we declare our queries + values via a flexible QueryFormat list:

const queries = [
    {query: 'select * from products where price > $1', values: [12.5]},
    {query: 'select * from payments where amount < ${amount}', values: {amount}}
];

Then we create a single-query formatted string:

const sql = pgp.helpers.concat(queries);

And then we execute it, and retrieve the result:

const [products, payments] = await db.multi(sql);

See: concat, multi.

Upvotes: 5

anas p a
anas p a

Reputation: 411

Try like this 

pg.connect(pgConString, function (err, client, done) {
    if (err) {
        callBack("DB connection failed. " + err, null);
        return;
    }
    client.query({
        text: "INSERT INTO COMPANY (ID,NAME) VALUES (1, 'Paul');",
        values: [1, "Poul1"],
        name: "insertQuery"
    });

    client.query({
        text: "DELETE FROM  COMPANY WHERE ID='12';",
        name: "deleteQuery"
    });

    client.on("error", function (err) {
        callBack("DB insertion failed. Error Message: " + err, null);
        return;
    });


});

Upvotes: 4

Related Questions