TinyTDS Testing with Rspec Mocks in Rails

Edu Depetris

- Jun 23, 2024
  • R Spec
  • Tiny Tds
  • Microsoft Sql
  • Testing
  • Ruby On Rails
In our Ruby on Rails project, we are using RSpec as the test framework and facing the challenge of connecting to external Microsoft SQL Servers to read and write information. For this connection, we’re using the TinyTDS gem, which leverages FreeTDS’s DB-Library.

The TinyTDS API is simple and consists of these classes:
  • TinyTds::Client - Your connection to the database.
  • TinyTds::Result - Returned from issuing an #execute on the connection. It includes Enumerable.
  • TinyTds::Error - A wrapper for all FreeTDS exceptions.

Here’s an example of how this gem works:

client = TinyTds::Client.new(username: 'sa', password: 'secret', host: 'mydb.host.net')

# TinyTds::Result
results = client.execute("SELECT c.CustomerID FROM SalesLT.Customer")  

results.each do |row|  
  puts row  
end

Now, let’s consider a hypothetical service that we want to test. This service connects to a Microsoft SQL server via TinyTDS and then performs an action with the result. In production, the service will use a TinyTDS wrapper.

# app/services/tiny_tds_example.rb

class TinyTdsExample
  def call
    client = TinyTds::Client.new(username: 'sa', password: 'secret', host: 'mydb.host.net')

    # TinyTds::Result
    results = client.execute("SELECT c.CustomerID FROM SalesLT.Customer")

    results.each do |row|
	  create_customer(row)
    end
  end
end

Our goal here is to stub the TinyTds::Result and validate the queries that we send to the server.

Let’s take inspiration from the WebMock gem on how stubbing will look: 

Here’s a simple example of how WebMock works:

stub_request(:post, "www.example.com/api/search").
  with(body: "abc", headers: { 'Content-Length' => 3 }).
  to_return({data: "abc"})

uri = URI.parse("http://www.example.com/")
req = Net::HTTP::Post.new(uri.path)
req['Content-Length'] = 3

res = Net::HTTP.start(uri.host, uri.port) do |http|
  http.request(req, "abc")
end    # ===> Success

We’ll create a similar method to stub_request that allows us to take an SQL query and then return the result that we want from the Microsoft SQL server. Our method will look like this:

stub_db_call(query:, response:)

Let’s create the structure of the test with RSpec:

require "rails_helper"

RSpec.describe TinyTdsExample, type: :service do

  let(:service) { described_class.new }
  
  describe "#call" do
    it "do something" do
			
	  # Our mock here

      expect { service.call }.to change { Customer.count }.by(2)
    end
  end
end

Let’s experiment with Rspec Mocks

sql_query = ""
response = []

fake_client = instance_double(TinyTds::Client)
fake_result = instance_double(TinyTds::Result)

allow(TinyTds::Client).to receive(:new).and_return(fake_client)

# Here we validate the query
expect(fake_client).to receive(:execute).with(query).and_return(fake_result)

# Here we stub the response
allow(fake_result).to receive(:each).and_return(response)

This works! 

Let’s take one more step and move this into a method that we can reuse:

# spec/support/tiny_tds_db_stub.rb

module TinyTdsDbStub
  def stub_db_call(query:, response:)
    fake_client = instance_double(TinyTds::Client)
    fake_result = instance_double(TinyTds::Result)

    allow(TinyTds::Client).to receive(:new).and_return(fake_client)
    expect(fake_client).to receive(:execute).with(query).and_return(fake_result)

    allow(fake_result).to receive(:each).and_return(response).once
  end
end

Now let’s use this new method and clean up the test file a bit:

require "rails_helper"

RSpec.describe TinyTdsExample, type: :service do

  let(:service) { described_class.new }
  
  describe "#call" do
    it "do something" do
      sql_query = %{
        SELECT c.CustomerID FROM SalesLT.Customer
      }.squish

      response = [
        { CustomerID: 123 },
        { CustomerID: 456 }
      ]
   
      stub_db_call(query: sql_query, response: response)

      expect { service.call }.to change { Customer.count }.by(2)
    end
  end
end

Happy coding!