4 ms·

Migrations should be separated out from other code changes. If you have a rolling deploy process, then you need to make sure your database changes are forwards

Migrations should be separated out from other code changes. If you have a rolling deploy process, then you need to make sure your database changes are forwards and backwards compatible. Assuming you've got a CI in place, making the migrations a separate, testable commit will let you do this easily. We did this at my last company with a small GitHub bot and a CODEOWNERS file.

kyriakos 7y ago
Exactly this. If your db changes are compatible in both directions then you are safe. It's not hard to achieve. Just make sure new columns have default values and your orm can deal with extra undefined columns and don't drop anything during migrations.
- seanwilson 7y ago
  When do you drop the old e.g. columns and tables that are no longer in use though? You schedule it for say a week later when you're sure you won't need to roll back that far?
  - matwood 7y ago
    Correct. We 'mark' no longer used database objects for removal at some future date. And mark is really just add a ticket to be completed in the future.
    - mooreds 7y ago
      How long does it stick around before final removal, typically?
      - matwood 7y ago
        Depends. Table removals are usually quicker because they have zero system impact once all the references in code have been removed. Column removals can require planning depending on the size of the table and if indexes will have to be changed.
    - seanwilson 7y ago
      Do you have a method to rollback database object removals if they cause problems?
      - jcampbell1 7y ago
        Assuming one step per day to rename a column: create new column, update code to write to new and old, update code to read from new, update code only write to new, rename old to something like xxx_del_pending, delete old column. This process lets you validate and rollback/recover if needed.
        seanwilson 7y ago
        Is there a quicker but still fairly safe alternative to this? If you're working solo for example, this doesn't sound practical.
        matwood 7y ago
        I'll answer both your questions here. Yes, we can rollback a db change using migrates but usually a true rollback requires a restore since data will be lost. In general what I have found is that database changes are unique to your situation. When an app is small it's fine for them to automatically run with the code commits. The system I deal with now has some very large tables, and running database migrations often requires planning. A column addition might be added weeks before the code is written to use the new column. It's just the nature of dealing with large tables. Working solo, typically also means smaller, so there is a lot more leeway. I would do whatever works for you, and realize it's a good thing if you ever large enough to need to address other problems.
procinct 7y ago
Say you were to rename a column, would you achieve this by duplicating the column with the duplicate having the new name?
- EspadaV9 7y ago
  Yes, this is we've just done recently. Renaming a column required creating a new one with the new name and saving the same data to both columns for a while before dropping old old column a few weeks later.
NicoJuicy 7y ago
That's not always possible in an easy way. It leads to more problems than it solves as soon as there are >1 developers. Your code is dependant on a current state of the database and you should factor that in with your deployments. My deployment have a optional Target on the migration for rollbacks and I have never had any problems. Depends on the tech you use ofc. But for. Net the entity framework make it easy. Your comment is only used as a excuse for imperfect deployments and creates too much additional problems. You should really do it like this, so the code decides how the connected database should. Otherwhise the human overhead to execute the correct scripts will have a problem sooner or later ( eg. If a separate team/person handjes deployments). It's also vague to know if someone else has configured everything correct ( at my current job, they put all the scripts in a separate branch. One application connects to another DB and ofc the DB deployments where not configured yet). Urgh
xorcist 7y ago
> you need to make sure your database changes are forwards and backwards compatible That's just another way of saying "you need to make sure you can roll forwards and backwards". The question was how do you do it. The answer should include the phrase "we test it".

kyriakos 7y ago

Exactly this. If your db changes are compatible in both directions then you are safe. It's not hard to achieve. Just make sure new columns have default values and your orm can deal with extra undefined columns and don't drop anything during migrations.

seanwilson 7y ago
When do you drop the old e.g. columns and tables that are no longer in use though? You schedule it for say a week later when you're sure you won't need to roll back that far?
- matwood 7y ago
  Correct. We 'mark' no longer used database objects for removal at some future date. And mark is really just add a ticket to be completed in the future.
  - mooreds 7y ago
    How long does it stick around before final removal, typically?
    - matwood 7y ago
      Depends. Table removals are usually quicker because they have zero system impact once all the references in code have been removed. Column removals can require planning depending on the size of the table and if indexes will have to be changed.
  - seanwilson 7y ago
    Do you have a method to rollback database object removals if they cause problems?
    - jcampbell1 7y ago
      Assuming one step per day to rename a column: create new column, update code to write to new and old, update code to read from new, update code only write to new, rename old to something like xxx_del_pending, delete old column. This process lets you validate and rollback/recover if needed.
      - seanwilson 7y ago
        Is there a quicker but still fairly safe alternative to this? If you're working solo for example, this doesn't sound practical.
        matwood 7y ago
        I'll answer both your questions here. Yes, we can rollback a db change using migrates but usually a true rollback requires a restore since data will be lost. In general what I have found is that database changes are unique to your situation. When an app is small it's fine for them to automatically run with the code commits. The system I deal with now has some very large tables, and running database migrations often requires planning. A column addition might be added weeks before the code is written to use the new column. It's just the nature of dealing with large tables. Working solo, typically also means smaller, so there is a lot more leeway. I would do whatever works for you, and realize it's a good thing if you ever large enough to need to address other problems.

procinct 7y ago

Say you were to rename a column, would you achieve this by duplicating the column with the duplicate having the new name?

EspadaV9 7y ago
Yes, this is we've just done recently. Renaming a column required creating a new one with the new name and saving the same data to both columns for a while before dropping old old column a few weeks later.

NicoJuicy 7y ago

That's not always possible in an easy way. It leads to more problems than it solves as soon as there are >1 developers. Your code is dependant on a current state of the database and you should factor that in with your deployments. My deployment have a optional Target on the migration for rollbacks and I have never had any problems. Depends on the tech you use ofc. But for. Net the entity framework make it easy. Your comment is only used as a excuse for imperfect deployments and creates too much additional problems. You should really do it like this, so the code decides how the connected database should. Otherwhise the human overhead to execute the correct scripts will have a problem sooner or later ( eg. If a separate team/person handjes deployments). It's also vague to know if someone else has configured everything correct ( at my current job, they put all the scripts in a separate branch. One application connects to another DB and ofc the DB deployments where not configured yet). Urgh

xorcist 7y ago

> you need to make sure your database changes are forwards and backwards compatible That's just another way of saying "you need to make sure you can roll forwards and backwards". The question was how do you do it. The answer should include the phrase "we test it".