Aqua Data Studio works with procedural language PostgreSQL pgTAP, PostgreSQL pgUnit, and SQLServer tSQLt. Giving users the functionality to execute tests and report their results.

This tool offers users the ease of running and writing unit tests for SQLServer and PostgreSQL.

The Unit Test Framework should be installed on the DB server.

PostgreSQL pgTAP

pgTAP is a unit testing framework for PostgreSQL written in PL/pgSQL and PL/SQL. It incorporates an exhaustive collection of assertion functions. Aqua Data Studio v22.0 supports the Unit Testing Framework, where users can collect all the tests in the database functions and run them together. All the work will be done by the Run test function, which finds and runs test functions in individual transactions, showing test results in grid/tabular format irrespective of the unit test framework used to run the tests.

To add pgTAP on PostgreSQL server:

CREATE EXTENSION IF NOT EXISTS pgtap WITH VERSION '1.1.0' CASCADE;

For Windows - if you get an error of missing files while running the query above, please follow the steps from this page.

Testing a hypothetical users table:

CREATE OR REPLACE FUNCTION setup_insert(
) RETURNS SETOF TEXT AS $$
BEGIN
    RETURN NEXT is( MAX(nick), NULL, 'Should have no users') FROM users;
    INSERT INTO users (nick) VALUES ('theory');
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION test_user(
) RETURNS SETOF TEXT AS $$
   SELECT is( nick, 'theory', 'Should have nick') FROM users;
END;

Once you define your unit testing functions, you can run tests at any time by using the runtests() functions:

SELECT * FROM runtests(test_user);
Each test function will run within its own transaction and roll back when the function completes.

pgTAP unit test case example:

CREATE OR REPLACE FUNCTION "public"."pgTap_sampletestclass_sampletestcase"

 
RETURNS SETOF TEXT LANGUAGE plpgsql AS $$

BEGIN

   RETURN NEXT has_table( 'domains' );

   RETURN NEXT has_table( 'stuff' );

   RETURN NEXT has_table( 'sources' );

   RETURN NEXT has_table( 'domain_stuff' );

END;

$$;

PostgreSQL pgUnit

PostgreSQL pgUnit allows users to run unit tests as stored procedures, based on the grouping of tests, setup, teardowns, and pre/postconditions. pgUnit supports setup and teardown functions.

Each unit test should have a "test_case_" prefix to be identified as a unit test.

List of prefixes for all types:

The underscore ( _ ) character is used to separate the test name into a hierarchical structure to share the setup, teardown, or the preconditions between several unit tests.

Use the "test_run_all" stores procedure to run the entire test suite.

select * from test_run_all();

Pick one or an entire group of tests based on their prefix using the "test_run_suite" stored procedure.

select * from test_run_suite('finance');
The statement will pick up all unit tests starting with the "test_case_finance" prefix together with the associated support functions and procedures.

To add pgUnit on PostgreSQL server:

CREATE EXTENSION DBLINK;

To learn more from pgUnit, please click here.

pgUnit test case example:

CREATE OR REPLACE FUNCTION "public"."test_case_sampleclass_sampletestcase" returns       void as $$

declare

 id BIGINT;

begin

 SELECT customer.createUser(1, 100) INTO id;

 perform test_assertNotNull('user not created', id);

 perform test_assertTrue('user id range improper', id >= 10000);

end;
$$ language plpgsql;

SQLServer tSQLt

SQLServer tSQLt works with Aqua Data Studio supported versions of SQLServer. It allows you to implement unit tests in tSQLt without having to use different tools for coding and unit tests. SQLServer tSQLt provides the ease to manage and create unit tests by automatically running within transactions, grouping tests within a schema, the output can be generated in plain text or XML, and it gives the ability to mock tables and views to isolate the code being tested.

To add SQLServer tSQLt

To learn more from tSQLt Unit Test Framework visit the following page.

SQLServer tSQLt unit test case example:

 CREATE or ALTER PROCEDURE [sampleclass].[test sampletestcase]

AS

BEGIN

DECLARE @actual MONEY;

   DECLARE @rate DECIMAL(10,4); SET @rate = 1.2;

   DECLARE @amount MONEY; SET @amount = 2.00;


   SELECT @actual = FinancialApp.ConvertCurrency(@rate, @amount);


   DECLARE @expected MONEY; SET @expected = 2.4;   --(rate * amount)

   EXEC tSQLt.assertEquals @expected, @actual;

 
END;

Aqua Data Studio has made the tool "Manage Unit Tests" easy to use for SQLServer and PostgreSQL database platforms. Users can find the feature under the Tools main application menu or by selecting Tools > Manage Unit Test from the right-click menu in the database Schema browser.

Choose the Database or Server in the connection dialog. In the Manage Unit Tests UI, users can choose which database to work on, SQLServer, or PostgreSQL.

This option is only enabled for SQLServer and PostgreSQL connections.

The Manage Unit Tests workspace offers different options.

In the Framework section, choose if you want to use a pgTap or a pgUnit.

In the Database section, select between the Databases you have available for Unit Testing.

In the search section, users can look for a specific name and Run a Test faster.

Once users have selected which Test Class and Test Case to Run, click on the Run Test icon.

The Results will appear at the bottom of the window. There is a section where users can select if they want to View as a Spreadsheet or to have the Results spreadsheet be sent to an email address. Remember that to use this feature you have to have the mail system enabled. You can configure it under File > Options > Email.