The question is published on by Tutorial Guruji team.
Right now I’m working on updating a Rails app and the database has some issues. It’s also being converted from MySQL to PostgreSQL.
There’s 3 columns being used to track one time value. The time the facility opens on Monday is being recorded as
monday_open_ampm. I’d like to merge these into a single time field.
There are also several fields being used for only 1% of the 3000+ records, so I’d like to break those out into a separate table.
What would be the best way to do this? I imagine it could probably be done in SQL with some kind of stored procedures/cursors. Is there a way to do it with Ruby/Rails?
The Rails way to deal with incremental database changes is to use migrations. Migrations let you apply incremental changes to your schema or database contents in an orderly fashion, even as you’re collaborating with a team. There are nice helpers for common tasks like creating and dropping tables, renaming columns, and simple things like that, but you can drop to arbitrary SQL if you need to (although, be aware that that will most likely tie you to your current database, and make further moves more difficult).
Basically, you can generate a new migration with
rails generate migration ConsolidateDateColumns (for example). This will create a template for you in the
db/migrate directory; see the Rails Guides entry to get started on writing them. When you’re ready to apply it, run
The advantages of doing it this way are that it lets you easily apply the same changes to different environments (development, test, production, staging, or across your development team) and keep them in sync, and it encourages you to keep things reversible whenever possible, so you maintain some degree of freedom to migrate back and forth if you need to.
One more thing: it sounds like you’re going to be doing a lot of major changes in quick succession. Make sure that you take a backup of your original database before you begin, and thoroughly test your work against a reduced test set in a separate environment before you run it against the real thing!