Database Continuous Integration comprises a process of builds automation, that involves creating and testing a build every time the development team makes changes to the database project scripts in order to quickly identify potential defects. In this case, developers get instant feedback on their changes, which help avoid a situation when bugs accumulate and developers turn to them only a few days or even weeks after the changes have been made.
The figure above shows the pipeline of the Database Continuous Integration process. These are the phases that database development goes through before it goes into Production or Delivery Integration.
At this stage, developers make changes to the database project schema (add objects, change objects, etc.).
After the developers have finished making changes to the database schema, they commit them to the version control system (such as Git, SVN). Then in order to initiate the creation of a new build, it is necessary to configure the database script change events in the version control system. Depending on the peculiarities of the development process, it is possible to choose to generate the creation of build either every time after the changes in the database schema have been made or set up a schedule of its generation.
During the build phase, the database is created from scripts (* .sql files) on the SQL server. There are several methods for organizing database scripts. The main of them are migration scripts and state-based ones. Depending on the method of scripts organization, certain database design tool will be used.
Successful completion of this phase is a successfully created database.
Once a database is created on a server, it needs to be tested using SQL unit tests. Before running unit tests, a database can be filled with test data using the data generation tools or the data import tools.
Unit tests and their launch are crucial, as they ensure that certain functionality still works properly after various changes made in the database design by developers.
Successful completion of this phase is successfully completed SQL unit tests.
At the end of the CI process, an artifact containing the database changes (a Zip or NuGet package for example) is created. Additionally, if required, database documentation can be generated and included in it. Next, the NuGet package (or Zip file) is published in the NuGet repository or put in a special folder. Later, this artifact can be sent to a deployment server or service.
Successful completion of this phase is a successfully created database artifact (Zip or NuGet package).
Nowadays Continuous Integration is an integral part of software development. I case it is performed often, it allows developers to identify bugs at an early stage and immediately fix them.
Theoretically, it is recommended to run the CI process several times a day, but depending on your development process, it can be conducted after each commit, or even once a day.