RSpec: How to mock SQL NOW()

Question

I can mock Time.now with a great timecop gem.

Time.now
 => 2018-05-13 18:04:46 +0300

Timecop.travel(Time.parse('2018.03.12, 12:00'))

Time.now
 => 2018-03-12 12:00:04 +0300

TeacherVacation.first.ends_at
Thu, 15 Mar 2018 12:00:00 MSK +03:00

TeacherVacation.where('ends_at > ?', Time.now).count
1

But (obviously) this wouldn't work while using NOW() in a query:

TeacherVacation.where('ends_at > NOW()').count
0

Can I mock NOW() so that it would return the results for a certain time?

Answer 1

Timecop is a great gem! I would recommend using Timecop.freeze instead of traveling for your instance; you want to keep your tests deterministic.

As far as I could find, there doesn't seem to be a way to mock SQL's functions. Some languages like Postgres allow overloading functions, but you would still need a way to interject, and there doesn't seem to be a way to use environment variables in SQL.

A co-worker seemed to be certain you could actually drop system/language functions and make your own, but I was concerned about how to recover them after you do that. Trying to go that route sounds like a pain.

Solutions?

Here are a couple of "solutions" that I've come up with today while fighting this problem. Note: I don't really care for them to be honest, but if it gets tests in place ¯\_(ツ)_/¯ They at least offer a way to get things "working".

Unfortunately there's no snazzy gem to control the time in SQL. I imagine you would need something crazy like a plugin to the DB, a hack, a hook, a man in the middle, a container that you could trick SQL into thinking the system time was something else. None of those hack ideas would surely be portable/platform agnostic unfortunately either.

Apparently there are some ways to set time in a docker container, but that sounds like a painful overhead for local testing, and doesn't fit the granularity of a per-test time to be set.

Another thing to note, for me we're running large complex raw SQL queries, so that's why it's important that when I run the SQL file for a test I can have proper dates, otherwise I would just be doing it through activerecord like you mentioned.

String Interpolation

I ran across this in some large queries that were being ran. This definitely helps if you need to push some environment variables through, and you can inject your own "current_date" if you want. This would help too if you needed to utilize a certain time across multiple queries.

my_query.rb

<<~HEREDOC
  SELECT *
  FROM #{@prefix}.my_table
  WHERE date < #{@current_date} - INTERVAL '5 DAYS'
HEREDOC

sql_runner.rb

class SqlRunner
  def initialize(file_path)
    @file_path = file_path
    @prefix = ENV['table_prefix']
    @current_date = Date.today
  end

  def run
    execute(eval(File.read @file_path))
  end

  private

  def execute(sql)
    ActiveRecord::Base.connection.execute(sql)
  end
end

The Dirty Update

The idea is to update the value from ruby land pushing your "time-copped" time into the database to overwrite the value generated by the SQL DB. You may need to get creative with your update for times, like querying for a time greater than a given time that doesn't target your timecop time that you'll be updating rows to.

The reason I don't care for this method is because it ends up feeling like you're just testing activerecord's functionality since you're not relying on the DB to set values it should be setting. You may have computations in your SQL that you're then recreating in the test to set some value to the right date, and then you're no longer doing the computation in the SQL so then you're not even actually testing it.

large_insert.sql

INSERT INTO some_table (
  name,
  created_on
)
SELECT
  name,
  current_date
FROM projects
JOIN people ON projects.id = people.project_id

insert_spec.rb

describe 'insert_test.sql' do
  ACTUAL_DATE = Date.today
  LARGE_INSERT_SQL = File.read('sql/large_insert.sql')

  before do
    Timecop.freeze Date.new(2018, 10, 28)
  end

  after do
    Timecop.return
  end

  context 'populated same_table' do
    before do
      execute(LARGE_INSERT_SQL)
      mock_current_dates(ACTUAL_DATE)
    end

    it 'has the right date' do
      expect(SomeTable.last.created_on).to eq(Date.parse('2018.10.28')
    end
  end

  def execute(sql_command)
    ActiveRecord::Base.connection.execute(sql_command)
  end

  def mock_current_dates(actual_date)
    rows = SomeTable.where(created_on: actual_date)
    # Use our timecop datetime
    rows.update_all(created_on: Date.today)
  end

Fun Caveat: specs wrap in their own transactions (you can turn that off, but it's a nice feature) so if your SQL has a transaction in it, you'll need to write code to remove it for the specs, or have your runner wrap your code in transactions if you need them. They'll run but then your SQL will kill off the spec transaction and you'll have a bad time. You can create a spec/support to help out with this if you go the route of cleaning up during tests, if I were in a newer project I would go with writing a runner that wraps the queries in transactions if you need them -- even though this isn't evident in the SQL files #abstraction.

Maybe there's something out there that lets you set your system time, but that sounds terrifying modifying your system's actual time.