RAILS 5.1
I have a RAILS application thats using PostgreSQL as database. I want to export/dump the RAILS database data from the RAILS perspective. So I'm independend from the database. Later then I want to use this export/dump file to load/import/seed the data back into the database.
I have tried the following GEMs:
seed_dump
It works, but it can't handle HABTM model relations.
yaml_db , It works, but the yaml format is not the format understood by a rails db:seed
Here's a practical example of exporting to JSON. I use rake tasks to do this sort of thing. In this example I'm dumping a users table.
namespace :dataexport do
desc 'export users who have logged in since 2017-06-30'
task :recent_users => :environment do
puts "Export users who have logged in since 2017-06-30"
# Get a file ready, the 'data' directory has already been added in Rails.root
filepath = File.join(Rails.root, 'data', 'recent_users.json')
puts "- exporting users into #{filepath}"
# The key here is to use 'as_json', otherwise you get an ActiveRecord_Relation object, which extends
# array, and works like in an array, but not for exporting
users = User.where('last_login > ?', '2017-06-30').as_json
# The pretty is nice so I can diff exports easily, if that's not important, JSON(users) will do
File.open(filepath, 'w') do |f|
f.write(JSON.pretty_generate(users))
end
puts "- dumped #{users.size} users"
end
end
And then import
namespace :dataimport do
desc 'import users from recent users dump'
task :recent_users => :environment do
puts "Importing current users"
filepath = File.join(Rails.root, 'data', 'recent_users.json')
abort "Input file not found: #{filepath}" unless File.exist?(filepath)
current_users = JSON.parse(File.read(filepath))
current_users.each do |cu|
User.create(cu)
end
puts "- imported #{current_users.size} users"
end
end
Sometimes as part of the import process I'll want a clean table to import into, in which case I'd start the taske with:
ActiveRecord::Base.connection.execute("TRUNCATE users")
This would not be the best way to handle Really Big tables, greater than, oh, 50,000 rows, and/or with lots of text fields. In which case the db native dump/import tools would be more appropriate.
Here's a HABTM example for the sake of completeness. There's still a linking table, but it has no model, so the only way to do something with it is raw SQL. Let's imagine our users have many roles, and vice versa (users M:M roles), for example:
class User < ApplicationRecord
has_and_belongs_to_many :roles
end
class Role < ApplicationRecord
has_and_belongs_to_many :users
end
There would necessarily be a joining table called users_roles which would have two columns, user_id and role_id.
See the Rails Guide on HABTM
To export, we have to execute SQL directly:
users_roles = ActiveRecord::Base.connection.execute("SELECT * from users_roles").as_json
# and write the file as before
And execute SQL to import
# read the file, same as before
user_roles.each do |ur|
ActiveRecord::Base.connection.execute("insert into users_roles (user_id, role_id) values ('#{ur[0]}', '#{ur[1]}')")
end