Import SQL file to Rails 4 ActiveRecord db?

Question

I've looked over several other questions on here, and they're vaguely similar, but not exactly what I'm looking for.

What I'm trying to do is import/"convert" a *.sql file which contains 8 tables, each of which contain roughly 24 columns. This file is actually fairly flat file, seeing as though the only queries that worked previous had to do with associating a shared :id between tables (so, SELECT * FROM table1, table2 WHERE id = '1' would pull all results, which was fine at the time).

I've searched around, but can't find a clever way to do this, so I'm asking you Rails pros for help now.

Is it something that has to be done via rails? Or could you do it via a console? What kind of DB du you use? — Albin, Mar 15 '15 at 09:15

score 1 · Answer 1 · edited May 23 '17 at 12:22

I'm assuming what you want is basically to convert your SQL file into a Rails database schema file without having to go through and do this yourself manually.

One quick way to do this would be to manually execute the SQL file, perhaps by logging into your database and loading the file that way, or by doing something like what was done in this question:

ActiveRecord::Base.connection.execute(IO.read("path/to/file"))

Once you have the schema that was defined in your .sql file actually loaded into your database, you will want to follow the steps outlined in this question:

First run rake db:schema:dump which will generate a db/schema.rb database file based on the current state of the database.

From here, you can create a db/migrate/001_original_schema.rb migration that references the schema.rb file as follows:

class OriginalDatabaseMigration < ActiveRecord::Migration
  def self.up
    # contents of schema.rb here
  end

  def self.down
    # drop all the tables
  end
end

score 1 · Answer 2 · answered Jun 15 '15 at 09:28

If I understood your question right you need to populate your db from .sql file. I am doing it this way:

connection = ActiveRecord::Base.connection

ql = File.read('db/some_sql_file.sql')
 statements = sql.split(/;$/)
 statements.pop
 ActiveRecord::Base.transaction do
  statements.each do |statement|
   connection.execute(statement)
  end
  end

Put your sql file to db folder.

score 0 · Answer 3 · answered Jul 23 '15 at 19:32

0

One way I was able to do this - using rails dbconsole

.import FILE TABLE Import data from FILE into TABLE

And essentially .import ./path/to/file TABLE_NAME

Works like a champ.

answered Jul 23 '15 at 19:32

etusm

4,082
1
30
26

score 0 · Answer 4 · answered Jan 05 '16 at 19:34

I have faced the same problem, i just created a script and parsed all the SQL sentences adding 'execute("' at the begining and '")' at the end of each line.

Then i created a new migartion as usual, and pasted all the output on the migration up script. That works for me.

Be aware of avoid any comments on the SQL file so the parsing will be easier.

Import SQL file to Rails 4 ActiveRecord db?

4 Answers4

Linked