Most engineering teams out there are asking a very different question…

Why don’t we have our database schemas under version control? All my functional code, Infrastructure code, configuration and even requirements are under version control. More importantly, one could take advantage of Continuous Integration and Continuous Deployment (CI/CD) capabilities that become available by bringing those database schemas under version control.

In most organizations the answer to “Why” comes down to the same reasons one would apply versioning to any other artifact, in the rest of the organizations, it becomes absolutely vital and disruptive.

Agility

Rapid iteration of a feature often slows down to a crawl waiting for specialized teams such as the DBA organization to make updates to the schema while product teams struggle to keep velocity up. This lack of tandem change often is seen as a recurring impediment for high-velocity teams.

Collaboration

All user experiences have a single goal i.e. to disseminate useful information at an appropriate place and time. These experiences are backed by data stores and its associated schema. Zero visibility into what has changed in these schemas over time and more importantly why it changed, adds additional hurdles to open collaboration.

Source of Truth

Most organizations do have a form of repository for their database schemas, often in shared drives and even version control in some cases using Sharepoint or OneDrive and other document stores. The limitation is with this source of truth being trusted i.e. a change could be made to the files in these stores but may never be applied to the actual database. There is no way to know without actually going into each environment and verifying the state of the active schema against the source of truth.

Continuous Integration

The state of the schema in each environment i.e. Dev, Stage, Prod could all be different and any change to be applied needs to be manually applied and verified in each environment. Opening up permissions to complete schema changes to individuals manually also increases the potential for errors, threat and compliance gap attack vectors.

Quality Assurance

Product teams are encouraged to test as early in the the software development lifecycle where possible and the database schema is typically one large gap that goes unverified. The ability to reliably re-create the database locally, within continuous integration and continuous deployment pipelines is a game changer. The current practice of test data management using direct database access and the refresh of test data after each test run, is often manual, time intensive, and error prone. Attempting to automate this process could be disjointed or breaks easily when the schema changes.

Having access to an Enterprise Database Schema Migration solution is the answer to resolving some of these impediments that an engineering team encounters in most organizations.

How do we Version Control a Database Schema?

An enterprise solution needs to support every major database out there, certain components are vital for such a solution and the rest can be replaced with tools and technologies relevant to your organization.

# Version Control Repository

Most organizations have an existing version control system for their application and infrastructure code. Depending on the functional value of the database and how systems integrate to it, you may choose one of two options listed below:

• Its own repo Works for most organizations and allows for greater control of a highly opinionated, standardized process to change.
• Tied to application Specific data sources that host transactional or session based data, static reference data are use cases where a separate repository for database schema version control may not make sense. In these cases, the application code and the database migration solution can be hosted within the same repository.

Robust testing of the schema, especially with relational data stores, reference data sets using Docker enable both local, integrated and post deployment verifications.

# Schema Migration Library

There are many tools and libraries out there that can be leveraged to build a schema migration solution. Two, stand out, due to their ease of use, versatility of installation, API access and a robust community.

Salient Features

• Migrations of Database Definitions, Database Manipulation queries
• Maven/Gradle based Migrations Support
• Native SQL file migrations
• Programmatic migrations
• Java API
• Free Community Editions
• Paid Premium Features (Our setup does not require this)

Note: There are equivalent NodeJS based libraries with fewer databases supported, however there are community supported NPM Wrappers for Flyway and Liquibase but are not recommended for an Enterprise solution.

Typical usage of these libraries is with the application code; however, this can be limiting as an enterprise solution is crafted to meet the process, governance, and security requirements of your organization.

Conclusion

The myriad of deployment options for an enterprise scale solution using Flyway or any other database schema migration tool that works for your flavor of Database and enables your engineering teams to iterate on the schema is immensely valuable.

The out of the box tools & libraries that Flyway and Liquibase provide are quite robust, and work for most use cases tied to an application. An enterprise solution that benefits regarding governance, support for any database out there, visibility to change and secure access to your data sources, requires a well defined solution.

Next Read: Enterprise DB Versioning Solution With FaaS (Coming Soon)

Preview: Using Function as a Service (AWS Lambda or equivalent for other cloud providers, setup an enterprise solution using the Flyway Java API). This article introduces the solution and potential considerations for your organization.

Resources