Find missing table indexes and foreign keys

indexes.rake

namespace :db do
  desc 'Find missing table indexes, foreign keys and null false columns'
  task indexes: :environment do
    require 'rainbow'
    require 'tables_health'

    connection    = ActiveRecord::Base.connection
    tables_health = TablesHealth.new(connection)

    tables_health.indexes_report
    puts
    tables_health.foreign_keys_report
    puts
    tables_health.null_false_columns_report
  end
end

tables_health.rb

# Report missing indexes, foreign keys and null false columns.
#
# @example
#   require 'rainbow'
#   require 'tables_health'
#
#   connection    = ActiveRecord::Base.connection
#   tables_health = TablesHealth.new(connection)
#
#   tables_health.indexes_report
#   puts
#   tables_health.foreign_keys_report
#   puts
#   tables_health.null_false_columns_report
class TablesHealth
  attr_accessor :db

  def initialize(connection)
    self.db = Db.new(connection)
  end

  def indexes_report
    with_table('Indexes') do |table|
      table[:index] ? :green : :red
    end
  end

  def foreign_keys_report
    with_table('Foreign Keys') do |table|
      table[:foreign_key] ? :green : :red
    end
  end

  def null_false_columns_report
    with_table('Null false columns') do |table|
      table[:null] ? :red : :green
    end
  end

  def with_table(title)
    title(title)

    db.tables.each do |table|
      text = table[:info].map do |column, info|
        color = yield(info)
        Rainbow(column).color(color)
      end

      puts "#{ table[:name].ljust(db.longest_table) } #{ text.join(', ') }"
    end
  end

  def title(text)
    puts text
    puts '-' * text.size
  end

  class Db
    attr_accessor :connection, :tables

    def initialize(connection)
      self.connection = connection
      load_tables
      clear_unwanted_tables
      load_indexes_and_foreign_keys
      clear_columns
    end

    def longest_table
      @longest_table ||= tables.map { |t| t[:name] }.max_by(&:size).size
    end

    def load_tables
      self.tables = connection.tables.sort.map do |table|
        columns = foreign_key_columns(table)
        info    = {}.tap do |hash|
          columns.each { |column| hash[column] = {} }
        end

        {
          name:    table,
          columns: columns,
          info:    info
        }
      end
    end

    def clear_unwanted_tables
      tables.reject! { |table| table[:columns].empty? }
    end

    def load_indexes_and_foreign_keys
      tables.each do |table|
        indexes      = connection.indexes(table[:name])
        foreign_keys = connection.foreign_keys(table[:name])
        columns      = connection.columns(table[:name])

        table[:columns].each do |column|
          hash = table[:info][column]
          find_index(column, hash, indexes)
          find_foreign_key(column, hash, foreign_keys)
          find_null_column(column, hash, columns)
        end
      end
    end

    def find_index(column, hash, indexes)
      hash[:index] = indexes.find do |index|
        index.columns.include?(column)
      end
    end

    def find_foreign_key(column, hash, foreign_keys)
      hash[:foreign_key] = foreign_keys.find do |foreign_key|
        foreign_key.column == column
      end
    end

    def find_null_column(column, hash, columns)
      hash[:null] = columns.find { |c| c.name == column }.null
    end

    def clear_columns
      tables.each { |table| table.delete(:columns) }
    end

    def foreign_key_columns(table)
      connection.columns(table).map(&:name).select do |column|
        column.ends_with?('_id' || column.ends_with?('_type'))
      end
    end
  end
end