Azure functions integration with Postgres in Node.js

Question

I want to access to Azure Postgres DB from an Azure function. Below is the node.js source code of Azure function.

 module.exports = async function (context, req) {
try {
    context.log('Start function!');
    var pg = require('pg');

    const config = {
        host: 'taxiexample.postgres.database.azure.com',
        user: 'postgres@taxiexample',
        password: 'QscBjk10;',
        database: 'taxi',
        port: 5432,
        ssl: false
    };

    var client = new pg.Client(config);
    const query = 'insert into taxi values (\'2\');';
    context.log(query);
    client.connect();
    var res = client.query(query);
    await client.end();

    context.log(res);

} catch (e) {
    context.log(e);
} finally {
    context.res = {
        status: 200,
        body: "ok"
    };
}

};

The record doesn't insert, the res object returns the following error:

2020-03-04T23:03:59.804 [Information] Promise {
  <rejected> Error: Connection terminated
      at Connection.<anonymous> (D:\home\site\wwwroot\HttpTrigger1\node_modules\pg\lib\client.js:254:9)
      at Object.onceWrapper (events.js:312:28)
      at Connection.emit (events.js:228:7)
      at Socket.<anonymous> (D:\home\site\wwwroot\HttpTrigger1\node_modules\pg\lib\connection.js:78:10)
      at Socket.emit (events.js:223:5)
      at TCP.<anonymous> (net.js:664:12)
}
2020-03-04T23:03:59.811 [Information] Executed 'Functions.HttpTrigger1' (Succeeded, Id=944dfb12-095d-4a28-a41d-555474b2b0ee)

Can you help me? Thanks

Could you please try to use ```.catch()``` method to get the detailed error message? — Jim Xu, Mar 05 '20 at 09:08
I've inserted the .catch() clause, but the code doesn't launch any exception. — Michel Foucault, Mar 05 '20 at 09:43
Could you please check the firewall of your database? Have you opened ```Allow access to Azure services``` in the firewall. Besides, could you please check if you have enabled ```Enforce SSL connection```(https://learn.microsoft.com/en-us/azure/postgresql/concepts-ssl-connection-security)? If you did that, please set ```ssl``` as ```true``` in your code or disabled it. — Jim Xu, Mar 06 '20 at 01:56

Michel Foucault · Accepted Answer · 2020-03-11T15:00:09.040

I've resolve it, it was a trivial programming error. Below is the correct source code

module.exports = async function (context, req) {

    try {
        context.log('Start function!');
        var pg = require('pg');

        const config = {
            host: 'example.postgres.database.azure.com',
            user: 'postgres@example',
            password: 'passwd;',
            database: 'test',
            port: 5432,
            ssl: true
        };

        var client = new pg.Client(config);
        const query = 'insert into test values (\'2\');';
        context.log(query);

        client.connect(err => {
            if (err) {
                console.error('connection error', err.stack);
            } else {
                console.log('connected');
                client.query(query, (err, res) => {
                    if (err) throw err;
                    console.log(res);
                    client.end();
                })
            }
        });

    } catch (e) {
        context.log(e);
    } finally {
        context.res = {
            status: 200,
            body: "ok"
        };
    }

};

Please explain - what did you change? I'm faced with a similar problem ... Did you only switch on SSL? I see you also used the callback of the connect-function - anything else? — SimonSimCity, Dec 07 '20 at 07:25

Azure functions integration with Postgres in Node.js

1 Answers1