Database tips

belongs_to_polymorphic.rb

module AngrySupport::BelongsToPolymorphic
  def belongs_to_polymorphic(name, allowed_classes:, **options)
    belongs_to name, polymorphic: true, **options

    validates "#{name}_type", inclusion: { in: allowed_classes.map(&:name), allow_nil: !!options[:optional] }

    define_singleton_method(:"#{name}_types") { allowed_classes }

    define_singleton_method(:"with_#{name}") do |type|
      type = case type
             when Class then type.name
             when String then type
             else type.class.name
             end
      where(arel_table[:"#{name}_type"].eq(type))
    end

    allowed_classes.each do |model|
      scope "with_#{name}_#{model.name.underscore.tr('/', '_')}", lambda {
        where(arel_table[:"#{name}_type"].eq(model.name))
      }
    end
  end
end

handle_job_database_errors.rb

module AngrySupport::Handle::Job::DatabaseErrors
  extend ActiveSupport::Concern

  ERRORS = [
    ActiveRecord::ConnectionNotEstablished,
    ActiveRecord::ConnectionTimeoutError,
    ActiveRecord::QueryCanceled,
    PG::InFailedSqlTransaction,
    PG::LockNotAvailable,
    PG::TRDeadlockDetected,
  ].freeze

  ERROR_STATEMENTS = [
    'deadlock detected',
    'could not obtain lock',
    'current transaction is aborted',
    'no connection to the server',
  ].freeze

  included do
    retry_on ActiveRecord::Deadlocked

    retry_on(*ERRORS, wait: 1.minute, attempts: 10) do |job, exception|
      ErrorReporting.job_discarded(job, exception)
    end

    rescue_from(ActiveRecord::StatementInvalid) do |exception|
      raise exception unless ERROR_STATEMENTS.any? { exception.message.include?(_1) }

      if retries_count <= 10
        retry_job wait: 5.minutes
      else
        ErrorReporting.job_discarded(job, exception)
      end
    end
  end
end

handle_race_coditions.rb

module AngrySupport::Handle::RaceCondition
  extend self

  def call(max_retries: 2, transaction: false)
    retries ||= max_retries
    if transaction
      ActiveRecord::Base.transaction do # rubocop:disable Style/ExplicitBlockArgument
        yield
      end
    else
      yield
    end
  rescue ActiveRecord::RecordNotUnique, PG::UniqueViolation, PG::InFailedSqlTransaction
    retries -= 1

    raise unless retries.nonzero?

    retry
  rescue ActiveRecord::RecordInvalid => e
    raise unless e.message.include? I18n.t('errors.messages.taken')

    retries -= 1
    raise unless retries.nonzero?

    retry
  end
end

sql.rb

module AngrySupport::Sql
  extend self

  def execute(sql, *args)
    sql = sanitize_sql([sql, *args]) if args.any?
    ActiveRecord::Base.connection.execute(sql)
  end

  def count(sql, **args)
    row = rows(sql, **args).first
    return 0 unless row

    row.first.second || 0
  end

  def sanitize_sql(sql, *args)
    ActiveRecord::Base.sanitize_sql([sql, *args])
  end

  def sanitize_like(query)
    "%#{ActiveRecord::Base.sanitize_sql_like(query.to_s.downcase).tr(' ', '%')}%"
  end

  def quote(value)
    ActiveRecord::Base.connection.quote(value)
  end
end

The whole list of tips is here
https://tips.rstankov.com/p/tips-for-database-design-part-2