David Zhang
Nov 2, 2018 · 5 min read

This story is part of a series to get started with Relational Databases in Serverless:


In the last post we saw how to create the right resources so connect Lambda functions to Aurora. We created triggers that took a complete query in argument such as DROP TABLE user; 😱 But going forward, that’s not something I’d recommend…

This post is not much about Serverless and Lambda, but more on how you can securely connect to the database you just created to execute specific queries like creating, altering, dropping tables…


Let’s get started

1. Create an EC2 instance in the same region as your Aurora database

Keep the default configuration: leave it in the default VPC (configured by AWS for easy access via SSH) — see AWS doc on how to connect to your instance

Everything is already installed on this instance 😍

2. Create a VPC Peering Connection

We fall in the scenario “DB Instance In a VPC / Accessed By An EC2 Instance in a Different VPC”

Get the VPC ID of your EC2 Instance and your Aurora Instance

VPC ID of Aurora Instance

To create a VPC peering connection with a VPC in the same region

1. Open the Amazon VPC console at https://console.aws.amazon.com/vpc/.

2. In the navigation pane, choose Peering Connections, Create Peering Connection.

3. Configure the following information, and choose Create Peering Connection when you are done:

- Peering connection name tag: You can optionally name your VPC peering connection. Doing so creates a tag with a key of Name and a value that you specify.

- VPC (Requester): Select the VPC in your account with which you want to create the VPC peering connection.

- Under Select another VPC to peer with: Ensure My account is selected, and select another of your VPCs.

4. In the confirmation dialog box, choose OK.

5. Select the VPC peering connection that you’ve created, and choose Actions, Accept Request.

6. In the confirmation dialog, choose Yes, Accept. A second confirmation dialog displays; choose Modify my route tables now to go directly to the route tables page, or choose Close to do this later.

3. Update Routes Tables for both VPC

Now that the VPC Peering Connection is created, we need both VPC to be able to find each other.

Let’s start with the default VPC (where the EC2 Instance lives)

  • Note the CIDR of your Aurora VPC
VPC CIDR
  • Create the route table for the EC2 VPC if not already created
  • Add the route to Aurora’s VPC using its CIDR and VPC Peering Connection (it will autocomplete)

Do exactly the same for Aurora’s VPC Route table (add EC2 VPC’s CIDR)

4. Update Aurora Security Group to allow inbound and outbound traffic from EC2 Security Group

  • Check Aurora’s Security Group
  • Click on the security group
  • Allow Inbound and Outbound traffic from EC2’s security group (you can check on the instance which one it uses)

Let’s test

  • Connect to your EC2 instance
  • Connect to your databasemysql -h <DATABASE)ENDPOINT> -P <DATABASE_PORT> -u <USERNAME> -p and type your password
  • And you should be all set 🎊 💥 ⭐️!

What’s next?

  1. We created an Aurora instance via serverless.yml and Lambda functions which can query the database.
  2. We created an EC2 instance to securely connect to the database via our local machine. That way we can create our tables and schema (and any other query we want).

Though, it a bit painful to always have to manually connect to the DB, create or alter tables when needed. 😱

In software engineering, schema migration (also database migration, database change management[1][2]) refers to the management of incremental, reversible changes to relational database schemas. A schema migration is performed on a database whenever it is necessary to update or revert that database’s schema to some newer or older version. […] Schema migration allows for fixing mistakes and adapting the data as requirements change. They are an essential part of software evolution, especially in agile environments. (#wikipedia)

That’s what we are going to see next 🙃!

Checkout the other stories of the series:

mos-engineering

Mission driven engineers fixing student financial aid. https://mos.com — We are hiring 🙃

David Zhang

Written by

mos-engineering

Mission driven engineers fixing student financial aid. https://mos.com — We are hiring 🙃

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