Why Database Design Can’t Stay Fixed

Agile development brought a fundamental shift in how we think about architecture: instead of freezing requirements and design up front, teams assume both will evolve through iteration. This works well for application code, but for years it ran headlong into a stubborn problem—databases. Traditional database practice treated schema as a settled contract, something to be designed once and then guarded against change. That mindset is incompatible with evolutionary architecture.

Our techniques, developed starting in 2000 on a project that grew to about 600 tables, treat the database as just another evolving component of the system. They rely on continuous integration, automated refactoring, and close collaboration between DBAs and developers. These methods apply equally to pre-production and released systems, greenfield projects, and legacy codebases. We've honed them on hundreds of projects, from small teams to large multi-national programs.

The Mechanics of a Schema Change

Consider a developer, Jen, implementing a story that requires users to see, search, and update the location, batch, and serial numbers of a product in inventory. The current schema has none of these fields—just a single inventory_code column that concatenates all three values. Jen must split that one column into location_code, batch_number, and serial_number.

Her change touches schema, data, and code:

  • Add the three new columns to the inventory table.
  • Write a data migration script that parses existing inventory_code values and populates the new fields.
  • Update application code to read and write the new columns.
  • Revise database code—views, stored procedures, triggers—that referenced the old column.
  • Replace any indexes built on inventory_code.
  • Commit the migration script and all related code changes to version control.

The migration script is the core artifact. It both alters the schema and transforms the existing data in a single pass:

ALTER TABLE inventory ADD location_code VARCHAR2(6) NULL;
ALTER TABLE inventory ADD batch_number VARCHAR2(6) NULL;
ALTER TABLE inventory ADD serial_number VARCHAR2(10) NULL;

UPDATE inventory SET location_code = SUBSTR(product_inventory_code,1,6);
UPDATE inventory SET batch_number = SUBSTR(product_inventory_code,7,6);
UPDATE inventory SET serial_number = SUBSTR(product_inventory_code,11,10);

DROP INDEX uidx_inventory_code;

CREATE UNIQUE INDEX uidx_inventory_identifier
  ON inventory (location_code,batch_number,serial_number);

ALTER TABLE product_inventory DROP COLUMN inventory_code;

Jen runs this migration against a local database copy on her machine. Then she updates the application code, running the existing test suite as she goes. Some tests that depended on the combined column must change; others need to be added. Once everything is green locally, she pushes all changes—migration scripts and code—to the shared mainline in version control.

Because splitting a column is a common database refactoring, Jen can consult standard references for the pattern rather than inventing it from scratch. After her push, the CI server applies the migration to the mainline database and runs the full test suite. If that passes, the same code flows through the deployment pipeline—QA, staging, and finally production—updating the live schema and data consistently at each step.

Small stories need only one migration. Larger ones benefit from being broken into several small, sequential database changes. Each change should be as small as possible: easier to get right, easier to debug, and naturally composable with others.

Evolutionary Design and the Database Problem

Agile methods, which gained traction in the early 2000s, fundamentally changed how teams approach change. Traditional, plan-driven processes—often dubbed “waterfall”—attempted to minimize change by locking down requirements and designs early. This worked poorly when requirements churned, as is common in many projects and dynamic business environments.

Agile flips this mindset. Instead of resisting change, its goal is to embrace and enable it, even late in development. This requires shifting from design as an up-front phase to design as an ongoing activity interleaved with construction, testing, and delivery. The critical contribution of agile is a set of practices that make this evolutionary design controlled and practical, preventing the chaos usually associated with a lack of upfront planning.

Central to this is iterative development. Each iteration runs a complete software life-cycle and ends with working, tested, integrated code for a subset of features. Iterations are short—from hours to a couple of weeks—and more skillful teams use shorter ones.

This model creates a major question for databases. For a long time, the database community treated schema design as something that absolutely required up-front planning. Late schema changes caused widespread application breakage, and post-deployment changes led to painful data migrations.

We've spent the last fifteen years applying evolutionary database design on large, complex projects: some with over 100 people spread across global sites, others with over half-million lines of code and more than 500 tables, and a few requiring 24×7 uptime with multiple production versions. We've tested iterations as long as a month and as short as a week—shorter ones always worked better. These techniques eventually became the standard for all our projects, informed by experience from other agile practitioners. The methods we describe below are what made this work.

