Practical guide to dynamic data masking: five best practices, a vendor comparison, an end-to-end walkthrough, and the compliance specifics auditors ask about.

Dynamic Data Masking Best Practices

Audit-surviving database access control: statement-level enforcement across machine, human, and AI agent workloads, with the mistakes that fail SOC 2.

Database Access Control (DAC) Best Practices

TRUNCATE and DELETE both remove rows, but they differ in speed, rollback behavior, and locking. Compare how they work in PostgreSQL, MySQL, and SQL Server.

TRUNCATE vs DELETE in SQL: Differences Across PostgreSQL, MySQL, and SQL Server

Six database design patterns for production systems: normalization, star schema, EAV, polymorphic associations, multi-tenancy, and anti-patterns.

Database Design Patterns Every Developer Should Know

pgvector adds vector storage and similarity search to PostgreSQL. Learn how to install it, query embeddings, build indexes, and decide when it is enough.

pgvector: How to Add Vector Search to PostgreSQL

Upsert inserts or updates a row based on key conflicts. Learn MySQL INSERT ON DUPLICATE KEY UPDATE, PostgreSQL ON CONFLICT, and SQL Server MERGE.

Upsert in SQL: INSERT ON DUPLICATE KEY vs ON CONFLICT Explained

AI agents need more than DDL. The full database lifecycle codified — schema, dependencies, ownership, history — gives LLMs the context to query correctly.

From Schema as Code to Schema as Context

How to give AI agents governed access to your databases. Identity, authorization, query control, audit, and masking — applied to a new principal type.

How to Govern AI Agent Database Access

Schema change detection and notification are essential for data pipelines. This article explains why a simple column rename can break your data pipeline and how to prevent it.

Schema Change Detection and Notification: Why a Simple Column Rename Can Break Your Data Pipeline

A comprehensive guide to understanding and analyzing Postgres query plans.

Postgres Query Plan: How to Read and Fix Performance Issues

This article explores the anatomy of Postgres permissions ERROR 42501.

The Anatomy of Postgres Permission ERROR 42501

Postgres Logs Explained: A Practical Guide for Production

A guide to multi-environment deployments in databases.

Database Multi-Environment Deployments - The Challenges and Patterns

A practical guide to HITRUST database compliance covering access control, audit logging, change management, and data protection.

Database Compliance for HITRUST: Controls, Risks, and Best Practices

Learn why requiring a primary key is important and how the "Require Primary Key" review rule protects your production database.

Require Primary Key: A SQL Review Rule Explained

Learn why enforcing NOT VALID in CHECK constraints is important and how the "Enforce NOT VALID in CHECK" review rule protects your production database.

Enforce NOT VALID in CHECK: A SQL Review Rule Explained

Postgres Audit Logging Guide

Learn why CASCADE operations in SQL can lead to catastrophic data loss and how the "Prohibit CASCADE" review rule protects your production database.

Prohibit CASCADE: A SQL Review Rule Explained

A comprehensive guide on migrating from Supabase to AWS, covering database, auth, storage, functions, realtime, and networking.

How to Migrate from Supabase to AWS

Enterprise database management is a comprehensive approach to organizing, securing, and optimizing database systems and workflows at scale, ensuring data integrity, availability, and compliance across large organizations.

What is Enterprise Database Management (EDM)?

What is Postgres Transaction ID (XID) Wraparound and how to monitor and prevent it.

Postgres Transaction ID (XID) Wraparound

How a CI/CD pipeline for database schema migration works end-to-end — what it gates, what it leaves outside, and the tools that fit.

How to Build a CI/CD Pipeline for Database Schema Migration

A comprehensive overview of the essential practices and principles necessary for achieving LGPD (Brazilian General Personal Data Protection Act), compliance within database operations, and its difference with GDPR

Database Compliance for LGPD: Implications and Best Practices

Explain what is Postgres LATERAL JOIN and how to use it.

What is Postgres LATERAL JOIN?

Introduction to MySQL Online DDL and its three algorithms: INSTANT, INPLACE, and COPY

MySQL Online DDL: A Practical Guide

A comprehensive guide to understanding the dilemma of singular vs. plural table naming in SQL databases.

SQL Table Naming Dilemma: Singular vs. Plural

An engineering perspective to evaluate Postgres case sensitivity

Postgres Case Sensitivity Explained

A comprehensive guide to understanding pg_database_owner in Postgres

What is pg_database_owner in Postgres

Which operation causes a Postgres table rewrite

Which Postgres Operation causes a table rewrite

An engineering perspective to evaluate Postgres row-level-security footguns

Postgres Row-Level Security Footguns

An engineering perspective to evaluate Postgres rollback strategies

Postgres Rollback Explained

Learn how Postgres locks affect schema migrations and which DDL operations conflict with DML to achieve zero-downtime deployments.

Postgres Schema Migration without Downtime

A checklist of gh-ost limitations and potential gotchas

gh-ost Limitations You Need to Know Before Production

5 quick wins for application developers to improve Postgres reliability and make both their own and DBA/infrastructure teams' lives easier.

5 Postgres Production Tips for Application Developers

Learn how to use CREATE INDEX CONCURRENTLY in Postgres to build indexes without blocking writes. Pitfalls, safety checks, and performance tips.

How to Use Postgres CREATE INDEX CONCURRENTLY

Learn how Bytebase enables organizations to implement comprehensive Database Security Lifecycle Management, unifying security and operations from development to production.

Database Security Lifecycle Management

This guide focuses on HIPAA compliance at the database layer, providing healthcare organizations and database teams with practical guidance for implementing HIPAA Security Rule requirements.

HIPAA Data Security and Retention Requirements

Compare the online index creation difference between Postgres and MySQL

Postgres vs. MySQL: Online Index Creation

SOC 2 Data Security and Retention Requirements for Database Systems and How Bytebase Helps

SOC 2 Data Security and Retention Requirements

Compare the different indexing options between Postgres and MySQL

Postgres vs. MySQL: Indexing Options

Compare the JSON support difference between Postgres and MySQL

Postgres vs. MySQL: JSON Support

While PostgreSQL RLS is a powerful feature, it has inherent design limitations that hinder broader adoption. In this post, we’ll examine these limitations and explore alternatives.

Postgres Row-Level Security (RLS) Limitations and Alternatives

Compare the DDL transaction difference between Postgres and MySQL

Postgres vs. MySQL: DDL Transaction Difference

Understanding DynamoDB Pricing (2025)

Complete guide to AWS Aurora pricing: instance types and costs, Aurora Serverless v2 pricing, I/O-Optimized vs Standard, reserved instance savings, and real-world cost examples. Learn how to optimize your Aurora costs.

AWS Aurora Pricing Explained: Instance Types, Serverless, and Cost Optimization (2026)

Understanding AWS RDS Pricing (2025)

Explain why MySQL provides a variety of timeout settings.

MySQL Timeout Explained

Learn the difference between DDL and DML in SQL databases. DDL (Data Definition Language) defines database structure with CREATE, ALTER, DROP. DML (Data Manipulation Language) manages data with SELECT, INSERT, UPDATE, DELETE. Includes examples and comparison table.

DDL vs DML in SQL: What is Data Definition Language and Data Manipulation Language?

The top 10 database schema design best practices with code snippets

Top 10 Database Schema Design Best Practices

Database version control is the practice of managing and tracking changes to a database schema and its associated data over time. Why best practice matters? Mitigate risks, improve collaboration, and ensure data audibility.

Database Version Control Best Practice

Data access governance is a critical component of any organization's security strategy. By implementing the right tools and policies, you can protect sensitive data, ensure regulatory compliance, and maintain a secure and productive work environment.

Data Access Governance: Why It Matters and How to Get it Right

Database Security Compliance is a critical framework that not only mitigates legal and financial risks but also enhances overall data governance.

Database Security Compliance

A comprehensive guide to Change Advisory Boards (CAB), including their purpose, limitations, popular tools, and best practices for database change management.

What is a Change Advisory Board (CAB)?

Understanding production databases, their critical importance, common deletion mistakes, and best practices for safeguarding these essential systems.

What is Production Database

Give an overview about Database Activity Monitoring and its difference from Database Monitoring

What is Database Activity Monitoring (DAM)?

A deep dive into multi-tenant database architecture patterns, exploring pros and cons of shared vs. isolated models, and how to choose the right approach for your SaaS application.

Multi-Tenant Database Architecture Patterns Explained

How to integrate ServiceNow, GitHub with Bytebase to Automate Database Changes.

ServiceNow and GitHub Integration for Database Change Automation

Pros and Cons between choosing uuidv1, uuidv4, uuidv7

How to Choose Database UUID?

What are the differences between Primary Key and Foreign Key

Primary Key vs Foreign Key: What are the Differences?

Explain write ahead logging in database and the implementation difference between different databases.

What is Write Ahead Logging (WAL)

The Just-in-Time Database Access workflow

Just-in-Time Database Access

Explain the different database permission levels

Database Security: Read, Write, and Admin Permission Levels

Explain database rollback and how to automate it

Database Rollback: When Shit Hits the Fan

Database DevSecOps is the practice of integrating security throughout the entire database development lifecycle, much like how DevSecOps evolved from DevOps.

What is Database DevSecOps?

Dynamic Data Masking (DDM) protects sensitive data in real-time by dynamically altering the data returned to the application or user without changing the data at rest

What is Dynamic Data Masking (DDM)

Database DevOps integrates DevOps practices into database management, automating processes like schema changes, testing, and deployment.

What is Database DevOps?

Explain why Postgres provides a variety of timeout settings.

Postgres Timeout Explained

Pros and Cons between choosing UUID or auto increment integer / serial as the primary key for SQL database.

How to Choose between UUID and Auto Increment Integer / Serial as the Primary Key?

A comprehensive overview of the essential practices and principles necessary for achieving GDPR compliance within database operations

Database Compliance for GDPR: Implications and Best Practices

How Bytebase tracks database changes throughout the lifecycle

How Bytebase Tracks Database Change

Explain the pricing details of Google Cloud AlloyDB and compare it with Google Cloud SQL

Understanding Google Cloud AlloyDB Pricing (updated for 2025)

Describe the current feature gap of Snowflake CI/CD story and present a solution

Snowflake CI/CD and DevOps Best Practice

Bytebase is an all-in-one solution for database development lifecycle management. It replaces fragmented database tools and unifies the workflow in a single place.

