cortze/unit-db-test

Unittest wrapper focused on making DB integrity tests

unit-db-test ·

Is your tool hard to debug? Would it be easier to check it at the DB level?

This module is for you! unit-db-test is a Python tool for conducting database tests. It is a unittest wrapper focussed on making DB integrity tests. As it is an extension over the unittest.TestCase it is easy to configure and easy to integrate with GitHub actions.

Features

• It has an integrated connection to a Postgres Database: easily configurable using the .env file.
• Fully compatible with SQLAlchemy and Pandas: get a pandas.Dataframe out of any SQL query you want to test.
• Unit-test oriented: Check if the pandas.Dataframe output matches the expected values, or catch it when the test fails.

Installation

You can install dbtests using pip:

pip install unit-db-test

Usage

Here's how you can use dbtests in your projects:

1. Create a ./test folder as with any other kind of unittests (it is in fact compatible with other unittests just make there is a connection to a Postgres DB), and create a test-file.

my-tool/
├── my-tool/
│   ├── __init__.py
│   ├── script_1.py
└── tests/
    ├── __init__.py
    └── test_script_1.py

2. Once the script is created, we need to import the dependencies:

# test_script_1.py
# Dependencies
import unittest
from unit_db_test.testcase import DBintegrityTest

3. Create a DBintegrityTest as if it was a unittest.TestCase. It is important to define the path to the .env that keeps the Postgres DB credentials:

class TestDBTestModule(DBintegrityTest):
    db_config_file = '.postgresql.env'

4. Create as many test functions as pleased:

    def test_not_null_items_in_column(self):
        # the query that SHOULDN'T create an assertion
        sql_query = """
        SELECT 
            id
        FROM test_table;        
        """
        df = self.db.get_df_from_sql_query(sql_query)
        self.assertNotNullItemsInColumn(df, 'id')

if __name__ == '__main__':
    unittest.main()

5. Run it as you please:

python -m unittest tests/test_script_1.py

The tests can be run locally or integrated with Github Actions. For more examples, please check:

• Github Action example
• Test example

Custom Assert

The dbtest module contemplates new assert functions over pandas.Dataframe objects. This way the result of a simple query can be easily checked with the standard unittest nomenclature.

The list of current Asserts is the following:

• assertNotNullItemsInColumn(self, df, column)
• assertCustomNullItemsInColumn(self, df, column, target)
• assertNoRows(self, df)
• assertNRows(self, df, target_rows)

There will be more to come (under demand most probably). Feel free to suggest new ones though!

Contributing

If you want to contribute to this project, please follow these guidelines:

• Fork the repository
• Create a new branch
• Make your changes
• Submit a pull request
• Bugs and Issues

If you encounter any bugs or issues, please report them here.

Contact

Author: Mikel Cortes (@cortze)

Feel free to reach out if you have any questions or suggestions!

License

This project is licensed under the MIT License - see the LICENSE file for details.

unit-db-test's Issues

Add support for Clickhouse db

Description

With the heavy migration of GotEth from PSQL to Clickhouse, there is a vast need to update this module to support the Clickhouse driver.

Tasks:

• Add support for Clickhouse DBs
  • add support for Clickhouse drivers
  • Maintain the SQL driver (make the user choose which one or both to use?)
• Add some DB endpoint benchmarks (Clickhouse vs PSQL endpoints) as Clickhouse offers both, a native Clickhouse connection and a SQL-like endpoint

I still need to check further how Clickhouse works, but this should be all.