Introduction to database continuous integration

Continuous integration (CI) is the process of ensuring that all code and related resources in a development project are integrated regularly and tested by an automated build system. Code changes are checked into source control, triggering an automated build with unit tests and early feedback in the form of errors returned. A stable current build is consistently available, and if a build fails, it can be fixed rapidly and re-tested.

A CI server uses a build script to execute a series of commands that build an application. Generally, these commands clean directories, run a compiler on source code, and execute unit tests. However, for applications that rely on a database back-end, build scripts can be extended to perform additional tasks such as creating, testing, and updating a database.

The following diagram illustrates a typical integration process. The automated continuous integration process begins each time the server detects a change that has been committed to source control by the development team. Continuous integration ensures that if at any stage a process fails, the ‘build' is deemed broken and developers are alerted immediately.

Diagram: database continuous integration process

Database code

Database code is code, and should therefore be treated in the same way as your application code. However, the principal difficulty underlying continuous integration for databases is the lack of a simple way to keep a database in source control and deploy it to a target server.

The database is unlike application code in as much as it contains a state that needs to be preserved after an upgrade. Where a production database already exists, DML and DDL queries modify the existing state of a database, and unlike for application code, there is no source code to compile. Migration and deployment therefore rely on creating upgrade scripts specifically for that purpose.

The lack of database source code makes it difficult to maintain a current stable version in source control. Creation and migration scripts can be checked into the source control repository, but despite its importance, the disciplined creation and on-going maintenance of these scripts is often not considered to be a core part of the database development cycle.

Migration scripts may contain ALTER and UPDATE statements to update the target version of the database with the development version; alternatively, the scripts may create a new database. Where changes are deployed to an existing database, all differences and dependencies must be accounted for. In some production deployments, this involves multiple targets with different schemas and data. In either case, the manual process is time consuming, prone to errors, and one that should not be left unresolved at the end of the project cycle.

Benefits of Database CI

1. Keeping a database up-to-date

Databases may figure in your CI process simply because the application code requires there to be a database present to function correctly. The database schema version corresponds to an analogous application code version. Any changes to the application code or the database structure could in theory break the system and should consequently trigger the CI process.

Once a database is maintained in source control, Red Gate tools are able to build a clean database from its source files to accompany the application build.

If you already have internal test databases that need to match the development databases, you can keep them up-to-date with the latest version using continuous integration.

2. Testing the database creation script

An artifact of the database build process, the database creation script, is one that builds the database from scratch. This is useful not only for when a test database needs to be built, but also if new installations of the application are required, for example, to new customers.

3. Testing the database upgrade script

Unlike for application code, upgrading a production database isn't a simple case of replacing it with a fresh copy. Databases have a mission critical state that needs to be preserved.

Safeguarding existing data is the most challenging task to be faced during the upgrade process. This is why it is a highly recommended best practice to repeatedly test the deployment script as part of the CI process and ensure that a working upgrade script can be generated at all times.

In order to test the upgrade, it is necessary to create a database at the version corresponding to the existing production database, and not just create a new database representing the latest version. Using Red Gate tools, the deployment script is generated, applied against the production-level CI database, and subsequently validated against the expected target version. If this validation fails, the failed upgrade process should be regarded as a ‘broken build', and measures should be taken to troubleshoot and promptly resolve the issue.

Using the Deployment Suite for Oracle for CI

The Deployment Suite for Oracle contains command line tools to handle the scripting and deployment process, and enables easy database source control. Learn how to setup CI for your Oracle database with the Deployment Suite for Oracle and TeamCity or Jenkins.