The best way to export rails database data

Question

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

Answer 1

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 sers 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

See this answer for more on inserting with raw SQL

Answer 2

I would agree with the people saying use the built in database tools to do it. Or figure out if there's a way to tell the database to export to CSV, and then import that way.

However, if you really want a database agnostic way, here's another way: use your own API.

By that I mean that in 2017 your Rails app really should not just output HTML, but also output JSON. Maybe you want to write a SPA type app in the future, or a mobile app. Making sure there's a Javascript representation of the object, in addition to the HTML version, is a great idea.

So, if you have /projects in your app, write a script that requests /projects as JSON. Save each object as its own file, then in your remote system post everything back in.

If there's anything not in the JSON representation (ie you don't list all the users on the project) make sure to hit those endpoints too and save everything to a separate JSON file.

Then write a player script that POSTS all those files to your destination service.

That's one way to do it. There other way is to write it entirely in Ruby in ActiveRecord - this might be useful as some kind of user data export functionality, so this could be an awesome way to do it too, but in my mind, "can we build a Javascript frontend or mobile app for this?" is usually asked way before, "can the user get their data out" ;)