Mocking Postgresql now() function for testing

Question

I have the following stack

• Node/Express backend
• Postgresql 10 database
• Mocha for testing
• Sinon for mocking

I have written a bunch of end-to-end tests to test all my webservices. The problem is that some of them are time dependent (as in "give me the modified records of the last X seconds").

sinon is pretty good at mocking all the time/dated related stuff in Node, however I have a modified field in my Postgresql tables that is populated with a trigger:

CREATE FUNCTION update_modified_column()
  RETURNS TRIGGER AS $$
BEGIN
  NEW.modified = now();
  RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';

The problem of course is that sinon can't override that now() function.

Any idea on how I could solve this? The problem is not setting a specific date at the start of the test, but advancing time faster than real-time (in one of my tests I want to change some stuff in the database, advance the 'current time' with one day, change some more stuff in the database and do webservice calls to see the result).

I can figure out a few solutions myself, but they all involve changing the application code and making it less elegant. I don't think your application code should be impacted by the fact that you want to test it.

Answer 1

Here's an idea: Create your own mock_now() with mock_dates table:

create table mock_dates (
    id serial PRIMARY KEY,
    mock_date timestamptz not null
);

create or replace function mock_now()
    returns timestamptz
    as $$
    declare
        RET timestamptz;
    begin
        -- Delete first added date and assign it to RET
        delete from mock_dates where id in (
            select id from mock_dates order by id asc limit 1
        )
        returning mock_dates.mock_date into RET;

        -- If no deletion happened just return the current timestamp
        if RET is null then
            return now();
        end if;

        -- Otherwise return the mocked date
        return RET;
    end;
$$
language plpgsql;

And insert some mocked dates

insert into mock_dates (mock_date) values ('2001-03-11 02:34:00'::timestamptz);
insert into mock_dates (mock_date) values ('2002-05-22 01:49:00'::timestamptz);

and use mock_now() instead of now(). It will return the timestamps inserted to the mock_dates table once (first in first out).

When the table is empty it will work like the default now().

Just ensure the mock_dates table is empty in production 😁

Or you could even define a different function for production which does not even try to read the mock_dates table.

Answer 2

I found this very neat gist which provides a fake NOW() function that lives in a separate schema. You load it into your test database and then modify the search path of each testing session to search override before pg_catalog. Two functions freeze_time and unfreeze_time are provided to enable and disable frozen time.