[LRUG] Rails schema line order changing

[James Adam]

I don’t think the actual database has been specified anywhere, but if it’s MySQL or MariaDB, it’s actually quite easy to re-order columns; I just the free Sequel Ace client, which turns it into a drag & drop operation. That makes it easy to modify your local development database to match `schema.rb` (and hopefully production):

1. Check out `schema.rb` from version control
2. Run `rails db:schema:dump`
3. If there are no uncommitted changes in `schema.rb`, you’re done!
4. If there are uncommitted changes, use the diff of `schema.rb` to see them in detail and fix your local database (by re-ordering columns or changing types or whatever).
5. Goto 2.

I also often see churn in the schema around column/database/server collation, which can be a little trickier to resolve due to how precedence of these values ends up being reflected into the schema dump, but it’s still possible to use the approach above to iteratively converge your local setup into line with what’s committed.

I believe with PostgreSQL it’s a little more complicated to reorder columns, in which case I’d strongly advocate just dropping your whole development database and taking a dump from a deployment environment (either structure-only, or including some/all data), then running `rails db:schema:dump` and committing that immediately as the new de facto schema that everyone needs to align with.

The `fix-db-schema-conflicts` gem will certainly keep the `schema.rb` file consistent and avoid version control churn, but at the expense of it not _actually_ reflecting the true column ordering on production or anyone else's database — whether or not that’s important will probably depend on how you are using your database. Personally I’d find it a bit weird not to have `id` as the first listed column in most of my tables, but that’s totally subjective.

Ultimately, there’s no substitute for diligence when committing. It’s not that unusual for my local database to have extra tables or columns from other branches that I’m working on or reviewing, and so as a rule, when I am committing changes that include anything in schema.rb I check every change to make sure it’s expected, and only stage the changes that I _know_ are intentional at this point. As Stuart mentioned in his original message, it can get annoying, but I think it’s really the only way to be confident that your schema matches production and everyone else’s.


> On 1 Mar 2021, at 21:46, Sam Livingston-Gray <geeksam at gmail.com> wrote:
> 
> I like this.  The only thing I would change is that instead of "whoever yells loudest gets to keep their database," you run `db:schema:dump` against the production database, then copy the resulting file to somewhere it can be committed to Git.  :)
> 
> On Mon, Mar 1, 2021 at 1:36 AM Patrick Gleeson <patrick.c.gleeson at gmail.com <mailto:patrick.c.gleeson at gmail.com>> wrote:
> Step one: Whoever yells loudest gets to keep their database
> Step two: Everyone else does rails db:reset on their development databases, and possibly just db:schema:load on their test ones if you have seed data you don't want in your test db.
> 
> The key is that db:schema:load reads from the schema file as-is, rather than reading from the migrations and re-constructing the schema from the database.
> 
>  - Patrick
> 
> Why aren't you coding? <https://whyarentyoucoding.com/>
> On Mon, 1 Mar 2021 at 08:10, Stuart Harrison <pezholio at gmail.com <mailto:pezholio at gmail.com>> wrote:
> Morning LRUG
> 
> I wanted to share an annoyance I've had on multiple projects, and wondered if anyone had any fixes for it? 
> 
> If there are multiple devs on a project committing a bunch of migrations and running `db:migrate`, it seems that, over time, the order of columns in the schema.rb file jumps around every time the command is run. 
> 
> I can get around this by only committing the relevant line(s) when I run a migration, but this does get annoying. 
> 
> I'm sure there is a solution, but it's disappeared out of my head, and seems to be an ungoogleable problem, so was wondering if anyone had come across a solution?
> 
> Cheers
> 
> Stu
> _______________________________________________
> Chat mailing list
> Chat at lists.lrug.org <mailto:Chat at lists.lrug.org>
> Archives: http://lists.lrug.org/pipermail/chat-lrug.org <http://lists.lrug.org/pipermail/chat-lrug.org>
> Manage your subscription: http://lists.lrug.org/options.cgi/chat-lrug.org <http://lists.lrug.org/options.cgi/chat-lrug.org>
> List info: http://lists.lrug.org/listinfo.cgi/chat-lrug.org <http://lists.lrug.org/listinfo.cgi/chat-lrug.org>
> _______________________________________________
> Chat mailing list
> Chat at lists.lrug.org <mailto:Chat at lists.lrug.org>
> Archives: http://lists.lrug.org/pipermail/chat-lrug.org <http://lists.lrug.org/pipermail/chat-lrug.org>
> Manage your subscription: http://lists.lrug.org/options.cgi/chat-lrug.org <http://lists.lrug.org/options.cgi/chat-lrug.org>
> List info: http://lists.lrug.org/listinfo.cgi/chat-lrug.org <http://lists.lrug.org/listinfo.cgi/chat-lrug.org>
> _______________________________________________
> Chat mailing list
> Chat at lists.lrug.org
> Archives: http://lists.lrug.org/pipermail/chat-lrug.org
> Manage your subscription: http://lists.lrug.org/options.cgi/chat-lrug.org
> List info: http://lists.lrug.org/listinfo.cgi/chat-lrug.org

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lrug.org/pipermail/chat-lrug.org/attachments/20210302/edd77955/attachment.html>