Rspec: How to test ActiveRecord::Base.connection.execute

Question

I'm using raw/bare-metal sql inserts to increase write performance on my service. I have something like this in my module -

insert = "('#{id}', '#{status}', '#{some_time_val}')"
sql_string = "INSERT INTO history ('device_id', 'status', 'time') VALUES #{insert}"
ActiveRecord::Base.connection.execute sql_string

When I write an rspec like below, it tests everything except whether or not the insert went through. So my expectations will never work because of the way rspec, database_cleaner etc do rollbacks and transactions. I tried using

  self.use_transactional_fixtures = false

and

  before(:all) do
    DatabaseCleaner.strategy = nil
  end

but the inserts still don't go through to my test database

describe Worker do
  let (:device1) {FactoryGirl.create(:device)}
  let (:device2) {FactoryGirl.create(:device)}
  let (:device3) {FactoryGirl.create(:device)}
  self.use_transactional_fixtures = false

  before(:all) do
    DatabaseCleaner.strategy = nil
  end

  it "does something" do
    devices = [{"status" => "Offline", "time" => "2013-09-17 18:17:17", "id" => device1.id},
                {"status" => "Online", "time" => "2013-09-17 18:18:18", "id" => device2.id}]
    Worker.any_instance.stubs(:devices).returns(devices) ## Not important for this question
    Worker.new.perform

    device1.reload.status.should == "Offline" # FAILS
  end

end

How would I test this? What's a good strategy to test raw sql inserts like this?

Answer 1

There are two schools of thought about how to properly test code: the 'methods' perspective is about ensuring your own correct behavior, and the 'results' perspective favors checking results. I'll provide both perspectives.

Methods:

You did not write ActiveRecord::Base.connection.execute and you are not responsible for it working correctly. Your test, then, can focus on what you passed into the execute method. A matching spec, then, might look something like this.

connection = double("Connection")
expect(ActiveRecord::Base).to receive(:connection) { connection }
expect(connection).to receive(:execute).with("...")
Worker.new.perform

where ... is the SQL that you intend to generate. Verifying the SQL works appropriately might be done somewhere else, or not done at all, depending on your taste. This assures you (and future devs) that the correct instruction has been delivered to your database.

Results:

This approach doesn't find reassurance in what approach is taken, it finds reassurance in outcomes. It is the approach you are taking in the above examples.

You don't show us what the perform method looks like above, so we're not sure if, perhaps, there is a problem with your implementation code, as opposed to the reload mechanism you're using to prompt ActiveRecord to notice your changes. ActiveRecord has a variety of performance enhancements designed to cloud direct binding between the database contents and what you are able to access in your Ruby VM's object graph. You can struggle with trying to override this behavior if you like, but if you're content to use ActiveRecord directly in your implementation, perhaps you are also content to do so in your tests?

d1_status = ActiveRecord::Base.connection.execute("SELECT status from history where id = #{device1.id} limit 1;").values[0][0]
expect(d1_status).to eq("Offline")

I note some troubling inferences here, though. You are editing the history table, but expecting results from what's presumably a Device model. The implementation of the status method then is also relevant to your problem. You may wish to write additional tests for the Device#status method, if you select this approach.