db/schema.rb

Reading the Rails commit logs, I see that db/schema.rb's purpose has
been clarified. It's intended to be the authoritative location for
your database schema, and rake tasks like db:reset use it to rebuild
the schema rather than running migrations.

This raises several questions for me.

1 ) What, then, is the preferred mechanism for "seed" data in the
database? Currently, many people use migrations to insert bootstrap
data. If schema.rb will be used for tasks like db:reset, none of this
data will be loaded.

2 ) What about those of us who specify additional options like charset
or table types? This information, last time I checked, doesn't seem to
be saved in the schema.rb.

3 ) Should there be yet another database-related Rake task for
development? Many of us create migrations, then modify them until
we're ready to commit. A simple "rake db:reset" used to re-run the
migrations from scratch, but the new semantics mean that migration
changes are never reflected.

Thanks in advance.

1 ) What, then, is the preferred mechanism for "seed" data in the
database? Currently, many people use migrations to insert bootstrap
data. If schema.rb will be used for tasks like db:reset, none of this
data will be loaded.

For a production database, migrations are the obvious option. I'll
probably get lynched but I just load fixtures to get my laptop's data
good to go...

2 ) What about those of us who specify additional options like charset
or table types? This information, last time I checked, doesn't seem to
be saved in the schema.rb.

Use the :sql schema format, perhaps you could supply patches to have
the rake tasks figure out which importer to run based on that setting?

3 ) Should there be yet another database-related Rake task for
development? Many of us create migrations, then modify them until
we're ready to commit. A simple "rake db:reset" used to re-run the
migrations from scratch, but the new semantics mean that migration
changes are never reflected.

rake db:drop db:create db:migrate will do what you need. if you think
there should be a task for this, come up with a name and a patch and
we can take a look.

Reading the Rails commit logs, I see that db/schema.rb's purpose has
been clarified. It's intended to be the authoritative location for
your database schema, and rake tasks like db:reset use it to rebuild
the schema rather than running migrations.

This raises several questions for me.

1 ) What, then, is the preferred mechanism for "seed" data in the
database? Currently, many people use migrations to insert bootstrap
data. If schema.rb will be used for tasks like db:reset, none of this
data will be loaded.

Good question. I'm looking forward to the answer(s).

2 ) What about those of us who specify additional options like charset
or table types? This information, last time I checked, doesn't seem to
be saved in the schema.rb.

The way to get this is `rake db:structure:dump`, which dumps the
database structure to db/<rails_env>_structure.sql. More info in this
thread from a few days ago:

http:/xrl.us/baojk [groups.google.com/group/rubyonrails-core/...]

3 ) Should there be yet another database-related Rake task for
development? Many of us create migrations, then modify them until
we're ready to commit. A simple "rake db:reset" used to re-run the
migrations from scratch, but the new semantics mean that migration
changes are never reflected.

I added "db:recycle" to the GenerallyUseful plugin for just this
reason.

http://agilewebdevelopment.com/plugins/generally_useful

It only supports MySQL at present, but I'll get to a few others when
I've got some time. Meanwhile, I'll happily take patches for anyone's
favorite non-MySQL db.

Cheers!

--j

Stephen,
Your questions are a mirror of mine. I am surprised that the emphasis
placed on migrations for building the development and production
databases does not extend to the test database.

There is a rake task proposed on ticket 8389 (http://
dev.rubyonrails.org/ticket/8389) that addresses this problem by
offering a third option to the config.active_record.schema_format
setting. With the setting set to :migration, the test database is
built with migrations. I have used this hack and it works tolerably.
But I would really like to see it in edge to ensure that tools
suppliers know it is an option. Also, right now it sets the test
database to the same schema version as development whereas I think it
should go to the latest version.

It's great feeling knowing that my migrations are being exercised
every time I run tests. I have been burned in the past with early
migrations no longer being valid (due to DB upgrade) and not catching
that problem until it was time to go into production. With this hack,
I no longer worry about that potential problem.

I also have moved to loading "seed" data with migrations. I really
just load three records (a system admin user, a public group and some
global permissions for them). But I expect to put some more seed data
in this way -including Globalize languages and such.

