New strategy for changing type of a column

[dbernheisel]

There's another way to safely change the type of a column. Essentially use an updateable view to help transition a new/old column to swap instantaneously.

1. Add a new column with the new type, and start writing to the new column, deploy.

# imagine if amounts were stored as strings in `amount` column
alter table("transactions") do
  # add :amount, :string # This column already exists and represents what's being replaced.
  add :amount_new, :decimal, precision: 12, scale: 2
end

2. Backfill to that new column. Wait for backfill to complete, verify 100% parity. You may need to add database constraints at this point if that's part of the business need.

3. Switch application reads to new column. Deploy.

field :amount, :decimal, source: :amount_new
field :amount_old, :string, source: :amount

4. Remove old column from schema. Deploy. At this point, the old column is no longer read.

5. Drop old column, rename the table to a temporary name and create an updatable view of with the original name while deploying that contains both columns which will be the same values in the database. This will ensure old code still works while it's being cycled out. Remove :source on the schema field. Deploy.

If you have other users using the database, such as business intelligence or other extractors, ensure they're aware of this change.

repo().transaction(fn repo ->
  repo.query!("ALTER TABLE transactions DROP COLUMN amount", [], [])
  repo.query!("ALTER TABLE transactions RENAME TO transactions_new", [], [])

  # This will be an updateable view
  repo.query!(
    "CREATE VIEW transactions AS SELECT *, amount_new AS amount FROM transactions_new", [], [])
end)

6. Bring everything back to sanity. Rename the new column to the original name, drop updatable view, and rename table back to normal. Deploy. This can happen once step 5 is fully rolled-out.

repo().transaction(fn repo ->
  repo.query!("DROP VIEW transactions", [], [])
  repo.query!("ALTER TABLE transactions_new RENAME amount_new TO amount", [], [])
  repo.query!("ALTER TABLE transactions_new RENAME TO transactions", [], [])
end)