database.yml should be checked in

It's generally acknowledged to be good practice to keep your production database.yml file away from your version control system. Why? So that people snooping around your version control system don't find your database password. There are plenty of examples out there that tell you how to achieve this with Capistrano.

When I say that database.yml should be checked in, I'm talking the version that you use during development.

Teams often resist sharing database config files so they can keep their individual database passwords private and specify operating system specific settings (such as the location of MySQL's socket file).

Can you share configuration between different developers? A couple of approaches spring to mind.

  1. Don't store database.yml in version control, but create database.yml.example instead. Force each developer to edit it themselves when they check out the code.
  2. Persuade your team mates to use the same password and check that in.

Neither is satisfactory. When I check my code out I expect it to just work, and I'm sure as hell not going to give anybody who can access the source a free run at my laptop's database.

Read on for my solution.

So how do you commit database.yml to your repository without storing your password in version control? Stick it in a config file. It just so happens that the perfect file for the job already exists. I quickly tire of typing my password when connecting to MySQL, but you can avoid retyping it by adding it to ~/.my.cnf:

$ cat ~/.my.cnf
[mysql]
user = root
password = gumo4Gum

Set the permissions on that file so that only you can read it:

$ chmod 600 ~/.my.cnf

Now you can just connect without any trouble. Neat.

$ mysql myapp_development
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.45-Debian_1ubuntu3.3-log Debian etch distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> 

Once you've gone to the trouble to make it easy to login to MySQL, why not let Rails connect using the same settings when it starts up?

This is how I've been doing it:

<%
def get_mysql_password
  script =<<EOF
import ConfigParser
parser = ConfigParser.SafeConfigParser()
parser.read("#{ENV['HOME']}/.my.cnf")
print parser.get("mysql", "password")
EOF
  %x(python -c '#{script}')
end
%>

mysql: &mysql
  adapter: mysql
  username: root
  password: <%= get_mysql_password %>
  host: localhost
  socket: <%= [
    '/var/lib/mysql/mysql.sock',
    '/var/run/mysqld/mysqld.sock',
    '/tmp/mysqld.sock',
    '/tmp/mysql.sock',
  ].detect { |socket| File.exist?(socket) } %>

development:
  database: myapp_development
  <<: *mysql

test: &test
  database: myapp_test
  <<: *mysql

Note the sneaky socket trick that allows Rails to try lots of different locations for MySQL's Unix socket---it allows you to develop the same app on a Mac, or various flavours of Linux/BSD, without editing database.yml.

So then, what's with that Python code? I suspect there's a rather good way of doing it in Ruby, but when I first started developing with Rails I'd just spent five years doing Python full time, and I knew that the ConfigParser module was designed to read files like ~/.my.cnf. So I used it, and it stuck.

Send me improvements!

How would you do it?

Can you write a neat snippet of Ruby that achieves the same thing as the Python code?

If anybody knows a way to make this trick work with Merb I'd love to hear it. Last time I tried, Merb wasn't processing the database config file with erb...

I love feedback and questions — please feel free to get in touch on Mastodon or Twitter, or leave a comment.