search

openSUSE / open-build-service

Build and distribute Linux packages from sources in an automatic, consistent and reproducible way #obs
https://openbuildservice.org
GNU General Public License v2.0
903 stars 437 forks source link

Problems with migrations and database inconsistencies #2840

Open evanrolfe opened 7 years ago

evanrolfe commented 7 years ago

I've noticed quite a few problems with the way we currently handle migrations and databases which is resulting in extra work for us developers to fix things that could have been avoided in the first place. For example the P2 and P4 cards in the current sprint and also this card in the backlog. By not following conventional rails procedures for managing the database we are just creating more unnecessary work for ourselves.

Here are some of the problems I've noticed and some suggested solutions:

Problem 1: Migrations are being merged to master that fail when ran on production

Solution: Any migrations need to be tested on locally cloned production data before being merged. (Maybe this could be added to acceptance criteria?)

Problem 2: Certain things in structure.sql are unclear why they are there - the structure does not always match whats defined in the migrations and its unclear which structure is the one we want.

Solutions:

  1. PR reviews need to carefuly check that the changes in structure.sql match the migrations in the PR (this could be automated - perhaps using a travis script)
  2. Migrations need to be amended so that they run sequentially and generate the entire db structure from running rake db:migrate - this way it is clear what the correct value of structure.sql is: its the file that gets generated when you run rake db:migrate on an empty db
  3. db/structure.sql must never be changed by hand

Problem 3: The production database structure gets changed manually sometimes and this has lead to "drift" between development & production databases.

Solution: The production database should never be changed manually - only through migrations.

evanrolfe commented 7 years ago

Regarding Problem 2 Solution 2, the rails documentation says:

Migrations, mighty as they may be, are not the authoritative source for your database schema. That role falls to either db/schema.rb or an SQL file which Active Record generates by examining the database. They are not designed to be edited, they just represent the current state of the database.

So maybe this is not the solution. Personally I was pretty sure that this was the solution but it looks like I may be wrong about this. Looking at stackoverflow the general consensus is to use structure.sql as the authoratative source for database structure but there is some debate about this for example here is a good discussion: "Schema.rb vs migrations".