Hi, I'm Ben Oakes and this is my geek blog.
Currently, I'm a Software Developer at Hedgeye.
Previously, I was a Research Assistant in the Early Social Cognition Lab at Yale University and a student at the University of Iowa.
I also organize NewHaven.rb.
I do development with Ruby, JavaScript, SQL, HTML, and CSS. I have an amazing fiancée named Danielle Smith.
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
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:
We have a simple application that doesn’t have an ActiveRecord dependency. It’s deployed to Heroku, and it’s been working fine on Rails 3.0.x since April 2011. We knew we weren’t using ActiveRecord for database connectivity, but we let it be, since it wasn’t causing any issues.
When upgrading to Rails 3.1, we found that every single page would give ActiveRecord::ConnectionNotEstablished on our staging environment on Heroku. The same error didn’t happen in development. Although we might have been able to get gem 'pg' set up and working, we really didn’t need an ActiveRecord dependency at all.
That’s what Rails 3.1.0 generates when running rails new myproject --skip-active-record. (Note that require "active_record/railtie" is commented out.) This solved our ActiveRecord::ConnectionNotEstablished problem, but gave us a few others, namely:
Fixtures
ActiveRecord::RecordNotFound (used for 404s)
Fixtures
There’s some normal stuff to get rid of in terms of spec_helper.rb and/or test_helper.rb. Here’s an example:
# File spec/spec_helper.rb
# # If you're not using ActiveRecord, or you'd prefer not to run each of your
# # examples within a transaction, remove the following line or assign false
# # instead of true.
# config.use_transactional_fixtures = true
You may have others. Tarantula had to be adjusted for us, for example.
ActiveRecord::RecordNotFound
ActiveRecord::RecordNotFound, however, was an interesting problem. Everything worked fine without ActiveRecord except the places where we were using ActiveRecord::RecordNotFound to give a HTTP 404 to the user agent. That seems strange in a lot of ways, because a 404 shouldn’t have anything to do with your chosen ORM. My first intuition was to do require 'active_record/errors' (see also the Rails docs), but that caused problems with assumptions in 'rspec/rails'.
Right now, the below is what we ended up with:
# File: config/application.rb
# Pick the frameworks you want:
# require "active_record/railtie"
require "action_controller/railtie"
require "action_mailer/railtie"
require "active_resource/railtie"
require "sprockets/railtie"
require "rails/test_unit/railtie"
# For errors like ActiveRecord::RecordNotFound
require "active_record"
Our automated tests (Rspec, integration tests, Tarantula, Selenium, etc) all pass with it and we no longer get ActiveRecord::ConnectionNotEstablished, but we still have an ActiveRecord dependency I don’t like. (There must be another error we can raise — I don’t entirely like the render '/404.html', status: 404 solution for several reasons.)
I ran into a Rails 3.0.1 timezone issue today that I didn’t see discussed many other places.
Basically, I just want to have a page in my app that shows the time in different time zones. That seems simple and something tailor suited for timezone support.
I started with this:
>> Time.now.in_time_zone('EST')
=> Tue, 23 Nov 2010 11:09:42 EST -05:00
Okay, so far so good. Next:
>> Time.now.in_time_zone('PST')
NoMethodError: undefined method `period_for_utc' for nil:NilClass
[...]
>> Time.now.in_time_zone('CST')
NoMethodError: undefined method `period_for_utc' for nil:NilClass
[...]
Wait, that’s odd… why doesn’t that work? After searching, I found you could use some city names like so:
>> Time.now.in_time_zone('Tokyo')
=> Wed, 24 Nov 2010 01:04:54 JST +09:00
But of course JST won’t work:
>> Time.now.in_time_zone('JST')
NoMethodError: undefined method `period_for_utc' for nil:NilClass
[...]
And neither will major American cities:
>> Time.now.in_time_zone('New York')
NoMethodError: undefined method `period_for_utc' for nil:NilClass
[...]
>> Time.now.in_time_zone('Chicago')
NoMethodError: undefined method `period_for_utc' for nil:NilClass
[...]
Nothing too relevant came up when I googled the above errors and phrases (part of why I’m posting this), but then I came across the rake time:zones:us and rake time:zones:all Rake tasks. They list valid timezones for you.
The thing that gets me is that 'EST' and 'Tokyo' work as expected, but 'PST' and 'New York' don’t. These are what I ended up with:
>> Time.now.in_time_zone('Eastern Time (US & Canada)')
=> Tue, 23 Nov 2010 11:08:12 EST -05:00
>> Time.now.in_time_zone('Central Time (US & Canada)')
=> Tue, 23 Nov 2010 10:06:08 CST -06:00
>> Time.now.in_time_zone('Pacific Time (US & Canada)')
=> Tue, 23 Nov 2010 08:06:23 PST -08:00
Ironically, it lists EST, CST, and PST in the results. It’s still confusing to me why the longhand version is the preferred notation here (sometimes), but at least you’re given the tools to look it up.
Like always, let me know if this post helps you through an error. We’re all in this together.
I recently ran into a problem where I needed to use validates_uniqueness_of with more than one scope. Basically, I have resources that users can either share or keep to themselves. Each resource has a description that should be unique. However, the scope of the uniqueness depends on the situation. In pseudo-code, I wanted something like:
if shared?
validates_uniqueness_of :description, :scope => :shared
else
validates_uniqueness_of :description, :scope => :user_id
end
(That is, if you share, the description should be unique across all the shared resources. Otherwise, it should just be unique within your stuff.)
That seems straightforward, but it’s complicated by the fact that Rails validations are at the class-level rather than the instance-level. To me, it makes more sense to execute validations at the instance-level.
Specifically because they’re not executed at the instance-level, validations like validates_uniqueness_of need “smelly” option keys like :if and :unless. If the validations were executed in the proper context (i.e., in the context of the instance that’s being validated), Rails wouldn’t need any of that hackery. The real if and unless could be used directly.
The Hashrocket folks said something similar in their Rails 3 Blog Club – Week 1 video. As far as I know, the situation is staying the same in Rails 3, even with all the validation refactoring. Even so, I think there’s a strong argument for moving validations to the instance level.
For example, if validations were executed in the context of the model instance, the above would have translated directly to something like this:
class MyModel < ActiveRecord::Base
# [...]
def validate
if shared?
validate_uniqueness_of :description, :scope => :shared
else
validate_uniqueness_of :description, :scope => :user_id
end
end
end
(Note for blog skimmers, the above code does NOT work.)
The :if / :unless keys still feel like a hack to me. (I’m scared they will break in non-obvious ways, to be honest — I think I’ve already found a couple of corner cases.)
Am I missing something when it comes to why validations were designed this way? If so, how would you approach this problem?
I was developing against SQLite for a while and didn’t notice that I had accidentally put "NULL" (i.e. as a string) in the fixtures. SQLite didn’t care about this, but MSSQL certainly did. Because I specified constraints on the column widths, I got the error:
ActiveRecord::StatementInvalid: DBI::DatabaseError: 22001 (8152) [FreeTDS][SQL Server]String or binary data would be truncated.
At first I thought you had to use nil in fixtures, but in fact, it’s actually NULL that you want.
It took me quite a while to figure out that Time objects aren’t treated exactly as you might expect when using :conditions in a find or named_scope. From what I can tell, without extra help, Time objects are just treated as dates. This is fine if you’re checking conditions like I was outside the same day, but not if they are in the same day. This came up in writing a unit test for a model. My tests for the named scopes weren’t testing within the same day (i.e. only for values like 2.days.ago), so they passed, but other tests that expected to be able to set the open and close dates to a time within the same day would fail (confusingly).
So, it was incorrect to use the following, because Rails was treating them like a :date instead of a :datetime.
named_scope :active, lambda { now = Time.now; {:conditions => ['? > open_date and ? < close_date', now, now] } }
Instead, the following was used to make sure the time parts are included:
named_scope :active, lambda { now = Time.now; {:conditions => ['? > open_date and ? < close_date', now.to_s(:sql), now.to_s(:sql)] } }
