How do I easily restructure my Rails' DB not yet in production?

Question

Problem: I'm creating a Rails app, single dev, running staged/prod servers on Heroku, not publicly released yet. Reworking my DB infrastructure, since I've done several migrations since creating tables. I know it's somewhat trivial, but I'm trying to get things cleaned up before initial launch:

1. Redo indexes.
2. Reorder/rename fields. I would prefer avoiding tables with timestamp fields randomly sandwiched in the middle and PostgreSQL doesn't allow simple field reordering (for this reason, I may standardize timestamps as the first fields moving forward, so future migrations aren't so noticeable).

Possible Solution(s): I'll need to drop my schema and reload a clean copy of it. I can:

1. Edit schema.rb structure for existing tables to my liking.
2. (?) Manually edit the [VERSION] timestamp in schema.rb.
3. (?) Edit latest migration file, duplicate schema.rb.
4. Run rails db:schema:load-esque (likely with additional db:reset-esque steps to drop the existing schema/structure first).
5. Delete older migration files.

Question #1: See 2.-3. Aside from the elephant in the room that this method isn't generally recommended long-term, when does rails db:schema:dump have a use case?, since it's essentially what I'm doing by hand? I don't believe it would generate models tables not generated through Rails beforehand, so that could get messy (without running rails generate model --skip-migration). Does it create a new migration, or at minimum does it update the schema.rb timestamp so as not to look backwards at prior migrations? Otherwise, I would think :dump would be unconventional to Rails' own system.

Question #2: I know it will break staged/production servers once I push the changes (again, I'll have to run step 5. on them or just replace my Heroku apps with fresh copies). However, would this method also break these too, and/or break future Rails migration steps? I'd rather make sure whatever I build can be launched cleanly without requiring additional steps by hand that I could have avoided.

Answer 1

As someone who as ditched old, embarrassing migrations for a flat, updated schema part way through an app development process, I only ever regretted it.

Migrations only run on deployment, so there's no real speed reason to squash them, combine them, rename them, or remove them (unless you are totally ditching whatever is in that migration).

AND, ALL your migrations only run once on your first deploy. From then on, only future migrations will be run. So the overhead is a one-time thing.

Having a few (hundred) migration files is really no big deal.

"But what about the fact that migration #3 adds a column which is later removed by migration #45?" That's how we write software: over time, with changes. It's fine.

If you need to redo indexes and rename fields, write another migration. Call it "CleanupBeforeProductionDeploy" if it'll give you that wonderful sense of cleaned up code.

Reordering columns is useless. This isn't Excel. Don't bother.

If you need to display fields in a certain order, use SQL or .map or .pluck or I'm sure a dozen other Ruby or RoR solutions, that's what they are for.

You've done good work, it sounds like your app is nearly ready to deploy. Congratulations, that's a serious milestone. So many of us start scratching something out and never push it across the finish line. Seriously, you should feel good.

Don't procrastinate with meaningless fussing that will just lead to errors.

Go push the code and be happy.

---

Rant over. If I haven't convinced you, here are some tips to at least keep you safe / sane.

The purpose of the schema is a shortcut for creating a new dB from scratch and just jumping to the end without running every migration.

The schema dump is used, for example, by Heroku when you are creating a backup of your production database.

(Just an aside, I use parity to get production data from my apps into my development environments so I can work with "real" data).

You could also use a schema dump from the actual database to create a new schema file if something happened to your schema file.

So, you can do this, even though you shouldn't waste your time.

Here would be my order of operations:

1. Keep writing migrations for all these changes you want to make. Yes, write one or more migrations to make all your changes. Seriously. Let Rails handle versioning and timestamping your schema file as you go. And, this way you can do this in stages and test things out.

2. For changing your table column orders I'd do the following, it's messy, but it would work:

rename_table :users, :users_disorganized

create_table :users do |t|
  ...
end

# write some complicated SQL to copy 'users_disorganized' data into 'users'

# safety catch in case copying things over didn't work
drop_table :users_disorganized unless User.all.size.zero?

Use SQL to map and copy the contents of users_disorganized into users.

Here's a good SO post on the topic of SQL inside migrations

SQL is really your only choice here since you don't have an ApplicationRecord Model for UserDisorganized.

Could you make a UserDisorganized model just to make copying the files over easier? Yes, then you'd have to remember delete that file after your production deploy.

Starting to see how time-intensive this is going to be?

Now repeat this process for every table where you want to reorder columns.

3. Once you are all done writing migrations, your pristine schema now has the latest timestamp and version and everything so don't mess with these values.

(you can change them, it'll probably be fine, but if you decide to set them too far in the future... then forget about doing that... then write a small migration just to patch a bug or add a feature... then try to run that migration... then spend 4 hours trying to figure out why nothing happens when you run rake db:migrate... all just to realize the schema timestamp is later than your migration timestamp... and so on)

4. Now you can gulp delete all your migrations. Yeah. You don't want them? Here's your chance to prove it. You still serious about this?

5. How do you then initialize your production database? Tell Heroku to run rake db:schema:load instead of rake db:migrate.

Good luck.

(and don't bother)