# Raw SQL in migrations

**URL:** <https://www.ruby-forum.com/t/raw-sql-in-migrations/165845>\
**Category:** Rails\
**Created:** [April 18, 2009, 5:02am UTC](https://www.ruby-forum.com/t/raw-sql-in-migrations/165845 "2009-04-18T05:02:05Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Tyler\_MacDonald](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/b5ac83/32.png) [@Tyler\_MacDonald](https://www.ruby-forum.com/u/Tyler_MacDonald)\
**Post date:** [April 18, 2009, 5:02am UTC](https://www.ruby-forum.com/t/raw-sql-in-migrations/165845/1 "2009-04-18T05:02:05Z")

</div>

I’ve taken to using this when I just want a migration to be a big wad of  
sql;

runsplit.rb in my lib/ directory:

class Runsplit \< ActiveRecord::Migration  
def self.runsplit(sql)  
transaction do  
sql.split(’;’).each do |stmt|  
execute(stmt) if (stmt.strip! && stmt.length\>0)  
end  
end  
end  
end

And then whenever I have a migration where want to do the SQL statements  
by  
myself, I make the migration inherit off of ‘Runsplit’, like this;

class AlterUserStates \< Runsplit  
def self.up  
runsplit %q{  
ALTER TABLE user\_states ALTER COLUMN user\_id DROP NOT NULL;  
ALTER TABLE user\_states ADD session\_id TEXT NULL;  
}  
end

def self.down  
runsplit %q{  
DELETE FROM user\_states WHERE user\_id IS NULL;  
ALTER TABLE user\_states ALTER COLUMN user\_id SET NOT NULL;  
ALTER TABLE user\_states DROP session\_id;  
}  
end  
end

This works really well, except when the actual SQL statement has  
semicolons  
in it. For that, I’m still using execute();

class GenerateSalt \< ActiveRecord::Migration  
def self.up  
execute %q{  
CREATE FUNCTION generate\_salt ()  
RETURNS CHAR(10)  
LANGUAGE PLPGSQL  
VOLATILE  
AS $$  
DECLARE  
rv CHAR(10);  
i INT;  
BEGIN  
i := 0;  
rv := ‘’;  
WHILE i \< 10 LOOP  
rv := rv || chr((random() \* 25)::int + 65);  
i := i + 1;  
END LOOP;  
RETURN rv;  
END  
$$  
}  
end

def self.down  
execute %q{DROP FUNCTION generate\_salt()}  
end  
end

---

<div class="post-metadata">

**Author:** ![Tyler\_MacDonald](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/b5ac83/32.png) [@Tyler\_MacDonald](https://www.ruby-forum.com/u/Tyler_MacDonald)\
**Post date:** [April 18, 2009, 5:49am UTC](https://www.ruby-forum.com/t/raw-sql-in-migrations/165845/2 "2009-04-18T05:49:14Z")

</div>

Tyler MacDonald wrote:

> I’ve taken to using this when I just want a migration to be a big wad of  
> sql;
> 
> runsplit.rb in my lib/ directory:
> 
> class Runsplit \< ActiveRecord::Migration  
> def self.runsplit(sql)  
> transaction do  
> sql.split(‘;’).each do |stmt|  
> execute(stmt) if (stmt.strip! && stmt.length\>0)  
> end  
> end  
> end  
> end

Why? Since (I think) Rails 2.2, all migrations are transactional  
anyway, and for older versions of Rails, you can just use the  
transactional\_migrations plugin. This just seems to me like a waste of  
effort.

In any case, if you have large amounts of raw SQL in migrations,  
something is probably wrong. What are you trying to accomplish?

## Best,

Marnen Laibow-Koser  
[http://www.marnen.org](http://www.marnen.org)  
removed\_email\_address@domain.invalid