(Note: this article might be better named “the most complicated database.yml file possible”)

Have you ever accidentally added your database.yml file to subversion or moved a database.yml file to a computer where it doesn’t belong? How about moving your dev server from your laptop to your desktop to your school’s web host to some new install of Suse you’re playing with?

Most of the time this is a big headache. You get errors like the one in the title where the socket was not found. Different flavors of *nix will put the default mysql socket in different places on the machine and it can be a pain to try to keep up with things.

Well, here’s a a database.yml file that can relieve you of ever having to remember which machine you’re on. Many thanks to LazyAtom go gave me the idea for this (who, in turn, got the idea from James Duncan Davidson

Behold – The Code:

login: &login
  adapter:  mysql
  username: user
  password: 
  socket:  <%=
    ['/opt/local/var/run/mysql5/mysqld.sock', # darwinports
     '/opt/local/var/run/mysqld/mysqld.sock', # darwinports, again
     '/var/run/mysqld/mysqld.sock',           # ubuntu/debian
     '/tmp/mysql.sock'].select { |f| File.exist? f }.first %>

development:
  <<: *login
  database: rails_development

test:
  <<: *login
  database: rails_test

production:
  <<: *login
  database: rails

Ta-Da. I just installed this on my dev box and things are working great already.

For those of you who run a staging server to test your app in production, you might want to check out the following setup (I’m seriously considering a staging server setup for my apps. Also, this is good if you’re absolutely dead-set on using the same database.yml file for multiple hosts and multiple configurations (just as long as you know the hostname).

<% host = `uname -n`.strip %>
user: &user
  <% if host.eql? 'staging' %>
  username: staging_user
  password: staging_pass
  <% else %>
  username: user
  password: pass
  <% end %>

login: &login
  <<: *user
  adapter:  mysql
  socket:  <%=
    ['/opt/local/var/run/mysql5/mysqld.sock', # darwinports
     '/opt/local/var/run/mysqld/mysqld.sock', # darwinports, again
     '/var/run/mysqld/mysqld.sock',           # ubuntu/debian
     '/tmp/mysql.sock']
     .select { |f| File.exist? f }.first %>

development:
  <<: *login
  database: rails_development

test:
  <<: *login
  database: rails_test

production:
  <<: *login
  database: rails

If you want to partition the login info for several different users on a large collaborative project you may want to have a special file (ignored by SVN) that holds just the login info for a given user:

login: &login
  adapter:  mysql
  username: user
  password: 
  socket:  <%=
    ['/opt/local/var/run/mysql5/mysqld.sock', # darwinports
     '/opt/local/var/run/mysqld/mysqld.sock', # darwinports, again
     '/var/run/mysqld/mysqld.sock',           # ubuntu/debian
     '/tmp/mysql.sock'].select { |f| File.exist? f }.first %>

# replace the above login details with information from ./config/dblogin.yml
<%= file = File.join(RAILS_ROOT, "config", "dblogin.yml")
    IO.read(file) if File.exist?(file) %>

development:
  <<: *login
  database: rails_development

test:
  <<: *login
  database: rails_test

production:
  <<: *login
  database: rails

The main problem I have with the rails default ./config/database.yml format is that it’s not DRY. Here’s an example of a default database config:

development:
  adapter: mysql
  database: development_db
  username: username
  password: password
  sochost:  localhost
test:
  adapter: mysql
  database: testing_db
  username: username
  password: password
  sochost:  localhost
production:
  adapter: mysql
  database: production_db
  username: username
  password: password
  sochost:  localhost

Now, thanks to the Typo team (who have also made this blog possible), we have an alternative format that you can use to keep the redundancies out of your file:

login: &login
  username: username
  password: password
  adapter:  mysql
  host:     localhost

development:
  database: development_db
  <<: *login

test:
  database: testing_db
  <<: *login

production:
  database: production_db
  <<: *login

If this isn’t slick enough for you, Doug Alcorn has improved upon this by letting you change the login details for each host that you might run this application on. His code:

login: &login
  username: username
  password: password
  adapter:  mysql
  host:     localhost

# replace the above login details with information from ./config/dblogin.yml
<%= file = File.join(RAILS_ROOT, "config", "dblogin.yml")
    IO.read(file) if File.exist?(file) %>

development:
  database: development_db
  <<: *login

test:
  database: testing_db
  <<: *login

production:
  database: production_db
  <<: *login

And then you can have a separate file next to database.yml called dblogin.yml – and it can be different for each host. Here’s an example of what dblogin.yml could look like:

login: &login
  username: danger
  password: jesus_was_probably_good_at_juggling
  adapter:  mysql
  host:     localhost