The previous article  – An introduction into unit testing of your database – showed the basic concepts of unit testing, how a unit test is written, the wording and how unit tests behave. In this second part, it’s explained where unit tests are executed, how they should be stored and how the development and testing process looks. 

The Development and Testing Process

The development and testing process, in general, is relatively simple. It starts with the developer writing code on his local machine. When everything works locally, and the unit tests are all successful, the changes and new functionalities are committed into version control. 

The version control system then triggers the continuous integration pipeline. The continuous integration system works as a central quality control instance. It deploys the changes in a continuous integration environment and runs the unit tests to assure they are working. The continuous integration system can run additional quality assurance jobs like static code analysis that checks for common bugs, security vulnerabilities and if coding guidelines are met. If any quality assurance job in the continuous integration pipeline fails, the developer gets informed and is required to make changes to the code. If everything works, the changes are ready to be deployed further along the deployment pipeline, E.g. in a test or integration environment and finally to production. When deployments include database changes, they often are triggered manually. 

The development and testing process as described above.

The important thing is that all unit tests and all test data belong into version control! One version control repository should contain all the application code, its tests and test data, configuration, setup scripts, documentation and everything else that is required to build and test the application again from scratch. 

Automate the execution of your unit tests with a continuous integration pipeline!

Versioning and Management of Test Data and Test Packages

Having your code and unit tests in the same version control repository makes many things more manageable. However, never deploy unit tests or test data to production! It’s crucial to separate production code from your test data clearly to be able to deploy them individually. 

To achieve a clear separation between test code and production code, a separation of the source code is also necessary in the source code repository. One way to store test code and test data separate from production code is shown below in the sample project structure.

Sample Project Structure for a Database Project

But you somehow need to know which test packages are testing which production packages and functions. One way to achieve this is to use similar naming of test packages and prod packages. It makes navigation easier and works as self-speaking documentation of your tests. The main rule to follow while organizing your unit tests is to have one test package per production package you are testing.

Summary

The setup and organization of unit tests are pretty straight forward if some rules are followed. It’s vital to keep your test data where your application code is. But also a clear separation from production logic to test logic and test logic to test data is required to keep your tests organized and your continuous integration and deployment straightforward. The data collection, maintenance, automation and data management is a developers job.

References

http://utplsql.org/

http://utplsql.org/utPLSQL/latest/