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

The easiest way:

bundle exec rails db < $SQL_FILE

example:

bundle exec rails db < my_db.sql

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

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.