How to copy production database data to the development database with Capistrano

At times it is useful to easily be able to copy the content of the production database to the database running on your local development machine. In my case I use mysql on the production server and sqlite on my MacBook Pro. Unfortunately the use of different database servers makes this task a bit more tricky.

With a bit of googling I found a bit of code here and there which I combined and modified and turned into the following snippet of code to be dropped into your Capistrano deploy.rb file.


desc "Copy production database to development database"
task :update_dev_db, :roles => :db do
db = YAML::load(ERB.new(IO.read(File.join(File.dirname(FILE), 'database.yml'))).result)['production']

filename = “#{db[‘database’]}_dump.#{Time.now.strftime ‘%Y%m%dT%:%H%M%S’}.sql”
remote_path = “#{current_path}/tmp/#{filename}”
local_path = “tmp/#{filename}”

on_rollback { run “rm #{remote_path}” }

run “mysqldump -u #{db[‘username’]} –password=#{db[‘password’]} –skip-opt #{db[‘database’]} –complete-insert=true –skip-quote-names –no-create-info > #{remote_path}” do |ch, stream, data|
puts data
end

get remote_path, local_path

converted = File.read(local_path).gsub(/\’/, ‘\’\”)
File.open(“#{local_path}_converted”, ‘w’) {|f| f.write(converted)}

system “sqlite3 db/dev.db < #{local_path}_converted”
system “rm #{local_path}”
system “rm #{local_path}_converted”
run “rm #{remote_path}”
end

There is a small problem with \n (new line) characters that on the production server get evaluated as they should. However, on the development machine after the data has been exported from mysql and imported into the sqlite database the \n characters will show as \n on the screen. But for me this is good enough for now.