DuckDB

Results into a table in a DuckDB file — a history you can query with SQL, with no database server. The table, where the file may live, and the rules for who has it open.

When to use it #

When results should be kept across runs and there is no database server to keep them in. The history is one file, written by every CAT tool (CAT Studio, CAT CLI, the PowerShell module, the Python module), and read with DuckDB SQL from CAT itself (a DuckDB@1 data source in a reporting project), from Python or R, from the DuckDB command line, from anything that opens a DuckDB file. Exporting it to Parquet is one statement: COPY (SELECT * FROM main.cat_test_result) TO 'results.parquet' (FORMAT PARQUET) — it works on a file opened read-only.

See Database outputs for what every database output does: one row per test as it finishes, columns matched by name, the table created when it does not exist. This page has what is specific to DuckDB: the table, where the file may live and who may have it open. The target is a DuckDB@1 data source. Table only — DuckDB has no stored procedures, so Procedure is refused.

How to set it #

The usual setup keeps the results next to the data under test. The tested data source is opened read-only, as the DuckDB@1 page recommends; the output names a second data source on the same file, without access_mode:

• Properties
• YAML

Data sources:
- Name: Warehouse
  Provider: DuckDB@1
  Technology: DuckDB
  Connection string: DataSource=%DATA_DIR%\warehouse.duckdb;access_mode=READ_ONLY

- Name: Results
  Provider: DuckDB@1
  Technology: DuckDB
  Connection string: DataSource=%DATA_DIR%\warehouse.duckdb

Output:
- Database: Results
  Table: main.cat_test_result

Before the first run, three things to know:

• The data source the output writes through must not carry access_mode=READ_ONLY. In CAT Studio’s DuckDB form switch Open read-only off for it. A read-only data source fails before any test runs, with a message that says so.
• One data source for both works too — the tests then run read-write as well, so a mistyped path creates an empty file instead of failing, and a test query could change the data.
• A file of its own is the other choice: DataSource=%RESULTS_DIR%\cat-results.duckdb. CAT creates the file when it does not exist, but not the folder. A relative path resolves against the working directory of the process that runs CAT, not against the project file — use an absolute path or an environment variable.

CAT creates the table, and the sequence behind its test_result_id, when they do not exist. The schema must exist; main always does.

What it looks like #

One row per test, the columns of your table — see the rows of the sample run on Database outputs.

Details #

Table #

Any subset of these columns works; custom columns must be nullable or have a default. The table may live in any schema that exists — name it in Table; a third part names the database, which for a file is its name without the extension (results.main.cat_test_result for results.duckdb). The sequence gives test_result_id its values: keep both the table and the sequence, or drop both.

CREATE SEQUENCE IF NOT EXISTS "main"."cat_test_result_seq";
CREATE TABLE IF NOT EXISTS "main"."cat_test_result"
(
    "test_result_id" INTEGER PRIMARY KEY DEFAULT nextval('"main"."cat_test_result_seq"'),
    "execution_guid" UUID NOT NULL,
    "project" VARCHAR NOT NULL,
    "full_name" VARCHAR NOT NULL,
    "test_result" VARCHAR NOT NULL,
    "started_on" TIMESTAMP NOT NULL,
    "finished_on" TIMESTAMP NOT NULL,
    "raw_message" VARCHAR,
    "exception" VARCHAR,
    "description" VARCHAR,
    "first_data_source" VARCHAR NOT NULL,
    "first_query" VARCHAR NOT NULL,
    "second_data_source" VARCHAR,
    "second_query" VARCHAR,
    "expectation" VARCHAR NOT NULL,
    "key" VARCHAR,
    "metadata" VARCHAR,
    "tags" VARCHAR,
    "suite" VARCHAR,
    "test_case" VARCHAR,
    "order" INTEGER,
    "name" VARCHAR,
    "timeout" INTEGER,
    "maximum_errors_logged" INTEGER,
    "thread_number" INTEGER NOT NULL,
    "message" VARCHAR,
    "number_of_errors" INTEGER,
    "log_number_of_errors" BOOLEAN
);

CAT runs this script at the start of every run, whether the table exists or not: IF NOT EXISTS makes it a no-op on a database that may be written. That is how a read-only database is caught before the first test.

Where the results live #

In the tested database file, or in a file of their own. As with every other database output, the warehouse is the usual target, and here it works with two data sources on one file, as in the example above. CAT opens the output’s connections before the first test and keeps them until the run ends, so every test connection on that file joins them, read-write, and reads normally. The two data sources may spell the path differently — the same file, however the path is written. For the length of a run the tested data source’s READ_ONLY is therefore not in force; between runs it is.

One CAT process has the file at a time. DuckDB locks the file: a process that writes excludes every other process, even one that only reads. Studio running a project while a pipeline writes the same results file, or two pipelines at once, fail — the second one before any test, when its output opens the file:

Could not set lock on file "…": Conflicting lock is held in <program> (PID n) by user <name>

Threads of one run are fine; they share the process. A results file of its own is what lets two projects, or Studio and a pipeline, test one database at the same time. The pipeline step or notebook that writes the file must be done before CAT runs; see Who else has the file open.

A read-only database is refused. Two causes, and the message names both:

Cannot write test results into the DuckDB database of data source 'Results': it is open read-only. An output needs to write: remove access_mode=READ_ONLY from that data source's connection string, or give the output a data source of its own on the same file without it. If the string is already read-write, something else in this CAT process held the file read-only when the run started - a query on it; run again.

Either the connection string says access_mode=READ_ONLY, or something else in the same CAT process — a query on that file — held it read-only at the very moment the run started. The second is rare; run again.

A killed run leaves a .wal file next to the database. The next open replays it, and the rows of the tests that finished are there. After a normal run there is no .wal.

Reading the history #

The writer must have finished: one process at a time has the file.

With the DuckDB command line:

duckdb cat-results.duckdb "SELECT test_result, count(*) FROM main.cat_test_result GROUP BY test_result"

With Python:

import duckdb

con = duckdb.connect('cat-results.duckdb', read_only=True)
print(con.sql("SELECT test_result, count(*) FROM main.cat_test_result GROUP BY test_result"))

With a second CAT project: a DuckDB@1 data source on the file with access_mode=READ_ONLY, and the queries of Track test results over time as the tests’ queries or a report’s source.

Specifics #

• Each thread of the run holds its own connection, and CAT releases all of them when the run ends, so the file is free the moment CAT is done.
• One row per test, written as the test finishes — about 10 ms a row.
• started_on and finished_on are a TIMESTAMP, the clock of the machine that ran the tests; execution_guid is a UUID; the texts are VARCHAR.
• An in-memory database (DataSource=:memory:) is not a results store: the table is created in a private database that is gone with the connection, and the first insert fails with DuckDB’s “table does not exist”.

Related #

• Database outputs — the shared mechanics.
• Properties — the column names CAT recognizes.
• DuckDB@1 — the data source.
• Track test results over time — the queries behind a trend, a dashboard and an alert.