Schema Versioning Without a Migration Framework

The challenge of evolving databases without a framework

Managing database schema changes without a dedicated migration framework creates significant operational risks for engineering teams. When developers alter tables directly on shared instances or pass ad-hoc DDL scripts in chat channels, schema drift sets in: the live database structure drifts away from the source of truth because changes were applied without going through a recorded migration[1]. Drift of this kind typically originates in emergency hotfixes and undocumented patches applied straight to production, and it surfaces later as application bugs, failed deployments, and in the worst case data loss[2].

Traditional migration frameworks require embedding database migration runners, tracking changelog tables, and writing boilerplate code into application services. For backend developers seeking a lighter footprint, adding runtime framework dependencies or managing sequential migration files introduces unnecessary deployment friction.

Backend engineers require a deterministic method to track database schema versions directly in source control without adding heavyweight middleware to the application runtime. Establishing this control prevents the common structural failures that happen when schemas diverge across environments:

• Column mismatch exceptions when newly deployed backend code references unapplied table modifications.
• Silent data corruption caused by mismatched data types or omitted check constraints across environments.
• Deployment roadblocks where DBAs and engineers cannot determine which DDL statements have already executed on target instances.
• Failed rollbacks caused by unrecorded manual alterations made during emergency hotfixes.

Eliminating these failure points requires treating the database schema as a distinct, version-controlled artifact that developers can modify, inspect, and deploy deterministically.

State-based vs migration-based database versioning

Database version management follows two primary architectural paradigms: migration-based versioning and state-based versioning. Understanding the operational trade-offs between these two approaches determines how your team manages schema evolution.

Migration-based deployments require developers to hand-write discrete, ordered SQL migration files (such as V1__create_users.sql and V2__add_email_idx.sql) that execute sequentially. The engine records each applied script in a dedicated database metadata table. While this approach captures explicit transition logic, it forces developers to calculate intermediate states manually and manage script ordering across competing branches.

State-based versioning takes the opposite approach by storing the desired end state of the entire database schema as the single source of truth. Developers declare the target structure, and a schema engine compares this declaration against the target database to compute the required differential DDL automatically compare two database schemas.

By adopting a state-based model, engineering teams let the comparison engine generate the schema upgrade script for most routine structural changes, reserving manual SQL interventions strictly for destructive or complex data transformations.

Saving the database design as an XML model

State-based schema management requires an offline design file format that fully describes relational and non-relational database structures without requiring an active server connection. DbSchema implements this by storing complete schema definitions in a dedicated .dbs project file.

The .dbs file uses structured XML to capture all database artifacts in human-readable plain text. Because the file stores structural definitions rather than database records, it remains compact, secure, and entirely isolated from sensitive customer data.

The model file preserves the following components inside its XML structure:

• Table and view definitions, column specifications, default values, and nullable constraints.
• Primary keys, unique keys, composite foreign keys, and storage-engine indexes.
• Virtual foreign keys that define logical relationships across tables or document collections without database-level enforcement.
• Visual layout coordinates, documentation comments, and entity relationship diagram groupings.

Working with an offline XML model allows backend developers to design new features, restructure entities, and define constraints while disconnected from live staging or production servers. The local file serves as the definitive reference point for the database design.

Versioning the XML schema file in Git

Placing database schema files into version control aligns schema evolution directly with application code lifecycles. Storing database schemas in Git allows engineering teams to track structural changes alongside the services that consume them.

When a developer creates a feature branch to add new backend endpoints, they modify the local XML schema file within the same repository. Branching, merging, and reviewing database changes follows standard software development workflows.

Follow this disciplined workflow to manage schema evolution through version control:

1. Create a dedicated Git feature branch for your application task.
2. Open the .dbs project file in DbSchema and apply structural changes offline.
3. Save the .dbs file and verify the XML modifications using git diff.
4. Commit the updated .dbs file directly alongside your application code changes.
5. Push the feature branch to your remote repository and open a pull request.
6. Conduct a peer review of the schema changes directly within the pull request interface.

Because the XML model lives inside the application repository, every merge into the main branch establishes a clear, auditable point in time for both the backend application and its supporting database schema.

Reviewing a before-and-after schema diff

