This post is going to show you how to configure a database instance using the Relational Database Service (RDS) and add it to your Elastic Beanstalk environment on Amazon Web Services.

What is RDS?

RDS is a service that allows you host a relational database in the cloud without worrying about the infrastructure required to do so.

Assumptions

This guide assumes you have created an account with Amazon Web Services and you have an Elastic Beanstalk Environment setup already. It also assumes you’ve been following along the Deploying a Rails 5 Application Stack to AWS using Elastic Beanstalk and RDS.

Why not configure a database in Elastic Beanstalk?

If you allow Elastic Beanstalk to manage your database instance, you incur a significant risk that your Elastic Beanstalk environment lifecycle operations may inadventently destroy your database. Amazon recommends decoupling your database instance from your Elastic Beanstalk environment. Its a little more complicated to setup - but worth it.

TLDR: Do not use the Database section of your Elastic Beanstalk Configuration in Production

Add Subnets to your VPC

  1. Go the the VPC Console in AWS
  2. Choose Subnets
  3. Click Create subnet
    • Name your Subnet: myapp_dev_2
    • Choose your VPC
    • Choose an Availability Zone
    • Enter 10.0.1.0/24 for the IP range
    • Click Create
    • Click Close
  4. Click Create subnet
    • Name your Subnet: myapp_dev_3
    • Choose your VPC
    • Choose a DIFFERENT Availability Zone than the one you just picked
    • Enter 10.0.2.0/24 for the IP range
    • Click Create
    • Click Close

Create your Database Instance in RDS

We are going to use MySQL for this part of the guide. For two reasons, one, it supports a free tier. Second, because it has a great free database management app for the mac called Sequel Pro.

  1. Go to the RDS Console
  2. Click Create database
  3. Check Only enable options eligible for RDS Free Usage Tier
  4. Choose MySQL
  5. Click Next
    • Choose db.t2.micro for the instance class
    • Under Settings, name your instance: my-app-name for example
    • Choose a Master Username: my_app_user for example
    • Choose a Master Password: DO NOT LOSE THIS
    • Click Next
    • Choose your VPC
    • Choose Yes to Public accessibility (So you can connect with Sequel Pro)
    • In database options, name your database: my_app_production
    • Click Create database
    • Click View DB instance details

Congratulations! You just created a MySQL Database instance on RDS!

Wait until the DB instance status is Available before moving on to the next steps.

Optional: Connect to your Database Instance using Sequel Pro

  1. Download and Install Sequel Pro
  2. Create a new connection in Sequel Pro and fill in the following values:
    • Name (name your connection, for example: myapp)
    • Host (Endpoint)
    • Username (The database master username you chose)
    • Password (Your password)
    • Database (The database name you chose)
    • Port 3306
    • Leave SSL unchecked
    • Click Test connection
    • Click Save changes
    • Click Connect to connect to your database!

Allow your Elastic Beanstalk Environment to Connect to your Database Instance

By default, your new RDS Database Instance will not allow connections from your Elastic Beanstalk Environment. Technically, we need the EC2 instance created by the Elastic Beanstalk environment to be able to connect to the database. To do this, we will add an entry to our RDS security group.

  1. Go to the Elastic Beanstalk Console in AWS
  2. Click your application environment
  3. Click Configuration from the menu
  4. Click Modify for the Instances section
  5. Review and record the Group ID for your selected Security Group (maybe just leave this window open)
  6. Go to the RDS Console in AWS
  7. Click Databases from the menu
  8. Choose your database from the DB Identifier column
  9. Click the VPC security group listed under the Security heading
  10. Click the Inbound tab (notice your IP was already added as allowed to connect)
  11. Click Edit
  12. Click Add rule
  13. Choose MYSQL/Aurora as the Type (or simply type 3306 for the port)
  14. Start typing your Security group id you recorded from Step 5 (you can type sg-)
  15. Select your Security group from the auto-complete list
  16. Give your rule a description: myapp (makes it MUCH easier to glance at inbound rules and know what they are)
  17. Click Save

