We recently had a need to use Heroku with an external MySQL database. Thankfully, there was already a gem that solved the problem. However, it didn’t have that much by the way of documentation, so I wrote this up and contributed it to the author. Also, I added X.509 support, which we required.

NOTE: After my pull requests are merged, you should look to the main repository’s README which has a copy of the below.

Example: MySQL with a CA Certificate

In this example, we are setting up a connection to an external MySQL server using the default initializer.

What we have:

  • A CA certificate called ca-cert.pem

  • A database is available at mysql://username:password@server/dbname (where username, password, server, and dbname are the appropriate values for our server)

  • Other security, e.g. firewalls. Please make sure to open the appropriate ports and grant the necessary access for your database to be available externally. Further discussion is outside the scope of this document, but for accessing EC2, you might start at devcenter.heroku.com/articles/external-services.

First, we configure Heroku with the appropriate environment variables:

# You may have to specify the app name or remote name here via --app or --remote, respectively
heroku config:add EXTERNAL_DATABASE_CA='ca-cert.pem'
heroku config:add EXTERNAL_DATABASE_URL='mysql://username:password@server/dbname'

By default, heroku_external_db looks for the CA cert in

config/ca, so we need to commit it:

mkdir -p config/ca
cp path/to/ca-cert.pem config/ca
git add config/ca
git commit -v # Using -v since we want to make sure the contents are what we expect (e.g. not a private key)

Additionally, we need the mysql gem in our Gemfile since we are setting up a MySQL server:

echo "gem 'mysql', '~> 2.8.1'" >> Gemfile
bundle install # Need Gemfile.lock too

Keep in mind that Heroku installs its own database.yml for Rails apps and we have to install pg as well. Unfortunately, shared databases are mandatory (but are free).

$ heroku addons:remove shared-database:5mb
-----> Removing shared-database:5mb from our-app... failed
 !     Shared databases cannot be removed

PostgreSQL may still be useful to you if, for example, you want to have feature toggles in a local database, but the main data kept externally. However in our case, it also means all developers will need MySQL and PostgreSQL running locally, which is unfortunate.

One workaround is only installing pg in production:

# File: Gemfile

# *Only* needed on production.
group :production do
  gem 'pg', '~> 0.11.0' # Regardless of whether you plan to use the database or not, Heroku requires you have 'pg' installed.
end

Then bundle like so:

bundle install --without production

With our gems updated, commit:

git commit -av # Commit Gemfile and Gemfile.lock

Another option may be overriding database.yml somehow, although it would add complexity. For more info, please see stackoverflow.com/questions/4204724/strategies-for-overriding-database-yml.

With our dependencies out of the way, we can move on to testing the connection.

If you are making a new application, you may wish to have a simple MVC for testing that the connection works. E.g., for a blog style application with posts do:

rails generate scaffold post
# NOTE you probably want to change the default "Post.all" to "Post.limit(5)" or something similar
git add .
git commit # ...
# Don't forget to set a default route, etc.

With all these changes committed, we can deploy to our Heroku app:

git push heroku master # Your remote may be different

Now, since we are connecting to an existing database, we don’t need to run any migrations. (Keep in mind that when sharing a database, it is best to have one authoritative source for migrations to live.) If in your situation you’re creating a new database, you may need to do that, run migrations, seed the database, etc at this point.

Open our-app.heroku.com and we should see our data. If you happen to run into a problem, please check the logs first:

heroku logs --tail # Again, you may need to specify an app

If you are having a problem, a good starting point is double checking your passwords, usernames, security settings, etc.

Example: MySQL with X.509

The process is very much the same as the above example, except two extra environment variables and files are required. Below are the extra steps.

What we have:

  • A CA certificate called ca-cert.pem

  • A client certificate called client-cert.pem

  • A client key called client-key.pem

First, we configure Heroku with the appropriate environment variables:

heroku config:add EXTERNAL_DATABASE_CA='ca-cert.pem'
heroku config:add EXTERNAL_DATABASE_CERT='client-cert.pem'
heroku config:add EXTERNAL_DATABASE_KEY='client-key.pem'

By default, heroku_external_db looks for the files in config/ca, so we need to commit them:

mkdir -p config/ca
cp path/to/ca-cert.pem path/to/client-cert.pem path/to/client-key.pem config/ca
git add config/ca
git commit

The rest of the process is the same as in “MySQL with a CA Certificate”.

Reactions