Because the project model is saved as structured XML, schema changes produce clean, readable text diffs inside pull request tools like GitHub, GitLab, and Bitbucket. Reviewers can quickly evaluate altered column types, new tables, and updated indexes before any code is approved.

Consider a scenario where an engineering team updates an existing customer accounts table. The developer modifies the account_tier column data type from a fixed character code to a variable string and adds a new verification_status column.

The resulting Git diff reflects these exact structural adjustments in the XML layout:

• Existing state: <column name="account_tier" type="char" length="2" jt="1" mandatory="y" />
• Modified state: <column name="account_tier" type="varchar" length="32" jt="12" mandatory="y" />
• Added column: <column name="verification_status" type="varchar" length="20" jt="12" mandatory="n" />
• Added index: <index name="idx_accounts_status" unique="NORMAL"><column name="verification_status" /></index>

Reviewers can confirm that the data type modification does not violate application expectations and that the new index properly supports upcoming queries. Standardizing on diffable XML files eliminates the ambiguity of unreviewed SQL scripts passed between team members.

Generating the SQL migration scripts

Once a pull request merges into the mainline branch, the next step is applying the schema changes to physical databases. DbSchema translates the declarative model state into executable DDL through its built-in schema synchronization engine.

DbSchema connects to the target database over JDBC, introspects the live catalog, and compares the remote structure against the local XML model. The comparison identifies every missing table, modified data type, added constraint, and removed index.

DbSchema generates the migration script through a structured multi-step process:

1. Introspect the target database schema catalog across all target namespaces.
2. Perform an object-by-object diff between the live catalog and the XML project model.
3. Display a visual side-by-side comparison highlighting all detected structural variances.
4. Selectively choose which differences to deploy to the database or pull back into the model.
5. Generate the dialect-specific SQL statements (ALTER TABLE, CREATE INDEX, ADD CONSTRAINT) and open them in the Execute Script In Database dialog, which steps through the generated DDL one statement at a time with an execution message per statement.

Generating migration scripts directly from state differences ensures that the generated SQL matches the exact gap between the target database and the approved model version, preventing duplicate statements or omitted dependencies.

Deploying synchronized schemas across environments

Deploying schema updates across development, staging, and production environments requires strict repeatability. Applying generated migration scripts through a structured promotion pipeline ensures that all environments maintain structural parity.

Maintain consistency across your deployment targets by executing these deployment steps:

1. Generate the migration DDL script against your local or development database to validate script execution.
2. Execute the validated DDL against your staging database during integration testing.
3. Inspect staging data integrity and verify that application queries execute against the updated schema without errors.
4. Schedule and execute the target migration script against the production database during the deployment window.
5. Re-synchronize the model against the production instance to verify that zero schema drift remains.

To version your schema this way, download DbSchema and open the model against your own database. Saving the design to a file and schema synchronization are Pro Edition features; the installation kit is the same for the Community and Pro editions and carries a 15-day Pro trial.

Frequently asked questions

What is a state-based database migration?

A state-based migration defines the desired end state of the database schema rather than the step-by-step SQL commands to get there. Tools compare this target state against the live database and automatically generate the necessary migration scripts for routine structural changes.

How do you version control a database schema without a framework?

Backend developers can save their database design as a plain XML file and track it in Git alongside application code. When the XML file changes in a pull request, DbSchema compares the new file against the live database to generate the exact SQL migration scripts required.

Why use an XML file for database schema design?

A plain XML file provides a highly readable, declarative format for a database schema. It stores the complete structure, including tables, columns, and virtual foreign keys, making it easy to diff in version control and preventing developers from needing a live database connection to plan changes.

Can you generate SQL migration scripts from a Git diff?

Yes. By storing the database schema as a state-based XML model in Git, developers can use DbSchema's schema synchronization to compare the modified XML file against the target database. The tool identifies every difference and generates the specific SQL migration scripts to apply those changes.

What is the difference between state-based and migration-based versioning?

Migration-based versioning requires developers to manually write incremental SQL scripts for every schema change. State-based versioning stores the ideal final schema in a single file, relying on a comparison tool to compute the differences and automatically generate the required deployment scripts.

Sources

1. prisma.io
2. liquibase.com