Yea I know: Heroku loves Postgres and Postgres is sooooooo great. But MySQL is great too and Heroku now supports it via a couple add-ons . It is not difficult to set up if you know what to do, but there are a surprising number of “gotchas” that are not well documented. These instructions will assume that you already have a Heroku app setup and you have a local Rails app with a Git.
NOTE: I’m using Rails 4.2. At the time of writing this Rails 5 just came out so there may be some differences.
Add mysql2 gem
The mysql2 gem is newer and more performant than the original mysql gem. I’m not sure of the support for the original, but I was getting mysterious 500 errors before switching to mysql2 so use the new one.
Add this to your Gemfile
gem 'mysql2', '~> 0.3.18'
NOTE: The version matters! If you don’t specify the version, you will get a newer version of the gem, which (at the time of writing) is not compatible with newer versions of Rails. Weird, I know. This “gotcha” might be fixed in Rails 5.
Don’t forget to run bundle install and then commit the changes. There are piles of StackOverflow questions out there posted by people who add the gem but forget to bundle before deploying to Heroku, so of course the Heroku build fails because it doesn’t know about the necessary dependancy.
Update database.yml
Modify your database.yml file so that your production section looks like this:
production:
url: <%= ENV['DATABASE_URL'] %>
This tells Rails to expect an environment variable with a magic string of database credentials that we’re going to create in a moment. (Commit this as well)
Add the database add-on in Heroku
Add a MySQL database add-on in Heroku. I used ClearDB.
Heroku will automatically add a config var with the database credentials in the form of a URL. You find the config vars under the Settings tab, and click the button to “Reveal config vars.”
If you use the ClearDB add-on, the name of the config var will be CLEARDB_DATABASE_URL and the value will look something like: mysql://abcdef:1234567@somewhere-someplace-123.cleardb.net/heroku_abcdefg123456?reconnect=true
If you’re curious to understand the parts of the string, here’s the structure: db://user:password@host_url/database_name
Rails will automagically parse this string and use the credentials. But it’s helpful to know what the individual parts are if you want to connect with a client.
Create a new config var
Create a new config var called DATABASE_URL and copy the exact same value from the original config var. By convention, a Rails app on Heroku will look for a config var with this exact name. I experimented with trying to access the credentials via the original CLEARDB_DATABASE_URL value but I could not get it to work. There are many factors that might affect this behavior.
There’s one more thing that needs to change. Recall that we installed the mysql2 gem. Rails will parse the protocol at the beginning of the config var to determine which MySQL adapter it should use. So we need to change the beginning of the config var from mysql:// to mysql2:// in order for things to work correctly. You might be able to leave the original value if you overtly specific the adapter in your database.yml file, but I haven’t experimented with this.
So now your updated config var should look something like this:
mysql2://abcdef:1234567@somewhere-someplace-123.cleardb.net/heroku_abcdefg123456
You’re done
You are now ready to deploy to Heroku.
$ git push heroku
$ heroku run 'rake db:setup'
Now your Rails 4.x app should be up and running with MySQL on Heroku