Optional: Test Elastic Beanstalk Environment’s Access to your RDS Database Instance

An easy and informative way to test access from your Elastic Beanstalk Instance to your Database is to connect to the EC2 Instance using SSH and run a command using the MySQL client. This isn’t just helpful for setup, but can be very helpful to troubleshoot issues if someone changes the Inbound rules or database password.

Get your RDS Database Instance Endpoint

  1. Go to the RDS Console in AWS
  2. Choose your database from the DB Identifier column
  3. Copy the Endpoint url under the Endpoint label

Connect to your EC2 Instance using SSH

  1. Navigate to the directory on your computer where you deploy your Elastic Beanstalk application from (Rails application directory)
  2. Connect to the EC2 instance for this environment using SSH by typing:
    • eb ssh
    • If you see the error: ERROR: This environment is not set up for SSH. Use “eb ssh –setup” to set up SSH for the environment.
    • Then follow our instructions on how to setup ssh
  3. Use the mysql client to test the connection by typing:
    • mysql -h (paste your endpoint url here) -u (type your Master username) -p
    • type your password when prompted
  4. You should see mysql> prompt
  5. Type exit to quit the mysql prompt
  6. Type exit again to close the SSH connection

Update Elastic Beanstalk Environment Configuration with your RDS Database Instance Connection Variables

Now that you have an RDS Database instance in the Available state, an Inbound rule setup in your Security group, and you’ve tested your connection from the EC2 instance to the database instance… you are ready to add the environment variables needed by your rails application to connect to the database!

View your RDS Database Instance Configuration

  1. Go to the RDS Console in AWS
  2. Choose your database from the DB Identifier column
  3. Leave this window open

Update your Elastic Beanstalk Environment Configuration

  1. Go to the Elastic Beanstalk Console in AWS
  2. Click your application environment
  3. Click Configuration from the menu
  4. Click Modify for the Software section
  5. Scroll down to Environment Properties
  6. Enter the following environment varible names and their values:
    • RDS_HOSTNAME (Endpoint)
    • RDS_PORT 3306
    • RDS_DB_NAME (The database name you chose)
    • RDS_USERNAME (The database master username you chose)
    • RDS_PASSWORD (Your password)
  7. Click Apply

Note: We could have used the eb setenv command to set these also, but that would have been tedious for this many variables.

If you forgot your database name you can connect to your EC2 instance using SSH, then connect to your rds database instance using the mysql client command we used above and type show databases; into the prompt.

Update the Production Database Configuration in your Rails Application

Last step! You have configured your Elastic Beanstalk environment to use your RDS Database instance and you’ve setup environment variables that declare the connection details - now its time to update your Rails application to use this database in production (after a deployment).

Optional: Install MySQL on macOS for Rails Development

If you’ve been following along and want to use Rails for your application database, you probably want to have a local installation for development.

  1. In the terminal, install MySQL using Homebrew
    • type: brew install mysql
  2. After the installation, start the MySQL service:
    • type: brew services start mysql

Note: the default user for this installation is root and the password is blank. The installation will only accept connections from localhost.

Add the mysql2 gem to your Rails Application

  1. Open your Rails application in the editor of your choice
  2. Add gem 'mysql2' to your Gemfile (you may only want to use this is production)
  3. Run bundle in the Terminal to install this gem

Update database.yml to use your Elastic Beanstalk Environment Variables

  1. Open your Rails application in the editor of your choice
  2. Edit config/database.yml and use the following values in the production configuration:

production:
  adapter: mysql2
  encoding: utf8
  database: <%= ENV['RDS_DB_NAME'] %>
  username: <%= ENV['RDS_USERNAME'] %>
  password: <%= ENV['RDS_PASSWORD'] %>
  host: <%= ENV['RDS_HOSTNAME'] %>
  port: <%= ENV['RDS_PORT'] %>
  1. Commit these changes
  2. Deploy your application using eb deploy

Congratulations! Your Rails Application in Elastic Beanstalk is now using your new RDS Database instance.