These are my notes from the first few chapters of Refactoring Databases by Scott Ambler and Pramod Sadalage. The book was published in 2006 and its examples are a bit outdated, but the underlying ideas translate well to modern Rails applications where migrations serve as the primary mechanism for schema evolution. A follow-up post will cover additional database refactoring techniques from the later chapters.
Many of the techniques in this book apply to large production databases with critical or sensitive data, heavy traffic, and zero tolerance for downtime. If you're working on a small app with a handful of users and can afford a maintenance window, a lot of this can be simplified. But the patterns are worth knowing for when you're operating at a scale where you can't just stop the world, run a migration, and hope for the best.
The case for database refactoring
Most Rails developers are comfortable refactoring application code. Extract a method, rename a class, move some logic from controller into a model, and so on. The test suite catches regression and you move on. Database schemas deserve the same treatment, but they rarely get it. Renaming a column touches the schema, the models, the queries, the serializers, and possibly an API contract.
Poor design at the data level has a way of rotting everything above it. When the schema doesn't properly represent the domain, you end up writing increasingly contorted application code to paper over structural problems, each workaround making the next feature harder to build. Small, iterative refactorings help you break out of that cycle. They let you evolve the schema alongside the application as your understanding of the domain improves, rather than trying to get the whole thing right up front.
And you will not get it right up front. Requirements shift as projects progress. Any early investment in detailed database architecture gets partially thrown away the moment those requirements change. Rails recognized this years ago, from the beginning. Database migrations treat schema changes as versioned, incremental artifacts applied in sequence, which means the framework already expects your database to evolve over time.
That doesn't mean you should skip modeling entirely. Spending some time early on identifying the main business entities and how they relate to each other is super valuable. The goal is to think through the structural decisions that would be expensive to reverse later, without getting bogged down in column-level details you don't need yet. Work through the specifics on a just-in-time basis as features are built.
The book describes four pillars of evolutionary database development. First, database refactoring itself: evolving the schema a small bit at a time to improve design quality without changing semantics. Second, iterative data modeling: making sure the schema keeps pace with the application code. Third, database regression testing: verifying that the schema actually works. Fourth, developer sandboxes: isolated environments where each developer can experiment before deploying.
In Rails, the first two are handled through migrations and model definitions, the third through your test suite, and the fourth through the convention of each developer running a local database with rails db:create and rails db:migrate.
Refactoring before adding features
for each desired change, make the change easy (warning: this may be hard), then make the easy change — Kent Beck
Here's a discipline worth adopting. When you need to add a new feature, ask yourself whether the current schema is the best possible design for supporting that feature. If it is, go ahead and build. If it isn't, refactor the schema first, then add the feature.
This sounds like it would slow you down, but in practice the opposite happens. If you start with a well-designed schema and refactor continuously to keep it that way, each feature addition becomes straightforward because you're always building on a solid foundation. The alternative, adding features onto a schema that doesn't properly support them, leads to the kind of code where every new query needs three joins and a subselect to work around a table that was never split when it should have been.
Consider a products table with a metadata JSON column that was initially convenient for storing arbitrary attributes. It's okay to begin with, and even works just fine. Then someone needs to build filtering and reporting against specific fields inside that JSON blob. The queries get complicated. Other developers can't figure out what's actually stored in there without reading application code.
Rather than building the filtering feature on top of this and accepting the complexity, the better move is to first extract the relevant fields into proper columns or a related table, migrate the data, update the models, and then build the feature against a schema that properly supports it.
The process of database refactoring
Each refactoring should change the schema in one small step. One migration that handles one logical change. If something breaks, you know exactly where it happened.
The process looks like this: verify that the refactoring is actually needed and worth the effort right now, choose the most appropriate refactoring, write the migration, migrate data if the structural change requires it, update the application code (models, queries, tests), and run the test suite. In Rails, the schema change and data migration typically live in the same migration file.
Two principles should govern how you apply refactorings. Smaller changes are safer. The larger a single migration, the more likely you are to introduce a defect and the harder it will be to find. When you need to make a large structural change, decompose it into a sequence of smaller ones. Splitting a table shouldn't be one migration. Create the new table first. Add columns. Migrate data. Update the application code to read from the new table. Remove the old columns in a subsequent migration after verifying everything works. Each step is independently testable and reversible.
Structural Refactorings
These change the shape of tables: dropping, adding, merging, renaming, replacing, or splitting columns and tables. What follows are the techniques from the first few chapters, adapted to how Rails applications actually work.
Drop Column
You drop a column when it's no longer used, or after moving its data somewhere else. The important thing is that by the time the migration runs, nothing should be referencing that column. Search for it in model scopes, raw SQL, serializers, and any gems that might generate queries against it. If you're using select("*") anywhere and accessing columns by position rather than name, that code will break when the column count changes.
If the column holds valuable data, move it first. If there's no natural destination, you can preserve it in an archive table keyed by the original table's primary key, though in most Rails apps this situation is uncommon.
# Step 1: Stop writing to the column in application code.
# Tell ActiveRecord to ignore it so cached schema doesn't break deploys.
class Contact < ApplicationRecord
self.ignored_columns += ["fax_number"]
end
# Step 2: Deploy the code change above first. Then drop the column.
class DropFaxNumberFromContacts < ActiveRecord::Migration[8.0]
def change
remove_column :contacts, :fax_number, :string
end
end
Drop Table
Dropping an entire table requires checking for foreign key constraints, associations in your models, and any raw SQL that references it. If the table holds historically important data and you're not completely certain nothing depends on it, rename it first (something like audit_logs_archived) and let it sit for a transition period. If nothing breaks and nobody comes asking for the data, drop it for real.
class ArchiveAuditLogs < ActiveRecord::Migration[8.0]
def up
rename_table :audit_logs, :audit_logs_archived
end
def down
rename_table :audit_logs_archived, :audit_logs
end
end
Add a Calculated Column
A calculated column stores a pre-computed value derived from other data: an order total, an average rating, a count of associated records. The point is to avoid recalculating the same thing on every request.
What could go wrong? The calculated value drifts from the source data, and now you have two sources of truth that disagree. Rails gives you a few options for keeping things in sync. ActiveRecord callbacks (or even database triggers) can update the column when the source data changes. For heavier calculations where you can tolerate some lag, a background job that recalculates periodically works well. Which approach you choose depends on how stale you can afford to be.
class AddTotalCentsToOrders < ActiveRecord::Migration[8.0]
def change
add_column :orders, :total_cents, :integer
Order.find_each do |order|
order.update_column(:total_cents, order.line_items.sum(:price_cents))
end
end
end
After the new calculated column is added, find all the places in the application code where this calculation was being used and update it to use the new column.
Move Column
Sometimes a column ends up on the wrong table. Shipping address fields on users need to move to orders or a separate shipping_addresses table once you realize users can have different addresses for different orders. Or you're normalizing to reduce redundancy. Or denormalizing to eliminate a join to improve performance.
The process: add the column on the destination table, backfill the data, update the application to read from and write to the new location, and drop the original column in a later migration. If the data is critical, rename the column and keep it for a while before dropping it.
class ExtractShippingAddressFromUsers < ActiveRecord::Migration[8.0]
def up
create_table :shipping_addresses do |t|
t.references :user, null: false, foreign_key: true
t.string :street
t.string :city
t.string :state
t.string :zip
t.timestamps
end
execute <<-SQL
INSERT INTO shipping_addresses (user_id, street, city, state, zip, created_at, updated_at)
SELECT id, shipping_street, shipping_city, shipping_state, shipping_zip, NOW(), NOW()
FROM users
WHERE shipping_street IS NOT NULL
SQL
end
def down
rename_table :shipping_addresses, :archived_shipping_addresses
# OR
drop_table :shipping_addresses
end
end
Rename Column
Renaming a column is often done to improve the readability of your schema. For columns that are part of an API contract or shared across services, or contain huge amounts of critical data, the safer approach is to add a new column with the better name, backfill data, update the application, verify nothing breaks, and then rename and finally drop the old column.
One column name to watch out for: type. Rails reserves it for Single Table Inheritance. A type column that isn't being used for STI will conflict with ActiveRecord's expectations and should be renamed.
# Step 1: Add the new column and backfill existing data.
class AddRoleToUsers < ActiveRecord::Migration[8.0]
def change
add_column :users, :role, :string
execute <<-SQL
UPDATE users SET role = type
SQL
end
end
# Step 2: Update application code to read from and write to `role`.
# Dual-write to both columns during the transition so rolling back
# the deploy doesn't lose data written after the migration ran.
# Step 3: Once all code reads from `role`, archive the old column.
class ArchiveTypeOnUsers < ActiveRecord::Migration[8.0]
def change
rename_column :users, :type, :archived_type
end
end
# Step 4: After a transition period with no issues, drop it.
class DropArchivedTypeFromUsers < ActiveRecord::Migration[8.0]
def change
remove_column :users, :archived_type, :string
end
end
Replace JSON column with a table
This is the modern version of what the book calls "Replace Large Object with Table." A jsonb column that started as a convenient way to store semi-structured data has, over time, developed into a structure you now understand well enough to model properly.
Extracting JSON into a table gives you real indexing, foreign key constraints, database-level validations, and queries that don't require Postgres-specific JSON operators. If the JSON blob duplicates data that exists elsewhere in your database, the extraction also eliminates that redundancy and the integrity problems that come with it.
The process: analyze the JSON structure, design the target table schema, write a migration that creates the table and deserializes each blob into rows, and update the application to use the new association. Rename the old JSON column to verify nothing still depends on it, and eventually drop it.
Say you have a products table with a details jsonb column that stores things like { "color": "red", "weight_grams": 450, "dimensions": { "length": 10, "width": 5 } }. Over time, you realize these fields are queried frequently and would benefit from proper columns and indexing.
# Step 1: Create the new table and backfill from the JSON column.
class ExtractProductDetails < ActiveRecord::Migration[8.0]
def up
create_table :product_details do |t|
t.references :product, null: false, foreign_key: true, index: { unique: true }
t.string :color
t.integer :weight_grams
t.integer :length
t.integer :width
t.timestamps
end
execute <<-SQL
INSERT INTO product_details
(product_id, color, weight_grams, length, width, created_at, updated_at)
SELECT
id,
details->>'color',
(details->>'weight_grams')::integer,
(details->'dimensions'->>'length')::integer,
(details->'dimensions'->>'width')::integer,
NOW(), NOW()
FROM products
WHERE details IS NOT NULL
SQL
end
def down
drop_table :product_details
end
end
# Step 2: Update models and application code to use the new association.
# Dual-write to both the JSON column and the new table during transition.
# Step 3: Rename the JSON column to confirm nothing depends on it.
class ArchiveProductDetailsJson < ActiveRecord::Migration[8.0]
def change
rename_column :products, :details, :archived_details
end
end
# Step 4: Drop it after the transition period.
class DropArchivedDetailsFromProducts < ActiveRecord::Migration[8.0]
def change
remove_column :products, :archived_details, :jsonb
end
end
Replace one-to-many with a join table
This refactoring prepares a one-to-many relationship to become many-to-many. Say employees currently belong to one manager via a manager_id foreign key. A new business requirement might be: employees need to report to multiple managers. The foreign key on employees can't represent that, so you introduce a join table.
Since one-to-many is a subset of many-to-many, the join table handles both the existing data and the new requirement. It also gives you a place to store metadata about the relationship (when the assignment started, the type of reporting relationship) that doesn't belong on either the employees or managers table.
class CreateManagerAssignments < ActiveRecord::Migration[8.0]
def up
create_table :manager_assignments do |t|
t.references :employee, null: false, foreign_key: true
t.references :manager, null: false, foreign_key: { to_table: :employees }
t.timestamps
end
execute <<-SQL
INSERT INTO manager_assignments (employee_id, manager_id, created_at, updated_at)
SELECT id, manager_id, NOW(), NOW()
FROM employees
WHERE manager_id IS NOT NULL
SQL
end
def down
drop_table :manager_assignments
end
end
Be careful with this one, though. If the relationship genuinely isn't going to become many-to-many, you've added an extra join to every query for no reason and made the schema harder to understand. Build for actual requirements, not speculative ones.
Use Surrogate Key
A surrogate key is an artificial identifier, typically generated by the system, such as an auto-incrementing ID, UUID, etc. A natural key is derived from real-world data that already exists in the domain, one that has business meaning, e.g. SSN, email, etc.
Rails defaults to surrogate keys through the auto-incrementing id column, so this refactoring mostly comes up when you're inheriting a database from a non-Rails application or when someone previously used a natural key (email, ISBN, SSN) as the primary key.
The problem with natural keys is that they change. A user updates their email address, and suddenly you're cascading foreign key updates across a dozen tables. Natural keys also couple the schema tightly to the business domain in ways that get inconvenient as the domain evolves.
The fix is to add a surrogate key column, populate it with unique values, add a unique index, and update all foreign key references. The original natural key column stays in the table with its unique constraint; it just stops being the primary key. This touches a lot of tables if the natural key was widely referenced, so plan to split it across multiple deployments.
The main idea from these chapters is that the database schema should be treated as a living artifact that evolves alongside the application through small, deliberate refactorings.
Rails already gives you the migration infrastructure. The important thing is to think about the schema changes and how they'll affect the data and the application, and then apply refactorings proactively (improving the schema before adding features), decomposing large changes into sequences of small ones, and resisting the temptation to work around structural deficiencies with increasingly complex application code.
The next post will cover data quality refactorings, referential integrity techniques, architectural refactorings, and much more.
Comments
Nothing yet. Say the first thing.
Sign in to join the conversation.