Alex's Slip-box

These are my org-mode notes in sort of Zettelkasten style

ActiveRecord Migrations Cheatsheet

Created on April 17, 2018

Updated on May 5, 2023

Tags: rails, ruby

:ID: A3972BA3-6BD6-4970-8B22-0065A1F7978D

# Casting with :using option (cast string to int)

See also this code from the Rails codebase

Example changing the data type from string to int and cast exiting values to int. 

change_column :people, :company_id, :integer, using: 'company_id::integer'

# Pass Arguments as Column Options

rails g migration user first_name{16} #  limit
rails g migration order price{5,2} #  precision, scale

# Rollback to Version or Step Count

rake db:migrate:down VERSION="2017102900000"
rake db:rollback STEP=2

# List Migrations

rails db:migrate:status

# Reversible

def change  reversible do |dir|
      dir.up do
        # up code
      end

      dir.down do
        # rollback code
      end
    end
end

# create_table(name, options, &block) / change_table(name, options,&block)

  • id:: instructions for primary key
create_table :clients, id: :clients_id do |t|
  create_table :clients, id: false do |t| #  for a join table
  • force: true: Drops the table if it already exists.
  • options:: Append column instructions to CREATE.
  • temporary: true: Creates a temp table for the duration of the connection. Useful for migrating data from one table to another.

# create_join_table

  • :table_name and :column_options are available.
  • Skips setting a primary key.
  • Does not by default add FK and indices need to add t.reference manually.
  • Naming convention: “categories_products / CategoriesProduct“
rails g migration CreateJoinTableRestaurantUser restaurants users

create_join_table :ingredients, :recipes
create_join_table(:users, :auctions, column_options: {type: :uuid})

# Column Data TypesDB agnostic data types:

create_table :users do |t|
t.primary_key :id
t.string (varchar(255), defaults to limit: 255)
t.text
t.integer (11 digits max)
t.float
t.decimal
t.datetime
t.timestamp
t.time
t.date
t.binary (bytea/blob)
t.booleanend

# Postgres Types

All Postgres Types:[https://github.com/rails/rails/blob/master/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb# L75-L116](https://github.com/rails/rails/blob/master/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb# L75-L116)

# JSONB Fields

  • If you need to index, use GIN or GiST index type.
  • GIN and GiST indexes support queries with @>, ?, ?&, and ?|
  • JSON Operators.
add_column :users, :settings, :jsonb, null: false, default: '{}'
add_index :users, :settings, using: :gin

# Array Fields

  • length option limits the number of items
t.string :tags, array: true, default: '{rails, ruby}’, length: 10

# indexing array fields

add_index :articles, :tags, using: 'gin'

# querying array fields

Article.where("? = ANY( tags)", “rails")

# Range Types

  • daterange
  • int4range
  • int8range
  • numrange
  • tsrange
  • tstzrange

Example: 

t.daterange :availability

# Column Options

:null (boolean)
:limit (integer)
:default
:precision (decimal, the total number of digits)
:scale (decimal, the number of digits to the right of the decimal)
:comment (Rails 5)
:index

Schema Tasks 

add_index
#  Partial index with :where option:   add_index(:clients, :status, where: 'active’)#  Partial composite unique indexadd_index :payments, [:order_id, :amount, :payment_date, :status]            name: 'unique_index_to_avoid_duplicate_payments’,            where: "status = 'Scheduled'”,            unique: true

add_columnadd_foreign_keyadd_reference

t.references :category
#  cascadingt.references :user, index: true, foreign_key: {on_delete: :cascade}

# polymorphic

t.references :category, polymorphic: true

# Overriding Index Name

t.references :protection_category, index: { name: 'index_financing_limits_on_protection_category_id'}

# Disable transactions

def change
  disable_ddl_transactions!

  add_index :events, :user_id, algorithm: :concurrently

# Running Migrations

db:create (dev and test)
db:create:all (all envs)
db:drop
db:drop:all
db:forward
db:rollback
db:migrate VERSION=20130313005347
db:migrate:down
db:migrate:up
db:migrate:redo
db:migrate:status
db:migrate:reset (resets DB using the migrations)
db:setup (uses the schema.rb)
db:schema:dump
db:schema:load
db:structure:dump
db:test:prepare (uses schema.rb)
db:version

# Adding Check Contraints

class CreatePaymentMethodOwners < ActiveRecord::Migration[5.1]
  def up
    create_table :payment_method_owners, id: false do |t|
      t.references :payment_method, foreign_key: true
      t.references :customer, foreign_key: true
      t.references :dealer, foreign_key: true
      t.references :provider, foreign_key: true
      t.references :employee, foreign_key: true

      t.timestamps
    end

    execute <<~SQL
      ALTER TABLE payment_method_owners
      ADD CONSTRAINT must_have_one_owner
      CHECK (
        (
          (customer_id IS NOT NULL)::integer +
          (dealer_id IS NOT NULL)::integer +
          (provider_id IS NOT NULL)::integer +
          (employee_id IS NOT NULL)::integer
        ) = 1
      );
    SQL
  end

  def down
    execute <<-SQL
      ALTER TABLE payment_method_owners
      DROP CONSTRAINT must_have_one_owner
    SQL

    drop_table :payment_method_owners
  end
end

# Data Migrations

Stick to using execute and write raw sequel. Referencing models in migrations can be more difficult to maintain. Using raw sql will allow continuous migration execution without depending on your codebase. 

ActiveRecord::Base.connection.execute(<<~SQL)
UPDATE blahs
SET ...
SQL

# With reversibility

def up
  ActiveRecord::Base.connection.execute(<<~SQL)
    UPDATE generated_reports
    SET (owner_id, owner_type) = (
      SELECT dealer_id, 'Dealer'
      FROM dealers_generated_reports
      WHERE generated_reports.id = dealers_generated_reports.generated_report_id
    )
  SQL

  drop_table(:dealers_generated_reports)
end

def down
  create_join_table :dealers, :generated_reports do |t|
    t.references :dealer, foreign_key: true
    t.references :generated_report, foreign_key: true
  end

  ActiveRecord::Base.connection.execute(<<~SQL)
    INSERT INTO dealers_generated_reports (dealer_id, generated_report_id)
    SELECT owner_id, id
    FROM generated_reports
    WHERE generated_reports.owner_id IS NOT NULL
  SQL
end

Search Results