Welcome to my first post! Today I am going to explain how to introduce unit tests to SQL code, at the repository level. This is really important, because it stops broken SQL from ever reaching your main branch. I have written an open-source solution, which sets up the basic files for you, greatly streamlining the process.
How it works:
Workflow file:
The primary component we care about in this case is the workflow file. If you are not familiar, this is a .yaml file that github uses to perform Actions on source code, when certain github operations are made (i.e. Push). If github finds this file in the foler '.github/workflows/', it will automatically locate it, and perform the actions on github-managed servers. These servers alreadey have Docker installed, so we can easily use containers for our tests. In the case of SQL, this file generate a database container, sets relevant environment variables, and runs the unit tests. If the tests fail, the commit should be rejected.
Unit tests:
You are probably familiar with them, but I'll go over it briefly. A unit test is just a part of CICD that tests atomic elements of your application. This ensures the integrity of each individual piece of functionality in your project.
TestQL:
A config file contains elements of the workflow file and the unit tests, each associated with a primary key of db-engine (postgres, mysql, oracle, etc.). This means that with one command, it will setup all the basic files you need to start writing your test logic.
How:
Clone the TestQL repository (https://github.com/ThugPigeon653/testQL-source) to the location you wish to install TestQL. Optionally, review the source code, for a deeper understanding of how the tool works.
Build the artifact. There are instructions in the readme, but all you have to do is run the LinuxBuild.sh or WindowsBuild.bat as administrator (depending on your OS). This builds the program, and adds it to PATH.
Use the tool in the repositoy containing your SQL. 'testql init --engine '. For a list of currently-available engines, see the readme.
Write your tests! Navigate to the 'test' folder. If you didn't already have one, TestQL will have created it. It will contain a new test file, which is already has database connection setup, as well as dependancies, and an empty test method. This is where you will take over - it's time to write your test logic (this topic is outside the scope of this tutorial). Refer to Unittest, and the python database-connection module in your test script (i.e. psycopg, cx_Oracle, etc.). Next time you push to your repository, the unit tests you have defined will be run - and any commits with failed tests will be rejected.
Keep in mind that this tool currently writes Python tests. Because these are never run on your machine, you do not technically need python to use the tool, but it is highly recommended. If this tool proves useful to anyone, I plan to make it work for more sql and test/scripting languages.
If this is helpful to you, star the repository so that I know to keep working on it. It will always be open-source and free, so I encourage anyone in the community to jump in contribute as well.
If you stuck it out, thanks for reading!
Cheers,
Lachie
Top comments (0)