Database as Code - the Good, the Bad and the Ugly

This is a series of articles about database version control and database-as-code (GitOps)

1. What is Database Change Management?
2. What is Database Schema Drift?
3. What is Database Version Control?
4. Database Version Control, State-based or Migration-based?
5. Database as Code - the Good, the Bad and the Ugly (this one)
6. The Database as Code Landscape

---

Before touching Database as Code, let's first addressing the more general concept, Configuration as Code (CaC). CaC is the practice of managing configuration resources in a source code repository. Typical configuration resources include:

• Infrastructure configuration like computation (VMs), networking resources (Load balancer). This is widely known as Infrastructure as Code (IaC) thanks to tools like HashiCorp Terraform, AWS CloudFormation.
• Monitoring and alerting configuration.
• Access control policies.
• Continuous Integration (CI) /  Continuous Delivery (CD) pipeline configurations like GitHub Actions, GitLab CI.
• Feature Flags.
• Last but not least, our topic today, database schemas, aka Database as Code.

For some configuration resource types, it has already become the de-facto standard to manage them in the code repository such as managing infrastructure using HashiCorp Terraform, managing monitoring/altering configuration with Prometheus.

Like managing application code, managing application database schema is the routine activity during software development. When talking about the practice of how to evolve the database schema, there are 3 rough ways:

1. Directly connect to the database, and make DDL changes. Quick but error-prone.
2. Have a review console where Developer submits a DDL ticket and let DBA review. DDL is applied to the corresponding database after review approval. This is sometimes referred as SQL review, and is UI-console based.
3. The so-called database as code, managing the database schema in version control system (VCS) like GitLab/GitHub. Whenever developer wants to make a schema change, she will create a new schema migration file containing those DDL statements and submit the file for review. After the review approval, the change will be automatically applied to the database, or in the lack of automation, the developer will apply the change manually like the 1st way.

The Good#

The industry has reached the consensus that Configuration as Code is superior than the UI based approach, managing database as code is no-exception. In our view, Database as Code has 3 appealing advantages:

1. Leverage the existing code repository infrastructure and avoid duplicate work. We get features like code versioning/branching, code search, code review, notifications and etc for free. Products like GitLab/GitHub has already invested tremendous effort into those areas.
2. Align with the Continuous Integration (CI) and Continuous Delivery (CD) DevOps workflow to automate the process of preparing a local/test database by replaying the schema migration files stored in the repository.
3. Have a single source of truth (SSOT) which is the database schema file stored in the repository. With SSOT, we can do things like drift detection, analyzing the database schema without connecting to the production DB (this would be otherwise hard since production network is usually isolated from the development network).

The Bad#

To be honest, from the pure technology perspective, there is little downsides with Database as Code approach.

Compare with other approaches, the main barrier here is still the learning curve and the engineering discipline to follow the best practice. To be fair, it's a bit stretch for any practice to require Google's engineering level to adopt.

The Ugly#

Database as Code looks good on paper, but in reality, there are quite a few missing bricks preventing its wider adoption.

Hard to setup initially#

1. Team need to figure out how to organize the migration files to manage many databases for different environments.
2. To setup the automated workflow where submitted migration file triggers the schema change, one needs to obtain the VCS instance root access privilege via OAuth, setup the proper webhook in the VCS project, observe the correct directory and etc. On top of that, team may customize rules like only allowing automatic database schema changes for some environments.

Slowed process and no continuous positive feedback loop#

It definitely takes longer to apply a change via Database as Code than applying the change directly to the database. Even the pure UI-based SQL review process feels more convenient. On the other hand, although we get useful features like versioning from VCS, it requires more work to unleash the true power:

1. Features like drift detection is only possible when there are deeper integration between code repository and database server, where the former stores the code versioning info and the latter stores the schema info and the schema version history.
2. Beyond database and code repository, it requires other high level development concepts to model the collaborative development workflow to deliver an easy-to-use solution.
3. To construct a truly automated database CI pipeline, it not only requires replaying the migration schema files, but also requires to construct the testing data. This usually requires integration with the application logic.

Not operational friendly open source database#

Core feature wise, both MySQL and PostgreSQL are very capable databases. However, they are not quite operational friendly compared with proprietary ones. e.g. neither database has any built-in schema versioning support, which in turns makes it more challenge to build good tooling around them.

Bytebase - democratize database schema management and especially Database as Code for teams#

Our team at Bytebase have worked on database for 10+ years and have managed some of world's largest database fleets at Google Cloud SQL and Ant Group OceanBase. We build Bytebase specifically to address the barriers preventing teams adopting better practice for managing their application database schemas:

1. We provide a web-based console for teams to collaborate on database related tasks.
2. We provide step-by-step point and click wizard for users to setup database as code (if you are still on the fence, we also provide UI-based SQL review solution).
3. We design first-class models like Project, Environment, Instance, Database to help teams adopt good practice for managing database schemas.
4. We surface all schema change related activities such as running status and history, schema migration history, so that users can receive continuous positive feedback by using the system.

There are other features we don't have space to cover here and we are actively working on adding more features. Our current goal is to democratize the application database schema management for teams, just like how GitLab/GitHub democratizes source code management. And we start from tackling MySQL and later PostgreSQL, the 2 most popular open source database systems nowadays.

Reference#

1. Evolutionary Database Design - Pramod Sadalage & Martin Fowler has a more detailed coverage on this topic.