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

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.

Answer 2

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 3

Here's how to create a mock now() function:

• Use schema "override" to define a function: override.now()
• Use search_path to make Postgres look in the override schema (how to fake a function)
• Abuse a configuration parameter to set the time: 'test.freeze_time' (how to declare a variable

Let's define the function first:

set session test.freeze_time = '';

CREATE OR REPLACE FUNCTION override.now() 
  RETURNS timestamptz IMMUTABLE PARALLEL SAFE AS 
$$
BEGIN
    if current_setting('test.freeze_time') = '' then
        return pg_catalog.now();
    else
        return current_setting('test.freeze_time')::timestamptz;
    end if;
END
$$ language plpgsql;

Now this is how you enable it:

set search_path = override,pg_temp,"$user",public,pg_catalog;
set session test.freeze_time = '2023-03-03 00:00:00Z';

test=# SELECT now();
          now           
------------------------
 2023-03-03 00:00:00+00
(1 row)

We have to mention pg_catalog explicitly because:

If pg_catalog is not in the path then it will be searched before searching any of the path items.

Also, unfortunately, we cannot use pg_temp to define our function because:

It is never searched for function or operator names.

Answer 4

To be honest, DB internal stuff is always hard to test from application code. In my experience, the best thing to do is to just verify the record's state.

So, rather than testing specifically that the now function is called internally, write a test that creates a new record, then verify that record has created and modified fields set. At that point they should equal each other, and probably be within the last second or two, so that's all stuff you can write assertions around.

Then you write another test that changes some value in the record, and write assertions that the modified stamp is different from the created stamp, is more recent, and probably within the last second d or two.