Skip to main content

getting-started-with-migrations

What is a migration?​

It's when you want to run a transaction in the database. A migration will get run as a single transaction unless you specify disable_ddl_transaction! in the migration

Applicable for:

  • Adding/Removing Tables
  • Adding/Removing Properties(Columns, Defaults, Constraints)
  • Adding/Removing Indexes
  • Adding/Removing Functions

You can use migrations to alter data as well, but not recommended.

How to get started:​

If you want to add a new table: rails g model [TableName] Otherwise: rails g migration [descriptionOfWhatYouWantToDo] <-- e.g. addAppIndexToAppAccesses

When you generate a migration, you'll have a file like: db/migrate/20191232224001_this_is_an_example.rb

class ThisIsAnExample < ActiveRecord::Migration[5.2]
  def change
  end
end

What is change?​

Change is actually two components rolled up into one. Change handles both up and down methods, where up is running a migration or transaction, and down is rolling back or undoing a migration.

Guide on ActiveRecord Migrations:​

https://guides.rubyonrails.org/active_record_migrations.html

Running the migration​

bundle exec rake db:migrate <--will run any migration not yet run bundle exec rake db:migrate:up VERSION=20191232224001 <-- will run migration for specific version

Rolling back the migration​

bundle exec rake db:rollback <-- will rollback the most recent migration run bundle exec rake db:migrate:down VERSION=20191232224001 <-- will rollback migration for specific version

Running SQL migrations (Views, Materialized Views, Functions)​

SEE 20190517180237_add_plpgsql_functions_and_views.rb for example You'll want to separate change into the separate up/down functions since rollback doesn't work well for SQL migrations

def up
  execute <<-SQL
    CREATE OR REPLACE FUNCTION sanitize_unicode(_text TEXT) RETURNS TEXT AS $$
      SELECT REGEXP_REPLACE(_text, '\\(?<!\\\\)(?:\\\\)*(?!\\)u0000', '\\\\u0000','g');
    $$ LANGUAGE SQL;
  SQL
  ### Or you can read the function from a file
  # sql = File.read("lib/database/sanitize_unicode_function.txt")
  # execute sql
end

def down
  execute <<-SQL
    DROP FUNCTION IF EXISTS sanitize_unicode(TEXT);
  SQL
end
  • Execute will run the function/query.
  • You can have the query directly in the migration file or as a separate file
  • When you run migrations in general, you should check for the existence of what you're creating/removing. The reason is because sometimes a migration may get run multiple times on an environment (for example because there is a version collision, so one version number gets updated).