Lastly, I too use non-default DB options and that is, of course, no
longer a problem.

I would recommend that you look at ticket 8389 and I would ask Michael
to look at it as well -it needs some "official" support (or a
statement to the contrary).

Regards,
Chris

Michael,
Please see me earlier comment and see ticket 8389 -that's the patch
for a task to support migration-based building of the test database.

-Chris

1 ) What, then, is the preferred mechanism for "seed" data in the
database? Currently, many people use migrations to insert bootstrap
data. If schema.rb will be used for tasks like db:reset, none of this
data will be loaded.

In my app I made a folder under vendor called "data". I write rake
tasks specifically for loading this data. This way I can load my data
willy-nilly, get new members on the team up to speed quickly, and
there's one easy-to-find place for all my data import scripts: lib/
tasks.

James

we are using timestamped migrations and the first one is stamped like this:

   00000000000042_schema.rb

thus, it's before any other timestamped migration, most of which look like this

   20071103035131_globalize_migration.rb

the '42' migration looks for a file, db/dump.gz, which iff found it assumed to be a binary dump of the db. this will typically be dumped from production, for instance. when found it's loaded. this has the side effect of also loading the schema_info table so it short circuits all the other migrations if, and only if, they were run on the production db. if you have a newer migration locally then that will be run.

we also have custom db:down and db:up tasks which drop the db, create it, and then run db:migrate.

so far this is working quite well.

regards.

a @ http://codeforpeople.com/

> 1 ) What, then, is the preferred mechanism for "seed" data in the
> database? Currently, many people use migrations to insert bootstrap
> data. If schema.rb will be used for tasks like db:reset, none of this
> data will be loaded.

There's a new plugin to fill that "gap", yaml_db:
http://blog.heroku.com/archives/2007/11/23/yamldb_for_databaseindependent_data_dumps/

I typically don't use migrations for "seed" data; instead, I use
something like "rake db:bootstrap" or something like that.

Is there a reason you prefer migrations?

--Jeremy

I've written a simple rake task that loads any .rb files found within
the RAILS_ROOT/db/fixtures/RAILS_ENV directory. I've bundled it up as
a plugin if anyone else wants to try it out:
http://code.google.com/p/db-populate/. README, examples, etc. to come
soon.

Josh's rake task looks like it provides a nice ability to load data
via Rake. But there is an inconsistent message being sent by Rails:

Use Migrations. Use Migrations. Use Migrations.

Except for testing, where we'll use a limited, one-off method (schema
dumper) ensuring that you'll never know if your migrations are broken
until it's too late.

Why? Migrations are perfectly capable of handling the test DB setup:
like schema dumper they are database-neutral, but they also support
seed data loading and where desired, DB-specific commands. Plus, they
are the blessed approach to building development and production
databases and could thus benefit from regular exercise during testing
as well.

Is there a good reason for the avoidance of migrations for building
the test DB, or is the use of schema dumper just a vestige of days
gone by? I continue to be surprised by the lack of comments from the
core team on this thread (Koz's early comment being the only one) and
the lack of feedback on ticket 8389.

For those of you that have bought into migrations like I have, add
your thoughts (or improvements) to the patch on ticket 8389.

-Chris

Josh's rake task looks like it provides a nice ability to load data
via Rake. But there is an inconsistent message being sent by Rails:

Use Migrations. Use Migrations. Use Migrations.

Except for testing, where we'll use a limited, one-off method (schema
dumper) ensuring that you'll never know if your migrations are broken
until it's too late.

You run your migrations against your development database, it's
hardly as bad as you're implying :).

