# Migrating data from old databse structure to new db structure **URL:** \ **Category:** Deployment\ **Created:** [November 6, 2008, 6:36pm UTC](https://www.ruby-forum.com/t/migrating-data-from-old-databse-structure-to-new-db-structure/152716 "2008-11-06T18:36:00Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![sri](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/e9a140/32.png) [@sri](https://www.ruby-forum.com/u/sri)\ **Post date:** [November 6, 2008, 6:36pm UTC](https://www.ruby-forum.com/t/migrating-data-from-old-databse-structure-to-new-db-structure/152716/1 "2008-11-06T18:36:00Z")
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. ---
**Author:** ![sri](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/e9a140/32.png) [@sri](https://www.ruby-forum.com/u/sri)\ **Post date:** [November 7, 2008, 7:42pm UTC](https://www.ruby-forum.com/t/migrating-data-from-old-databse-structure-to-new-db-structure/152716/2 "2008-11-07T19:42:26Z")
On Thu, Nov 6, 2008 at 8:53 AM, Sri [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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](http://anthony.ettinger.name) ---
**Author:** ![sri](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/e9a140/32.png) [@sri](https://www.ruby-forum.com/u/sri)\ **Post date:** [November 8, 2008, 6:33pm UTC](https://www.ruby-forum.com/t/migrating-data-from-old-databse-structure-to-new-db-structure/152716/3 "2008-11-08T18:33:42Z")
> 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 removed\_email\_address@domain.invalid ‘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 ---
**Author:** ![sri](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/e9a140/32.png) [@sri](https://www.ruby-forum.com/u/sri)\ **Post date:** [November 8, 2008, 6:34pm UTC](https://www.ruby-forum.com/t/migrating-data-from-old-databse-structure-to-new-db-structure/152716/4 "2008-11-08T18:34:17Z")
> 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