Running migrations with a different database ID

As we developed our virtual desktop provisioning application, we had to integrate against some PHP code that wanted direct access to the database. (See next tip about that)

We started to maintain the properties of that access including low-priviledge database login, and associated limited access views for it. To do this, we initially gave our normal RoR database login the right permissions. We got less and less enamored of this:

  1. it gave our RoR application power that it really didn’t need.
  2. it made our migrations MySQL specific, and we liked to use sqlite on our laptops for development.

We couldn’t do a lot about the second point because RoR doesn’t abstract some of things we wanted to do, and sqlite doesn’t even support them. Initially, we just avoiding running those migrations that edited views, or GRANTed permissions by wrapping the code itself in:

if ENV['RAILS_ENV'] == 'production' execute "alter view webview_table as select uuid,firstname,lastname,username from logins;" execute "GRANT SELECT (`id`, `filesrv_ip`, `title`, `code`, `baynum`) ON `locations` TO 'webuser'@'localhost';" end

What we decided to do was to make a new database stanza in database.yml:

development_migration: adapter: mysql database: application_development username: root password: mypasswd host: localhost socket: <%= RAILS_ROOT %>/test/cluster/run/mysqld/mysqld.sock

We run this with:

RAILS_ENV=development_migration rake db:migrate

We are looking for a way to make that the default for db:migrate, but our rake-fu isn’t high enough yet.

To make this work during capistrano deployments, we add to config/deploy.rb:

set :rails_env, "production_migration"