Keeping your database in sync with your current branch

Posted by Michael Reinsch on 2013-03-03

With git, you will most likely find yourself working with branches a lot. Git makes working with branches very easy, and we love the benefits of feature branches.

Branching usually works great with Rails projects, all code and assets are in git. The only thing that gets in the way: the database. While the Database schema is versioned through migrations (which are again in git of course), the database itself is outside. And committing your database to git is a bad idea.

But it would be great to have your development database follow your current branch automatically, right? So for instance, when you switch from a feature branch to master to apply a bug fix, you'd like the database to automatically switch to the schema used in master. And then when you switch back to your feature branch, you'd like the database to be exactly the way you had left it.

For this reason we use SQLite for development. The database itself is just one file, and so we can manipulate it easily. Luckily Rails is also database agnostic (unless you're writing your own queries in SQL), so it is easy to use MySQL or PostgreSQL in production and SQLite for development. Using a build server and a good test coverage makes sure to catch any differences before they hit production.

For development, we use the following code to switch between database files based on the git branch. If a database file for a branch doesn't exist, it'll create a new copy based on the master database file.

The following module goes into lib/development_database_switch.rb:

module DevelopmentDatabaseSwitch

  def development_database
    branch_based_database("development")
  end

  def test_database
    branch_based_database("test")
  end

  protected
  def branch_based_database(base_name)
    # we only want to run git in dev/test modes
    master_db_name =  "db/#{base_name}.sqlite3"
    if git_branch_name
      branch_db = Rails.root.join("db/#{base_name}.#{git_branch_name}.sqlite3")
      FileUtils.cp Rails.root.join(master_db_name), branch_db unless branch_db.exist?
      branch_db.to_s
    else
      master_db_name
    end
  end

  def git_branch_name
    return unless Rails.env.development? || Rails.env.test?
    @_db_switch_branch_name ||= begin
      branch = `git branch --no-color`.match(/\\* (\\S+)\\s/m)[1]
      branch != 'master' && branch != '(no' ? branch : nil
    end
  end
end

We then include this module at the end of the Application class in config/application.rb:

module RailsAppName
  class Application < Rails::Application
    ...

    require "development_database_switch"
    include DevelopmentDatabaseSwitch
  end
end

Then in config/database.yml, we can now use:

default_sqlite: &default_sqlite
  adapter: sqlite3
  pool: 5
  timeout: 5000

default_mysql: &default_mysql
  adapter: mysql2
  host: localhost
  encoding: utf8
  pool: 5
  timeout: 5000

development:
  <<: *default_sqlite
  database: <%= Rails.application.development_database %>

test:
  <<: *default_sqlite
  database: <%= Rails.application.test_database %>

production:
  <<: *default_mysql
  database: production
  username: user
  password: password

We are quite happy with this setup, as it allows us to more rapidly switch between branches, without having to remember to rollback or migrate the database all the time.

In addition, there is now one more reason to branch: whenever I create a migration these days, I also create a feature branch. That way I can easily undo any changes by removing the feature branch's database file. No more worries about half executed migrations or issues when rolling back, leaving the database in a broken state.