Extract db rails to JSON file

Question

How can I extract data in rails sqlite3 db to a file?

I use rails 4.

I would like to extract all the data in my database so that I can reload them later when I reset my database, or when I switch to another type of db.

Answer 1

Use the yaml_db gem. https://github.com/ludicast/yaml_db

rake db:data:dump 

to output data (Data is stored in a separate file after a reset) and

 rake db:data:load 

to load data into another database

Answer 2

I totally agree with .dump instead of generating a json dump. But just for curiosity, I wrote a script to convert all models to json.

 Rails.application.eager_load! # To load all models app/models/**/*.rb all_records = ActiveRecord::Base.descendants.map &:all all_records.to_json 

But it may take so long to execute in a real environment with many records.

Another way (which I recommend for this case) , since Sqlite3 is just a file, just copy the db/development.sqlite3 file to db/development.sqlite3.backup . When you want to restore it, just copy it back cp -f db/development.sqlite3.backup db/development.sqlite3 . Remember that .dump creates an ASCII text file with inserts and creates statements, you cannot restore it to the database that it extracted, because it will try to duplicate records.

Answer 3

Just create a database dump file for your database and use it whenever you want.

Assuming you want to reset the database for db/development.sqlite3 , this is straight from Sqlite3 Help :

Use the " .dump " .dump to convert the entire contents of the database into a single ASCII text file. This file can be converted back to by moving it back to sqlite3.

To create a dump file:

 echo '.dump' | sqlite3 db/development.sqlite3 | gzip -c > dev.dump.gz 

To restore a dump file:

 zcat dev.dump.gz | sqlite3 development.sqlite3 

Answer 4

You can simply write this line in seeds.rb

 File.open("post.json", "w") { |f| f.write Post.all.to_json } 

here Post should be replaced with your model name. then run in terminal:

 rake db:seed 

Answer 5

I used to extract all db in json using phpmyadmin custom export. it creates easy to export json data for your database. Hope this helps you.

goto phpmyadmin

export / type json