Correctly configuring a Postgres DB for a Rails 4.2 app on Heroku

Question

I'm confused about how to configure the DB in a Rails 4.2 app that uses Postgres and Heroku.

Following the advice in this Heroku guide, you'll get a config/database.yml like this:

default: &default
  adapter: postgresql
  encoding: unicode
  pool: 5
  timeout: 5000

development:
  <<: *default
  database: app_name_development

test:
  <<: *default
  database: app_name_test

production:
  <<: *default
  database: app_name_production

But when I tried this, my development and test environment were using the same DB as the staging environment (note the file has no configuration for staging). That's no good.

This Heroku guide for connecting to the DB in Ruby mentions that any Rails apps before 4.2 would have their database.yml file overwritten by Heroku. Heroku will parse the DATABASE_URL environment variable and create a new database.yml file.

So I guess it's possible to just leave out the configuration in database.yml for any environments that you have on Heroku, such as staging and production. Your database.yml file could essentially look like Hound's (note the lack of a production configuration).

development: &default
  adapter: postgresql
  encoding: unicode
  database: app_development
  pool: 5

test:
  <<: *default
  database: app_test

But since we're using Rails 4.2, I don't think Heroku will override the database.yml file. In that case, do you have to specify DB configuration in database.yml for our environments on Heroku? Or is it still safe to leave them out? If we do need to specify the configuration for Heroku environments, will the following be sufficient?

staging:
  url: <%= ENV['DATABASE_URL'] %>

production:
  url: <%= ENV['DATABASE_URL'] %>

I'm also confused about the proper configuration for the development and test environments. As I mentioned above, the first configuration shown has those environments using the staging DB on Heroku, instead of a local DB.

This Heroku guide says to export the DATABASE_URL environment variable for your app to connect (once Postgres is installed and you can connect to it).

Assuming you export the DATABASE_URL env var as specified in the article, what does your configuration for development and test have to look like? Do we go with the configuration as shown in the first guide, e.g.

default: &default
  adapter: postgresql
  encoding: unicode
  pool: 5
  timeout: 5000

development:
  <<: *default
  database: app_name_development

test:
  <<: *default
  database: app_name_test

Or do we use a configuration as shown in this Heroku guide (which uses host and username)

development:
  adapter: postgresql
  host: localhost
  username: user
  database: app-dev

Update 1: Here's what I now know. A staging and production config isn't necessary in config/database.yml if you deploy to Heroku, no matter your Rails version. Prior to 4.2, Heroku would generate it's own database.yml file based on the value of the DATABASE_URL environment variable, overwriting your config file (if it exists). Since Rails 4.2, your app will use the DATABASE_URL environment variable directly (bypassing the database.yml file), so Heroku doesn't need to (and won't) generate a config file.

I also figured out why my development and test environments were using the remote staging DB from the Heroku app instead of the local DBs specified in their database.yml config. It was because my local .env file for development is based off of my staging .env file which contains environment variables for connecting to the database such as DATABASE_URL. Because DATABASE_URL was present in my development .env file, my Rails 4.2 app was using it and thus connecting to the staging DB. To fix it, I removed those environment variables from the development .env file and created the local DBs (and ran the migrations) with bundle exec rake db:setup.

Update 2: This section of the Rails Guides goes into more detail about how to configure the DB, worth a read: http://guides.rubyonrails.org/configuring.html#configuring-a-database

Getup

Answer 1

Most of your assumptions are correct. The following is a reasonable database.yml configuration file.

default: &default
  adapter: postgresql
  encoding: unicode
  pool: 5
  timeout: 5000

development:
  <<: *default
  database: app_name_development

test:
  <<: *default
  database: app_name_test

staging:
  url: <%= ENV['DATABASE_URL'] %>

production:
  url: <%= ENV['DATABASE_URL'] %>

Make sure that the RAILS_ENV is properly set on Heroku (either staging or production), or Rails will default to development.

Locally, the test will pick the test environment. By default, the app will start in development mode, using the development environment.

Marvelous

Simone, is it a good practice at the moment (same stack + Puma server) to put 'pool: <%= ENV['MAX_THREADS'] || 5 %>' to the production option? My database.yml is gitignored currently, but for threaded servers heroku suggests to set pool value to be the same as MAX_THREADS in the database.yml file. I'm asking this question to make sure I don't do any stupid thing and because heroku's docs phrasing sounds contradictionary when it come to database.yml. – Liner

Answer 2

In fact, many developers choose to ignore the database.yml in version control and never publish it into the repo. The reason for that is, that databases may be different on different machines, that's a reason not to keep the configuration common.

I'm working on a Rails 4.2 project right now, and Heroku has no problem with having no database.yml at all (both with PostgreSQL and MySQL, we tested both). Why? Because DATABASE_URL provides all the information necessary to access the database, even adapter name. How? Here's the formula:

adapter://username:password@hostname:port/database?options

Locally, I'm using Postgres with peer authentication: the database server assumes the same username that I'm using in my OS, username is covered, password is useless. Local machine is assumed when no host is given, although I can't tell if it tries to communicate via TCP/IP or Unix domain sockets, so I'm fine without host.

So the configuration you refer to as "shown in the first guide" is reasonable: it contains a minimum amount of settings and allows you to create more environments quite easily.

Gemsbok

I recommend never using "staging" on Heroku devcenter.heroku.com/articles/… – Variegate

Answer 3

Heroku doesn't create a database.yml on rails 4.2 because as of that version rails will detect the presence of that environment variable and use it to configure the database connection.

Adding

production:
  url: <%= ENV['DATABASE_URL'] %>

Makes it a little more obvious what is happening for those who might not be aware of it but won't change the behaviour. The configuring rails guide has more info in interactions between database.yml and DATABASE_URL if you need it.

Hyalo

Answer 4

To connect with ActiveRecord without Rails (e.g. sinatra):

url = URI.parse(ENV['DATABASE_URL'])
ActiveRecord::Base.establish_connection(
  encoding: 'unicode',
  pool: 5,
  timeout: 5000,
  reconnect: true,
  adapter: url.scheme,
  host: url.host,
  database: url.path.sub(%r{^/}, ''),
  username: url.user,
  password: url.password
)

Faitour