Operations Hub Professional - Custom Coded Workflow action that can be used to connect to and query a MySQL database. Important to note that you must define your secrets within your own workflow. They would store the database user name, password, hostname and table name. In this example we connect to the database, which is hosted on AWS and sear…

connectAndQueryMySQLDatabase.js

// Include the mysql node library
const mysql = require('mysql');

// Function is called when custom code action is executed
exports.main = async (event, callback) => {

  	// Define variables using CRM data as an input
    const email = event.inputFields['email'];
    const firstName = event.inputFields['firstname'];
    const lastName = event.inputFields['lastname'];
    let resultFound = false;

  	// Configure our connection to mySQL database, this is using secrets I defined
    var con = mysql.createConnection({
        host: process.env.MYSQL_HOST,
        user: process.env.MYSQL_USER,
        password: process.env.MYSQL_PASSWORD,
        database: process.env.MYSQL_DB
    });

  	// Connect to the mySQL database
    con.connect(function (err) {
        if (err) throw err;
        console.log("CONNECTED"); //For debugging
        // Create mySQL query to search for record in database with matching email
        var sql = "SELECT * FROM customer_info WHERE emailAddress ='" + email + "'";
      	// Query the database using the query defined previously
        con.query(sql, function (err, result) {
            if (err) throw err;
            if (result.length > 0) {
				//RESULTS FOUND
                console.log("RESULT FOUND: " + JSON.stringify(result));
                var results = JSON.stringify(result);
                let parsed = JSON.parse(results);
                console.log(parsed[0])
                resultFound = true;
            } else {
                //NO RESULTS ADD TO DATABASE
                console.log("NO RESULTS FOUND"); //For debugging 
                var sql = "INSERT INTO customer_info (firstName, lastName, emailAddress) VALUES ('" + firstName + "', '" + lastName + "', '" + email + "')";
                resultFound = false;

                con.query(sql, function (err, result) {
                    if (err) throw err;
                    console.log("1 RECORD INSERTED"); //For debugging
                });
            }

            con.end(); // terminate connection

          	// Pass resultFound variable to callback object for conditional logic in workflow
            callback({
                outputFields: {
                    resultFound: resultFound
                }
            })
        })
    });
}