Migrating database between projects

A developer's story

@MaciejWalusiak

Untitled Kingdom

@MaciejWalusiak

First Approach:

Dump & Load

Second Approach:

Use gem (yamldb)


---
movies:
  columns:
  - id
  - title
  - year
  - producer_id
  records:
  - - '1'
    - 'Jurrasic Park'
    - '1993'
    - '31'
  - - '2'
    - 'The Thing'
    - '1982'
    - '18'

...

{ 
  "movies" => {
    "columns" => [
      "id", 
      "title", 
      "year", 
      "producer_id"
    ],
    "records" => [
      [
        "1", 
        "Jurrasic Park", 
        "1993", 
        "31"
      ],
      [
        "2", 
        "The Thing", 
        "1982", 
        "18"
      ]
    ]
  }
}

Third Approach:

Own export script

FileUtils.mkdir 'dump'

# list of models to export or ActiveRecord::Base.descendants.map(&:name)
klass_names = %w(Movie Producer ...)

klass_names.each do |klass_name|
  data = klass_name.constantize.all.to_json
  File.write("dump/#{klass_name.parameterize}.json", data)
end


[
  {
    id:          1,
    title:       "Jurrasic Park",
    year:        "1993",
    producer_id: 31
  },
  {
    id:          2,
    title:       "The Thing",
    year:        "1982",
    producer_id: 18
  }
]

Import:

when using id

producer_data = JSON.parse(File.read('dump/producer.json'))
movie_data = JSON.parse(File.read('dump/movie.json'))

producer_data.each do |data|
  Producer.where(name: data['name']).first_or_create!
end

movie_data.each do |data|
  movie = Movie.where(title: data['title']).first_or_create!(
    year:          data['year'],
    producer_id:   producer_data.find do |producer|
      Producer.find_by(name: producer['name']) }
    end
  )
end

Problems:

you need unique columns like 'name', to match associations
it's getting complicated with nested relations

Export with uuid:

database = {}
klass_names = %w(Movie Producer ...)
klass_names.map do |klass_name|
  database[klass_name] = klass_name.constantize.all.to_a
end

# Add uuids to resources
database.each do |table|
  table.each do |resource|
    resource['uuid'] = SecureRandom.hex
  end
end

# replace ids with uuids for associations
database.each do |table|
  associations = table.constantize.reflect_on_all_associations(:belongs_to) # has_one etc... 
  table.each do |resource|
    associations.each do |association|
      foreign_key = association.options[:foreign_key] || "#{association.name}_id"

      associate = database[association.class_name].find do |r|
        r['id'] == resource.delete(foreign_key)
      end

      resource[foreign_key.sub('_id', '_uuid')] = associate['uuid']
    end
  end
end

File.write("dump.json", database.to_json)

Import:

when using uuid

producer_data = JSON.parse(File.read('dump/producer.json'))
movie_data = JSON.parse(File.read('dump/movie.json'))

producer_data.each do |data|
  Producer.where(uuid: data['uuid']).first_or_create!(
    name: data['name']
  )
end

movie_data.each do |data|
  movie = Movie.where(uuid: data['uuid']).first_or_create!(
    title:         data['title'],
    year:          data['year'],
    producer_uuid: data['producer_uuid']
  )
end

Thank you!

@MaciejWalusiak