PostgreSQL

You can save results of your tests into a PostgreSQL database.

Introduction

You can easily store results of your tests into a table in PostgreSQL:

PostgreSQL CAT output in a table

You can then easily create nice reports using your favorite reporting platform, such as Power BI or SSRS.

How to Instruct CAT to Save Test Results

CAT saves results to a database you have defined in Data Sources section of your project file. (If you load list of data sources from an external provider, that will also work.)

Data Sources:
- Name: MyDWH
  Provider: Postgres@1
  ConnectionString: "%MY_DWH_CONNECTION_STRING%"
Tests:
- Name: .....   # abbreviated
Ouptut:
- Database: MyDWH # name of an existing data source
  Table: public.cat_test_result

In this case, CAT will insert results of tests into the table of your choice. If the table does not exist, CAT will attempt to create it.

Second option is to use a procedure:

Ouptut:
- Database: MyDWH # name of an existing data source
  Procedure: public.save_cat_test_result

In this case, CAT will use a stored procedure to save the results of tests into a table. Feel free to tweak the procedure to suite your needs.

If the procedure does not exist:

  • CAT will attempt to create the procedure (with the name you specified, public.save_cat_test_result in the example above)
  • It will also create a table with name public.cat_test_result if it does not exist.

Output Test Results into a Table

Do you want to create the table manually? Makes perfect sense. If CAT has only reading permissions on the database, which is highly recommended, it will not be able to create the table or procedure if it does not exist.

Use this script to create the table:

CREATE TABLE public.cat_test_result
(
    test_result_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    execution_guid UUID NOT NULL,
    project VARCHAR(200) NOT NULL,
    full_name VARCHAR(700) NOT NULL,
    test_result VARCHAR(30) NOT NULL,
    started_on TIMESTAMP NOT NULL,
    finished_on TIMESTAMP NOT NULL,
    raw_message TEXT NULL,
    exception TEXT NULL,
    description TEXT NULL,
    first_data_source VARCHAR(200) NOT NULL,
    first_query TEXT NOT NULL,
    second_data_source VARCHAR(200) NULL,
    second_query TEXT NULL,
    expectation VARCHAR(50) NOT NULL,
    key VARCHAR(200) NULL,
    metadata VARCHAR(300) NULL,
    tags VARCHAR(1000) NULL,
    suite VARCHAR(200) NULL,
    test_case VARCHAR(200) NULL,
    "order" INT NULL,
    "name" VARCHAR(300) NULL,
    timeout INT NULL,
    maximum_errors_logged INT NULL,
    thread_number INT NOT NULL,
    message TEXT NULL,
    number_of_errors INT NULL,
    log_number_of_errors BOOLEAN NULL
);

Don’t forget CAT is highly configurable, the name of the table can be anything, it also can be in different schema. Change it in your script, change it in the Outputs section of the project file. CAT process must have INSERT permission on the table.

The structure of the table is not fixed. CAT checks the structure of the table you provided in the project file and adapts to it. That means - if you are not interested into some columns, just drop them. Do you want only three columns, e.g. full_name, test_result and started_on? No problem, CAT will happily save the results into your table with those three columns only. See the complete list of properties supported on the output.

You can add additional custom columns to the table, but they must be either nullable or must have defined a default value. To avoid conflict with any new future columns, use some prefix or suffix for your column names (name of your company, underscore, …).

Use Procedure Instead

You might prefer a stored procedure. It allows you to perform additional actions, such as enrich your own logs, run other processes when a specific test(s) fail, …

Here is the script. Again, you can change name of the procedure, name of the table it uses, omit some parameters you don’t need, use your naming conventions, …

create procedure {procedureName}
(
    c_execution_guid UUID,
    c_project VARCHAR(200),
    c_full_name VARCHAR(700),
    c_test_result VARCHAR(30),
    c_started_on TIMESTAMP,
    c_finished_on TIMESTAMP,
    c_raw_message TEXT,
    c_exception TEXT,
    c_description TEXT,
    c_first_data_source VARCHAR(200),
    c_first_query TEXT,
    c_second_data_source VARCHAR(200),
    c_second_query TEXT,
    c_expectation VARCHAR(50),
    c_key VARCHAR(200),
    c_metadata VARCHAR(300),
    c_tags VARCHAR(1000),
    c_suite VARCHAR(200),
    c_test_case VARCHAR(200),
    c_order INT,
    c_name VARCHAR(300),
    c_timeout INT,
    c_maximum_errors_logged INT,
    c_thread_number INT,
    c_message TEXT,
    c_number_of_errors INT,
    c_log_number_of_errors BOOLEAN
)
language plpgsql
as
$$
begin
    insert into "public"."cat_test_result"
    (
        execution_guid, project, full_name, test_result, started_on, finished_on, raw_message, exception, description,
        first_data_source, first_query, second_data_source, second_query, expectation,
        "key", "metadata", tags, suite, test_case, "order", "name",
        "timeout", maximum_errors_logged, thread_number, "message", number_of_errors, log_number_of_errors
    )
    values (
        c_execution_guid, c_project, c_full_name, c_test_result, c_started_on, c_finished_on, c_raw_message, c_exception, c_description,
        c_first_data_source, c_first_query, c_second_data_source, c_second_query, c_expectation,
        c_key, c_metadata, c_tags, c_suite, c_test_case, c_order, c_name,
        c_timeout, c_maximum_errors_logged, c_thread_number, c_message, c_number_of_errors, c_log_number_of_errors
    );
end
$$;

The list of parameters of the procedure is NOT fixed. CAT again checks the metadata of the procedure before it executes tests. CAT will call the procedure with only the parameters you defined.

How Does it Work?

Before CAT starts executing tests, it “prepares” a command for saving test results - one for each thread. It checks the metadata of table/procedure you specified in the project file and prepares INSERT or CALL command with only columns/parameters that are needed. If the table or procedure does not exist, CAT will attempt to create it (and will fail if it does not have permissions).

Immediately after a test is executed, CAT attempts to either insert a row in the results table (if you configured with Table: tablename) or execute a procedure (if you configured Procedure: ProcedureName).

This feature works even if you run tests in parallel - each thread keeps its own connection to the database for storing outputs.

← Oracle@1
Postgres@1 →