Migrations: getting SQL output only?

Rails
1

Is there a way to generate the SQL that would be run for a given “rake
migrate” command without running the migration itself?

I’d like to be able to do this for any variant of “rake migrate”, such
as
“rake migrate VERSION=XXX”.

Anyone know? Thanks.

2

Check out this…

http://www.agilewebdevelopment.com/plugins/activerecord_io_mode_plugin

I think that should help you…

Mark

On 8/30/06, Dan T. [email protected] wrote:

–
Posted via http://www.ruby-forum.com/.

–
Mark Van H.
[email protected]
http://lotswholetime.com

3

Mark Van H. wrote:

Check out this…

http://www.agilewebdevelopment.com/plugins/activerecord_io_mode_plugin

I think that should help you…

This looks like it is generally useful in certain cases, but would
probably be very difficult to implement for my needs. If I understand
the little synopsis correctly, I would have to modify every call to an
activerecord object that generates a database query.

Again, what I want to do is be able to generate the SQL that would be
run in a migration, without actually running the migration against the
database.

So if I were to use this plugin in this case, it would mean majorly
hacking all the migration code in Rails, and then having to do it again
whenever I upgrade Rails.

I am still wondering if there is an easier way. And if not, perhaps I’d
like to propose this as a feature request (how does one do that,
anyway?).

Here is why I want this feature:

Migrations appear to me to be the best solution so far for tracking
changes to schemata and data. It seems to me there is no reason why
migrations can’t be used in non-rails projects. Just create a rails app,
edit database.yml, start generating migrations and check them into
version control along with your java, php, perl, etc. code.

That’s great as far as it goes, but what about running the migrations on
production? Where I work, we deploy web applications to servers run by
many different ISPs. Some are Windows boxes, some are Linux. There is no
guarantee that ruby/rails will be installed on any of these boxes, and
no guarantee that we can prevail upon the ISPs to do so.

So it seems like it would be great to be able to capture the SQL
generated by “rake migrate” and then run that SQL on another machine.
Obviously one would have to be careful that the migration level on one’s
development machine matches the level on the target production server.

I am aware that the Agile book (2nd ed) warns against running migrations
against production databases. I understand the risks. Unfortunately the
book is pretty vague on how you are supposed to do this. It seems that
using anything other than migrations causes you to lose the benefits of
migrations. My scenario for running migrations on production is to first
copy the production database and run the migrations against that copy,
and test to make sure nothing is broken. Then backup the production db
and run the migrations against it.

Sorry to be long-winded, but I thought it would be beneficial to discuss
my particular need. Using rails migrations in non-rails projects is
something that could raise the profile of rails in those communities and
ultimately perhaps gain some more converts. Plus it allows non-rails
developers to reap the benefits of migrations.

4

On 30/08/06, Dan T. [email protected] wrote:

Migrations appear to me to be the best solution so far for tracking
changes to schemata and data. It seems to me there is no reason why
migrations can’t be used in non-rails projects. Just create a rails app,
edit database.yml, start generating migrations and check them into
version control along with your java, php, perl, etc. code.

Not entirely what you’re asking for, but I’m about to write a rails
backend ui
to manage a database used by a mod_perl frontend. mod_perl relies on a
load of constraints, grants, etc that are hard to capture with
migrations.

I found Robbys page on using raw SQL in migrations to be a good match
for what I need:

http://www.robbyonrails.com:8680/articles/2005/11/11/rails-migrations-and-postgresql-constraints

You’d still need ruby and activerecord, but that’s not a big deal in my
case.

–
Rasputin :: Jack of All Trades - Master of Nuns
http://number9.hellooperator.net/

5

This looks like it is generally useful in certain cases, but would
probably be very difficult to implement for my needs. If I understand
the little synopsis correctly, I would have to modify every call to an
activerecord object that generates a database query.

Not true, all you need to do is…

output_sql = []

ActiveRecord::Base.send_sql_to output_sql
#do all the migrations

Now the array output_sql will have all the generated sql in it, and
nothing
will have hit the db. In fact, you can also make output_sql a file
instead
of an array. You just need to put this at the begining of the the
migrations. Let me know if you need a more concrete example and ill see
what
i can get together tonight.

mark

–
Mark Van H.
[email protected]
http://lotswholetime.com