0

I have a question about how to properly perform a database migration, specificaly a rename column.

Suppose the table has a column A and I want to rename it to B. If I do the rename and then deploy the code that references B, after performing the migration and before deploying the code, I will have errors, because the code will not be up to date with the schema. On the other hand If I deploy the code first, I will also have errors because the code will reference the column B until the migration is performed.

So I thought that the best way would be, before deploying the code, create a new column B with the data of A and then deploy. At first I thought that this will be okay, but then I realized that new data can be inserted in the table after making the column B with the data of A. So, what is the standard way to do this?

Thanks in advance.

gpinzon
  • 23
  • 3
  • Why can't you deploy both code and table changes at the same time? – RobertBaron Jun 09 '19 at 23:23
  • How would you do it at the same time? I mean, aren't the dB and the code two separated components? – gpinzon Jun 09 '19 at 23:34
  • You take the DB offline, apply the changes, and take the DB back online. This is one scenario. – RobertBaron Jun 09 '19 at 23:36
  • But that will result in downtime. Isn't there a way to avoid that? – gpinzon Jun 09 '19 at 23:37
  • You would have to provide more details about the constraints and DB technology that you have, and specify that you need to update with no downtime. From what I know (and I am not a DB expert), often column renames requires copying data to a temporary table. – RobertBaron Jun 09 '19 at 23:46
  • Well it's not a question about a particular dB technology (I work with PostgreSQL and RoR if you want to provide some example with that), I just wanted to know if there is a standard way to do this. In my question I specified that I don't want to have errors in the process and turning the dB off will produce errors. – gpinzon Jun 09 '19 at 23:50
  • You can turn the DB off without errors, it becomes gradually unavailable as operations on it terminate. Once the updates are done, you set the DB on. Of course, users experience a down time. This is one standard way of doing it. – RobertBaron Jun 10 '19 at 00:01

1 Answers1

0

If your requirement is to do this with no downtime you could try this:

1) Add your new column B
2) Add a trigger that updates Column B every time Column A is updated, 
   and Column B every time Column A is updated. 
   Note that RECURSIVE_TRIGGERS must be set to OFF
2a) If you want to you could add a second column CodeVersion which can help identify 
    what version of the code was used, and which direction to copy data in the trigger,
    and avoid any issues with recursion
3) Copy all the data from Column A to Column B
4) At your leasure, upgrade the code. You could do this over a period of time
5) Once everyone is on the new version, remove the trigger and the old column
TomC
  • 2,759
  • 1
  • 7
  • 16