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 April 11, 2024

Tags: postgres, sql, 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

# JSONB Fields

  • jsonb supports indexing (json does not)
  • 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

# add_ examples

See Disabled transactions for concurrently 

add_index :payments, [:order_id, :amount, :payment_date, :status],
          name: 'unique_index_to_avoid_duplicate_payments',
          where: "status ='Scheduled'",
          unique: true

add_column
add_foreign_keya
add_reference

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

# polymorphic

t.references :category, polymorphic: true

$ rails g migration CreateWidgets imageable:references{polymorphic}

# Overriding Index Name

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

# Disable transactions / Concurrently add indexes

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 Constraints

add_check_constraint :things, "completed_at > created_at", name: 'things_completed_after_created'

Add validate: false to add NOT VALID to the check which would only enforce the check on updates and inserts. Existing records would not be enforced. This option would also mean the exclusive lock on the table due to adding the constraint would be much shorter.

To enforce the constraint on existing rows: 

validate_check_constraint :things, name: 'things_completed_after_created'

# Enforcing rails polymorphic as FK constraint example

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

# Enabling Postgres extensions

enable_extension ​'citext'​ ​unless​ extension_enabled?(​'citext'​)

# Postgres generated columns

Requires postgres v12+ See also https://www.postgresql.org/docs/current/ddl-generated-columns.html#DDL-GENERATED-COLUMNS

This might be useful to compose queries where you would want to select on some computation of several columns.

Warning see also https://github.com/ankane/strong_migrations#adding-a-stored-generated-column 

t.virtual, :height_in, as: (height_cm / 2.54), stored: true

# 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