SES Tracking Lambda To Mysql

index.js

const mysql = require('mysql2');

// const pool = mysql.createConnection({
//     host: process.env.MYSQL_HOST,
//     user: process.env.MYSQL_USER,
//     password: process.env.MYSQL_PASSWORD,
//     database: process.env.MYSQL_DB,
// });

const pool = mysql.createPool({
    host: process.env.MYSQL_HOST,
    user: process.env.MYSQL_USER,
    password: process.env.MYSQL_PASSWORD,
    database: process.env.MYSQL_DB,
    waitForConnections: true,
    connectionLimit: 25,
    queueLimit: 0
});


exports.handler = function(event, context, callback) {
    context.callbackWaitsForEmptyEventLoop = false;
    console.log(context.awsRequestId);
    console.log("Received event:", JSON.stringify(event, null, 2));
    var data = JSON.parse(event.Records[0].Sns.Message);
    if (data) {
        var headerValue = function(name) {
            var needle = data.mail.headers.find(obj => {
                return obj.name === name;
            });
            if (needle && needle.hasOwnProperty('value')) {
                return needle.value;
            }
        }

        var item = {
            requestId: context.awsRequestId,
            messageId: data.mail.messageId,
            eventType: data.eventType,
            timestamp: event.Records[0].Sns.Timestamp,
            // timestamp: data.mail.timestamp,
            source: data.mail.source,
            destination: data.mail.destination.toString(),
            company_id: headerValue('COMPANY-ID'),
            message_recipient_id: headerValue('MESSAGE-RECIPIENT-ID'),
            from: headerValue('From'),
            reply_to: headerValue('Reply-To'),
            to: headerValue('To'),
            subject: headerValue('Subject'),
        }

        switch (data.eventType.toLowerCase()) {
            case 'delivery':
                item.event_type_id = 1;
                break;
            case 'send':
                item.event_type_id = 2;
                break;
            case 'reject':
                item.event_type_id = 3;
                break;
            case 'open':
                item.event_type_id = 4;
                break;
            case 'click':
                item.event_type_id = 5;
                break;
            case 'bounce':
                item.event_type_id = 6;
                break;
            case 'complaint':
                item.event_type_id = 7;
                break;
            case 'rendering failure':
                item.event_type_id = 8;
                break;
            case 'deliverydelay':
                item.event_type_id = 9;
                break;
            default:
                item.event_type_id = 0;
        }

        if (data.bounce) {
            item.bounced_action = data.bounce.bouncedRecipients[0].action;
            item.bounced_status = data.bounce.bouncedRecipients[0].status;
            item.bounced_diagnosticCode = data.bounce.bouncedRecipients[0].diagnosticCode;
        }

        if (data.open) {
            item.open_ipAddress = data.open.ipAddress;
            item.open_userAgent = data.open.userAgent;
        }

        if (data.delivery) {
            item.delivery_smtpResponse = data.delivery.smtpResponse;
        }
        if (data.click) {
            item.click_link = data.click.link;
            item.click_ipAddress = data.click.ipAddress;
            item.click_userAgent = data.click.userAgent;
        }

        pool.query('INSERT INTO notifications ' +
            '(' +
            '`requestId`, ' +
            '`messageId`, ' +
            '`timestamp`, ' +
            '`click_ipAddress`, ' +
            '`click_link`, ' +
            '`click_userAgent`, ' +
            '`bounced_action`, ' +
            '`bounced_status`, ' +
            '`bounced_diagnosticCode`, ' +
            '`company_id`, ' +
            '`message_recipient_id`, ' +
            '`delivery_smtpResponse`, ' +
            '`destination`, ' +
            '`eventType`, ' +
            '`event_type_id`, ' +
            '`from`, ' +
            '`open_ipAddress`, ' +
            '`open_userAgent`, ' +
            '`reply_to`, ' +
            '`source`, ' +
            '`subject`, ' +
            '`to`' +
            ')' +
            ' VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', [
                item.requestId,
                item.messageId,
                item.timestamp,
                item.click_ipAddress,
                item.click_link,
                item.click_userAgent,
                item.bounced_action,
                item.bounced_status,
                item.bounced_diagnosticCode,
                item.company_id,
                item.message_recipient_id,
                item.delivery_smtpResponse,
                item.destination,
                item.eventType,
                item.event_type_id,
                item.from,
                item.open_ipAddress,
                item.open_userAgent,
                item.reply_to,
                item.source,
                item.subject,
                item.to,
            ],
            function(error, results, fields) {
                if (error) {
                    callback(error);
                    // throw error;
                }
                else {
                    // connected!
                    console.log("Results...");
                    console.log(results);
                    callback(null, 'OK');
                }
            });
    }
};

package.json

{
  "name": "sestomysql",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "author": "",
  "license": "ISC",
  "dependencies": {
    "mysql2": "^2.3.3"
  }
}