Putting a Database Schema in Git

DbSchema is the tool this guide works through, because it saves the whole design - schema, diagrams and layouts - as one plain-XML .dbs file that Git can diff line by line. MySQL Workbench and Navicat store their models as binaries, which is where most schema-in-Git attempts stall.

Key takeaways

• Storing the database model as a plain XML file makes it version-controllable in Git and lets tools generate migration scripts between versions.
• Elite DevOps performers are 3.4 times more likely to incorporate database change management.
• Hybrid version control combines state-based ideal models with migration-based tracking.

Why Databases Need Version Control

Application code moves rapidly through automated CI/CD pipelines, but database schemas often remain manual bottlenecks. Databases are stateful assets that hold critical business data, which means deploying an untested structural change or dropping a column can cause irrecoverable data loss and application downtime.

DORA's Accelerate State of DevOps research found that elite DevOps performers are 3.4 times more likely to incorporate database change management into their process than lower performers[1]. Survey research into database deployments also reports that 57% of all application changes require a corresponding database schema change[2].

Core Risks of Unversioned Databases

Managing schema modifications through manual SQL scripts or live database edits introduces severe operational liabilities across engineering teams.

• Schema drift across environments: Staging, testing, and production databases diverge over time when hotfixes bypass version control.
• Lack of auditability: Teams cannot determine who modified a table, when a column was added, or why an index was created.
• High deployment failure rates: Unordered or conflicting DDL scripts fail during production releases, blocking application deployments.
• Difficult rollbacks: Reverting broken schema changes without structured history often corrupts existing production records.

Implementing structured version control treats database DDL with the same rigor as application code, ensuring repeatable releases and stable data integrity.

The Three Types of Version Control

Version control architectures evolved across three primary models: local, centralized, and distributed systems. Understanding these categories clarifies how modern teams manage both application source code and database models.

Comparing Version Control Architectures

Local version control keeps revisions inside a simple database on a single developer workstation. Centralized systems introduce a central server hosting all versioned files, requiring developers to check out files over a network connection. Distributed systems provide every contributor with a full local clone of the entire repository history.

Distributed architectures eliminated single points of failure and enabled modern branching strategies. This decentralized model provides the exact foundation needed for team-wide database schema management.

Git: The Most Common Version Control System

Git is the undisputed industry standard for software development. Market-share trackers consistently rank it first in the version control software category, well ahead of centralized alternatives such as Subversion. Software engineers across every tech stack rely on Git daily to track code revisions, isolate feature work, and coordinate releases.

Why Git Fits Database Schema Versioning

Git operates on file snapshots rather than file difference streams. This design offers distinct advantages when managing database structures alongside backend code.

• Full offline repository access: Inspect complete commit history, review past schema states, and branch without network access.
• Isolated feature branching: Develop complex table alterations on dedicated branches without impacting the main development schema.
• Native line-by-line diffing: Review precise text diffs in pull requests before schema changes merge into main branches.
• Immutable commit history: Cryptographic commit hashes guarantee an unalterable audit log of every database structure change.

When database schemas are represented in plain-text formats rather than opaque binaries, Git becomes a powerful engine for database change management.

What is a Database Version Example?

In practice, database schema versioning follows one of two paradigms: state-based version control or migration-based tracking. The difference decides what actually lands in your repository.

State-Based vs. Migration-Based Tracking

In state-based tracking, the repository stores the desired end state of the entire database schema. Dedicated comparison tools inspect the target database, calculate differences against the declared state, and generate deployment scripts dynamically. In migration-based tracking, developers write discrete, sequentially numbered SQL scripts (such as V1init.sql and V2add_status.sql) that apply incremental alterations step by step.

A hybrid approach combines the clarity of state-based visual modeling with the predictability of migration scripts. Teams maintain a declarative visual schema model in Git, then generate automated, safe migration scripts between specific version tags during deployment pipelines.

Best Practices for Database Version Control

Integrating database schemas into Git requires disciplined development workflows. Applying proven database DevOps conventions prevents drift, reduces lock conflicts, and accelerates delivery.

Core Workflow Guidelines

Maintain a single repository as the authoritative source of truth for your database design. Never execute manual DDL modifications directly against production databases.

1. Store schema models in version control: Keep declarative schema definitions in the same repository as the application code or a dedicated schema repository.
2. Use feature branches for schema updates: Isolate table creations, column modifications, and index adjustments on dedicated Git branches.
3. Review schema diffs in pull requests: Conduct peer reviews on every schema file modification prior to merging into main.
4. Validate DDL in automated test pipelines: Spin up temporary database containers in CI to verify that schema scripts apply cleanly without syntax or constraint errors.
5. Detect and reconcile schema drift: Compare live databases against the repository model regularly to identify unauthorized manual adjustments.

