Jacob Chan

Using MySQL and Node.js for Alexa Skills (AWS RDS and AWS EB)

10 Feb 2019

In this post, we’ll discuss tips and tricks on how to get started with using AWS Relational Database Service (RDS). Please note that the example in this post is hosted in AWS Elastic Beanstalk (EB).

Initializing MySQL RDS Instance

In AWS, you can create an RDS database easily by navigating to the RDS dashboard. Select the MySQL engine. Name the database something that will be useful for later, when you connect this database instance to your application. Please note that the port it must be open on will be 3306, and note the security group. The EB must be deployed to the same security group. Upon completion, it will provide a URL, username and password.

Initializing & Configuring the Elastic Beanstalk Instance

When initializing EB instance, ensure that the security group is the same as the above RDS instance. The EB application will be using Node.js. When configuring, go to the application’s dashboard, and click “Configure”. The image below is a portion of what you will see. The red box will take you to where you want to set your environment variables. The green box is where the security group is displayed.

EB Config Page

In the index.js, the connection to the code is called using the mysql npm package. The code sample below shows the environment variables necessary to connect.

var mysql = require('mysql');
var connection = mysql.createConnection({
host: process.env.MYSQL_HOST || 'localhost',
port: process.env.MYSQL_PORT || '3306',
user: process.env.MYSQL_USER || 'root',
password: process.env.MYSQL_PASSWORD || 'password',
database: process.env.MYSQL_DATABASE || 'database_name'
});

Next, wherever you want to make a database call, you can simply create a promise, and call connection.query. Note: the function that is wrapping this must be denoted as asynchronous, with the async keyword.

return new Promise(function(res, rej) {
    connection.query('INSERT INTO users (name) VALUES ("'+sessionAttributes.user_name+'", 1);', (error, results, fields) => {
    if (error) throw error;
    //code
    });
}).then((speechText, exists) => {
    // code
})

Within the first parameter of connection.query, you can send any SQL query, such as SELECT, INSERT, DELETE, etc.

Note: in order to run the index.js on EB, one might have to add this as a config file in .ebextensions. (Concretely, add this in .ebextensions/02_nodecommand.config)

option_settings:
aws:elasticbeanstalk:container:nodejs:
    NodeCommand: "npm run start"