Create a serverless API with AWS Lambda, AWS API Gateway and MySQL

In this article guides you through the setup of an API in AWS with using a MySQL database. The big advantage of this approach is that you’re going to have a running API and a database within a very short time.

Babette Landmesser
Dec 10, 2019 · 5 min read

If you want to use PostgreSQL, check out this article: Deploying a serverless REST API with Node.js, AWS Lambda, API Gateway, RDS and PostgreSQL.

We’re starting with preparing the AWS. Hence, make sure you have an account for the AWS console and sign in. Also check for your local node and npm version. You need npm version 6 or higher.

Create Permissions

Start by adding the permissions to create database entries and deploy our functions.

In your AWS account, please navigate to Services > IAM and select Users. It’s recommended to create a new IAM user for this API system.

Insert a user name (e.g. serverless) and make sure you select „Programmatic access“. In the next step, select from existing policies and choose

  • AmazonS3FullAccess (for storing your zipped code)
  • AWSLambdaFullAccess (for executing lambda functions)
  • AmazonRDSFullAccess (for accessing the database)
  • AmazonAPIGatewayAdministrator (for managing the endpoints via API Gateway)
  • AWSCloudFormationFullAccess (for deploying and creating stacks for the code)

Complete this step with submitting the user and don’t forget to store your access key ID and the access secret.


Create the database

Then head over to Services > RDS and click the button “create database”. For this short tutorial, choose MySQL in the free tier template. Add a database identifier and a master user with password. Next, select the smallest available instance size. Be aware: if you already know that you’re going to have large amounts of data stored, choose another option.

For now, select your database to be publicly available. I won’t cover security groups of AWS in this topic which you’ll need in case you don’t want it to be publicly available. Next thing you should do is expand the section „Additional configuration“. This is what I missed in my first attempts. Here you can give your database an actual name which makes it easier to connect to it later. All the other options are fine to stay with default values. So then, create the database.

You can now connect your database tool (e.g. Sequel Pro) to the database.

To follow the complete tutorial, create a table called “functions” and add a field “ID” and “name”. Fill it with dummy content.


Install serverless locally

Pre-Requirement: You have node (> v6) and npm installed on your machine

Run npm install -g serverless.

This installs the serverless CLI globally to your machine. Later, you’ll need the commands. Navigate to your project folder and run serverless. The framework is guiding you through your setup. You also need your previously added IAM user for serverless. The CLI asks you to add your credentials to deploy automatically your serverless application (later).

Next, install the serverless-offline package to your local folder: npm install serverless-offline. This enables local testing of the API without deploying to AWS after every change.

Inside your newly created folder you see a file serverless.yml. The tool generated code into it and some helpful comments are there as well. Now you’re ready to set up some paths.

Start changing your serverless.yml by adding your AWS region.

In my example, the API and Lambda functions should run in eu-central-1 (Frankfurt).

Also add the following to the end of the file to actually make the offline package available:

plugins:
- serverless-offline

Add Endpoints and Functions

You’re set up to create your endpoints and function calls. First, create the “functions” attribute. After that add an endpoint.

The pattern is the following:

functions:
myTestFunction:
handler: folder/javascript-file.myTestFunction
events:
- http:
path: /path
method: get
cors: true

This means a call of /path via HTTP and GET executes the function “myTestFunction”. This function is located in the javascript-file.js.

Declare the function as async inside your javascript-file.js:

myTestFunction: async (event, context, callback) => { }

Connect to the database

The next step now is to prepare the javascript to actually be able to connect to the database. Create a db-config.js and make sure you add it to .gitignore. Just add the following structure to this file:

module.exports = {
database: 'databasename',
host: 'hostname',
port: 'portnumber',
user: 'db-user',
password: 'db-password',
};

Run npm i serverless-mysql for enabling MySQL calls in serverless application. Add a new file called db-connect.js and connect to the database with the following code:

const dbConfig = require('./db-config');
const mysql = require('serverless-mysql')({
config: dbConfig
});
module.exports = mysql;

This establishes the database connection. Import it to the created javascript-file.js. In the very first line paste:

const db = require('../db_connect');

After that you’re able to execute SQL queries like db.query(). Attention, functions like this are asynchron and have to be awaited.


Call the database and return values

Coming back to your myTestFunction, simply add some code similar to this:

myTestFunction: async (event, context, callback) => {
context.callbackWaitsForEmptyEventLoop = false;
const functions = await db.query('SELECT * FROM functions'); await db.end(); if (functions) {
callback(null, {
statusCode: 200,
headers: {
'Access-Control-Allow-Origin': '*',
'Access-Control-Allow-Credentials': true,
},
body: JSON.stringify(functions),
})
} else {
callback('error', {
statusCode: 400,
headers: {
'Access-Control-Allow-Origin': '*',
'Access-Control-Allow-Credentials': true,
},
body: {
message: 'No functions found.'
},
})
}
},

This fetches all contents from the functions table. As you can see, the code awaits the query function and the end of the database connection. This makes sure that it fetched all results. Then it checks if the results is not null or false and returns the callback. As body it provides the stringified result.

I had to add these headers to every response because of CORS from my machine to the AWS.


Deploy your application

You’re nearly done. Head back to your console and run serverless deploy. If you previously set up the AWS connection for serverless properly, it should do all the rest of the magic for you. After the command ran successfully, try your API with a REST-service of your choice. You should see a similar response like this:

[
{
id: 1,
name: "funnyFunctionName"
},
{
id: 2,
name: "boringFunctionName"
},
]

Congratulations, you did it! 🥳

Babette Landmesser

Written by

Frontend / Angular Developer from Mainz, Germany. https://babettelandmesser.de

More From Medium

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade