AWS Aurora MySQL database cannot be queried by Node Lambda function

Question

I'm trying to use a Lambda function to write data to an AWS Aurora MySQL database.

code

For some reason it doesn't seem to work. My code so far is very simple but it doesn't execute correctly. This is a simple Lambda function that is called when a user registers to Cognito and should create a new entry in the SQL database containing the user's credentials.

const mysql = require('mysql')

const db = mysql.createConnection({
    host: "****",
    port: *****,
    user: "*****",
    password: "*****",
    database: "*****",
})

export const lambdaHandler = async (event: any, context: any,) => {
    const user = {
        uid: event.request.userAttributes.sub,
        username: event.request.userAttributes.nickname,
        email: event.request.userAttributes.email,
    }

    console.log("before query")

    const query = `INSERT INTO users (Uid, Username, Email) VALUES (${user.uid}, ${user.username}, ${user.email})`
    await db.query(query, (err: any, result: any) => {
        if (err) console.error(err)

        console.log("query response: " result)
    })

    console.log("after query")

    return event;
};

The output of the function is as follows:

START RequestId: bb1d2143-97e4-4761-a48a-f482cf94f73b Version: $LATEST
2022-03-17T14:02:35.956Z bb1d2143-97e4-4761-a48a-f482cf94f73b INFO before query
2022-03-17T14:02:35.961Z bb1d2143-97e4-4761-a48a-f482cf94f73b INFO after query
END RequestId: bb1d2143-97e4-4761-a48a-f482cf94f73b
REPORT RequestId: bb1d2143-97e4-4761-a48a-f482cf94f73b Init Duration: 0.14 ms Duration: 138.07 ms Billed Duration: 139 ms Memory Size: 128 MB Max Memory Used: 128 MB

Note: The query function did not find the log record (should throw an error or log the response)

Strategy

The following policies are included in the execution role of the Lambda function: AWSLambdaVPCAccessExecutionRole and AWSLambda_FullAccess

Others

The Lambda function and Aurora are in different subnets of the same VPC. I'm not sure if this will cause a problem. But wouldn't this cause the function to throw an error? Error similar to missing authorization?

I don’t understand what I’m missing at this point

Answer 1

The problem is that db.query uses callback style instead of promise style. You need to convert it to use promises.

One way is to use Util.promisify...

const util = require('util');
const mysql = require('mysql');

const db = mysql.createConnection({
    host: "*****",
    port: *****,
    user: "*****",
    password: "*****",
    database: "*****",
});

// 将db.query转换为返回promise的函数
const promisifiedQuery = util.promisify(db.query);

export const lambdaHandler = async (event: any, context: any,) => {
    const user = {
        uid: event.request.userAttributes.sub,
        username: event.request.userAttributes.nickname,
        email: event.request.userAttributes.email,
    };

    console.log("before query");

    // 顺便说一下，这样做是不好的。请阅读有关如何避免SQL注入的内容。
    const query = `INSERT INTO users (Uid, Username, Email) VALUES (${user.uid}, ${user.username}, ${user.email})`;

    await promisifiedQuery(query).then(result => {
        console.log("query response: " + result)
        console.log("after query")
    }).catch(console.error)

    return event;
};