vets-api database migrations

Last Updated: 16 May 2024

vets-api should not require downtime for database updates. All database modifications must be performant and not negatively affect normal site operation. To ensure the continued availability of database resources, we follow these these documented development patterns and procedures.

Deployment

To account for the varying complexities with migrations defined below, Rails database migrations for vets-api are triggered by an automatic db-migrate job for all environments, including sandbox and prod.

Hot Compatibility

As a result of deployment patterns (blue/green or rolling deployment) multiple versions of vets-api may run at the same time. Since the database is a shared resource, it's critical all running versions of vets-apimaintain compatibility with the running schema. Introduction of a non-compatible change necessitates a series of deployments.

Automatic Protection

The zero_downtime_migrations gem prevents several classes of migrations that are known to cause issues. This is not a catch-all, and database migrations must still undergo thoughtful review.

Constraints

Postgres constraints aside from null are currently out of the scope of the vets-api implementation and these guidelines. Implementation of constraints should be limited to performance optimization, and must be thoroughly evaluated on a case-by-case basis.

Structuring Schema Migrations

Database migration rollback will not be performed on development, staging, or production environments. Using the down or change method of a Rails migration may be useful for local development, but other environments must always move forward.

Data Migrations

Data migrations must be included as a rake task outside of rails database migrations.

CODE

# lib/data_migrations/user_status_default_update.rb
# frozen_string_literal: true
module DataMigrations
  module UserStatusDefaultUpdate
    module_function

    def run
      User.where(status: null).
        update_all(status: 'active')
    end
  end
end

# rakelib/user_status_default_update.rake
namespace :data_migration do
  task user_status_default_update: :environment do
    DataMigrations::UserStatusDefaultUpdate.run
  end
end

Removal of both files is the responsibility of the developer who built the upgrade following confirmation of deployment to production.

Tables

Table Addition

Good

Add the database migration to create the new table

CODE

 class CreateFutureClaims < ActiveRecord::Migration
   def change
     create_table :future_claims, if_not_exists: true do |t|
       t.user_uuid null:false
     end
   end
 end

Deploy to production.

Old versions of the application will not be affected by new table creation. This operation can be considered generally safe!

Table Removal

Old versions of the application will error when attempting to write or read records from a table dropped during a migration.

Good

Remove existing ORM models and any related integrations
Deploy to production

Create a migration to remove the table:

CODE

 class DropFutureClaims < ActiveRecord::Migration
   def change
     remove_table :future_claims
   end
 end

Deploy to production (Migrations are automatically ran during deployment)

Indexes

Index updates must always be included in a separate migration. Updating the index is done outside of the transaction, which permits disabling the DDL transaction selectively for the update.

Calling disable_ddl_transaction! in your migration allows each command in the migration to be committed to the database independently. This is necessary sometimes but be aware it’s outside the safety of a database transaction. In a normal transaction, Rails will roll back the transaction if any command in the migration fails, undoing any changes made up to that point in the migration. If disable_ddl_transaction! is used, but the migration fails, the migration will be partially complete, leaving things in a failed state. This is why we advise index updates to be in a migration of their own and to use disable_ddl_transaction! only when necessary.

Index Addition

Index addition is generally safe, but must be performed with the concurrently algorithm and outside of the DDL transaction to avoid locking. Take care to ensure that the application is not capable of writing non-unique values and that existing data is unique when generating a unique index.

Good

Create the new index:

CODE

 class AddIndex < ActiveRecord::Migration
   disable_ddl_transaction!

   def change
     add_index :users, :first_name, algorithm: :concurrently, if_not_exists: true
   end
 end

Deploy to production.

Index Removal

Removing an index can also be performed with concurrently outside of the DDL transaction:

Good

Remove the existing index:

CODE

 class RemoveIndex < ActiveRecord::Migration
   disable_ddl_transaction!

   def up
     remove_index :users, :first_name, algorithm: :concurrently
   end
 end

Deploy to production.

Columns

Adding a column with a default value

A new column with a default value requires that postgres update all existing records in the table along with the schema update. This may cause performance issues.

Bad

CODE

# Adds a boolean to a benefits submission for the fake "chapter 9999" form
class AddChapter9999ToEducationBenefitsClaims < ActiveRecord::Migration
  def change
    add_column :education_benefits_claims, :chapter9999, :boolean, default: false
  end
