Can I use an external SQL file in a Rails migration?

Question

I need to create a Rails migration that creates many triggers and stored procedures.

Normally, this could be used using the execute method, but due to the size of the statements, I would prefer to store them in an external file and refer to it from the migration.

How can i do this? Is it possible?

Answer 1

You can simply save them in a text file and read them through the File object.

 sql = "" source = File.new("./sql/procedures.sql", "r") while (line = source.gets) sql << line end source.close execute sql 

It is ugly, but it works. I highly recommend storing stored procedures / triggers inside migrations for easy rollback.

If you are executing the “external file” method, you need to save two additional files for one transfer, one to add all the material and one to delete if:

 rake db:rollback 

Answer 2

Mikes answers work without problems if you have only one statement in the file, but if there are more statements (e.g. multiple attachments and updates), ActiveRecord will fail because it does not support multiple statements with a single default call.

One solution would be to modify ActiveRecord to support multiple statements, as indicated here .

Another solution would be to ensure that your SQL file contains only one statement per row and uses a loop like

 source = File.open "db/foo.sql", "r" source.readlines.each do |line| line.strip! next if line.empty? # ensure that rows that contains newlines and nothing else does not get processed execute line end source.close 

Answer 3

I did the following that we needed:

 class RawSqlMigration < ActiveRecord::Migration def up execute File.read(File.expand_path('../../sql_migrations/file.sql', __FILE__)) end end