All the Database Tools Bytebase Replaces

Database schema drift is the case where the actual schema in the live database is different from the source of truth. It's also one of the most frequent root cause of the database related outages.

What is Database Schema Drift?

Comparing PostgreSQL data masking solutions - PostgreSQL Anonymizer and Bytebase Dynamic Data Masking.

Postgres Data Masking

Collaboration and Teamwork, Change Tracking and History, Migration Scripts, Branching and Merging, Deployment and Rollback, VCS Integration GitOps are key aspects of database version control

What is Database Version Control?

Compare MySQL data masking solutions. MySQL Enterprise Data Masking, Percona Data Masking Plugin and Bytebase Dynamic Data Masking.

MySQL Data Masking

Snowflake provides auto-suspend and auto-resume to help customers to save budget when the warehouse is inactive

What is Snowflake Auto-Suspend and Auto-Resume?

While Snowflake revolutionizes data management and analytics, effectively managing schema changes and tracking change history becomes increasingly crucial.

Snowflake Schema Change and CI/CD Best Practice

Database schema migration involves modifying the structure of objects within relational databases, evolving their schema from the current state to a new desired state. The two main schema transformation strategies are migration-based migration and state-based migration.

What is Database Schema Migration?

The common mistakes when making schema migrations and the best practice to avoid those pitfalls.

How to Handle Database Migration / Schema Change?

The second release of the 🐰  year, the version number was upgraded from 1.12.0 to 1.12.1, and although it was a minor upgrade (since no schema changes were involved), it still included 230 code commits

Bytebase 1.12.1 Deep Dive

First release of the Year of Rabbit 🐰 is out, the version number was upgraded from 1.11.0 to 1.12.0. - SQL Review Policy - Price Plan Update.

Bytebase 1.12.0 Deep Dive

We are thrilled to announce the first release launch of 2023, marking a new beginning for our product and our valued customers. The version number was upgraded from 1.10.0 to 1.11.0. - MongoDB - Schema Diagram - Schema Editor.

Bytebase 1.11.0 Deep Dive

Last week, we released Bytebase 1.10.0. Let's take a deeper look into the new features - Audit Log - Terraform Provider to manage user roles in PostgreSQL - Support for large SQL files.

Bytebase 1.10.0 Deep Dive

If we can develop a complete Database CI/CD workflow, and break the wall between developers and DBAs, will it improve release efficiency?

VCS Integrated Database CI/CD Workflow Best Practice

Last week, we released Bytebase 1.9.1. Let's take a deeper look into the new features - schema editor, mark issues as Need Attention, database access control, and data masking.

Bytebase 1.9.1 Deep Dive

Database Change Management (DCM) explained — definition, why it is hard, the ideal workflow, state-based vs change-based approaches, tool evaluation criteria, and how it fits into DevOps.

What is Database Change Management (DCM)?

The three ways to integrate Bytebase SQL Review with GitHub. GitHub Action, GitHub App, and GitOps CI

Three Ways to Integrate Automatic SQL Review into GitHub

Last week, we released Bytebase 1.9.0. Let's take a deeper look into the new features - Sync Schema for PostgreSQL, SQL Editor Admin Mode, and External Approval via Feishu.

Bytebase 1.9.0 Deep Dive

Last week, we released Bytebase 1.8.0. Let's take a deeper look into the new features - MySQL Sync Schema, Admin Mode for SQL Editor, and External Approval via Feishu.

Bytebase 1.8.0 New Features Dive

A database schema defines the logical structure of your data: tables, columns, types, keys, and constraints. Learn how schemas work and evolve.

What is a Database Schema?

SQL review catches bad SQL before it hits the database. This guide covers the three layers (linting, semantic rules, and policy with approval) and compares the main SQL review tools.

SQL Review: From Linting to Governance

Explain and address the confusion between Developer (Dev), Site Reliability Engineer (SRE), (Operations) Ops and DevOps.

Dev, SRE, Operations, DevOps - What’s the Difference?

We dive the rationale behind the industry divergence between state-based and migration-based approach. In the end, we also show how Bytebase can help to get the best of both worlds.

Database Version Control, State-based or Migration-based?

Database-as-Code has many benefits in today's DevOps world. However, there exists several missing bricks preventing its wider adoption. We touch many points around Database-as-Code and also explain why Bytebase could help the transition.

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


This is the last release in 2022, and we upgraded Bytebase from 1.9.1 to [1.10.0](/changelog/bytebase-1-10-0). With 200+ commits, let’s take a closer look at what new capabilities are released this time 👇

## Audit Log