end

Good

Create the new column without a default value

CODE

 class AddChapter9999ToEducationBenefitsClaims < ActiveRecord::Migration
   def change
     add_column :education_benefits_claims, :chapter9999, :boolean
   end
 end

Add a migration to set the default value for all new records (this does not update existing records)

CODE

 class AddChapter9999EducationBenefitsClaimsDefault < ActiveRecord::Migration
   def up
     change_column_default :education_benefits_claims, :chapter9999, false
   end
 end

Create a data migration

CODE

 # lib/data_migrations/chapter9999_education_benefits_default.rb
 # frozen_string_literal: true
 # TODO: delete after running
 module DataMigrations
   module Chapter9999EducationBenefitsDefault
     module_function

     def run
       EducationBenefitsSubmission.where(chapter9999: null).
         update_all(chapter9999: false)
     end
   end
 end

 # rakelib/submission_dependent_type_change.rake
 namespace :data_migration do
   task chapter9999_education_benefits_default: :environment do
     DataMigrations::Chapter9999EducationBenefitsDefault.run
   end
 end

Deploy to production (Migrations are automatically ran during deployment)
When all application instances are running on the same minor version, perform the data migration
CODE
```
 $ RAILS_ENV=production bundle exec rake data_migration:chapter9999_education_benefits_default
```
Remove data migration module and task

Adding a column without a default value

Create the new column

CODE

 class AddNoteToEducationBenefitsClaims < ActiveRecord::Migration
   def up
     add_column :education_benefits_claims, :note, :text
   end
 end

Deploy to production.

Postgres will lock on ALTER TABLE, but the operation can complete quickly and does not require updates to data on disk.

Adding a non-null column with a default (🐉)

Bad

CODE

class AddStatusToUser < ActiveRecord::Migration
  def change
    add_column :users, :status, :string, null: false, default: 'active'
  end
end

This requires rewriting values for each existing row.

Good

Add the column without a default or constraint

CODE

 class AddStatusToUser < ActiveRecord::Migration
   def change
     add_column :users, :status, :string
   end
 end

Add the default for new records

CODE

 class AddDefaultStatusToUser < ActiveRecord::Migration
   def change
     change_column_default :users, :status, 'active'
   end
 end

Create a data migration

CODE

 # lib/data_migrations/user_status_default_update.rb
 # frozen_string_literal: true
 # TODO: delete after running
 module DataMigrations
   module UserStatusDefaultUpdate
     module_function

     def run
       User.where(status: null).
         update_all(status: 'active')
     end
   end
 end

 # rakelib/user_status_default_update.rake
 namespace :data_migration do
   task user_status_default_update: :environment do
     DataMigrations::UserStatusDefaultUpdate.run
   end
 end

Deploy to production (Migrations are automatically ran during deployment)
When all instances are running the latest version of the application, run the data migration
CODE
```
 $ RAILS_ENV=production bundle exec rake data_migration:user_status_default_update
```

Set the not null constraint

CODE

 class ChangeUsersStatusNotNull < ActiveRecord::Migration
   def change
     change_column_null :users, :status, true
   end
 end

Deploy to production (Migrations are automatically ran during deployment)

No default is specified, so no default values must be written by postgres. However, the existing application versions will error when attempting to add a new record to the users table since they are not aware of the email column.

Adding a non-null column without a default

Bad

CODE

class AddEmailToUser < ActiveRecord::Migration
  def change
    add_column :users, :email, :string, null: false
  end
end

No default is specified, so no default values must be written by postgres. However, the existing application versions will error when attempting to add a new record to the users table since they are not aware of the email column.

Good

Add the column without a default or constraint

CODE

 class AddEmailToUser < ActiveRecord::Migration
   def change
     add_column :users, :email, :string
   end
 end

Create a data migration to populate the new field

CODE

 # lib/data_migrations/user_status_default_update.rb
 # frozen_string_literal: true
 # TODO: delete after running
 module DataMigrations
   module UserEmailPopulation
     module_function

     # Populate user email address from external service
     def run
       User.where(email: null).
         find_each do |user|
         user.email = user.external.email
       end
     end
   end
 end

 # rakelib/user_email_population.rake
 namespace :data_migration do
   task user_status_default_update: environment do
     DataMigrations::UserEmailPopulation.run
   end
 end

