< Return to Blog

Replace a MySQL table in Production via a Rails 4 Migration

In a recent client project, I've been re-building a Rails 2.x eCommerce site in Rails 4.2, and when this is launched on production, I wanted to be able to drop tarballs of various tables straight into the git repo and have a migration unpack those tarballs and import them into the DB; the drop-table clauses in the SQL will ensure existing tables will be selectively replaced upon import.

The entire unpacking of the compressed tarball is done in memory, and the script looks for the file in the bootstrap folder in the app root. The database.yml file is used to fetch login credentials, and that's how the extracted SQL file is imported into MySQL.

require 'rubygems'
require 'rubygems/package'
require 'zlib'
require 'fileutils'

module Util
  module Tar
    # Creates a tar file in memory recursively
    # from the given path.
    #
    # Returns a StringIO whose underlying String
    # is the contents of the tar file.
    def tar(path)
      tarfile = StringIO.new("")
      Gem::Package::TarWriter.new(tarfile) do |tar|
        Dir[File.join(path, "**/*")].each do |file|
          mode = File.stat(file).mode
          relative_file = file.sub /^#{Regexp::escape path}\/?/, ''

          if File.directory?(file)
            tar.mkdir relative_file, mode
          else
            tar.add_file relative_file, mode do |tf|
              File.open(file, "rb") { |f| tf.write f.read }
            end
          end
        end
      end

      tarfile.rewind
      tarfile
    end

    # gzips the underlying string in the given StringIO,
    # returning a new StringIO representing the
    # compressed file.
    def gzip(tarfile)
      gz = StringIO.new("")
      z = Zlib::GzipWriter.new(gz)
      z.write tarfile.string
      z.close # this is necessary!

      # z was closed to write the gzip footer, so
      # now we need a new StringIO
      StringIO.new gz.string
    end

    # un-gzips the given IO, returning the
    # decompressed version as a StringIO
    def ungzip(tarfile)
      z = Zlib::GzipReader.new(tarfile)
      unzipped = StringIO.new(z.read)
      z.close
      unzipped
    end

    # untars the given IO into the specified
    # directory
    def untar(io, destination)
      Gem::Package::TarReader.new io do |tar|
        tar.each do |tarfile|
          destination_file = File.join destination, tarfile.full_name

          if tarfile.directory?
            FileUtils.mkdir_p destination_file
          else
            destination_directory = File.dirname(destination_file)
            FileUtils.mkdir_p destination_directory unless File.directory?(destination_directory)
            File.open destination_file, "wb" do |f|
              f.print tarfile.read
            end
          end
        end
      end
    end
  end
end

class ImportAccountsFromSqlFile < ActiveRecord::Migration
  include Util::Tar

  def change
    require 'fileutils'
    require 'tmpdir'

    archive_filename = 'accounts'

    data_dir = Dir.mktmpdir
    at_exit {FileUtils.rm_rf(data_dir)}
    data_path = File.expand_path("#{archive_filename}.sql", data_dir)

    db_yaml = YAML.load_file(Rails.root.join('config','database.yml'))
    db_u = db_yaml[Rails.env]['username']
    db_p = db_yaml[Rails.env]['password']
    db = db_yaml[Rails.env]['database']
    db_host = db_yaml[Rails.env]['host']

    `cp #{Rails.root.join('bootstrap', "#{archive_filename}.tar.gz")} #{data_dir}`
    tarball = data_path.gsub('.sql', '.tar.gz')

    io = ungzip(File.open(tarball, 'rb'))
    untar(io, data_dir)

    options = "-o "
    options << "-h#{db_host} " if db_host && db_host.present?
    options << "-u#{db_u} -p#{db_p}"

    puts "---> SQL PATH: #{data_path}"
    `mysql #{options} #{db} < #{data_path}`
    ActiveRecord::Base.connection.execute('UPDATE accounts SET user_id = NULL')
  end
end