Implementing these practices early in the project lifecycle prevents schema technical debt and keeps schema changes reviewable by the same pull-request process backend teams already use for application code.

Git Integration in Visual Database Design Tools

Selecting the right database design tool determines how effectively your team can integrate schema modeling into Git repositories. Tools differ fundamentally in their underlying file formats, offline capabilities, and version control support.

Tool Comparison for Git-Based Schema Management

Tools that store the design model in a plain XML file are the ones you can diff line by line in a pull request. DbSchema is one of them: it saves the whole project - schema, diagrams, layouts, virtual foreign keys and comments - as a single indented XML .dbs file. MySQL Workbench provides a free Community edition but saves models in a proprietary zipped binary format (.mwb) that cannot be reviewed via plain-text Git diffs. Navicat Premium Lite offers basic connection features for free, but restricts schema modeling and advanced synchronization to paid tiers while using proprietary formats.

The best fit for Git-based workflows is a tool that decouples the design model from live database connections and keeps the schema file completely diffable in the repository. Whether you want that diff to be visual or expressed as migration code is a separate decision - see visual schema diff or a code-only migration library.

Putting Your Schema in Git: A Worked Walkthrough

DbSchema saves every project as a plain XML file with a .dbs extension. Because this file is structured text, it tracks natively in Git alongside your backend application code. You can branch, commit, review pull requests, and resolve merge conflicts using your standard Git tooling.

The Git Workflow, Step by Step

Follow this step-by-step procedure to manage a visual database model with Git:

1. Reverse-engineer or design the model: Connect to your database to reverse-engineer an existing schema into an interactive ER diagram, or build new tables from scratch in offline design mode.
2. Save the project file in your repository: Save the model as a .dbs XML file in your Git repository root or database directory.
3. Create a feature branch and modify tables: Check out a new Git branch, adjust tables or foreign keys visually in the designer, and save the file.
4. Commit and review XML diffs: View the line-by-line XML changes in your Git client or GitHub/GitLab pull request.
5. Synchronize against target databases: Open the updated model against development, staging, or production databases, review the visual diff, and generate safe SQL migration scripts.

Worked Example: Reviewing a Schema Diff in Git

When you add a column or modify a constraint, the design tool rewrites the clean XML structure. Below is a representative before-and-after Git diff showing the addition of an indexed status column to an orders table:

• Previous XML snippet: <table name="orders"><column name="id" type="serial" /><column name="total" type="numeric" /></table>
• Updated XML snippet: <table name="orders"><column name="id" type="serial" /><column name="total" type="numeric" /><column name="status" type="varchar" length="32" mandatory="y" /><index name="idx_orders_status" unique="NORMAL"><column name="status" /></index></table>
• Git diff view: + <column name="status" type="varchar" length="32" mandatory="y" />

• <index name="idx_orders_status" unique="NORMAL">
• <column name="status" />
• </index>

This structured XML format allows reviewers to verify column nullability, data types, and index definitions directly in pull requests without running external tools. The model is then compared against live instances to deploy safe DDL across environments.

Download DbSchema and open the design model against your own database: the Pro Edition lets you version database models in Git, compare live environments, and generate safe migration scripts across your deployment pipeline.

Frequently asked questions

Does Navicat have a free version?

Navicat offers a free tier called Navicat Premium Lite for basic database operations. However, advanced schema design, data modeling, and Git version control capabilities are restricted to its commercial editions.

Is there a free version of MySQL Workbench?

Yes, Oracle provides the MySQL Workbench Community edition for free. It includes visual database design and SQL development tools, though teams requiring dedicated, file-based Git integration often use alternatives that store the model as plain text.

What are the three types of version control?

The three primary types of version control systems are local, centralized, and distributed. Distributed systems like Git allow every developer to have a full local clone of the repository, making them the most popular choice for modern software and database schema versioning.

What are the best practices for version control?

Best practices for database version control include keeping a single schema model in a shared repository, using feature branches for individual work, reviewing schema diffs before merging, and testing migration changes in a staging environment prior to production deployment.

What is the most common version control system?

Git is the most common version control system globally, ranking first in market-share trackers for the version control category. Its distributed architecture, robust branching capabilities, and strong community support make it the standard for tracking both application code and database schemas.

Put Your Own Schema Under Version Control

Download DbSchema and open a model against your own database: the free Community Edition reverse-engineers the schema and draws the diagram, and Git Collaboration, offline design and schema synchronization come with the Pro and Architect editions.

Sources

1. red-gate.com
2. devopsdigest.com