Known Limits

We have not solved every problem. We’ve successfully handled situations where hundreds of retail stores each have their own database that must all be upgraded at once. What we haven't explored is a large group of sites with significant customization, such as a small business application deployed to thousands of companies with user-modified schemas.

Another emerging frontier is the use of multiple schemas within a single database environment. We've handled a handful, but haven't pushed into the tens or hundreds. Several projects have reported success, but we haven't yet cranked up the scale ourselves—though we expect this to be a priority in the next few years.

We don't consider these unsolvable. When we wrote the original version of this article, we hadn't yet solved the problems of 24×7 uptime or integration databases. We found ways around those and expect to keep pushing the limits here too—until then, we won't claim we can handle them.

Making the Practices Work

Several practices underpin this approach to evolutionary database design, and they all depend on breaking down the traditional barriers between the DBA team and application developers.

DBAs and Developers Work Together

Any development task can require a database change. When this happens, the developer must talk to the DBA rather than just blundering ahead. The developer understands the new functional requirements, but the DBA has a much better view of the complete data picture, including upstream and downstream dependencies on the schema that a developer working on a single feature might not see.

Pairing is an effective way to handle this. When a developer pairs with a DBA on a database alteration, the developer learns more about the database and the DBA learns about the context causing the change. This should be a two-way street: developers should ask for the DBA's help when they think they are doing something significant, and DBAs should proactively look through the project's stories and the committed migrations to spot likely data impacts. Making this collaboration work requires removing the communication barriers. Having the DBA and developers sitting close to each other and using the same communication channels is essential.

All Database Artifacts Live in Version Control

Developers keep application code, test suites, and build scripts in version control. All database artifacts should be there too, in the same repository as the rest of the project. This creates a single place to look for anything needed to build and deploy the software, keeps a complete record of database changes for audit purposes, and prevents the deployment pain of a database getting out of sync with the application code that accesses it.

Capture All Database Changes as Migrations

A common pattern in many organizations is for developers to make changes to a development database using schema editing tools and ad-hoc SQL. At release time, the DBA then compares the development and production databases and hand-crafts the promotion. This is troublesome because the context behind the development changes is lost and needs to be rediscovered by a different group of people at the worst possible moment.

The preferable approach is to capture the change itself as a first-class, version-controlled artifact during development. Every database change—schema alterations, database code updates, reference data changes, and even fixes to bugs in production data—must be written as a migration script that goes through the same testing and deployment pipeline as the application code. Schema editing tools and ad-hoc DDL or DML should never be used to alter schemas or standing data; only migrations should be used.

Each migration needs a unique identifier, and we need to know which migrations have already been applied to any given database. This is done by giving each migration a sequence number and using a changelog table to record when a migration is applied. The numbering also manages sequencing constraints. For instance, you must apply a migration that alters a table before applying a migration that inserts data into it based on that alteration.

A useful pattern is to keep migrations for new features in a separate folder from those dealing with production data fixes, if data-correction migrations need to ship on a faster cadence. Tools like Flyway, which track migration state per database, can manage each folder with its own metadata table (or just set a property like flyway.table to point to a different changelog location).

Everyone Gets Their Own Database Instance

Development organizations have traditionally shared a single database to minimize the administrative burden. This causes constant interruptions as half-finished changes from one developer disturb the others. Just as developers use private working copies of their code to experiment, they can also use their own database instances to try things out without stepping on a colleague's toes. These can be separate schemas on a shared server, or more commonly now, a database running locally on a developer's workstation, ideally in a VM that can be built using tools like Vagrant.

This sharing applies to everyone on the team. QA can work in their own space without getting confused by changes they didn't make themselves being applied underneath them. DBAs should also have a private playground for exploring modeling options and performance tuning changes.

Having separate databases is not just about avoiding conflicts. It enables the workflow of a developer joining the team and running a script to spin up her own schema and populate it quickly. Database environments should be built and burnt down at will—like phoenixes—rather than slowly accumulating non-reproducible characteristics over time.

Integrate Changes Frequently

