Database Continuous Integration (CI) is an automated process that builds and tests a database whenever changes are made to project scripts. The goal is to quickly identify potential defects. This approach provides developers with immediate feedback on their changes, which helps prevent bugs from accumulating and being discovered days or weeks later.
A database CI pipeline includes several stages that changes pass through before they are delivered to production or a continuous delivery (CD) process.
Developers modify the database schema by creating, updating, or removing database objects.
After changes are complete, developers commit them to a version control system, such as Git or Subversion (SVN). The CI workflow triggers a build based on defined events, for example, after each commit or on a schedule.
Database scripts can follow different approaches, such as migration-based or state-based. The selected approach determines the tools and deployment strategy.
This phase is considered complete when the database has been successfully created.
After the database is created, it is validated by running SQL unit tests. Before testing, the database can be populated with test data by using data generation or data import tools.
Unit tests verify that existing functionality continues to work after changes are applied.
This phase is considered complete when all SQL unit tests have been successfully executed.
After validation, the CI process produces an artifact that contains the database changes, such as a ZIP file or a NuGet package. Optionally, database documentation can be generated and included.
The artifact is then published to a repository or another target location and can be used in later deployment stages.
This phase is considered complete when the database artifact (ZIP file or NuGet package) has been successfully created.