How do I get a node.js lambda function to write to postgresql and return the error/response?

Question

I'm trying to build an API using AWS Lambda & node.js which writes a record into a postgres database. I then want to return the response from the db back as the API response.

I can get write action to work from my laptop, but not as a Lambda function. It just doesn't seem to execute the client.query function. I think this is because the event.handler runs asynchronously but I'm stuck on how to make this work.

Secondly I want to pass the res or err back out of the client.query function so I can return it as the API response. I can't figure out how to get those out of the query function even running locally.

I thought declaring response at the start would make it global but whilst debugging it is undefined at the point where I'm trying to return it.

const {Client} = require('pg');

var response

exports.handler = async (event) => {

    const body = JSON.parse(event.body);
    const query = {
        text: "insert into testTwo (test) values($1)",
        values: [body.text],
    }

    const client = new Client({
        user: '',
        host: '',
        database: '',
        password: '',
        port: 5432,
    });
    client.connect();

    client.query(query, (err,res) => {
        console.log("Why doesn't this execute?");
        if (err) {
            response = {
                statusCode: 400,
                body: JSON.stringify(err)
            };
        } else {
            response = {
                statusCode: 200,
                body: JSON.stringify(res)
            };
        }
    client.end();

    });

    return response;

};

Can you wrap this line client.connect(); in try/catch and confirm the connection is correctly established since if not this can cause the issues you are describing — Tamir Klein
– Tamir Klein, Commented Apr 30, 2019 at 17:57
I tried this and nothing gets logged either locally or as lambda function. I then tried adding a call back instead. Running locally I can see the connection being made successfully. In Lambda nothing gets logged, neither error nor success. — Kester Harding
– Kester Harding, Commented May 2, 2019 at 12:21
Where do you host your postgresql server? It seems your Lambda doesn't have access to this server. Another option is that your Lambda is in a VPC and your internet connection is blocked (See this answer as an example) — Tamir Klein
– Tamir Klein, Commented May 2, 2019 at 12:31
Thanks for taking the time to look at this - I eventually figured it out and got it working. — Kester Harding
– Kester Harding, Commented May 2, 2019 at 17:12

Kester Harding · Accepted Answer · 2019-05-02 17:11:31Z

OK So I eventually got it working, though this isn't perfect as it doesn't deal with errors in the SQL yet.

The mistake was using the callback function instead of using client.query as a promise. By adding await to the promise it kept Lambda running until the query resolved.

Working code as follows:

exports.handler = async(event) => {

    const {Client} = require('pg');

    const body = JSON.parse(event.body);
    const query = {
        text: "insert into test (text) values($1)",
        values: [body.text],
    };

    const client = new Client({
        //credentials
    });

    client.connect();

    const result = await client.query(query);
    const resultString = JSON.stringify(result);    

    client.end();

    const response = {
        "statusCode":200,
        "body":resultString
    };

    return response;

};

Collectives™ on Stack Overflow

How do I get a node.js lambda function to write to postgresql and return the error/response?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related