Schema Change Management Tools: A Practical Overview

In this post, we explore what database schema change is all about and provide an overview of its history, tools and challenges, and the path toward a modern schema migration solution.

Who Needs a Stable and Predictable Database Anyway?

All jokes aside, as a developer, your focus is on creating the code that makes your application work. But for your application to be useful, it needs to work with data, usually lots of it. And that data needs to be well organized and fully accessible in your database for your application to perform properly. 

Database schemas handle this by keeping all entity data (e.g., customers, products, orders, etc.) structured in a way that your application can insert, retrieve and process them all as needed.

But what happens when you add some new functionality to your application or you develop a brand-new application that requires incorporating additional data entities? 

That’s where schema migration or schema change management comes into the picture. These terms reflect the process of managing your database schema over time with actions such as adding new tables, altering existing tables, or changing the relationships between tables.

This ensures that new features are properly supported with (hopefully) improved efficiency and data integrity, all while ensuring zero or minimal downtime and a smooth transition for the application using the database. 

Let’s now explore the history of database management, some of the tools used, and how modern solutions can support you in our highly dynamic world of microservices and agile application development.

A Brief History of Database Management

Before the 1970’s databases were typically either flat files or hierarchical structures. Making changes involved physically modifying the files or altering the hierarchical relationships, which often required rewriting the entire database from scratch. This process was labor-intensive and error-prone.

With the advent of relational databases, the concept of schemas emerged. Initially, database changes were handled by modifying the schema directly using SQL commands. However, making structural changes often meant significant downtime, as the entire database had to be reorganized or migrated.

As databases became more complex, DBAs (database administrators) started using custom, manual scripts to add or modify database tables, columns, and relationships. However, scripts had to be carefully managed, and it was easy to make mistakes leading to data inconsistencies and errors.

Version control systems, initially designed for software development, started being applied to database changes. DBAs and developers began using tools like Git or Subversion to track database schema changes. However, this approach often involved manual synchronization between code and database changes, requiring careful coordination and additional effort.

Over time, specialized database migration tools and frameworks emerged to automate and streamline the process. These tools allowed developers to more easily define database changes using scripts or configuration files. They provided mechanisms to apply and manage changes automatically, handle dependencies, and roll back changes if needed. 

Let’s take a look at two of these tools.

Popular Schema Migration Tools: Liquibase and Flyway 

As we mentioned earlier, database changes used to be handled manually or through custom scripts which could be error-prone and time-consuming. Keeping track of which changes were applied to a database and ensuring consistency across environments was also challenging.

Flyway and Liquibase both address these challenges with the following key features:

• Automation: Both Flyway and Liquibase automate the process of applying database schema changes, saving time and reducing errors. Instead of manually executing scripts, you can define your changes using predefined formats and let the tools handle the execution. However, Flyway allows only SQL for defining changes, while Liquibase provides more flexibility allowing you to use SQL as well as XML, YAML, and JSON. 
• Version Control: Both Flyway and Liquibase bring version control to database schema changes. They maintain a record of which migrations have been applied to a database, allowing you to easily track and manage the schema over time with built-in rollback functionality in the event of any issues. 
• Dependency Management: Both tools provide mechanisms for managing dependencies between different migrations. This means that you can specify dependencies between migration scripts, ensuring that they are applied in the correct order. For example, if you are adding a new table and within that table, you want to add a specific column, these tools will make sure the script for the table will be executed before the script for the column. 
• Seamless Integration: Flyway and Liquibase integrate with popular CI/CD and build tools, such as Jenkins, Gradle, and Maven. This allows for the incorporation of database migrations into the overall development and deployment workflows. Developers can configure the tools to automatically execute migrations during builds or as part of deployment pipelines.

However, the explosion of containerization and microservices requires schema change management tools that natively work with Kubernetes as well as support the many types of databases used by modern organizations. This brings us to the latest generation of schema change management tools. 

Modern Schema Change Management Tools 

To support the widespread adoption of Kubernetes, SchemaHero was developed for managing Kubernetes-native databases such as etcd, Vitess, and others. It simplifies the process of deploying and managing database schemas in a Kubernetes environment with features like declarative schema management and automated schema updates. SchemaHero uses Kubernetes manifests, such as YAML files, to define the desired state of the database schema and allow you to define the schema as a Kubernetes Custom Resource.

While SchemaHero helps with Kubernetes-native databases, the common trend of using many types of databases within organizations leaves DBAs overwhelmed with requests for new schema creation, migration, and review. This often leaves developers unfamiliar with the intricacies of database management, waiting for days or longer before they can safely move their latest code to production. 

To help developers be more independent in regards to schema migrations, opensource solutions like Atlas are being developed with the following capabilities:

Declarative Approach to Schema Planning 

This allows you to plan appropriate, safe, and efficient changes to your database using a Terraform-like syntax that supports any ORM or framework you use to build your applications.

CI/CD for Schema Changes

This provides native, continuous integration and deployment for databases, providing automated checks and reviews to ensure that no risky or problematic changes ever reach production. This also supports microservices architectures, managing and coordinating schema migrations of various microservices within a single deployment unit to ensure safe rollout and recovery from failures.

Migration Troubleshooting and Remediation

When schema changes go off the rails, built-in troubleshooting helps you figure out and fix what went wrong without having to connect to the database or perform risky operations such as manually editing metadata tables.

Drift Detection and Schema Monitoring 

Once changes are successfully rolled out, you can monitor and detect drift between the expected state of the system and its actual state.

All combined, these features enable you, as a developer, to rapidly and safely move your code to production without having to wait for the database team.

Say Goodbye to Context Switching 

As a developer, you have enough on your plate to code the next big thing. Having to handle complex database migration is a distraction and can derail your progress. Fortunately, many companies and the open-source community are tackling these issues with advanced platform engineering and DevOps tools that make life easier for Devs and Ops.