Deploy to production (Migrations are automatically ran during deployment)
When all instances are running the latest version of the application, run the data migration
CODE
```
 $ RAILS_ENV=production bundle exec rake data_migration:user_status_default_update
```

Set the not null constraint

CODE

 class ChangeUsersStatusNotNull < ActiveRecord::Migration
   def change
     chagne_column_null :users, :status, true
   end
 end

Deploy to production (Migrations are automatically ran during deployment)

Changing a column type (🐉)

Changing the column type is a complicated process to perform properly with hot-compatibility. It involves adding a new column with the new type, providing backwards-compatible support for the old column, and then performing a subsequent rename operation.

Bad

CODE

class UpdateTemporaryDependentsInteger < ActiveRecord::Migration
  def change
    change_table :submissions do |t|
      t.change :dependents, :integer
    end
  end
end

After migration, the old version of the application may attempt to write an invalid value to the dependents column, causing an error on insert/update. While this can be mitigated in this example by consistent type checking outside of the database, the process can cause more problematic failures for more complex changes. Additionally, postgres will lock the table and rewrite all records with this operation, which can block requests.

Good

Create a new column with the new type (for a column with a default value, the default must be backfilled in a separate operation - see Adding a column with a default value)
CODE
```
 class AddTemporaryDependentsInteger < ActiveRecord::Migration
   def change
     add_column :submissions, :dependents_int, :integer
   end
 end
```

Write to both columns

CODE

 class Submission
   ...

   def dependents
     self[:dependents_int] || self[:dependents].to_i
   end

   def dependents=(v)
     self[:dependents] = v
     self[:dependents_int] = v.to_i
   end
 end

Create a data migration

CODE

 # lib/data_migrations/submission_dependent_type_change.rb
 # frozen_string_literal: true
 # TODO: delete after running
 module DataMigrations
   module SubmissionDependentTypeChange
     module_function

     def run
       Submission.connection.execute <<-QUERY
         UPDATE submissions
           SET dependents_int = dependents::int;
       QUERY
     end
   end
 end

 # rakelib/submission_dependent_type_change.rake
 namespace :data_migration do
   task submission_dependent_type_change: :environment do
     DataMigrations::SubmissionDependentTypeChange.run
   end
 end

Deploy the changes to production.

When no instances are running on the old version, run the data migration

CODE

 $ RAILS_ENV=production bundle exec rake data_migration:submission_dependent_type_change

Utilize the new column exclusively

CODE

 class Submission
   ...
   def dependents
     dependents_int
   end

   def dependents=(v)
     self[:dependents_int] = v
   end
 end

Drop the old column, add a new one with the proper type

CODE

 class ChangeDependentsType < ActiveRecord::Migration
   def change
     remove_column :submissions, :dependents
     add_column :submissions, :dependents, :integer
   end
 end

Write to both columns

CODE

 class Submission
   ...
   def dependents
     self[:dependents] || self[:dependents_int]
   end

   def dependents=(v)
     self[:dependents] = v
     self[:dependents_int] = v
   end
 end

Create a data migration

CODE

 module DataMigrations
   module SubmissionDependencyIntToDependencyUpdate
     module_function

     def run
       Submission.connection.execute <<-QUERY
         UPDATE submissions
           SET dependents = dependents_int;
       QUERY
     end
   end
 end

Deploy the changes to production.

When no instances are running on the old version, run the data migration

CODE

$ RAILS_ENV=production bundle exec rake data_migration:submission_dependency_int_to_dependency_update

Remove hot compatibility code from the model
CODE
```
class Submission
  ...
end
```
Deploy the changes to production. All instances are now reading and writing exclusively to the new column with the proper type.

Remove the column

CODE

class RemoveTemporaryDependentsInteger < ActiveRecord::Migration
  def change
    remove_column :submissions, :dependents_int
  end
end

Deploy the changes to production.

Changing a column default

There are no issues changing a column default outside of null constraints provided the old version of the application can function with the new default value after migrations are performed.

CODE

class ChangeUsersStatusDefault < ActiveRecord::Migration
  def change
    change_column_default :users, :status, 'pending'
  end
end

Help and feedback

Suggest content changes to this page.
Submit new Platform Website content.
Get help from the Platform Support Team in Slack.
Submit a feature idea to the Platform.