# Preference for raw SQL in migrations?

**URL:** <https://www.ruby-forum.com/t/preference-for-raw-sql-in-migrations/170229>\
**Category:** Rails\
**Created:** [June 14, 2009, 3:18pm UTC](https://www.ruby-forum.com/t/preference-for-raw-sql-in-migrations/170229 "2009-06-14T15:18:55Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![ncancelliere](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/9f8e36/32.png) [@ncancelliere](https://www.ruby-forum.com/u/ncancelliere)\
**Post date:** [June 14, 2009, 3:18pm UTC](https://www.ruby-forum.com/t/preference-for-raw-sql-in-migrations/170229/1 "2009-06-14T15:18:55Z")

</div>

I have observed that in a few projects (for larger enterpriseish type  
companies) that folks prefer to use raw SQL in the migrations instead  
of methods like add\_column or create\_table.

The con I see in doing this is that your project is less portable,  
you’re tied to whatever database you’re using. Not too big a deal I  
suppose since most databases use the same syntax to create tables, add  
columns, etc.

So then my next though is, if that’s the case then why do it? It  
seems less error prone to write ’  
add\_column :addresses, :first\_name, :string’ than the SQL to do the  
same thing. It’s also a lot more readable than the SQL statement is.

The one thing I don’t see migrations doing are foreign\_key constraints  
in the database. I suppose a lot of Rails developers depend on the  
ActiveRecord associations and validations to enforce these?

I’m interested in finding out what other folks are doing in terms of  
writing migrations.

---

<div class="post-metadata">

**Author:** ![ncancelliere](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/9f8e36/32.png) [@ncancelliere](https://www.ruby-forum.com/u/ncancelliere)\
**Post date:** [June 14, 2009, 3:58pm UTC](https://www.ruby-forum.com/t/preference-for-raw-sql-in-migrations/170229/2 "2009-06-14T15:58:39Z")

</div>

ncancelliere wrote:

> I have observed that in a few projects (for larger enterpriseish type  
> companies) that folks prefer to use raw SQL in the migrations instead  
> of methods like add\_column or create\_table.

I have seen that too. I believe that is due to not understanding the  
best practices for dealing with migrations.

For example, one argument I have seen for writing SQL migrations is that  
they will be more resistant to future changes in the model object.  
That’s true, but if you’re running old migrations, generally something  
else is wrong (that’s what rake db:schema:load is for).

> The con I see in doing this is that your project is less portable,

I agree. More than a line or two of SQL in a migration is generally not  
a great idea, I think.  
[…]

> So then my next though is, if that’s the case then why do it?

See above.

[…]

> The one thing I don’t see migrations doing are foreign\_key constraints  
> in the database.

Then you haven’t looked hard enough. 🙂 See  
[http://github.com/harukizaemon/foreign\_key\_migrations](http://github.com/harukizaemon/foreign_key_migrations) . I use this on  
all my projects, and at some point I hope to patch it so it also deals  
with check constraints.

> I suppose a lot of Rails developers depend on the  
> ActiveRecord associations and validations to enforce these?

Probably. But the _good_ Rails developers know better. 😃

> I’m interested in finding out what other folks are doing in terms of  
> writing migrations.

Well, that’s what I think…

## Best,

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

---

<div class="post-metadata">

**Author:** ![ncancelliere](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/9f8e36/32.png) [@ncancelliere](https://www.ruby-forum.com/u/ncancelliere)\
**Post date:** [June 14, 2009, 7:29pm UTC](https://www.ruby-forum.com/t/preference-for-raw-sql-in-migrations/170229/3 "2009-06-14T19:29:08Z")

</div>

Thank you so much for the thoughtful reply. I will check out that  
plugin/gem you linked to.

Cheers,  
Nicholas

On Jun 14, 8:58 am, Marnen Laibow-Koser \<rails-mailing-l…@andreas-