Continuous Integration (CI) is a key practice for mainline software builds. It works just as well for database changes: as soon as a migration is checked into mainline, the CI server applies it to its own database and runs the full test suite. If all is well, the CD pipeline packages both the application code and the migration scripts together, keeping a synchronized version history of both. Migrations that are being developed get pulled, tested, and pushed following the usual practice of frequent integration:

  • Jen starts with a schema change for a new feature. If it's simple—like adding a column—she just writes the migration herself. For something more complex she brings the DBA in on it.
  • Before pushing, she integrates with any colleague changes that have landed in mainline. Usually the migration sequence numbers can just be renumbered to slot the new change after the existing ones, but if there is a conflict it shows up physically as two migrations having the same sequence number. At that point she can pick one to be 8 and the other to be 9, then run all the migrations from a clean slate to check the data change works with the schema change made by the other developer.
  • When she is integrated and all tests pass, she pushes to mainline, and the CI server runs the migration in the mainline integration database.

Frequent, small integrations are strongly preferred because the pain of an integration increases disproportionately with its size. Doing many small changes is much easier in practice than doing large, infrequent batch ones.

A Database is More Than a Schema

The definition of the database includes the standing data (states, countries, currencies, address types) and sample test data as well as the schema. This data is needed to support automated testing. Version-controlling the sample data in the same way as the schema makes builds reproducible and lets developers check the impact of a migration on existing data.

It is worth putting real data into the sample set as early as possible, even in the very first iteration. Real data forces early thinking about legacy data conversion and can make the domain experts more comfortable and productive because they are looking at data they recognize, rather than working with made-up test values from a fictional world.

Think of Changes as Database Refactorings

None of the database changes on their own change the observable behavior of the system, so we can safely treat them as refactorings. But a database refactoring actually involves three different changes that must be done together: changing the schema, migrating the data in place, and modifying the database access code. This adds a third dimension to the usual refactoring workload and makes it even more important to keep these changes small.

Many refactorings are easy wins because they are not destructive. These include introducing a new nullable column; the access code can run fine without referencing it, and the column quietly fills up with values over time. But destructive changes such as making a column non-nullable or renaming a table need more planning. To execute a destructive change like this safely, we can make use of a transition phase—a period when the database supports both the old and new access patterns simultaneously. This may require extra database objects as bridges, adding a view or a trigger to simulate an old table by reading from a new one. The transition phase is temporary and must be removed as soon as the downstream systems have migrated over, but it offers an effective way to support complex situations when you cannot change all access code at once.

Automate Applying Refactorings

Standardized automated refactoring tools from the code world don't translate directly to database systems well because the rules for handling data are highly dependent on context, which is code and data specific. Instead we prefer to handle database refactoring by writing specific SQL scripts and using tools to automate the application of the migration sequence. These tools can bring any instance up to the latest state, update test databases, and—provided you can take the system offline temporarily—update production as well.

There are many tools available for automating database migrations, including Flyway, Liquibase, MyBatis Migrations, and DBDeploy. They work well to automate forward changes gracefully, but we haven't usually found it worthwhile to automatically reverse the changes (rolling back data) because our data loss scenarios tend to be easily recoverable from the same version-controlled scripts that brought us forward. What's more beneficial is writing migrations in a way that the database access layer can work with both old and new versions of the schema for a period. That way, the schema can be deployed ahead of the new code that uses it, eliminating the risk of a failed "big bang" deployment.

Enable On-Demand Database Updates

When you pull the application code from version control and a colleague has pushed a new migration, you also need to be able to pull in the database changes and apply them to your local database easily. The most common issue is a sequence number clash when you've written your own migration with the same number. The response to a clash starts with renaming your migration to the next available number. After that, you test on a clean copy of the database by removing everything and applying all the migrations from scratch to confirm that the new sequence works end-to-end. The fact that each migration is very small makes it easy to spot the exact cause of a conflicting change if one appears. When in doubt, just follow the integration steps: pull mainline, update your schema, and rerun the test suite.

Keep a Clear Database Access Layer

To understand how database refactorings will impact an application, it is vital to be able to see where and how the database is accessed. If SQL is scattered randomly across the code base, this is nearly impossible to do. Having a clear database access layer (implemented using one of the data source architectural patterns) has a number of immediate benefits. It reduces the areas of the code where developers need expert SQL knowledge, and it gives the DBA a clear, manageable section of the code to review for things like query optimization and index design decisions.

Release Frequently

