Connect to Your Local Database Using Twilio Functions

Time to read: 4 minutes

March 04, 2020

Written by

Twilion

Connect to Your Local Database Using Twilio Functions

In this post, you’ll learn how to connect to a local or remote database such as MySQL and make queries using Twilio Functions.

Prerequisites and Items accomplished

Sign up for a free Twilio Account
Configure MySQL on a local machine
Configure Ngrok on a local machine

Working with Twilio Functions

Twilio Functions (Beta) provides a complete runtime environment for executing your Node.js scripts. Functions integrates the popular package manager npm and provides a low latency Twilio-hosted environment for your application.

Building apps with Functions

Here's an example of what a Functions script looks like:

exports.handler = function(context, event, callback) {

  // code

  // invoke callback function

}

The handler method is the starting point of your function, and accepts the following arguments:

The context object includes information about your runtime, such as configuration variables, environment variables.
The event object includes information about a specific invocation, including HTTP parameters from the HTTP request.
The callback function completes the execution of your code.

Configure a local MySQL instance

MySQL is an open-source relational database management system. MySQL is a very popular database, and we’re also using it today for its quick and easy installation. Please refer to the following for details on installing and configuring a local instance of MySQL.

Once your local instance is up and running, make sure it is accessible via the internet. Run the following queries to check if it's running on the appropriate port and networking is enabled:

SHOW VARIABLES LIKE 'skip_networking';

The above query should result in skip_networking value= off by default.

SHOW VARIABLES LIKE 'port';

The above query should result in a port value set to 3306 by default.

Let’s create and use a database by executing the following script:

create database User;
use User;

Once the database is selected, create a user table and insert some values by executing the following script.

CREATE TABLE Users (

phone_number VARCHAR(15) NOT NULL, 

first_name VARCHAR(30) NOT NULL, 

last_name VARCHAR(30) NOT NULL, 

PRIMARY KEY (phone_number)

 );

INSERT INTO Users (phone_number, first_name, last_name)
VALUES ("+12345678902", "Pooja", "Srinath");

Expose network services with ngrok

TCP tunnels allow you to expose any networked service that runs over TCP. ngrok offers secure tunneling capabilities to expose your local development environment. If you haven’t yet, visit ngrok’s download page and download and install the tool.

Once you have the tool installed, here is the script to run:.

$ ngrok tcp 3306

The output of the script returns the following details in the terminal:

The port numbers are subject to change – these are ephemeral ports. Hence, we recommend copying the values that are seen in your terminal.

To validate the exposed service is available from the broader internet, run the following script to affirm that MySQL is accessible:

mysql --protocol=tcp --host=0.tcp.ngrok.io --port=15432 --user=root -p

Create Your Twilio Functions

In the following sections, we’re going to be building Twilio Functions. If you’d like more context on Twilio and our serverless tools, please refer to the following articles:

Configure the Function environment variables

The environmental variables for a Twilio Function are configured in the configure section of the Twilio functions portal, as shown in the image below.

Setting environment variables in Twilio Functions

The key-value pairs are nothing but the details of your DB configurations obtained from validating the exposed services. (“Configure a local MySQL instance” above)

The entries of the configure section are provided in the table below:

Key	Value
database	User
host	0.tcp.ngrok.io
password	password
port	18187
user	root

Configure the Function dependencies

The node dependencies are added through the configure section of the Twilio Functions console, as shown below.

We haven't specified the version – Twilio’s console picks the latest versions available by default. However, I recommend you use mysql > 2.18.1

Create a new Function in the Twilio Console

Upon configuring the above values, create a Twilio Function to connect to a local DB and also perform the necessary actions.

Async and await act as syntactic sugar on top of promises, making asynchronous code easier to write and to read afterward.

Create a function named WriteToMySQL and also specify the path, as mentioned in the screenshot.

Overview of Twilio Function with local database

Paste the below code in the code console

const mysql = require("mysql");

exports.handler = async function(context, event, callback) {
  context.callbackWaitsForEmptyEventLoop = false;
  const config = {
    host: context.host,
    port: context.port,
    user: context.user,
    password: context.password,
    database: context.database
  };

  console.log("connected", config);
  try {
    const db = new Database(config);

    db.connection.connect();
    const users = await db.query("select * from users");
    await db.close();
    console.log(users);
    callback(null, users);
  } catch (e) {
    callback(e);
  }
};

class Database {
  constructor(config) {
    this.connection = mysql.createConnection(config);
  }
  query(sql, args) {
    return new Promise((resolve, reject) => {
      this.connection.query(sql, args, (err, rows) => {
        if (err) return reject(err);
        resolve(rows);
      });
    });
  }
  close() {
    return new Promise((resolve, reject) => {
      this.connection.end(err => {
        if (err) return reject(err);
        resolve();
      });
    });
  }
}

From the above code, we can read the values from a database.

In the line const users = await db.query('select * from users');, we wait for the connection to be established to query the entries of the table users. Below is the code to write to the database.

const mysql = require("mysql");
exports.handler = async function(context, event, callback) {
  context.callbackWaitsForEmptyEventLoop = false;
  const config = {
    host: context.host,
    port: context.port,
    user: context.user,
    password: context.password,
    database: context.database
  };

  console.log("connected", config);
  try {
    const db = new Database(config);
    db.connection.connect();
    const user = {
      phone_number: "+12222222222",
      first_name: "Happy",
      last_name: "debugging"
    };
    const users = await db.query("insert into users set ?", user);
    await db.close();
    console.log(users);
    callback(null, users);
  } catch (e) {
    callback(e);
  }
};

class Database {
  constructor(config) {
    this.connection = mysql.createConnection(config);
  }
  query(sql, args) {
    return new Promise((resolve, reject) => {
      this.connection.query(sql, args, (err, rows) => {
        if (err) return reject(err);
        resolve(rows);
      });
    });
  }
  close() {
    return new Promise((resolve, reject) => {
      this.connection.end(err => {
        if (err) return reject(err);
        resolve();
      });
    });
  }
}

In both the codes provided above, we have used the object-oriented programming approach in Javascript.

Debugging your Functions

If you get an error stating -"MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client", I recommend running the following commands:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
-- then
FLUSH PRIVILEGES;

In-order test these functions quickly, you can spin up a studio project to receive an incoming call to the Twilio number. Say a friendly message and then connect the say widget to the functions widget. This will make a call to the above serverless Twilio function. Once the values are returned from the function, pass those responses as an SMS to your phone number.

Example Studio flow for connecting to a database

If you're unfamiliar with Twilio Studio or with how to buy a number, please refer to the following articles:

Conclusion:

Reading and writing to a database is simple with Twilio Functions. And better – you don't have to worry about complex server setup as the runtime environment is provided by our Twilio Functions console.

Pooja Srinath is a Senior Solutions Engineer at Twilio. She's focused on learning new things and building cool apps to help budding developers. She can be found at psrinath [at] twilio.com.