Tests

In order to ensure models are correct, Quary provides testing tools that allow you to build confidence in your models. Testing provides assurance that your models are correct and that they will continue to be correct as you make changes. Although testing may initially feel like a burden, over time the confidence far outweighs the cost of writing tests.

Running tests

CLI

Testing in the CLI can be done by using the following command.

quary test

Visual Studio Code Extension

Testing in the Visual Studio Code extension can be done running the QUARY: Test command in the Command Palette (Ctrl+Shift+P/Cmd+Shift+P).

Writing tests

Standard column tests

In addition to writing some SQL tests, Quary also provides the facility to write tests in project files for some standard tests that apply to columns in the model.

Not null

Asserting id in products is not null can be done as following:

models:
  - name: products
    columns:
      - name: id
        tests:
          - type: not_null

Unique

Asserting id in products is unique can be done as following:

models:
  - name: products
    columns:
      - name: id
        tests:
          - type: unique

Relationship

Asserting id in products is a foreign key to product_catalog can be done as following:

models:
  - name: products
    columns:
      - name: id
        tests:
          - type: relationship
            info:
              model: product_catalog
              column: product_id

Accepted Values

Asserting type in products is in a particular set of accepted values can be done as following:

models:
  - name: products
    columns:
      - name: inventory_status
        tests:
          - type: accepted_values
            info:
              values: in-stock,sold-out

Less Than

Asserting price_in_usd in products is less than 100 can be done as following:

models:
  - name: products
    columns:
      - name: price_in_usd
        tests:
          - type: lt
            info:
              value: 100

Less Than Or Equal

Asserting price_in_usd in products is less than or equal to 100 can be done as following:

models:
  - name: products
    columns:
      - name: price_in_usd
        tests:
          - type: lte
            info:
              value: 100

Greater Than

Asserting price_in_usd in products is greater than 100 can be done as following:

models:
  - name: products
    columns:
      - name: price_in_usd
        tests:
          - type: gt
            info:
              value: 100

Greater Than Or Equal

Asserting price_in_usd in products is greater than or equal to 100 can be done as following:

models:
  - name: products
    columns:
      - name: price_in_usd
        tests:
          - type: gte
            info:
              value: 100

Standard model tests

In addition to writing some tests that apply to columns, Quary also provides the facility to write tests in project files for some standard tests that apply to models.

Multi column unique

Asserting that name and price_in_usd in products is unique can be done as following:

models:
  - name: products
    tests:
      - type: multi_column_unique
        info:
          columns: name,price_in_usd

SQL tests

Tests can be added to the tests directory. The tests are written in the SQL language and are assertions that if correct, return an empty result set. For example, suppose we have a list of products that looks like following:

The schema is represented by the following SQL:

CREATE TABLE products
(
    id           INTEGER   NOT NULL PRIMARY KEY,
    time_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    name         VARCHAR(255),
    price_in_usd DOUBLE PRECISION
);

A simple assertion that we could foresee is that we don't expect any prices to be negative. So we could write a test that looks as following:

SELECT * FROM products WHERE price_in_usd < 0;

If we save this file as tests/price_in_usd.sql, this will check that no prices are below 0.

Note the direction of the inequality sign. Your assertions should return incorrect values, in the case where all is right the assertion should return an empty result set.