Migrating data from old databse structure to new db structure

Rails Deployment
1

Hi,

I’m working on the phase 2 development of my project. Phase 1 of this
project is currently used by lot of people. In Phase 2, there are some
new features and the database structure and relationships are
different. Now, i have to move the data in the old database to the new
database.

I’m a newbie to Rails. Can you please help me.

Thanks,
Sri.

2

On Thu, Nov 6, 2008 at 8:53 AM, Sri [email protected] wrote:

Thanks,
Sri.

I’m curious about this as well…I’d like to take a snapshot of the
database in production and be able to load it in development. What
methods are available with capistrano/rails to handle this?

–
Anthony E.
408-656-2473
http://anthony.ettinger.name

3

I’m curious about this as well…I’d like to take a snapshot of the
database in production and be able to load it in development. What
methods are available with capistrano/rails to handle this?

You can take a dump of your production database and load it directly
on your development box if both are using the same database software.

postgres:
prod$ pg_dump blah > blah.psql
prod$ cat blah.psql | ssh [email protected] ‘cat > ~/backups/sqldumps/
blah-2008-11-08.psql’
dev$ dropdb blah_development && createdb blah_development && cat ~/
backups/sqldumps/blah-2008-11-08.psql | psql blah_development

A similar process is possible with mysql using mysqldump and mysqladmin.

Ben Hoskings

4

I’m working on the phase 2 development of my project. Phase 1 of this
project is currently used by lot of people. In Phase 2, there are some
new features and the database structure and relationships are
different. Now, i have to move the data in the old database to the new
database.

Is phase 2 a different codebase to phase 1? If you’re still working on
the same codebase, the changes you’ve made to the database layout
should have been done with migrations. Changing resource structure &
relationships often requires a lot more work than just changing the
table layout, so to do that you can add extra migrations to do the work.

For example, say you started with two tables, articles and authors,
articles had an author_id. All working and online as phase 1. Then,
you decide to redesign the app to allow articles to have multiple
authors—the obvious solution is to add a join table.

However, adding the join table is only one of the three sets of
modifications to the database, each of which should be done in its own
migration:

  • create the authorships join table with author_id and article_id
  • insert a record into authorships for each existing article, using
    articles.id and articles.author_id
  • remove the author_id column from articles

Next, you have to update your ActiveRecord models to reflect the
change. For example, articles now

has_many :authors, :through => :authorships

instead of

belongs_to :author

Encoding all this in migrations means that when you eventually deploy
phase 2 of your application, you can perform all the required
massaging of your data at once by migrating your database.

Unfortunately, it gets messy. A weakness of migrations is that they
run against the models in your app as they currently exist, not as
they did when you wrote the migration.

For example, say you add a datetime column to the articles table
called published_at, to record a timestamp when the article was
published (perhaps unpublished articles are visible only by their
authors, for editing). You then add some new code to the article model:

def published?; !published_at.nil? end
validates_spelling_of :body, :if => proc {|article| article.published? }

Now, that will migrate up and work fine. But when you finally deploy
that code to the server and run all your migrations, any migrations
that run before that one, and that try to create or update an
article, will fail - because saving triggers the validators, which
will attempt to access the published_at column, which doesn’t yet
exist because that migration is yet to run.

Personally I think Rails should handle this in some way. Perhaps we
need the ability to freeze models to specific versions and store them
along with the migrations that require it.

Cheers
Ben