With the update to Rails 7.0 `update_colum` is no longer allowed, when a model is in the `readonly?` state. While refactoring this to use SQL queries, I ran into the issue that my code works in the rails console, but not in my rspec tests. I suspect checking out a `connection` from `ActiveRecord::Base.connection_pool` is causing this.

execute_sql_service.md

Ok, I've refactored the service to not use the connection_pool, but to use a new connection instead.

And this also satisfies the rspec tests. I still don't understand what the issue was, but this is a working solution,
and as far as I see it also prevents us from blocking more and more connections, but instead closing them reliably.

This is the new code:

class ExecuteSqlService
  class << self
    def call(sql) # rubocop:disable Rails/Delegate
      new.call(sql)
    end
  end

  def call(sql)
    log(sql)

    @connection = ActiveRecord::Base.connection
    @connection.exec_query(sql)

    # was:
    # connection.exec_query(sql)
  ensure
    @connection.close

    # was:
    # close_connection
  end

execute_sql_service.rb

# frozen_string_literal: true

# Service that calls `connection.exec_query(sql)`
# with any given (SQL) string.
# Takes the `connection` out of `ActiveRecord::Base.connection_pool`
# and checks it back in after running it.

class ExecuteSqlService
  class << self
    def call(sql) # rubocop:disable Rails/Delegate
      new.call(sql)
    end
  end

  def call(sql)
    log(sql)
    connection.exec_query(sql)
  ensure
    close_connection
  end

  private

  def connection_pool
    @connection_pool ||= ActiveRecord::Base.connection_pool
  end

  def connection
    @connection ||= connection_pool.checkout
  end

  def close_connection
    connection_pool.checkin connection
  end

  def log(sql)
    Rails.logger.info("ExecuteSqlService called with: #{sql}")
  end
end

execute_sql_service_spec.rb

# frozen_string_literal: true

# These specs pass. As does calling the service in the rails console.

RSpec.describe ExecuteSqlService do
  subject(:service_call) { described_class.call("SELECT count(*) FROM users") }

  describe "execute SQL query" do
    it "runs an SQL query" do
      result = service_call

      expect(result).to be_an(ActiveRecord::Result)
      expect(result.rows.first).to eq([0])
    end

    it "runs an SQL query and returns the connection to the pool" do
      stat_before = ActiveRecord::Base.connection_pool.stat
      connection_count = stat_before[:connections]

      service_call

      stat_after = ActiveRecord::Base.connection_pool.stat
      expect(stat_after[:connections]).to eq(connection_count)
    end
  end
end

order.rb

# frozen_string_literal: true

# Obviously the Order has much more code, but this should be the relevant method.
# Under Rails 6.1 it was possible to call `update_column(:sent_to_rums_at, Time.current)`
# on closed orders. But with the update to Rails 7.0 they fixed this, so the `readonly`
# prevents `update_column` or `touch` changes.
#
# Therefore the idea to execute some SQL to emulate the implemented behavior.

class Order < ApplicationRecord
  def readonly?
    status_was == "closed"
  end
end

touch_sql_service.rb

# frozen_string_literal: true

# Service that emulates ActiveRecord's `touch` method to set a timestamp on the columns of a record.
# Builds the SQL string and then calls `ExecuteSqlService` with it.

class TouchSqlService
  class << self
    # pass either id or uuid
    def call(table:, column:, id: nil, uuid: nil)
      sql = "UPDATE #{table} SET #{column} = '#{Time.current.utc}'"

      uuid_clause = "uuid = '#{uuid}'" if uuid.present?
      id_clause = "id = '#{id}'" if id.present?
      where_clause = [uuid_clause, id_clause].compact.join(" AND ")

      raise ArgumentError.new("id or uuid must be present") if where_clause.blank?

      ExecuteSqlService.call("#{sql} WHERE #{where_clause}")
    end
  end
end

touch_sql_service_spec.rb

# frozen_string_literal: true

# See comments below which specs pass or not.
#
# When running the code in the rails console, it works, e.g.:
# `TouchSqlService.call(table: :orders, column: :sent_to_rums_at, uuid: order.uuid)`
# `ExecuteSqlService.call("UPDATE orders set sent_to_rums_at = '#{Time.current.utc}' WHERE uuid= '#{order.uuid}'")`

RSpec.describe TouchSqlService do
  include ActiveSupport::Testing::TimeHelpers

  subject(:service_call) { described_class.call(table: :orders, column: :sent_to_rums_at, uuid: order_uuid) }

  describe "touch closed order" do
    context "with valid arguments" do
      let(:order_uuid) { order_form.uuid }
      let(:order_form) { create(:emptying_order_form, sent_to_rums_at: nil, status: "closed") }

      # passing spec
      #
      it "calls ExecuteSqlService" do
        freeze_time do
          sql = "UPDATE orders SET sent_to_rums_at = '#{Time.current.utc}' WHERE uuid = '#{order_uuid}'"

          expect(ExecuteSqlService).to receive(:call).with(sql)

          service_call
        end
      end

      # breaking spec - no change
      #
      it "updates the sent_to_rums_at timestamp in a block" do
        freeze_time do
          expect { service_call; order_form.reload }.
            to change(order_form, :sent_to_rums_at).from(nil).to(Time.current)
        end
      end

      # breaking spec - no change
      #
      it "updates the sent_to_rums_at timestamp" do
        freeze_time do
          order_form

          service_call

          order = Order.find_by!(uuid: order_form.uuid)

          puts order.inspect

          expect(order.sent_to_rums_at).to eq(Time.current.utc)
        end
      end
    end

    context "when neither id nor uuid are given" do
      let(:order_uuid) { nil }

      # passing spec
      #
      it "throws an error" do
        expect { service_call }.to raise_error(ArgumentError)
      end
    end
  end
end