Frequent releases to production provide rapid feedback and broaden what we learn from software in real use. With every change captured in a migration, releasing with confidence becomes a routine activity, making frequent releases a very natural enabler of the whole practice of evolutionary database design.

Adapting the Practices

These techniques are a starting point, not a rigid prescription. Different project structures and deployment models call for adjustments.

Supporting Multiple Live Versions

A simple project with a single code line can get by with a single database version. More complex projects, however, often need to support multiple versions simultaneously for A/B testing or rolling deployments like canary releases. Each of these releases may require its own test data or specific fix verification. This essentially means managing multiple versions of production code, with the added twist that the database must support them all at once.

A useful approach in this scenario is to maintain a single repository for the database schema and migrations, with all application versions depending on that repository. This forces a high-bar for backwards compatibility: every version of the application code that is live must work with the same database version.

When the Database Ships with the App

Some products are shipped to thousands of end customers, making it impossible to control the upgrade process centrally. In these cases, it’s better to let the application handle its own upgrade. Package all database changes with the application code—since you can't know which version the customer is upgrading from—and let the application run the necessary migrations on startup using a tool like Flyway or one of its many counterparts.

Shared Databases Across Applications

In many enterprises, a single database is shared by multiple applications, a pattern known as Shared Database Integration. When one application changes the database, it can easily break the others. To manage this, treat the database as its own separate code repository that all dependent applications consume. This common repository should include automated behavior tests to ensure cross-application dependencies are intact, failing the build if a change would affect a dependent application. This is the same discipline you'd apply to any shared software component: test its own behavior, but also test the contract it offers to downstream consumers, much like consumer-driven contracts.

NoSQL and the Implicit Schema

This article has focused on relational databases, which remain the most common. NoSQL databases are often marketed as easier to evolve because they are "schemaless". But being schemaless doesn't remove the need to manage schemas; it simply means there is an implicit schema defined by the code that reads and writes the data. That implicit schema still must be managed, and it is still best done through data migrations stored in the source code repository. The lack of a physical storage schema does offer a new technique: the ability to support multiple read strategies for different application versions, which can simplify evolution—but it is still something that requires conscious effort.

Scaling with Automation, Not Headcount

These practices may sound like they require a large team of database specialists, but that has not been our experience. On projects with roughly thirty developers and a total team of close to a hundred, we would routinely have around a hundred copies of schemas running on various workstations. This entire operation was managed by just one full-time DBA, with a couple of developers assisting part-time.

Smaller projects need even less. On teams of about a dozen people, we find no full-time DBA is required. A couple of developers with an interest in database issues can handle DBA tasks part-time, bringing in a specialist only for major design or architecture decisions.

The key enabler is automation. When you are determined to automate every task, a lot of work can be handled with far fewer people. This is especially true with the growing popularity of DevOps and related provisioning and containerization tools like Puppet, Chef, Docker, Rocket, and Vagrant.

Our experience has led us to rely on databases that evolve as fluidly as application code, which is essential for shortening release cycles. This way of working is now standard practice for us, and our goal in sharing it is to see these techniques adopted more widely, helping software better support the people who use it.

Helpful Tools

These practices rely on a healthy amount of automation. Here are several tools we've found valuable.

  • Liquibase, a framework to manage database migrations
  • MyBatis migrations, a framework to manage database migrations
  • Flyway, a framework to manage database migrations
  • DBDeploy, a framework to manage database migrations
  • DBmaestro, a commercial tool to enable evolutionary database development
  • RedGate, a commercial tool to enable evolutionary database development
  • Datical, a commercial tool to automate database release management
  • Jailer, a tool to extract subsets of data from a database
  • DiffKit, a framework to compare two sets of data and report differences
  • DbUnit, a JUnit extension for testing databases
  • DbFit, a tool for writing readable unit and integration tests for database code
  • Data Anonymization, a tool to anonymize production data for development use

Beyond developer tooling, analysts and QA often need a straightforward way to view and modify test data. On our projects, we created an Excel application with VBA scripts to pull data down from the database, let users edit it, and then write the changes back. While there are many specialized database editors, Excel is so ubiquitous that it lowers the barrier for people who aren't database experts.

Everyone on a project—developers, QA, analysts—should be able to explore the database design easily to understand the available tables and how they relate. A simple web application that queries database metadata is a convenient interface for this purpose, and such an app can be built as part of the project's standard tooling.