Is there a good reason for the avoidance of migrations for building
the test DB, or is the use of schema dumper just a vestige of days
gone by? I continue to be surprised by the lack of comments from the
core team on this thread (Koz's early comment being the only one) and
the lack of feedback on ticket 8389.

Migrations are designed for cases where the data in your db matters,
with cases like a test database you might as well just drop and
recreate every table.

But more than that, *most* of the migrations in my current
application (200ish out of 300ish) are for maintaining *data*, they
don't make schema changes at all. Things like fixing up data after a
bug messed it up, changing the prices of various plans, altering
lookup data Etc. My test database doesn't need to know about them.
Schema.rb works fine, I'm not sure what additional benefits you're
looking for.

Josh’s rake task looks like it provides a nice ability to load data
via Rake. But there is an inconsistent message being sent by Rails:

Use Migrations. Use Migrations. Use Migrations.

Except for testing, where we’ll use a limited, one-off method (schema
dumper) ensuring that you’ll never know if your migrations are broken
until it’s too late.

You run your migrations against your development database, it’s

hardly as bad as you’re implying :).

Is there a good reason for the avoidance of migrations for building
the test DB, or is the use of schema dumper just a vestige of days

gone by? I continue to be surprised by the lack of comments from the
core team on this thread (Koz’s early comment being the only one) and
the lack of feedback on ticket 8389.

Migrations are designed for cases where the data in your db matters,

with cases like a test database you might as well just drop and
recreate every table.

Many times drop and recreate the database is not works, especially when you have some functions which
is beyon the ability of active_records, such as view schema, contrib function in postgresql, etc.

I have wrote a post about “testing with postgresql”. Hope it’s meaningful.


http://sishen.lifegoo.com/?p=50

Many times drop and recreate the database is not works, especially when you
have some functions which
is beyon the ability of active_records, such as view schema, contrib
function in postgresql, etc.

I have wrote a post about "testing with postgresql". Hope it's meaningful.

http://sishen.lifegoo.com/?p=50

You can set the schema_format to :sql and it should work well.

Many times drop and recreate the database is not works, especially when you
have some functions which
is beyon the ability of active_records, such as view schema, contrib
function in postgresql, etc.

I have wrote a post about “testing with postgresql”. Hope it’s meaningful.

http://sishen.lifegoo.com/?p=50

You can set the schema_format to :sql and it should work well.
You will know it only works partially if you have read my post, :slight_smile:
And also, set the schema_format to :sql is a must.

"You run your migrations against your development database, it's hardly as bad as you're implying :)."

True, but... In development, you run the new migrations -you don't
typically tear down the DB and run all of them from the start.
Consequently, older migrations become stale and broken (due to DB
upgrades, model changes, etc.) -and you don't learn about that until
it's too late. I maintain that the current functionality leaves no
reasonable means of keeping migrations up-to-date and tested in their
entirety. It doesn't sound like you use your migrations in their
entirety to build a DB so perhaps you have not been bitten by this
problem.

I see this issue is distilling into three separate arguments for
migration-based test DB setup:

  1. migrations (in their entirety) need testing too
  2. migrations are the most capable means of building a database
(including test).
  3. Rails is sending an inconsistent message: migrations for
development and production, but schema dumper for test.

What's the argument for the schema dumper?

This thread is probably reaching the end of it's useful like, but I
would like to close with a request: please consider endorsing (test,
+1) ticket 8389 or something similar.

Chris

I see this issue is distilling into three separate arguments for
migration-based test DB setup:

  1. migrations (in their entirety) need testing too

I think the fact that I immediately disagree with this is a sign that
we're facing two distinct problems which require two distinct
solutions.

If you're building a single application with a single database
installation, you will *never* need to run your migrations from start
to finish. Basecamp is up and running, no-one's out there
downloading it and setting up a new installation. The old migrations
are simply interesting relics of previous schema versions. Of no
interest to anyone.

By contrast if you're constantly installing new versions of your
application all over the place (say, mephisto) then your needs are
completely different.

  2. migrations are the most capable means of building a database
(including test).
  3. Rails is sending an inconsistent message: migrations for
development and production, but schema dumper for test.

What's the argument for the schema dumper?

This thread is probably reaching the end of it's useful like, but I
would like to close with a request: please consider endorsing (test,
+1) ticket 8389 or something similar.

I think that we're well past the point of getting some ROI on this
thread, and it's definitely not something that's going to change in
the short term. Let's get a plugin out of 8389 and see how people like
using it. We can re-evaluate the pros and cons when it comes time to
do 2.1.