5 ms·

How do you deal with DB migrations?

timdorr 7y ago
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".
diafygi 7y ago
We do something similar, where we just have a branch that is "prod", and the deploy script just checks out that branch, packages up all the things, and deploys them to the servers. If you want to roll back, just force-push the "prod" branch to whatever commit and run deploy again. For database migrations, we (1) design them so they can be applied without breaking the existing app and (2) make a "pre-launch" commit that adds those migrations to the codebase but doesn't have any code that uses them yet. To deploy, we merge the "pre-launch" into "prod" and deploy, and since the app doesn't use the new db changes yet, it will happily continue working fine. Then, at our leisure we can manually run the migration (either through the framework built-in migration tools or manually through the db shell). Then, we can merge the full "launch" branch into "prod" and deploy again, which will push the code that starts using the db changes. To roll back, we move "prod" back to "pre-launch" and deploy, which moves the app back to the state where the code isn't using the db changes, but the changes are still expected to be in place. Then, we manually roll back the migrations using the reverse of whatever migration method you used originally, which is fine since nothing in the codebase in the "pre-launch" commit is using the db changes. Then, we move "prod" back to whatever commit we need to roll back to and deploy again. It takes a bit of planning and forethought, but it means no downtime and you have all the time you need to manually apply and roll-back db changes that can take a while (adding indexes to huge tables, etc.).
atombender 7y ago
For us, database migrations are a separate step, and we make sure to know whether a migration results in breakage. If it does, we take the app down first (with a maintenance message) so that errors won't bubble up to the users and APIs. If we need to roll back a deploy that has migrations, we just roll back the migration. Every migration requires a "down" step that reverses itself. Our Go apps use Goose [1], but there are many other solutions. [1] https://github.com/pressly/goose https://github.com/pressly/goose

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".

diafygi 7y ago

We do something similar, where we just have a branch that is "prod", and the deploy script just checks out that branch, packages up all the things, and deploys them to the servers. If you want to roll back, just force-push the "prod" branch to whatever commit and run deploy again. For database migrations, we (1) design them so they can be applied without breaking the existing app and (2) make a "pre-launch" commit that adds those migrations to the codebase but doesn't have any code that uses them yet. To deploy, we merge the "pre-launch" into "prod" and deploy, and since the app doesn't use the new db changes yet, it will happily continue working fine. Then, at our leisure we can manually run the migration (either through the framework built-in migration tools or manually through the db shell). Then, we can merge the full "launch" branch into "prod" and deploy again, which will push the code that starts using the db changes. To roll back, we move "prod" back to "pre-launch" and deploy, which moves the app back to the state where the code isn't using the db changes, but the changes are still expected to be in place. Then, we manually roll back the migrations using the reverse of whatever migration method you used originally, which is fine since nothing in the codebase in the "pre-launch" commit is using the db changes. Then, we move "prod" back to whatever commit we need to roll back to and deploy again. It takes a bit of planning and forethought, but it means no downtime and you have all the time you need to manually apply and roll-back db changes that can take a while (adding indexes to huge tables, etc.).

atombender 7y ago

For us, database migrations are a separate step, and we make sure to know whether a migration results in breakage. If it does, we take the app down first (with a maintenance message) so that errors won't bubble up to the users and APIs. If we need to roll back a deploy that has migrations, we just roll back the migration. Every migration requires a "down" step that reverses itself. Our Go apps use Goose [1], but there are many other solutions. [1] https://github.com/pressly/goose https://github.com/pressly/goose