Migrations questions: MySQL -> postgreSQL

Rails
1

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

2

On 7/16/06, Bill W. [email protected] 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
[2] Should Database Manage The Meaning?

3

On 7/16/06, Bill W. [email protected] 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

4

On 7/16/06, Isak H. [email protected] 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 :slight_smile:

Pat

5

On 7/16/06, Pat M. [email protected] wrote:

re" length_of validation correct?
factoring into the equation?
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