5. Testing

5.1. Basic Test (sql)

Tesseract generates tests from YAML files. This makes it very easy to read, maintain and organise.

These files can be found in the tests directory. The most simple file may look like:

tests:
  my_test:
    sql: SELECT 1 + 2
    result:
    - {"col1": 3}

In the example above we have created one test called my_test that will run the SQL statement and confirm that the server returns one row containing that exact data.

5.1.1. Result (result)

Specify the expected output of the last sql statement. The data returned from the server must be exactly the same (including order) as the result items.

5.1.2. Result in Any Order (result-unordered)

If the order in which the records isn’t important or is unpredictable you can use result-unordered instead of result.

tests:
  two_columns:
    data: table1
    sql: "SELECT foo, foo * 2 FROM table1"
    result-unordered:
    - {"foo": 123, "col2": 246}
    - {"foo": 124, "col2": 248}
    - {"foo": 125, "col2": 250}

5.1.3. Parser (as)

All tests that contain a sql attribute will be run through the parser and the statement will be rendered. This rendered statement is expected to be the same as this sql value. If you expect a different rendered string then you specify what the result should be through as:

tests:
  alternate_operator:
    sql: 'SELECT null != 123'
    as: 'SELECT null <> 123'
    result:
    - {"col1": null}

5.1.4. Ignoring the Parser (parse)

Sometimes the SQL rendered from the SQL provided is not predictable, so we have to disable the parser test:

tests:
  json_object_with_two_elements:
    sql: 'SELECT {"foo": "bar", "baz": 123}'
    parse: false
    result:
    - {"col1": {"foo": "bar", "baz": 123}}

5.1.5. Commenting (comment)

Test can have an optional comment, this is preferred over using YAML inline comments so that comments can be injected is creating reports in the future.

tests:
  my_test:
    comment: Test everything!
    sql: 'SELECT 123'

This is also used at the root of the document to comment on the entire test suite like:

comment: |
  This file is responsible for stuff.

tests:
  my_test:
    comment: Test everything!
    sql: 'SELECT 123'

5.1.6. Tags (tags)

tags can be set at the file level which means that all tests in the file have the same tag:

comment: |
  All the tests are for 'foo'.

tags: foo

tests:
  ...

If you need multiple tags you can separate them with spaces:

tags: bar foo

It is not required, but it is good practice to keep the tags sorted alphabetically.

Tags are defined in tags.yml. While also not required for tags to be defined here is is good practice to leave a description.

5.1.7. Repeating Tests (repeat)

If a test lacks some predictability or you need to test the outcome multiple times for another reason you can use the repeat. This will still generate one test but it will loop through the repeat many times.

tests:
  my_test:
    sql: 'SELECT 123'
    repeat: 20
    result:
    - {"col1": 123}

5.2. Failures

5.2.1. Expecting Errors (error)

Use the error to test for an expected error:

tests:
  incompatible_types:
    sql: SELECT false AND 3.5
    error: No such operator boolean AND number.

Errors will be raised by the parser or by executing the SQL statement(s).

5.2.2. Expecting Warnings (warning)

You can assert one or more warnings are raised:

tests:
  json_object_duplicate_item_raises_warning:
    sql: 'SELECT {"foo": "bar", "foo": "baz"}'
    as: 'SELECT {"foo": "baz"}'
    warning: Duplicate key "foo", using last value.

  multiple_warnings_can_be_raised:
    sql: 'SELECT {"foo": "bar", "foo": "baz", "foo": "bax"}'
    as: 'SELECT {"foo": "bax"}'
    warning:
    - Duplicate key "foo", using last value.
    - Duplicate key "foo", using last value.

5.3. Data Sets (data)

It is common that you will want to test against an existing data fixture. Instead of inserting the data you need manually you can use fixtures:

data:
  table1:
  - {"foo": 125}
  - {"foo": 124}
  - {"foo": 123}

tests:
  where:
    data: table1
    sql: SELECT * FROM table1 WHERE foo = 124
    result:
    - {"foo": 124}

5.3.1. Randomizing Data (data-randomized)

For some tests you may want to randomize the order in which the records are loaded in. It is often used in conjunction with repeat.

data:
  table1:
  - {"foo": 125}
  - {"foo": 124}
  - {"foo": 123}

5.4. Verifying Notifications

When under test all notifications throughout the entire test case will be recorded. They can be asserted after all the SQL is executed. To test for a single notification:

tests:
  notification_will_be_fired_for_insert:
    sql:
    - CREATE NOTIFICATION foo ON some_table
    - 'INSERT INTO some_table {"a": "b"}'
    notification:
      to: foo
      with: {"a": "b"}

If you need to assert more than one notification:

tests:
  multiple_notifications_can_be_fired_from_a_single_select:
    sql:
    - CREATE NOTIFICATION foo1 ON some_table WHERE a = "b"
    - CREATE NOTIFICATION foo2 ON some_table WHERE a = "b"
    - 'INSERT INTO some_table {"a": "b"}'
    notification:
      - to: foo1
        with: {"a": "b"}
      - to: foo2
        with: {"a": "b"}

Or validate that no notifications have been fired:

tests:
  notification_will_respect_where_clause:
    sql:
    - CREATE NOTIFICATION foo ON some_table WHERE a = "c"
    - 'INSERT INTO some_table {"a": "b"}'
    notification: []