# Migrations questions: MySQL -\> postgreSQL

**URL:** <https://www.ruby-forum.com/t/migrations-questions-mysql-postgresql/98607>\
**Category:** Rails\
**Created:** [July 16, 2006, 8:12pm UTC](https://www.ruby-forum.com/t/migrations-questions-mysql-postgresql/98607 "2006-07-16T20:12:08Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![wpwood](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/d6d6ee/32.png) [@wpwood](https://www.ruby-forum.com/u/wpwood)\
**Post date:** [July 16, 2006, 8:12pm UTC](https://www.ruby-forum.com/t/migrations-questions-mysql-postgresql/98607/1 "2006-07-16T20:12:08Z")

</div>

I started my app before migrations were a best practice and have been  
using SQL scripts. Now I’m looking at potentially having to move from  
using MySQL to postgreSQL to use a particular hosting provider. I  
understand migrations are the way to go to make this ‘easy’ but it also  
looks like the use of migrations introduces extra work in other areas.  
I’d really appreciate feedback from anybody with experience on a some  
questions I’m mulling around.

The app is simple: form-completion -\> generate XML file -\> delete data  
from db

The form entry fields are all defined as varchar(n). The largest is  
about 80 characters. MySQL truncates any entry. With migrations, my  
understanding is that the fields get declared as string. So it looks  
like, at a minimum, moving to migrations is going to make me put  
validates\_length\_of on every text\_field in every model to protect  
against SQL injection. (I already escape all text output in the views)  
Is my understanding about migrations using string, and the correllated  
assumption re" length\_of validation correct?

Just how ‘easy’ do migrations make it to move from MySQL to postgreSQL?  
I’m assuming the DDL for postgreSQL is the major benefit. But, never  
having used postgreSQL, I’m wondering if there are application level  
changes that are going to be required above and beyond what migrations  
can do for me.

It’s obvious there’s going to be at least ‘some’ work involved in moving  
to migrations. OTOH, the only thing currently driving the question is  
the potential use of a particular hosting service. Is there, for this  
simple type of app, any real benefit to moving to postgreSQL that I  
ought to be factoring into the equation?

Thanks in advance for any experience / thoughts you’d be willing to  
share.

Best regards,  
Bill

---

<div class="post-metadata">

**Author:** ![wpwood](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/d6d6ee/32.png) [@wpwood](https://www.ruby-forum.com/u/wpwood)\
**Post date:** [July 16, 2006, 8:33pm UTC](https://www.ruby-forum.com/t/migrations-questions-mysql-postgresql/98607/2 "2006-07-16T20:33:53Z")

</div>

On 7/16/06, Bill W. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> The app is simple: form-completion → generate XML file → delete data from

Hi Bill,

I think you should be able to easily manage the schema creation  
itself. In RAILS\_ROOT you can just do rake db:schema:dump and it’ll  
dump your schema to db/schema.rb. You can then just create a  
migration numbered 000 or 001, and paste the inner part of the schema  
into the migrations up method.

As far as your issue with varchar goes, varchar doesn’t mean a whole  
lot in postgresql. Afaik, it’s not implemented any differently from a  
standard char field. If you have limits though it will truncate your  
strings.

Finally, imo I think that it’s much better to put that kind of  
validation in the model anyway. Do you really want to just  
automatically truncate any text that goes in? You might right now,  
but at some point you might want to have some kind of error. Even if  
you just want to truncate it, it’s pretty simple in Rails  
def before\_save; self.my\_field = my\_field[0…79]; end

You might be interested in reading DHH’s “Choose a Single Layer of  
Cleverness” post [1] if you haven’t already. Alex Bunardzic wrote a  
follow-up [2] that provides some good reasoning behind that opinion.  
I suggest you read through each post, along with the comments, and see  
which approach suits you best for your particular project.

Pat

[1] [http://www.loudthinking.com/arc/000516.html](http://www.loudthinking.com/arc/000516.html)  
[2] [Should Database Manage The Meaning?](http://lesscode.org/2005/09/29/should-database-manage-the-meaning/)

---

<div class="post-metadata">

**Author:** ![wpwood](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/d6d6ee/32.png) [@wpwood](https://www.ruby-forum.com/u/wpwood)\
**Post date:** [July 16, 2006, 8:55pm UTC](https://www.ruby-forum.com/t/migrations-questions-mysql-postgresql/98607/3 "2006-07-16T20:55:29Z")

</div>

On 7/16/06, Bill W. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> The form entry fields are all defined as varchar(n). The largest is about  
> 80 characters. MySQL truncates any entry. With migrations, my  
> understanding is that the fields get declared as string. So it looks like,  
> at a minimum, moving to migrations is going to make me put  
> validates\_length\_of on every text\_field in every model to protect against  
> SQL injection. (I already escape all text output in the views) Is my  
> understanding about migrations using string, and the correllated assumption  
> re" length\_of validation correct?

This is database-dependent. If you look in postgresql\_adapter.rb,  
you’ll find this:

```
  def native_database_types
    {
      :primary_key => "serial primary key",
      :string => { :name => "character varying", :limit => 255 

```

},  
:text =\> { :name =\> “text” },  
:integer =\> { :name =\> “integer” },  
:float =\> { :name =\> “float” },  
:datetime =\> { :name =\> “timestamp” },  
:timestamp =\> { :name =\> “timestamp” },  
:time =\> { :name =\> “time” },  
:date =\> { :name =\> “date” },  
:binary =\> { :name =\> “bytea” },  
:boolean =\> { :name =\> “boolean” }  
}  
end

So, you can still choose between :string or :text depending on if you  
want a field to automatically have a size limit or not.

– James

---

<div class="post-metadata">

**Author:** ![wpwood](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/d6d6ee/32.png) [@wpwood](https://www.ruby-forum.com/u/wpwood)\
**Post date:** [July 16, 2006, 10:29pm UTC](https://www.ruby-forum.com/t/migrations-questions-mysql-postgresql/98607/4 "2006-07-16T22:29:19Z")

</div>

On 7/16/06, Isak H. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> There’s so little effort involved, and the payoff is huge. Just make  
> sure you don’t go beyond enforcing integrity, business logic in the db  
> is evil.

eek. I use postgresql and do just what you suggested - take advantage  
of referential integrity in the db, and business logic goes in the  
model. I was just telling him that he should probably put that  
auto-truncation in his model. I definitely consider it worthwhile to  
switch over to postgres 🙂

Pat

---

<div class="post-metadata">

**Author:** ![wpwood](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/d6d6ee/32.png) [@wpwood](https://www.ruby-forum.com/u/wpwood)\
**Post date:** [July 16, 2006, 10:23pm UTC](https://www.ruby-forum.com/t/migrations-questions-mysql-postgresql/98607/5 "2006-07-16T22:23:07Z")

</div>

On 7/16/06, Pat M. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> > re" length\_of validation correct?  
> > factoring into the equation?  
> > [http://lists.rubyonrails.org/mailman/listinfo/rails](http://lists.rubyonrails.org/mailman/listinfo/rails)  
> > into the migrations up method.  
> > you just want to truncate it, it’s pretty simple in Rails  
> > def before\_save; self.my\_field = my\_field[0…79]; end

You’re badly mistaken.

I’m only aware of one ‘database’ with low enough regard for the user’s  
data that it will silently alter them to fit the column definitions.

Bill is switching away from it…

> You might be interested in reading DHH’s “Choose a Single Layer of  
> Cleverness” post [1] if you haven’t already. Alex Bunardzic wrote a  
> follow-up [2] that provides some good reasoning behind that opinion.  
> I suggest you read through each post, along with the comments, and see  
> which approach suits you best for your particular project.

For most applications bad data is worse than no data. If you can make  
the db enforce the integrity of your data, why wouldn’t you?

There’s so little effort involved, and the payoff is huge. Just make  
sure you don’t go beyond enforcing integrity, business logic in the db  
is evil.

IMHO switching from mysql to postgres is a no-brainer. I don’t know  
the specifics of your application, but not tying yourself or your  
customer to that pile of dung is reason good enough.

I’m sure there’s a little work involved, postgres is a fair bit  
stricter than mysql and will throw errors if you feed it invalid  
sql/data instead of just making a guess, but it _will_ pay off.

I don’t have too much experience with migrations, but they seem to do  
a good job at maintaining column definitions.

If you use parametrized queries (i.e. [“update stuff set a = ? where  
id = ?”, param1, param2]), rails and the db adapters will take care of  
escaping everything for you. In either case, field length isn’t a  
concern in that area.

Good luck,  
Isak