Automating Database Migrations with Containers and Git

On the eve of the start of the course “Infrastructure platform based on Kubernetes” we invite everyone to free demo lesson, within which aboutLet’s take a quick look at the kubernetes device, talk a little about how components interact, analyze the main approaches to ensuring cluster security, talk about resource limits, network policies, startup privileges, etc.


Implementing the delivery of custom database migrations using script manifest files

Managing database migrations for multiple environments and teams can be challenging. This article describes how a combination of Git, containers, and database clones is used to implement delivery to development, test, and staging environments in seconds.

Although Git is often used in conjunction with database containers, the approach described here introduces two new elements. Instead of replicating databases from backups or building from a data source, we clone identical secure production database environments and deliver them within seconds. Database clones are writable and make it easy to implement data masking and synthetic test data. The second element is the script manifest file used when creating and applying custom migration scripts.

A developer can work with a clone of the production database in a feature branch – the scripts for that branch are automatically applied. At the same time, the testing team can work in the release branch with an identical clone of the production database – a set of release scripts is used for it. At some pipeline stage, you can test rollback of the release branch with a third identical safe clone of the production database, thanks to the automatic application of update and rollback scripts.

This article is based on SQL Server, but these methods are also supported by Postgres and MySQL.

Components

You can use any public or private Git repository, including GitHub, GitLab, or Git in a private virtual machine.

A database image is a collection of production databases recreated from backups or database files using data masking scripts. Users can specify scripts from branches and Git repositories to support specific development, testing, and staging environments. Once you have backups of the transaction log, the image can be updated incrementally.

The script manifest file is a text file that lists the scripts to run.

A database container is a database instance that provides services for working with a database.

A clone of databases is an imaged, writeable database that is delivered in seconds and requires only 40 MB of disk space.

Creating a database image

The Dockerfile includes another path to production database backups and data masking scripts. At run time, the Git repository is cloned into the container filesystem with a special branch checkout. Also at runtime, users or the pipeline expose two environment variables highlighted in red, indicating a Git branch and a scripting manifest file (more on that below). The final step involves a PowerShell command that creates a concatenated “all.sql” script that reflects the order of the scripts in the manifest file, which is then run.

The image is created using the standard docker command:

>docker build -t microservice1 c:pathtodockerfile

Manifest files

A manifest is a text file that lists the paths to scripts relative to the root of the Git repository in the order in which they should be executed. Multiple manifest files can be used. One manifest might list upgrade scripts, and the second might include upgrade scripts followed by rollback scripts. File manifest.txt may include:

Development and test environments

Development starts by shipping a SQL Server container with a production database clone, but no Git scripts. As work progresses, the scripts are committed to the repository, a manifest file is created, and the list of scripts is committed in the order in which they should be executed.

Users and pipelines provide environments using docker commands or a restful API. The output of the script log is available using the Rest API. Frameworks are used to unit test SQL scripts and applications with specific manifest files and Git branches. Shipping includes user input in red.

Testing release branches

As tasks are completed, the working branches are merged into a release branch with updated manifest files. Testers and DevOps pipelines can now deliver release test environments using the same database image, referencing the release branch and the updated manifest file. Database migration with Git maintains a full development / test lifecycle with a consistent database environment.

Data management and security

This approach not only simplifies and automates database development and testing, it also creates a secure, centralized data store. Where organizations get involved in the fight against sprawling virtual machines and instances, containers allow consolidation to a container host. A single database image easily supports 20 to 50 environments concurrently, potentially reducing storage by up to 95%.


Sign up for a free demo lesson.

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *