How can I import a SQL file into a Rails database?

Question

I've got a .sql file that I'd like to load into my Rails database using a Rake task. How can I do this?

Abel · Accepted Answer · 2018-07-11T17:21:32.650

71

The easiest way:

bundle exec rails db < $SQL_FILE

example:

bundle exec rails db < my_db.sql

edited Jul 11 '18 at 17:21

answered Apr 14 '17 at 21:50

Abel

3,989
32
31

1

How could you add this to a Rake task? – Miguel Peniche Jul 06 '17 at 22:34
3

with rails 5+ `rails db < my_sql.sql` would work as well – Ali Ghanavatian Sep 01 '19 at 14:55
1

is it possible to set password as parameter for this task? – Yuri Karpovich Aug 21 '21 at 19:42
Yuri Karpovich, I don't know if it is possible to set password as parameter for this task, but it is possible to automatically provide the password from database.yml. `bundle exec rails db -p < my_db.sql`. You can find this and more options in `rails db --help` – mario199 Jan 30 '23 at 07:46

score 48 · Answer 2 · edited May 23 '17 at 12:25

48

The Easy Way

This works for simple cases.

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

Solution found on the Ruby On Rails mailing list from 2006 (but still works in 2011 on Rails 3.1).

Footnotes

This related question implied this solution, but rejected it for big imports. I wanted to show it explicitly, since it works for smaller ones.
The file I was trying to import contained a LOCK TABLES followed by an insert. The data was for a MySQL database. Mysql2 said it had an invalid SQL syntax error until I removed the lock and unlock statements.

edited May 23 '17 at 12:25

Community

1
1

answered Dec 01 '11 at 14:07

Nathan Long

122,748
97
336
451

6

Update years later: instead of a Rake task, for PostgreSQL I just use `rake db:drop db:create` and then `cat somefile.sql | psql name_of_database`. Dumping is `pg_dump name_of_database > somefile.sql`. – Nathan Long Aug 27 '15 at 14:20

score 17 · Answer 3 · edited Jul 10 '19 at 08:06

17

On MySQL this gave me a syntax error. Splitting the sql into statements made it work.

sql = File.read(sql_file)
statements = sql.split(/;$/)
statements.pop # remove empty line
ActiveRecord::Base.transaction do
  statements.each do |statement|
    connection.execute(statement)
  end
end

edited Jul 10 '19 at 08:06

fkoessler

6,932
11
60
92

answered Mar 05 '15 at 09:23

joost

6,549
2
31
36

I get `ActiveRecord::StatementInvalid: PG::UnableToSend: another command is already in progress` – Miguel Peniche Jul 06 '17 at 22:37
1

My answer was for MySQL not Postgres. Seems like you have multiple processes accessing your db. – joost Aug 09 '17 at 07:31
If you forget the ';' at the end of your SQL query, the `statements.pop # remote empty line` will remove your last query. You can use the following to avoid that, `statements.pop if statements[-1] =~ /^\s+$/` – arthur bryant Sep 15 '18 at 01:03
This is because Rails security prevents chaining queries. You can have multiple files, each with one query, and execute each file separately – Eduard Aug 12 '21 at 09:15

How can I import a SQL file into a Rails database?

3 Answers3

The Easy Way

Footnotes

Linked