Rails rake tasks to sync your remote database to your local development environment

23
Feb

posted 06:16 am in Ruby on Rails
tags / rake, capistrano, database, MySQL, Ruby on Rails

So, you’re managing or developing a Ruby on Rails application that you deploy regularly using Capistrano (as any sane Rails developer should), and you want a really easy way to sync up the database on your remote production server with the development environment you have running on your local machine. This is really useful for debugging your app, especially if you have a lot of user-generated content. It’s also just really helpful for design and development if your local environment looks exactly the same as your live environment.

Well, I know it’s not that hard to log in to your remote database, export the whole database, copy it back to your machine, and load it into your development environment. But after a few times doing this, you realize there’s got to be a better way.

Since Capistrano gives us so much flexibility to do pretty much anything on our remote machine, there is an easy way. Here are a few tasks that you can freely copy and paste into your application that will automate all this:

Connect to your production database server
Dump your production MySQL database (sorry, this only works for MySQL right now!)
Download the MySQL dump file to your local machine
Replace the contents of your local development environment with the data and structure from your live production environment

Usage

rake db:production_data_refresh

Yep! That’s all there is to it. It uses the database connection information that you have already set up in your config/database.yml file, so no passwords are ever transmitted. It also uses SSH and SFTP to do all the remote communication and data transfer, so everything is nice and secure.

It is highly recommended that you set up public key authentication so you don’t have to log in with your username and password every time Capistrano wants to connect to your remote host.

Get your copy and paste finger ready … the code is right below the fold

Add these tasks to your lib/tasks/my_app.rake file:

namespace :db do
  desc "Dump the current database to a MySQL file" 
  task :database_dump do
    load 'config/environment.rb'
    abcs = ActiveRecord::Base.configurations
    case abcs[RAILS_ENV]["adapter"]
    when 'mysql'
      ActiveRecord::Base.establish_connection(abcs[RAILS_ENV])
      File.open("db/#{RAILS_ENV}_data.sql", "w+") do |f|
        if abcs[RAILS_ENV]["password"].blank?
          f << `mysqldump -h #{abcs[RAILS_ENV]["host"]} -u #{abcs[RAILS_ENV]["username"]} #{abcs[RAILS_ENV]["database"]}`
        else
          f << `mysqldump -h #{abcs[RAILS_ENV]["host"]} -u #{abcs[RAILS_ENV]["username"]} -p#{abcs[RAILS_ENV]["password"]} #{abcs[RAILS_ENV]["database"]}`
        end
      end
    else
      raise "Task not supported by '#{abcs[RAILS_ENV]['adapter']}'" 
    end
  end

  desc "Refreshes your local development environment to the current production database" 
  task :production_data_refresh do
    `rake remote:exec ACTION=remote_db_runner --trace`
    `rake db:production_data_load --trace`
  end 

  desc "Loads the production data downloaded into db/production_data.sql into your local development database" 
  task :production_data_load do
    load 'config/environment.rb'
    abcs = ActiveRecord::Base.configurations
    case abcs[RAILS_ENV]["adapter"]
    when 'mysql'
      ActiveRecord::Base.establish_connection(abcs[RAILS_ENV])
      if abcs[RAILS_ENV]["password"].blank?
        `mysql -h #{abcs[RAILS_ENV]["host"]} -u #{abcs[RAILS_ENV]["username"]} #{abcs[RAILS_ENV]["database"]} < db/production_data.sql`
      else
        `mysql -h #{abcs[RAILS_ENV]["host"]} -u #{abcs[RAILS_ENV]["username"]} -p#{abcs[RAILS_ENV]["password"]} #{abcs[RAILS_ENV]["database"]} < db/production_data.sql`
      end
    else
      raise "Task not supported by '#{abcs[RAILS_ENV]['adapter']}'" 
    end
  end

end

Now, add these tasks to your config/deploy.rb file:

desc 'Dumps the production database to db/production_data.sql on the remote server'
task :remote_db_dump, :roles => :db, :only => { :primary => true } do
  run "cd #{deploy_to}/#{current_dir} && " +
    "#{rake} RAILS_ENV=#{rails_env} db:database_dump --trace" 
end

desc 'Downloads db/production_data.sql from the remote production environment to your local machine'
task :remote_db_download, :roles => :db, :only => { :primary => true } do  
  execute_on_servers(options) do |servers|
    self.sessions[servers.first].sftp.connect do |tsftp|
      tsftp.get_file "#{deploy_to}/#{current_dir}/db/production_data.sql", "db/production_data.sql" 
    end
  end
end

desc 'Cleans up data dump file'
task :remote_db_cleanup, :roles => :db, :only => { :primary => true } do  
  delete "#{deploy_to}/#{current_dir}/db/production_data.sql" 
end 

desc 'Dumps, downloads and then cleans up the production data dump'
task :remote_db_runner do
  remote_db_dump
  remote_db_download
  remote_db_cleanup
end

Enjoy! Please let me know if anything doesn’t work the way you think it should.