We continue to enhance Bytebase’s security control capabilities with the new [Audit Log](https://docs.bytebase.com/security/audit-log) module, which is accessible to Workspace admins and DBAs. The current Audit Log records member changes, database transfers, and SQL Editor queries. In future releases, Bytebase will be able to capture more events in the Audit Log.

![_](/content/blog/1-10-0-new-features/audit-log.webp)

## Enhanced Terraform Bytebase Provider

Next up is the enhanced [Terraform Provider](https://registry.terraform.io/providers/bytebase/bytebase/latest/docs/resources/database_role). We added instance and environment resources management in the last version, and for this version, we added RBAC for PostgreSQL. This way, the entire user access lifecycle in PostgreSQL can be managed by the Terraform Bytebase Provider, and we plan to implement the same for MySQL soon.

![_](/content/blog/1-10-0-new-features/terraform-provider-role.webp)

## Large SQL Files

Finally, it is worth mentioning the newly added support for large SQL files, up to **100 MB**. This request came from a user who wanted to make large amounts of data changes. Bytebase implemented this feature in a relatively clever way, as we introduced a sheet model in the SQL Editor, so for large SQL files, Bytebase handles it by having the user upload the SQL to the sheet first.

![_](/content/blog/1-10-0-new-features/upload-sql-sheet.webp)

That’s it for the 1.10.0 deep dive. Looking back over the past year, at a 2-week release frequency, team Bytebase submitted ~4,000 PRs and released 25 versions.

Some features come naturally, such as the Audit Log; some were inspired by our users, such as the Terraform Bytebase Provider; and some have been foreshadowed at the beginning of 2022, such as the introduction of the sheet model. We are thrilled to see that the sheet model works perfectly with large SQL files.

In 2023, Bytebase will continue to bring value to our users, and we are also looking forward to inspiration from our users.



## Let’s chat about the cumbersome database change process

Database change has long been the most critical step in a release. If you do it well, you are one step closer to improving CI/CD efficiency. As business complexity and database instances increase, it gets harder and harder to manage database change. Let's see if any of the following scenarios sound familiar:

- SQL scripts are scattered in various computers, and any modification needs repeated manual confirmation.
- Change requests need to be submitted in multiple systems, which can easily be missed.
- DBA only intervenes to review the SQL right before execution: blocking the problematic SQL will delay the release while passing it will create long-term hidden problems.
- Change requests are complex: imagine n slightly different SQL scripts for multiple application versions or different tenants. If SQL scripts are stored in standard document folders, that’s just a recipe for disaster.
- There is no way to archive and audit the change scripts afterward.

They may not sound so bad if you only need to manage one or two databases, but when you face as few as a dozen to 1,000+ instances, this will be torture. We have a platform to manage code, a variety of issue trackers, and multiple SQL review tools, but why is this process still such a pain in the ass?

All inefficient collaboration can be backtracked to fragmented processes and unshared information, if we can develop [a complete Database CI/CD workflow](https://docs.bytebase.com/tutorials/gitops-github-workflow), and break the wall between developers and DBAs, will it improve efficiency?

![](/content/blog/database-cicd-best-practice/complete-cicd-workflow.webp)

In this article, we will cut through the lens of developer workflow and explore how to design a pragmatic VCS integration solution.

## Define your organization's iteration process

Organizations are influenced by department divisions and tech stack. These eventually form their release process, which is difficult to change. There are many ways to categorize the release process. Since our goal is to optimize change management, let’s divide it into two by application iteration.

**Centralized iteration**: couple multiple applications together, release one version at a time, and manage them by batch. This works for functions that are tightly coupled together, so changes need to be planned holistically for up and downstream businesses. This development process is commonly found in traditional industries with high business complexity, such as finance.

**Decentralized iteration**: to improve business iteration efficiency, organizations try to decouple applications as much as possible, with each team independently responsible for the development and release of its application or module. Some emerging IT and SaaS companies are adopting this model, of which microservices are a typical practice.

However, most companies (huge ones, at least) will not fully adopt only one form: they usually adopt different iteration models in different departments, between core and sideline business, critical and supporting functions. We must combine different models and design the most suitable Database CI/CD solution for different models.

## Define database management boundary

With a defined iteration process, it is natural to derive different ways of database management. It is essential to clearly define the management boundaries of the database across different teams, as the different management styles determine how the process should be optimized. Since the DBA is the database admin, so our management boundary analysis should revolve around this role. While some organizations have mature DBA teams, others may not have a DBA at all, but in any case, database change is inevitable, and the difference is simply who is responsible for it. There are three mainstream practices for managing databases:

1. The DBA is fully responsible for all the database work, the boundary is drawn between the DBA and the devs. The devs are only responsible for submitting change requests and are far from database management.
2. The development teams are fully autonomous. This is usually because there is no DBA (or platform engineering) in the organization. The management boundary is drawn between different development teams, and each project team is only responsible for its own database.
3. The DBA is in charge of database-related work, but the devs are more deeply involved. Some critical work still belongs to the DBA, while some work is assigned to the development teams, and also divided by project.

![](/content/blog/database-cicd-best-practice/database-management.webp)

In an organization aiming at Database DevOps, the development team should be encouraged to participate more in the database change work, while the DBA should work with infra and SRE teams to develop a database management framework. This includes tools selection, review policy development, and automated CI/CD process implementation to improve overall efficiency by empowering the devs instead of dealing with mundane work alone. After all, for most organizations, a single DBA often faces hundreds of devs. With this goal in mind, we recommend the third model in which the DBA (or underlying platform engineering team) works with the development team to build the database management framework and allows the development team to have controlled access to the database.

Of course, different organizations should be more specific in deciding which tasks to give autonomy to the development team, considering their own R&D and business processes.

- Organizations using centralized iteration usually have a more complex business as well. The DBAs need to have more profound control over each specific change. However, this does not prevent collaboration with the devs in the early stages such as testing and pre-integration, which will significantly improve the efficiency for the official release.
- In organizations using decentralized iteration, the development teams are in charge of most of the work, including reviewing changes to the production environment, while a small number of DBAs (or none at all) are involved in only some of the most critical aspects or solving more complex problems.

## Implementing GitLab/GitHub integrated Database CI/CD

Once you’ve defined your organization’s R&D processes, it’s time to get down to business.

### Introducing VCS for Change Script Management #GitOps

The first thing is to get your SQL scripts in order - SQL is code, too. And it makes sense to use a version control system (VCS) such as GitLab or GitHub to manage SQL. To ensure manageability, we need to structure the SQL scripts in the VCS in a way that makes sense.

**By project**

For organizations using decentralized iteration, most of the time, each database belongs to a specific application, which can be seen as the management boundary. Bytebase uses this model, i.e. managing the database by application project, which is in line with the habits of most organizations. A good practice to group change scripts is by application project name, as the root directory. These scripts can be stored in their respective application code repositories or in a single code repository, depending on the organization’s management habits.

**By change batch**

For organizations with centralized iterations, since each batch of changes may involve multiple applications and these scripts are interrelated and affect each other, it’s better to store change scripts grouped by batch in VCS rather than by application, which means that each batch directory contains change scripts for multiple databases. For easy management, it is recommended to group scripts by environment or database name. In Bytebase, it is recommended to group the scripts by application to manage database review access.

![](/content/blog/database-cicd-best-practice/script-change-management.webp)

### Have the devs perform SQL review: the shift-left approach

This shift-left approach makes it possible for the SQL scripts to be reviewed as they are submitted to VCS,

To avoid complex SQL blocking your release, the shift-left approach should be introduced: SQL review is integrated into the existing developer workflow, making it possible for the SQL scripts to be reviewed as they are submitted to VCS. Common checks include:

- Syntax: standard SQL syntax checking.
- Executability: does the object associated with the statement exist?
- Naming: table, index, field names, etc. should be meaningful and easy to manage, for example, index names should contain corresponding tables and fields, and no special characters in table names.
- Table creation: mandatory primary key, comments, etc.
- Index rules: restrict index type, restrict the number of index fields, restrict index field type, etc.
- Field rules: such as restricting the type, length, default value of fields, etc.
- Query restrictions: such as query must have filter conditions, etc.

Bytebase has ~100 standard built-in SQL Lint and supports configuring review rules that incorporate your business needs. You can choose one of the following [rule level](https://docs.bytebase.com/sql-review/review-policy#change-rule-level)s: `Error`, `Warning` or `Disabled`. When the rule is `Disabled`, it will not take effect. To better implement the rules, it is highly recommended that the DBA and the dev teams agree on the SQL review rules, and rule levels and decide which rules should be enabled at which stage. In prod, an application often includes multiple environments: from dev, testing, staging to prod. To balance efficiency and security, different review policies should be used at different stages. Still, it is recommended that SQL scripts should be included in VCS management from the testing environment. An example of a strategy:

![](/content/blog/database-cicd-best-practice/strategy.webp)

- When creating your scripts, the SQL statements are pre-checked by Bytebase's [VCS-integrated review capabilities](https://docs.bytebase.com/gitops/sql-review-ci) to help identify violations.
- During the execution phase, Bytebase will initiate a secondary check. The closer you get to prod, the tougher the rules.

## Defining an automated deployment workflow

Bytebase aims to fully automate the change release process. This is achieved by identifying script paths under a given code repository, so that each script can be associated with a given database. Let's look at an example of Bytebase's path matching template: `Base_DIR/{{{ENV_ID}}/**/{{DB_NAME}}__{{VERSION}}__{{TYPE}}__{{DESCRIPTION}}.sql`

In this directory structure,

- Base_DIR is the specified root directory (can be multi-level).
- `{{ENV_ID}}` should match the destined environment identifier of the database.
- \*\* is a wildcard to match one or more directories.
- `{{DB_NAME}}` is the database name to be changed.
- `{{VERSION}}` is the version number of this change.
- `{{TYPE}}` is the change type (DDL or DML).
- `{{DESCRIPTION}}` is an optional description string.

**By combining placeholders with wildcards, you can precisely match the target database managed by Bytebase in any directory structure.**

**If you manage scripts by application project**, the script categorization structure in VCS corresponds to the database categorization structure in Bytebase, and each Project in Bytebase monitors only the script changes in the directory corresponding to its own.

![](/content/blog/database-cicd-best-practice/script-by-project.webp)

**If you manage scripts by change batch**, the change scripts in each change in VCS have a one-to-many relationship with the database sorting structure in Bytebase, but each change script can still be precisely matched to the specified database by setting the placeholders appropriately.

![](/content/blog/database-cicd-best-practice/script-by-change-batch.webp)

## Finalize configuration in the Bytebase Console

The last thing we need to do is to implement the above in Bytebase.

This process can be divided into three main tasks:

1. **[Create a Project](https://docs.bytebase.com/get-started/step-by-step/create-a-project).** Divide the databases into groups according to defined database management boundaries, and assigning them to different projects, with the development team's management boundaries defined by the project and the DBA/Ops/Platform teams having global access.

   ![](/content/blog/database-cicd-best-practice/create-project.webp)

2. **[Create Schema review policy](https://docs.bytebase.com/sql-review/review-policy/#create-schema-review-policy).** Set up review policy for each environment, taking into account the specific policy and rule levels agreed upon with the development teams.

   ![](/content/blog/database-cicd-best-practice/schema-review.webp)

3. **[Set up GitOps](https://docs.bytebase.com/gitops/overview)**. Configure the GitOps workflow in a given project to monitor changes in the VCS and automatically kick start the review process.

   ![](/content/blog/database-cicd-best-practice/gitops.webp)

For a more detailed implementation, please refer to [How to Setup Database CI/CD with GitHub](https://docs.bytebase.com/tutorials/gitops-github-workflow).



The penultimate release of 2022 brings Bytebase from 1.9.0 to [1.9.1](/changelog/bytebase-1-9-1). Although it’s only a PATCH version, the 237 commits still bring us tons of new features. Let’s take a closer look at what's released during this World Cup cycle ⚽️.

## Schema Editor

First up is the [Schema editor](https://docs.bytebase.com/change-database/schema-editor).

For most developers, handwriting SQL is troublesome and error-prone. So, we made a visual Schema Editor that can generate SQL statements with just a few clicks.

![_](/content/blog/1-9-1-new-features/schema-editor.webp)

We included the support for MySQL and TiDB first, and PostgreSQL will be available soon.

## Need Attention

Next ups is a small yet sweet feature.

The issue creator might want to remind the reviewer during the review process. We added the **[Need Attention](https://docs.bytebase.com/change-database/change-workflow/#issue-need-attention)** feature, which adds a reminder bell to the Review interface. The corresponding issue title will become bold in the reviewer interface when you click it.

![_](/content/blog/1-9-1-new-features/need-attention-bell.webp)

![_](/content/blog/1-9-1-new-features/need-attention-bold.webp)

The overall interaction took inspiration from Google’s internal Code Review tool. I remember clearly the day I first saw a bold entry in my review list. I’ve used it ever since, and I think it can improve the process’s efficiency.

Regarding how to nudge people, some collaborative tools choose to ping others, others choose to use bolded font. Direct or restrained, the design philosophy is just included in these interaction details.

## Database Access Control

Now, let’s chat about Bytebase’s security capabilities.

We introduced [Database Access Control](https://docs.bytebase.com/security/database-permission/overview) with this release, leveraging Access Control Policy and Environment Tier.

Some users suggested that they don’t want developers to access all databases in the production environment by default. This can be achieved by the DBA setting the `Environment Tier` to `Protected`. But at the same time, the user wants to open access to some databases in this environment, which is possible by whitelisting those databases.

![_](/content/blog/1-9-1-new-features/protected-env.webp)

![_](/content/blog/1-9-1-new-features/accesss-control-env.webp)

The overall Bytebase design follows the same security practice of default blacklisting. This is yet another feature focusing on data security after [Data Masking](https://docs.bytebase.com/security/data-masking/overview).

## Data Masking

In the last release, we introduced data masking for MySQL.

In this cycle, we have enhanced its capabilities, and it now tops the mainstream goInception-based solutions in the market.

| Query Type                   | Bytebase | goInception |
| ---------------------------- | -------- | ----------- |
| Simple query statements      | ✔️       | ✔️          |
| Subquery                     | ✔️       | ❌          |
| NATURE JOIN                  | ✔️       | ❌          |
| JOIN ... USING(...)          | ✔️       | ❌          |
| Common Table Expression(CTE) | ✔️       | ❌          |

goInception claims to be able to mask data. Still, it’s a bit too easy to bypass😓. Companies, if you use goInception, I am sweating for your data security.

And unlike goInception, which only supports MySQL, we will soon bring full data masking capabilities to PostgreSQL.

## Summary

Well, that’s it for this release. The next one in two weeks will also be the last of 2022.

Our 2021 goal was to cover the basic capabilities needed in database development. From my recent communication with users, I can tell that this piece is pretty much achieved: when they ask if we have a particular feature, most of the time, we can answer YES.

Next year, our task will move from satisfying users’ pain points to gradually bringing them delights. Frankly speaking, compared with the coding tools, the database tools are still relatively primitive. Although existing database tools inherit DBA’s expertise, they lack an understanding of DevTools and the overall developer workflow.

At AWS re:Invent 2022, Amazon released its one-stop developer platform, CodeCatalyst, joining GitHub, GitLab, and JetBrains. All of these claim to be one-stop, but let’s be honest, none of them really is, because they haven’t scratched the surface of database development. The developer workflow is not self-contained without including the database development activity.

After all, databases are indispensable to most applications.

![_](/content/blog/1-9-1-new-features/amazon-codecatalyst.webp)

![_](/content/blog/1-9-1-new-features/github.webp)

![_](/content/blog/1-9-1-new-features/jetbrains.webp)

![_](/content/blog/1-9-1-new-features/gitlab.webp)



Related reading:

1. What is Database Change Management? (this article)
1. [Securing Database Change: From Bastion to Integrated Platforms](/blog/top-database-change-management-tools)
1. [State-based vs. Change-based Database Version Control](/blog/database-version-control-state-based-vs-migration-based)
1. [Database Version Control Best Practice](/blog/database-version-control-best-practice)
1. [The 6 Levels of Database Automation](/blog/database-automation-levels)

## Definition

**Database Change Management (DCM)** is the discipline of proposing, reviewing, deploying, and auditing every change that touches a database — both structure and data — the same way code changes flow through version control and CI/CD.

For relational databases, SQL is the medium. Changes fall into three categories:

- **DDL** (Data Definition Language) — schema: `CREATE`, `ALTER`, `DROP`.
- **DML** (Data Manipulation Language) — data: `INSERT`, `UPDATE`, `DELETE`.
- **DCL** (Data Control Language) — permissions: `GRANT`, `REVOKE`.

Most DCM tools focus on DDL. Mature DCM covers all three — because the outage risk, data risk, and compliance risk each live in a different category, and you can't manage one without touching the others.

In practice, those changes don't come from one place. Application servers, cron jobs, ad-hoc scripts, and humans on the CLI all converge on the same database:

![change sources](/content/blog/what-is-database-change-management/change-path.webp)

That's what DCM has to wrangle.

## Why It's Hard

Writing a migration is easy. Shipping it safely across a team, across environments, across time is hard. The recurring failure modes:

### 1. Uncoordinated changes

Two developers rename the same column in different branches. A DBA adds an index in production directly. An application deploys a migration while another service is mid-read.

![uncoordinated changes](/content/blog/what-is-database-change-management/common-situation.webp)

Without a single place where every change is proposed and serialized, you get [schema conflicts, lost changes, and outages](/blog/schema-migration-outage-fault).

### 2. Environment drift

Dev, staging, and production drift apart. A column exists in dev but not in prod. A constraint is active in staging but disabled in prod because "it was slow." The next migration assumes a shape that only exists in one environment, and the deployment to the other two breaks.

### 3. Irreversible mistakes in production

`DROP TABLE` ran against the wrong database. A `DELETE` without a `WHERE` clause landed before anyone caught it. A migration locked a 50M-row table for 20 minutes during peak traffic. Each of these is a specific failure with a specific prevention — but only if changes go through review before they reach production.

### 4. Compliance and audit gaps

A SOC 2 auditor asks: "Who approved the schema change that touched the PII table on March 14, and what was the SQL?" If the answer lives in Slack screenshots, Jira tickets, and a DBA's memory, you have a problem. Audit trails need to be automatic, tamper-evident, and queryable.

### 5. Rollback complexity

Most DDL is not cleanly reversible — `DROP COLUMN` loses the data; `ALTER TABLE` doesn't have a built-in undo. A change made in a hurry without a [rollback plan](/blog/database-rollback) becomes a recovery project. DML changes are worse because you have to reconstruct state, not just schema.

### 6. Access sprawl

The `root` credential is in five config files. Seven developers have direct prod access "for emergencies." No one knows who ran the last hand-typed `UPDATE`. [Access control and query control](/blog/database-access-control-best-practices) need to be part of DCM, not a separate track.

## The Ideal Workflow

Modern DCM treats database changes like code changes:

![ideal workflow](/content/blog/what-is-database-change-management/ideal-situation.webp)

1. **Propose** — a developer writes the SQL (or generates it from a schema definition) and submits it as a change request — the database equivalent of a pull request.

2. **Automated review** — the system lints the SQL against a rule set: naming conventions, destructive operations without backups, missing indexes, `UPDATE` without `WHERE`, etc. Modern [SQL review](/blog/is-sql-review-necessary) engines carry 200+ rules and catch the common mistakes before a human looks at the change.

3. **Human review** — a DBA or platform engineer reviews the intent, the business impact, and anything the linter can't catch (e.g., "does this migration preserve the invariant the finance team depends on?").

4. **Approval** — depending on risk, the change routes through a [custom approval flow](https://docs.bytebase.com/change-database/approval) — e.g., DDL on prod requires DBA + manager sign-off; DML touching PII requires security review.

5. **Staged rollout** — the change deploys through environments in order: dev → staging → prod. Each stage is a checkpoint. Cross-database or cross-tenant rollouts run as a single batch with canary support.

6. **Audit** — every action — who proposed, who approved, what SQL ran, when, against which database — is recorded immutably. The audit log is the answer to every compliance question.

7. **Rollback plan** — for any change beyond trivial, the rollback SQL is written and stored alongside the forward migration.

## State-based vs. Change-based

Two philosophies for how changes are expressed:

**Change-based (imperative)** — you write the migration SQL yourself: `ALTER TABLE orders ADD COLUMN status VARCHAR(20);`. Each change is a numbered, ordered file. Flyway, Liquibase, and Bytebase's default workflow use this model.

**State-based (declarative)** — you define what the schema should look like, and the tool computes the diff and generates the migration automatically. Atlas, Schemachange, and Bytebase's [state-based workflow for PostgreSQL](https://docs.bytebase.com/gitops/state-based-workflow/overview) use this model.

| | Change-based | State-based |
|---|---|---|
| Source of truth | Ordered migration files | Desired schema (HCL, SQL, ORM) |
| Data migrations | Natural fit — you write the SQL | Awkward — declarative models describe shape, not data movement |
| Review diff | The migration file itself | Tool-generated SQL (must be reviewed before apply) |
| Drift detection | Hard — requires reconciling applied migrations with live schema | Built in — diff against desired state |
| Rollback | Explicit `DOWN` migrations (if you wrote them) | Re-apply previous state |

Most real teams run a [hybrid](/blog/database-version-control-state-based-vs-migration-based) — state-based for greenfield schema work, change-based for data migrations, conditional backfills, and anything stateful.

## How to Evaluate a DCM Tool

The market has many tools — desktop clients with migration features, CLI migration engines, full platforms. When picking one, look at:

1. **SQL review depth** — how many rules, how configurable per environment, engine-specific coverage. A tool that catches "missing index on a foreign key" saves more incidents than one that only lints syntax.

2. **Approval flow flexibility** — can you define rules like "DDL on prod requires DBA approval, DML touching PII requires security approval"? Or is it just "one approver clicks yes"?

3. **Multi-environment and multi-tenant rollout** — can a single change deploy across dev → staging → prod with gates? Across 500 tenant databases with canary?

4. **GitOps integration** — does committing SQL to Git automatically create a reviewable change? Does the SQL review result show up in the PR?

5. **Access control and data masking** — DCM that ignores DCL is only doing half the job. Can it enforce role-based access, [dynamic data masking](https://docs.bytebase.com/security/data-masking/overview), and just-in-time data access?

6. **Audit log** — is every action recorded with user, time, SQL, and target? Is it queryable and exportable?

7. **Rollback support** — can the tool store and execute rollback SQL? Can it detect and flag [schema changes made outside](/blog/database-version-control-best-practice) the tool?

8. **Engine coverage** — not just "does it connect" but "does it understand engine-specific syntax and best practices" (e.g., MySQL online DDL, PostgreSQL `CONCURRENTLY`, Oracle invisible indexes).

9. **Deployment model** — self-hosted for air-gapped environments? Cloud for fast onboarding? Both?

10. **Developer experience** — GUI for DBAs, CLI/API for automation, IDE integration for developers. The tool gets used more when friction is low.

For a broader breakdown, see [Securing Database Change: From Bastion to Integrated Platforms](/blog/top-database-change-management-tools).

## DCM and DevOps

Classical DevOps moved application deployment from ticket-driven ops to code-driven pipelines. DCM does the same for databases. The [6 levels of database automation](/blog/database-automation-levels) are the maturity ladder:

- **Level 0** — SSH into prod, run SQL by hand.
- **Level 1** — SQL files in Git, but applied manually.
- **Level 2** — migration tool runs on deploy, no review.
- **Level 3** — changes go through review + approval before deploy.
- **Level 4** — full platform: review, approval, GitOps, multi-env rollout, audit, access control, masking.
- **Level 5** — autonomous operation with policy-as-code, drift detection, and automatic remediation.

Most teams sit at Level 2. The gap from Level 2 to Level 4 is where DCM earns its keep — that's where outages stop happening on Fridays and auditors stop asking uncomfortable questions.

## How Bytebase Approaches DCM

[Bytebase](https://www.bytebase.com/) is a database DevSecOps platform built around this workflow:

![bytebase project](/content/blog/what-is-database-change-management/project.webp)

- **Change as issue** — every DDL/DML change is proposed as an issue (the database equivalent of a PR).
- **SQL review** — 200+ engine-specific rules, configurable per environment.
- **Custom approval flow** — route changes through rules based on environment, change type, or data sensitivity.
- **Multi-environment rollout** — single issue, staged deployment across dev → staging → prod.
- **GitOps** — GitHub, GitLab, Bitbucket, Azure DevOps — SQL files in Git become reviewable change issues.
- **Access control + data masking** — workspace/project roles, column-level dynamic masking, just-in-time access.
- **Audit log** — every action, every user, every target, queryable and exportable.
- **23 engines** — 9 RDBMS, 6 NoSQL, 7 data warehouses, plus Elasticsearch.

It's the [only database change management solution acknowledged by CNCF](https://landscape.cncf.io/?selected=bytebase), with growing adoption over the incumbents:

![star history](/content/blog/what-is-database-change-management/star-history.webp)

## FAQ

### Is DCM the same as schema migration?

No. Schema migration is one part of DCM — specifically the DDL part. DCM also covers DML (data changes), DCL (permissions), review workflow, audit, and access control. A migration tool like Flyway or Liquibase handles schema migration. A DCM platform handles the full lifecycle.

### Do I need DCM if my team is small?

If one person writes and deploys every database change and no compliance auditor is asking questions, probably not. The moment a second person can change production — or the moment you need an audit trail — you need DCM. Starting early is cheaper than retrofitting.

### Is DCM just "database DevOps"?

Overlapping but not identical. Database DevOps is the broader cultural and tooling shift toward treating databases like code. DCM is the specific discipline inside that shift focused on proposing, reviewing, deploying, and auditing changes. DCM is the "change review + approval + rollout + audit" slice of database DevOps.

### Can I do DCM with just Git and CI?

To a degree — you can store SQL files in Git, have them reviewed in PRs, and run a migration tool on deploy. That gets you to Level 2–3 in the automation ladder. What Git + CI can't give you: engine-specific SQL review, runtime access control, data masking on query results, dynamic data access requests, or an audit trail that covers direct database connections (not just CI-driven changes).

### What's the difference between state-based and change-based?

Change-based: you write the migration SQL (ordered files). State-based: you declare the desired schema and the tool generates the migration. State-based is cleaner for schema work; change-based is necessary for data migrations. Most teams use both. See [State-based vs. Change-based](/blog/database-version-control-state-based-vs-migration-based).

### Does DCM cover data changes or just schema?

Both. A mature DCM platform treats DML changes with the same review rigor as DDL — a `DELETE FROM orders WHERE ...` that runs against production is as consequential as a `DROP COLUMN`. Review, approval, audit, and rollback apply equally.

---

Further reading:

- [Database Version Control](/blog/database-version-control)
- [Securing Database Change: From Bastion to Integrated Platforms](/blog/top-database-change-management-tools)
- [State-based vs. Change-based Database Version Control](/blog/database-version-control-state-based-vs-migration-based)
- [Database Version Control Best Practice](/blog/database-version-control-best-practice)
- [Database Rollback](/blog/database-rollback)
- [How to Build a CI/CD Pipeline for Database Schema Migration](/blog/how-to-build-cicd-pipeline-for-database-schema-migration)
- [The 6 Levels of Database Automation](/blog/database-automation-levels)
- [Database Multi-Environment Deployments](/blog/database-multi-environment-deployments)
- [Database Access Control Best Practices](/blog/database-access-control-best-practices)
- [Database as Code](/blog/database-as-code)



## Overview

The review of SQL change scripts is a critical part of the database change process, which used to be
the sole responsibility of the DBA. Developers often submitted the SQL scripts to the DBA just before
the changes go live, leaving the DBAs minimal time to review the scripts (or request changes), making it difficult to ensure the quality of changes.

Bytebase is a database CI/CD tool for developers to make sane database changes with powerful
automatic SQL Review capabilities. To better accommodate developers' workflow, Bytebase integrates
its capabilities into GitHub, allowing developers to manage SQL scripts in their GitHub repositories
and perform SQL Review there. This eliminates the need to switch between multiple tools and, more
importantly, allowing SQL Review to be conducted at the Pull Request (PR) stage rather than waiting
until the deployment stage (shift-left).

![shift-left](/content/blog/integrate-sql-review-into-github/shift-left.webp)

To cope with different needs, Bytebase provides three integration modes for users to choose from:

- [GitHub Action](#github-action)
- [GitHub App](#github-app)
- [GitOps CI](#gitops-ci)

In this article, we will introduce the differences between the three modes in detail.

## GitHub Action

Bytebase automates GitHub Action-based SQL Review by simply putting pre-generated workflow files
into the `.github` directory to integrate SQL Review rules into the CI process.

### Setup

1. Obtain the GitHub Action workflow file

   Visit the [Bytebase GitHub Action](https://github.com/marketplace/actions/sql-review) to fetch
   the appropriate configuration file.

1. Manually create a workflow file to activate the default SQL Review rules

   You need to manually create the workflow file `.github/workflows/sql-review.yml` in your repository
   and modify a few key parameters based on the provided template to cater to your needs.

1. Customize the SQL Review template

   If you are not satisfied with the default review rules, Bytebase provides a visual SQL Review
   rule configuration page [SQL Review Guide](/sql-review), where you can export your custom
   rule to a YAML file with one click, and point the override-file-path parameter in the Action
   workflow file to that file to make the custom rule take effect.

   The detailed configuration process can be found in the [GitHub Action document](https://docs.bytebase.com/sql-review/github-action).

### Result

Once configured, when SQL is detected in the submitted PR, the SQL Review process will be
triggered automatically and the detailed review results will be displayed inline in the changed
file.

![file-comment](/content/blog/integrate-sql-review-into-github/file-comment.webp)

### Pros and Cons

✅ **Free.** Users can use GitHub Action for free.

✅ **Lightweight.** Users don't need to deploy any additional components, just generate a single file
in the code repository and activate it.

✅ **Multiple database types are supported at the same time.** You need to explicitly specify the
database type by file path to support SQL Review for multiple databases at the same time.

❌ **More complex configuration.** Initial configuration or updating SQL Review rules is more complex
due to the need to manually edit YAML.

❌ **Basic SQL Review capability.** Due to the inability to connect to a database, this mode can only
review SQL text based on static rules.

❌ **Single active SQL Review policy.** Only one set of SQL Review rules are allowed to be active at
the same time.

❌ **Single presentation.** SQL Review results can only be viewed in the file being reviewed and
there is no overview of SQL Review results.

❌ **CI-only.** This mode only covers SQL Review and is not integrated with the subsequent SQL
deployment processes.

## GitHub App

You can install the Bytebase GitHub App into your repository and integrate the SQL Review process
into the CI.

### Setup

1. Install the [Bytebase GitHub App](https://www.bytebase.com/docs/sql-review/github-app)
   and activate SQL Review by connecting with a specific repository.
1. Customize SQL Review Rules

   You can visually configure your own SQL Review rules at [hub.bytebase.com](https://hub.bytebase.com),
   and all changes will take effect immediately. See the [GitHub App documentation](https://www.bytebase.com/docs/sql-review/github-app) for more details on the configuration process.

### Result

Once configured, the review process will be triggered automatically when SQL is detected in the
submitted PR.

You can see the overview of the SQL Review results in the PR overview.

![file-comment](/content/blog/integrate-sql-review-into-github/file-comment.webp)

Per file detailed review results will also be displayed inline in the file.

![pr-comment](/content/blog/integrate-sql-review-into-github/pr-comment.webp)

### Pros and Cons

✅ **Free.** Users can use the APP for free.

✅ **Lightweight.** Users don't need to deploy any additional components, just one click to deploy the
GitHub App and activate it.

✅ **Easy to configure.** Point-and-Click configuration.

✅ **Comprehensive presentation.** You can view the SQL Review results in both the PR overview and
per-file page.

❌ **Basic SQL Review capability.** Due to the inability to connect to a database, this mode can only
review SQL text based on static rules.

❌ **Explicitly specify the database type.** Due to the inability to connect to a database, this schema
can only be explicitly configured to take effect on one of the supported database types.

❌ **Single active SQL Review policy.** Only one set of SQL Review rules are allowed to be active at
the same time.

❌ **CI-only.** This mode only covers SQL Review and is not integrated with the subsequent SQL
deployment processes.

## GitOps CI

The Bytebase Console-based integration mode provides more powerful functionality:

1. Unlock more advanced SQL Review rules by connecting with the changed database.
1. Integrate with the subsequent CD processes.

### Setup

1. Deploy Bytebase Console and complete the basic configuration

   This mode requires the deployment of Bytebase Console, and you need to import your database environment into Bytebase.
   Please refer to [Bytebase Installation Guide](https://www.bytebase.com/docs/get-started/deploy-with-docker)
   for the detailed process.

1. Activate SQL Review CI with one click

   The GitHub Action can be configured automatically by configuring GitOps Workflow in the Project
   Configuration page from Bytebase Console.

1. Customizing SQL Review rules

   If you are not satisfied with the default SQL Review rules, you can configure it visually from the
   Bytebase Console and all changes will take effect immediately.

   See the [GitOps CI documentation](https://www.bytebase.com/docs/gitops/sql-review-ci)
   for details on how to do this.

### Result

Once configured, the review process will be triggered automatically when SQL is detected in the
submitted PR.

![file-comment](/content/blog/integrate-sql-review-into-github/file-comment.webp)

### Pros and Cons

✅ **Easy to configure.** Point-and-Click configuration.

✅ **Advanced SQL Review capabilities.** Because Bytebase can connect to the database to fetch the
structure and statistic, it can provide more advanced SQL Review capabilities.

✅ **Multiple SQL Review policies in effect at the same time.** Multiple SQL Review policies can be
configured, associated with specific environments (Test, Prod).

✅ **Integration with CD.** Automatically triggers the subsequent SQL deployment process after
the SQL Review passes and the code is merged, enabling a streamlined database CI/CD experience.
This unifies the SQL Review standards between the application developer and DBA teams.

✅ **No need to specify database type.** Bytebase can connect to the database to determine the database
type automatically.

❌ **Paid subscription.** Bytebase Enterprise subscription is required.

❌ **More setup.** Users need to deploy Bytebase Console and prepare the database environment.

❌ **Single presentation.** SQL Review results can only be viewed in the file being reviewed and
there is no overview of SQL Review results.

## Comparison Table

|                       | GitHub Action                  | GitHub App                                    | GitOps CI                                                          |
| --------------------- | ------------------------------ | --------------------------------------------- | ------------------------------------------------------------------ |
| For                   | **Individual and small teams** | **Individual and small teams**                | **Teams needing complete database change management**              |
| Pricing               | Free                           | Free                                          | Enterprise subscription                                            |
| Setup                 | Deploy GitHub Action           | Deploy GitHub App                             | Deploy Bytebase Concole and prepare database environment           |
| Configuration         | Code-based with UI assistance  | Point-and-Click                               | Point-and-Click                                                    |
| SQL Review Capability | Pure SQL statement based       | Pure SQL statement based                      | SQL statement + database metadata and statistics                   |
| Result Presentation   | Per-file inline comment        | Per-file inline comment + PR overview comment | Per-file inline comment                                            |
| Policy                | Single active policy           | Single active policy                          | Multiple active policies, auto match with the database environment |
| CI/CD Integration     | SQL Review only                | SQL Review only                               | SQL Review and SQL change deployment, streamlined CI/CD            |



[This release](/changelog/bytebase-1-9-0) brings Bytebase back to its regular schedule: once every two weeks. The version is upgraded from 1.8.0 to 1.9.0, the MINOR version upgrade also means that we have evolved Bytebase's schema again. With a total of 205 commits, let's check out what's new. 👇

![_](/content/blog/1-9-0-new-features/1-9-0-commits.webp)

## Bytebase Terraform Provider

First up is the [Bytebase Terraform Provider](https://registry.terraform.io/providers/bytebase/bytebase/latest/docs).

GitHub’s [Octoverse 2022](https://octoverse.github.com/) mentioned that HCL is the fastest-growing language on GitHub, and HCL corresponds to the popularity of Terraform.

![_](/content/blog/1-9-0-new-features/octoverse-2022.webp)

Whether you're a public cloud powerhouse or a SaaS newcomer, developing a Terraform Provider for your service has become a standard. As part of the toolset to manage your infra, Bytebase has received requests from users who wish to use Terraform to manage Bytebase resources.

![_](/content/blog/1-9-0-new-features/bytebase-terraform-provider.webp)

We present the first preview to manage your Bytebase **Environment** and **Database Instance**. Terraform and Bytebase are a natural pair, as Terraform provisions the database environment and then passes the information to the Bytebase Terraform Provider. A few HCL files are all it takes to automate the entire database development environment setup.

A few more iterations, and we will have a killer solution.🔥

## Schema Sync for PostgreSQL

After introducing [schema sync for MySQL](/blog/how-schema-sync-work) library table synchronization some time ago, we now bring you [schema sync for PostgreSQL](https://docs.bytebase.com/change-database/synchronize-schema).

We are confident that Bytebase's schema sync for PostgreSQL solution is the best in the industry because we have the best PG Parser on the market (and is also a constant WIP to perfect it).

There is a lot more that a good parser can do. We have invested a lot of R&D work upfront and now we start to receive dividends to implement features like schema synchronization elegantly.

![_](/content/blog/1-9-0-new-features/pg-schema-sync.webp)

## Data Masking

Previously, Bytebase was focused on database change management (DCM). And another critical piece is data security. This release is our first footstep into this territory, with the ability to [mask database fields by tagging them](https://docs.bytebase.com/security/data-masking/overview).

There's not much to say about the feature alone, after all, many of our friends have them too. The difference is that Bytebase is only here now because we have just laid the groundwork for the system.

Bytebase will launch a series of features around data security, bringing the capabilities previously only available on Oracle and SQL Server platforms to open-source databases like MySQL and PostgreSQL.

And we want to make it easy to use, make developers, DBAs, and audit teams happy, and eventually make the auditors at the Big Four accounting firms happy.

When auditors arrive at a company for an IT data audit and see that they're using Bytebase to manage their databases, they get a slight tingle of joy because they know they won't have to work late today.

Auditors, wait for it.

![_](/content/blog/1-9-0-new-features/data-masking.webp)

## SQL Editor Admin Mode

For the finale, we present [SQL Editor Admin mode](https://docs.bytebase.com/sql-editor/admin-mode) with a completely fresh look.

The last release introduced Admin mode, which allows DBAs to execute admin commands. After two weeks of iteration, we have upgraded the Admin mode, to a more command line-like user experience.

A picture is worth a thousand words. I'll bet $1,000 that of the 4 features introduced today, this must be DBAs’ favorite.

![_](/content/blog/1-9-0-new-features/sql-editor-admin-mode.webp)

## Summary

It’s already Dec. 2022, except for the team building last month, we have maintained a steady two-week delivery rhythm this year.

Looking back at the roadmap I set for Bytebase at the beginning of this year, I think we are mostly on track. There are some regrets, but there are also a lot of surprises from the team and our users.

When I finished Bytebase's core modeling over a year ago, I was a bit insecure, not knowing if the model would stand the test of time. After a year of iterations and user scenarios, it has remained stable. Upon the core model, Bytebase has broadened its boundaries from MySQL to PostgreSQL, from GitLab to GitHub, and from data change to data security.

In 2023, we'll release a cloud version, returning to our team's old roots of cloud services. ☁️

See you next time.



Last week, we released [Bytebase 1.8.0](/changelog/bytebase-1-8-0). Let's take a deeper look into some of the new features.

## MySQL Sync Schema

![_](/content/blog/1-8-0-new-features/mysql-sync-schema.webp)

You can now specify to synchronize the schema from database A to database B, and Bytebase will automatically generate the intermediate DDL statements to be executed.

Usually, developers make database changes in the test environment first and then apply the changes to the prod environment afterward. Before `Sync Schema`, you need to carefully write out the statements that need to be changed by themselves. Now, you can simply let Bytebase know which database schema needs to be applied to the target database.

We are planning to add Sync Schema support for PostgreSQL soon.

## Admin Mode for SQL Editor

![_](/content/blog/1-8-0-new-features/sql-editor-admin-mode.webp)

A key selling point of Bytebase is that all scenarios that need database access can be done through Bytebase (aside from the app itself accessing the database). Before this release, Bytebase could already perform database changes and queries on behalf of the application developers.

However, dedicated DBAs, or those responsible for database operations in DevOps teams, need to perform higher privileged operations.`Admin Mode` meets this need, and it is only available to DBAs or Owner role holders in a workspace and needs to be switched on manually in the SQL Editor.

Thus, all scenarios in which human beings deal with databases can be performed via Bytebase, except for those where the application deals with the database. Companies no longer need to distribute database access keys or configure separate bastion/jump servers, as Bytebase controls access and operations. Bytebase also identifies dangerous statements, reducing the chance of human mistakes, while the DBA can configure SQL review policy and control database access at the global level.



This is a series of articles about database schema change / database schema migration:

1. What is a Database Schema? (this one)
1. [What is a Database Schema Migration?](/blog/what-is-database-migration)
1. [How to Handle Database Migration / Schema Change?](/blog/how-to-handle-database-schema-change)
1. [Top Database Schema Migration Tools](/blog/top-database-schema-change-tool-evolution)

---

A database management system (DBMS) consists of three main components:

- The database server
- The database data
- The database schema

The database server is a computation component and is stateless. The database data holds the state while the database schema holds the structure of the state (metadata). Database users often use SQL to instruct the database server to manipulate the underlying database data. SQL stands for _Structured Query Language_ which is based on the structure that the database schema provides.

## The database server

The database server is a process or a set of processes running inside a computation environment. The computation environment provides processing power. It could be a physical computer, a [virtual machine](https://en.wikipedia.org/wiki/Virtual_machine) or a lightweight [container](https://en.wikipedia.org/wiki/OS-level_virtualization).

## The database data

The database data records the business application information such as order information, customer profile, etc. The database data is stored on the physical disk. People always use a cylinder to represent a database because, in old days, database data is stored there. Oracle, the world's largest database company, headquarters are a set of cylinder buildings.

![_](/content/blog/what-is-database-schema/oracle-headquarter.webp)

## The database schema

The database schema describes the **logical structure** of the database data. It often resides together with the database data. A more general term is metadata, but in the database domain, it's always referred to as database schema or just schema. Other synonyms include data dictionary, data catalog, etc.

In the formal definition, a database schema is a set of formulas called integrity constraints. These constraints are defined as various database objects such as tables, fields, relationships, views, indexes, stored procedures, triggers, etc.

![_](/content/blog/what-is-database-schema/employee-schema.webp)

Above is a database schema for a database storing the employee data. The database schema is the blueprint of how the database data is organized. A DBMS can have a database schema with empty data, but not vice versa. The database data is meaningless without the database schema.

## Database schema vs. database

A database holds the actual data (rows and records). A schema defines the structure of that data (tables, columns, types). One common source of confusion: PostgreSQL and MySQL use the word "schema" differently.

| | PostgreSQL | MySQL |
|---|---|---|
| `CREATE SCHEMA` | Creates a namespace within a database | Synonym for `CREATE DATABASE` |
| A database can contain | Multiple schemas (`public`, `sales`, `analytics`) | One schema (the database itself) |
| Default schema | `public` | N/A |

In PostgreSQL, a single database can have multiple schemas, each with its own set of tables. In MySQL, "schema" and "database" mean the same thing. SQL Server uses schemas as security boundaries within a database, similar to PostgreSQL's namespaces.

## Types of database schemas

Database schemas exist at three levels of abstraction:

| Level | Audience | Contains |
|---|---|---|
| **Conceptual** | Business stakeholders | Entities and relationships, no technical detail |
| **Logical** | Developers and architects | Tables, columns, keys, constraints (database-agnostic) |
| **Physical** | DBAs and infrastructure | Storage engine, indexes, partitions, database-specific syntax |

In practice, most teams start at the logical level. The physical schema is what actually runs in your database: the SQL [DDL](/blog/what-is-ddl-and-dml-in-sql-database/) statements that create tables, indexes, and constraints.

For naming conventions and structural decisions, see [Top 10 Database Schema Design Best Practices](/blog/top-database-schema-design-best-practices/). For broader architectural patterns like normalization, star schema, and multi-tenant designs, see [Database Design Patterns Every Developer Should Know](/blog/database-design-patterns/).

## How database schemas evolve

Database schema evolves as the business evolves. These changes are called [schema migrations](/blog/what-is-database-migration/), and they require more care than application code changes because a bad migration can lock tables, cause downtime, or break running applications.

Teams typically manage schema changes through versioned migration files. For the full process, see [How to Handle Database Schema Change](/blog/how-to-handle-database-schema-change/).

When schema changes happen outside the migration process (manual `ALTER TABLE` in production, for example), the actual database structure drifts from what the migration files describe. This is called [schema drift](/blog/what-is-database-schema-drift/), and it causes deployment failures and data inconsistencies.

[Bytebase](/) is a database DevSecOps platform that helps teams manage this lifecycle with SQL review, schema drift detection, and migration history tracking across multiple databases.

## FAQ

**What is the difference between a schema and a database instance?**

A schema is the structure (tables, columns, types). An instance is the data stored in that structure at a given point in time. The schema rarely changes; the instance changes with every INSERT, UPDATE, or DELETE.

**Can a database exist without a schema?**

Not in a relational database. Every table has a defined structure. NoSQL databases like MongoDB are sometimes called "schemaless," but they still have implicit structure in the documents they store.

**What tools can I use to visualize a database schema?**

Most database GUI clients (DBeaver, pgAdmin, MySQL Workbench) can generate ER diagrams from an existing database. See [Top Database Schema Diagram Tools](/blog/top-database-schema-diagram-tools/) for more options.



SQL review is the practice of checking SQL changes (schema migrations and data operations) for correctness, safety, and policy compliance before they run against a database. It sits between SQL linting, which catches syntax and style, and code review, which is a human reading a pull request. The scope of SQL review is narrower than either. It is automated, database-aware, and in mature implementations it lives inside the change workflow with an approval gate attached. See [what is database change management](/blog/what-is-database-change-management/) for the broader category.

## The three layers of SQL review

Most tools called "SQL review" occupy one, two, or all three of these layers. The category confusion in the market comes from the fact that a layer 1 tool and a layer 3 tool both answer "do you do SQL review?" with "yes."

### Layer 1: linting

SQLFluff is the clearest example. A linter checks SQL for syntax correctness and style conventions: consistent keyword casing, correct indentation, no forbidden functions. This is the same category of work as Prettier or ESLint for JavaScript. Useful, and it prevents style arguments in code review, but it does not know what the SQL does.

### Layer 2: semantic rules

Layer 2 adds rules that understand the database. A missing primary key, an `ALTER TABLE` that rewrites a large table synchronously, a `DELETE` with no `WHERE` clause, a foreign key column with no index: these are not syntax errors, but they will hurt in production. Example rules at this layer:

- [Require a primary key](/blog/sql-review-rule-explained-require-primary-key/) on every table, so rows are addressable and replication works.
- [Prohibit cascading deletes](/blog/sql-review-rule-explained-prohibit-cascade/) in foreign key constraints, so a `DELETE` on one row does not silently wipe dependent rows.
- [Enforce `NOT VALID` check constraints](/blog/sql-review-rule-explained-enforce-not-valid-in-check/) and follow them with a validation step, so partially-validated constraints do not linger.
- Require an index on every foreign key column, so joins on the FK do not scan the entire child table.

SQLFluff does not cover these. SonarQube covers a handful. Database-native review tools (Liquibase Quality Checks, Bytebase SQL Review, Flyway Teams SQL checks) cover dozens to hundreds.

### Layer 3: policy and approval

Layer 3 is where SQL review becomes governance. The layer decides not only whether the SQL is clean, but whether the person can apply it, to which environment, under what approval. A rule that warns in dev may block in prod. A change touching a PII-classified table may require DBA sign-off regardless of the SQL itself. Exemptions are logged with the justification. This is the layer where static linters stop.

## Static linters vs review platforms vs DBA review

Not every tool called a "SQL review tool" does the same work. The practical differences show up in where the review runs, what rules it can enforce, whether policy is per-environment, and whether human approval lives inside the review or has to be stitched in from the pull request.

| Tool | Rule count | Per-env policy | Human approval in the review | Where review runs |
|---|---|---|---|---|
| SQLFluff | ~30 style rules | No | No | CLI · CI · Python lib |
| SonarQube | ~20 SQL rules | No | No | CI · IDE · REST API |
| Liquibase Quality Checks | 60+ (Pro) | Partial | No (PR gate external) | CLI · CI · Java lib |
| Flyway Teams SQL checks | 74+ (SQLFluff + Redgate rules) | No | No (PR gate external) | CLI · CI · Java lib |
| Redgate SQL Prompt | Linting only | No | No | IDE |
| **Bytebase** | **200+ (all tiers)** | **Yes** | **Yes (in change issue)** | **GUI · CI · REST API** |
| Manual DBA review | Varies | Ad hoc | Yes | Email / Slack |

Two columns in this table matter more than they look. Per-environment policy is where governance starts: a linter with the same rule in dev and prod forces teams to pick one, either lax enough that nothing blocks or strict enough that dev is painful. Per-environment policy lets the rule warn in dev and block in prod, which is what teams actually need.

"Human approval in the review" is where the approval flow lives. In most stacks the approver reads a PR diff in GitHub while the SQL review results sit in a CI log or a separate console. Seeing the rule result and the approval decision in the same pane is a different UX, and it is the one that changes whether the approver notices the violations.

## How Bytebase does SQL review

Bytebase exposes the same rule engine through three paths: the GUI change issue, a CI pipeline check, and a REST API. The rules, the per-environment policy, and the audit trail are shared. The difference is where the developer or connected tool submits the SQL.

**Change issue (GUI).** The primary path. A developer opens an issue for the target database, pastes or uploads the SQL, and submits. Rules run inline against the target environment's policy: warnings for style, errors for safety violations. The approver opens the same issue. Rule results, the SQL itself, the environment policy, and the approval button all sit in the same pane. The approver is reviewing the SQL and its rule output at the same time, which is the point of the design.

A concrete example: a developer submits `ALTER TABLE users DROP COLUMN email`. The rule engine flags the change as non-additive (destructive, because an active application may still reference the column). The issue routes to a DBA reviewer per the production environment's policy. The DBA opens the issue, sees the destructive-change warning alongside the SQL, checks the deployment plan, and rejects with a comment pointing at the application code still reading the column. One issue holds the SQL, the rule violation, the review decision, the approver, and the audit record. For the compliance side of that trail, see [audit log for SOC 2](/blog/soc2-audit-logging/).

**CI / GitOps.** For teams with a GitOps database workflow, the same rule engine runs as a check on pull requests in GitHub or GitLab. Violations show up as PR comments and a CI status. The trade-off against the GUI path: the approver is now a code reviewer reading the PR diff, and the rule output lives in the CI log, a click away. Setup details are in [integrate SQL review into GitHub](/blog/integrate-sql-review-into-github/).

**REST API.** The same engine is also exposed via a REST API, language-agnostic, so calling it does not require running a JVM the way embedding Liquibase or Flyway does. This is how teams wire Bytebase into Jenkins shell steps, ServiceNow-driven approvals, or internal dashboards that are not covered by the native GitHub/GitLab integration.

In the teams we work with, the reason Bytebase ends up in the stack is usually this: their CI already runs SQLFluff or a schema check, but they still need a human to sign off on production DDL. The automated check and the human approval normally live in separate tools and separate queues, and the audit trail gets stitched together after the fact. Consolidating them into one workflow is the differentiator they hired Bytebase for.

## FAQ

**What is SQL review?**
SQL review is the automated check of SQL changes against a set of rules (semantic, performance, and policy) before the SQL runs against a database. It sits between SQL linting (syntax and style only) and code review (a human reading a pull request). In mature implementations, SQL review also includes a human approval gate where the approver sees the rule results alongside the SQL.

**Does SonarQube do SQL review?**
SonarQube lints SQL for code-quality issues at the source level. It does not know which environment the SQL will run in, does not gate on approval, and does not sit in the database change workflow. It is a different category of tool from a database-aware SQL review platform like Bytebase or Liquibase Quality Checks.

**What rules does SQL review check?**
Rule categories across major SQL review tools fall into five groups: syntax and style (casing, forbidden functions), naming (table/column conventions), safety (non-additive schema changes, `DELETE` without `WHERE`), performance (missing indexes, inefficient joins, large synchronous `ALTER TABLE`), and schema compatibility (breaking changes, FK integrity). Rule counts range from ~30 in SQLFluff to 200+ in Bytebase. Not every tool covers every category.

**What's the difference between SQL linting and SQL review?**
Linting is layer 1: syntax, style, and format. SQL review adds layer 2 (semantic rules that understand the database) and layer 3 (per-environment policy with approval gates). A linter can live in an IDE or a CLI. A full SQL review runs inside the change workflow because the rule results inform the approval decision.

**Is SQL review the same as code review?**
No. Code review is a human reading a pull request to evaluate design and correctness. SQL review is automated rule enforcement plus, in platform implementations, a human approval gate. The two are complementary: code review reads the SQL's intent, SQL review checks it against the rules and the production policy before it runs.



Developers (Dev), Site Reliability Engineer (SRE), and Operations (Ops) are job types, while DevOps is a system. If we take a soccer analogy, Dev, SRE, and Ops correspond to positions like forward, midfield, and back, while DevOps is a formation like [4-3-3](<https://en.wikipedia.org/wiki/Formation_(association_football)#4%E2%80%933%E2%80%933>).

## Dev

Also called Development Engineer, Software Engineer (SWE), Software Developer, or simply Developer (Dev). Their primary responsibility is to write code and implement software business logic. The logic could be fulfilling a grocery order, scheduling a cab to your place, etc. Developers mainly deal with the code.

## Ops

Also called IT Operations, Production Engineer (PE), or simply Operations (Ops). They are responsible for data center management, machine installation, network wiring, monitoring, and alerting. In the early days, a large amount of the operations’ work deals with physical equipment. This work requires a lot of manual intervention and is risky. In the last ten years, as Cloud services gradually replace the physical data center, the traditional operations workload has been significantly reduced, and the pure Ops job has ceased to exist.

_Veterans all remember the exciting days of carrying such a pager to be standby 7x24_

![dev-sre-ops-intersection](/content/blog/dev-sre-ops-devops-difference/pager.webp)

## SRE

Site Reliability Engineer (SRE), coined by Google in 2003. The following factors contribute to the birth of this role:

1. Large-scale online services like Google are complex and require high service stability.
1. Developers are usually more concerned about delivering software but less interested in the ongoing maintenance after launch. Moreover, to launch the feature sooner, Developers are inclined to ignore the maintenance aspect of the software. This is mostly represented by Facebook’s infamous motto, ["Move fast and break things."](https://en.wikipedia.org/wiki/Meta_Platforms#History)
1. Traditional Ops need to transition to a new role.

1 and 2 prompted the need for a specialized job function, of which 2 also balances the development pace (please do not break that much), while 3 provides the labor supply for SRE. Since SRE’s primary responsibility is to ensure service stability, it’s a natural transition for Ops to move into an SRE role.

Simply put, SRE is an upgraded version of traditional Ops. Google invents an accurate name:

- Site infers scale.
- Reliability defines the responsibility.
- Engineering dictates methodology.

**Thus SRE is to address the reliability issue for large scale service in a systematic way.**

SRE differs from Ops in the following ways:

1. SRE no longer deals with physical equipment that the cloud services take care of.
1. SRE employs a more systematic approach to ensure service stability, such as building automation tools and working with the development team together to develop [Service Level Objective (SLO)](https://en.wikipedia.org/wiki/Service-level_objective) / [Service Level Agreement (SLA)](https://en.wikipedia.org/wiki/Service-level_agreement) / [Service Level Indicator (SLI)](https://en.wikipedia.org/wiki/Service_level_indicator). This triplet is the contract between SREs and Developers to work towards a shared goal of providing a better service.
1. SRE has a stronger engineering background. Some SREs possess quite strong engineering skills. For example, the creator of the monitoring software [Prometheus](https://prometheus.io/) was an SRE at Google.

There is no intersection between Dev and Ops, while SRE, as mentioned earlier, is Ops with Dev skillset, but still a bit more on the Ops side.

SRE gradually takes over the Ops role and become the one carrying the pager.

## DevOps

DevOps is a system. As just mentioned, there is no intersection between Dev and Ops work, while DevOps is to combine these two work types. More precisely, DevOps is to let Dev take on the Ops work. Under DevOps, there is no traditional Ops role, and Dev and SRE may share the Ops responsibility. And there is a trend that more organizations adopt pure DevOps. These engineering shops assign all Ops responsibility to the Dev team, which further eliminates the SRE role. Under such setup, it's the Dev team carrying the pager.

## Why People Get Confused

1. Confused about the difference between SRE and Ops. Simply put, SRE is Ops who can code.
1. Confused about whether DevOps is a system or a job function. This depends on the context. Initially, DevOps represented the system combining Dev and Ops work. This system may have both Dev and SRE roles, or it may only have a Dev role. When the system only has a Dev role, the system name, DevOps, is also used to indicate the so-called DevOps engineer’s job function. After all, if a soccer formation blurs the boundaries of the forward, midfield, and back, the formation will become a free formation, and all players can be called free men.
1. Confused about the difference between DevOps and SRE when DevOps is treated as a job function. Simply put, DevOps is the Dev that does Ops work, while SRE is the Ops that does Dev work.

## The Road Ahead

_Old Ops never die, they simply fade away._

With the advent of Cloud computing, many Ops job duties no longer exist, and the pure Ops role will cease. But there remains the question of the dynamics among Dev, SRE, and DevOps roles. Which mix will become the mainstream in the next ten years?

Let’s look at Dev and SRE first.

Dev and SRE belong to different groups and always have a love and hate relationship. On the one hand, they share the same goal to deliver a better service to the end user. On the other hand, they achieve the goal from a different angle. If we take a look at the [DORA](https://www.devops-research.com/research.html) four key metrics:

1. Deployment frequency
1. Lead Time for Changes
1. Change Failure Rate
1. Time to Restore Services

The first two measure velocity, and the last two measure stability. In a group containing both Dev and SRE, Dev optimizes for velocity, and SRE optimizes stability. Some elite groups can simultaneously achieve better velocity and stability by delivering frequent incremental changes. However, many groups are battling between velocity and stability. As we all know, push to production is the No 1 reason causing production outages. That’s also why many engineering teams employ the no Friday release policy.

This battle would never end as long as there are separate Dev and SRE teams. But there is a light ahead thanks to the proliferation of Cloud infrastructure, SaaS, and Open Source.

While the physical pagers can only be memorized in the computer science museum nowadays, the Ops responsibility and spirit are carried over by services like [PagerDuty](https://pagerduty.com), [Datadog](https://datadog.com).

Devs are now quite capable of taking over Ops work. First, much less firefighting since the Cloud service is much more stable than the in-house one. Second, tools to handle Ops work are more accessible to average engineers. Thus we believe DevOps will further thrive and become dominant, and the pure Dev and SRE role will diminish and only be observed in very large engineering shops down the road.



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

1. [What is Database Version Control?](/blog/database-version-control)
1. Database Version Control, State-based or Migration-based? (this one)
1. [Database as Code - the Good, the Bad and the Ugly](/blog/database-as-code)
1. [The Database as Code Landscape](/blog/database-as-code-landscape)
1. [Database Version Control Best Practice](/blog/database-version-control-best-practice)

---

> In Infrastructure as Code (IaC) space,  state-based approach has become the de-facto standard. By contrast, in Database as Code (DaC) space, team still prefer migration-based approach to manage their database schemas. Below we give an overview of these 2 approaches, dive the rationale behind the industry divergence. In the end, we show how Bytebase can help to get the best of both worlds.

Infrastructure as Code (IaC) and Database as Code (DaC) both belong to Configuration as Code (CaC), and there are 2 different approaches to manage the configuration files:

- State-based version control (declarative)
- Migration-based version control (imperative)

## State-based version control (declarative)

State-based approach stores the desired end state of the entire schema in the code repository. For MySQL, it means to store the schema dump created by _mysqldump_.

It's worth mentioning in IaC,  popular systems like Kubernetes, HashiCorp, and Terraform all adopt this approach.

## Migration-based version control (imperative)

Migration-based approach stores the migration scripts in the repository. Each script contains a set of DDL statements such as CREATE/ALTER/DROP TABLE. The desired schema state is achieved by executing each of those scripts in a deterministic order.

Migration-based approach is more intuitive since this is how we change things in normal life.

It's also worth mentioning in IaC, before Kubernetes, Terraform era, this the way every team managing their infra by having a bunch of shell scripts containing imperative commands to provision the resources

## For Infrastructure as Code,  the industry has already shifted to state-based approach

State-based IaC delivers couple key benefits:

1. Keep a straightforward single source of truth (SSOT) in the repository. Configuration is represented by a single source file. For migration-based approach, people need to derive the SSOT from many migration files.
2. Usability. It's simpler to describe the end state without worrying about the ordering dependency. It's the Kubernetes/Terraform backend does the heavy lifting to reconcile the system to the desired state.

Also tools like Kubernetes, Terraform serves as the catalyst for the adoption.

## For Database as Code, migration-based approach is still the mainstream

Unlike its counterpart, team still prefer the migration-based approach to manage database schema. On one hand, this is due to the supply part.

1. Most existing tooling around code based schema management favor migration-based approach.
2. Same with the popular application frameworks, they all use Up/Down method to manage schema change/rollback. This is a variation to write migration in a specific programming language instead of raw SQL

But why in the database management domain, no state-based system has gained popularity in the last 5 years like IaC peers?

We attribute to couple reasons:

### Lack of system support

State-based or migration-based describes the view from user's perspective, if we look from the system's perspective, state-based approach requires much more engineering effort to get right.

Kubernetes has the built-in controller pattern to support the state-based approach from day 1. That's not the case for database systems. Neither MySQL nor PostgreSQL has any built-in feature to support state-based approach like how Kubernetes does. It's much harder to do if the system itself doesn't provide such support.

### Managing data (stateful) resources is much complex than managing computing/networking (stateless) resources

You may wonder HashiCorp Terraform also faces the similar problem since the infrastructure it manages are those public cloud providers which are blackbox.

Well, first that's why Terraform worths its hype:) It has done a terrific job of handling the complexity of reconciling between user's desired infrastructure state and cloud provider's actual state under the hood.

Another reason is the state-based approach is inherently a fit to manage ephemeral computing/networking resources since they can be destroyed and rebuilt. Database holds data (the state), to deliver a complete state-based approach for DaC, it not only needs to solve the schema (metadata) reconciliation problem like its IaC peer, but also requires to deal with the data.

## Team have better control using migration-based approach than state-based approach, the cost of database mistake is too high

Migration-based approach uses step by step command to instruct the database to make changes. By contrast, state-based approach appears to be a blackbox, and sometimes, this may lead to unexpected outcome. Let's give an example:

_We have 2 engineers Alice and Bob, Alice first made a schema change by adding a columnA to a table, meanwhile, Bob uses the schema version before Alice change, adds a ColumnB and check in after Alice's change._

With the state-based approach, Bob's checkin would cause the system to overwrite Alice's work since the desired state from Bob's checkin dose not include Alice's change. But with the migration-based approach, because of its incremental nature , it won't cause problem.

> State-based version control requires more engineering discipline and tooling support to prevent conflicting changes, while migration-based version control is more forgivable to such conflict.

The stakes are high when dealing with data, and people tend to prefer the method providing better control and safety fallback at the cost of usability.

## How Bytebase could help

State-based approach takes the Infrastructure as Code by storm with the help of Kubernetes, HashiCorp Terraform alike.

> But for database version control, we believe the current right approach is migration-based.  This is partly due to the lack of engine support in MySQL, PostgreSQL, partly due to the complexity of managing data (application state), as well as the high cost of making mistakes.

At the same time, we also understand the benefit of state-based approach. That's why in [version 0.5.0](https://bytebase.com/changelog#release-050), Bytebase introduces the schema snapshot and write back feature:

1. For every schema migration, Bytebase will record the schema snapshot.
2. If team manage database schema under version control system, they can configure Bytebase to write the schema snapshot back to the repository at a specified path.

With that, it makes Bytebase become a hybrid schema migration system. Team using Bytebase retain all the existing benefits of migration-based approach, meanwhile they now can have a single latest schema file. This file is the single of truth of their database schema, which brings the core merit from state-based approach. Please do checkout out [user guide](https://docs.bytebase.com/gitops/overview) on how to configure this.

As a side note, I do hope database engine could innovate in the direction as Kubernetes to pave the way for state-based approach. Actually, Google's own database system Spanner does provide these support and you know what, every team at Google use the state-based approach to manage their database schemas.

The whole industry are not quite there yet, but at least Bytebase can bridge the gap.



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

1. [What is Database Version Control?](/blog/database-version-control)
1. [Database Version Control, State-based or Migration-based?](/blog/database-version-control-state-based-vs-migration-based)
1. Database as Code - the Good, the Bad and the Ugly (this one)
1. [The Database as Code Landscape](/blog/database-as-code-landscape)
1. [Database Version Control Best Practice](/blog/database-version-control-best-practice)

---

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](https://www.terraform.io/), [AWS CloudFormation](https://aws.amazon.com/cloudformation/).
- Monitoring and alerting configuration.
- Access control policies.
- Continuous Integration (CI) /  Continuous Delivery (CD) pipeline configurations like [GitHub Actions](https://github.com/features/actions), [GitLab CI](https://docs.gitlab.com/ee/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](https://prometheus.io/).

> **On the other hand, for some other configuration resource types like database schemas, managing them in code repository hasn't become that popular yet. An evidence is Database as Code hasn't received its own Wikipedia page yet.**

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.

> **Actually, every Google engineering team manages the database schema in the code repository and it has served them well for 10+ years.**

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](https://cloud.google.com/sql) and [Ant Group OceanBase](https://www.oceanbase.com/en). 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.

> _BTW, if you like this article, you might also be interested in our product [Bytebase](https://bytebase.com), an open source, web-based schema change and version control tool. Try our [live demo](https://demo.bytebase.com) or checkout our [installation guide](https://github.com/bytebase/bytebase#installation) (it only requires 1 command line, 5 seconds setup if you already have docker)._

## Reference

1. [Evolutionary Database Design](https://martinfowler.com/articles/evodb.html) - Pramod Sadalage & Martin Fowler has a more detailed coverage on this topic.


Bytebase ブログ

データベース開発のスタンダードを体験する

ソリューションとプラットフォーム

比較

法的事項

リソース

会社

ソリューションとプラットフォーム

リソース

比較

会社

法的事項