Drama-Free Database Migration: How to Change Data Structure in Web Applications

Adding a new column, renaming a table, or changing data formats often seems like simple tasks. The problem is that web application databases are usually used by many parts simultaneously: frontend pages, APIs, reports, scheduled jobs, and integrations with other services. A single change made directly in production can lead to errors that only become apparent after users access certain features.

This is where database migration helps. Migration is a record of changes to the database structure created in the form of files and executed in sequence. This way, the team not only knows the current state of the database but can also track how the database reached that state.

Why should database changes be treated like code?

Without migration, database changes are often made manually through applications like phpMyAdmin or SQL commands that are only stored on someone's computer. This method may be sufficient for small projects, but it quickly becomes problematic when there are multiple developers, more than one server, or deployment processes that need to be repeated.

Imagine a developer adding a phone_number column on their laptop but forgetting to apply the same change on the staging server. The latest code is then tested and appears to fail because that column does not exist. Another situation could be more dangerous: changes have been made in production, but there is no clear record of commands when the team needs to set up a new server.

Migration makes database changes part of the source code. Changes can be reviewed through pull requests, tested in staging, and executed in a consistent order.

Examples of changes that should use migration

• Adding a new table, such as orders or invoices.
• Adding columns like status, created_at, or user_id.
• Creating or deleting indexes to improve query performance.
• Changing relationships between tables.
• Moving data from an old structure to a new structure.
• Changing data types or NULL rules on columns.

Frameworks like Laravel, Symfony, Rails, Django, and various database tools provide migration systems. However, the principles remain the same even if you are using pure PHP or running SQL directly.

Important principle: changes must be traceable

Migration files typically have names that include timestamps or sequence numbers. For example, a PHP project might have a file named 2026_09_24_100000_add_status_to_orders.php. This name indicates when the change was made and its purpose.

The content of a migration should focus on one clear change. Do not mix user table additions, order structure changes, and old data deletions in one large file. Smaller migrations are easier to understand, test, and fix when issues arise.

If the tool being used supports it, provide two-way changes: a process to apply changes and a process to roll them back. However, rollback does not mean that all migrations can always be safely undone. Deleting columns or data can result in lost information that cannot be recovered.

Do not make new columns mandatory right away

One common mistake is adding a new column with a NOT NULL rule when the table already contains a lot of data. The database will reject old rows if there are no values to fill that column. Even if the migration tool provides a default value, significant changes can still lock the table or slow down the application.

A safer pattern is to make changes in several stages:

1. Add the new column as nullable or with a safe default value.
2. Deploy code that starts writing data to the new column, without immediately stopping reading from the old column.
3. Gradually populate old data using scripts or background processes.
4. Ensure the entire application is using the new column.
5. Only then, if necessary, change the column to mandatory and remove the old structure.

This pattern is known as expand and contract. The expand phase adds new structures without breaking compatibility. The contract phase cleans up the old structure once it is no longer in use.

Simple example of compatible changes

For instance, the application previously stored full names in the full_name column. You want to separate it into first_name and last_name. Do not immediately delete full_name in the first deployment.

The first migration adds two new columns:

ALTER TABLE users
ADD COLUMN first_name VARCHAR(100) NULL,
ADD COLUMN last_name VARCHAR(100) NULL;

After that, the application code can write to the new columns while still reading full_name as a backup. Old data is gradually moved. Once all parts of the application are using the new columns, only then can the old column be deleted through a separate migration.

This example also shows that migration is not just about SQL. It needs to be designed alongside application code changes, data population processes, and deployment strategies.

Pay attention to indexes and table sizes

Adding indexes can speed up searches, but the process of creating them can also consume a lot of CPU, memory, and time. On small tables, the impact may not be noticeable. On tables containing millions of rows, creating indexes directly can disrupt user queries.

Before creating an index, check the queries that are frequently run. Indexes should be created based on real needs, not just adding indexes to all columns. Also, pay attention to the order of columns in composite indexes, as that order affects the queries that can use them.

For large databases, study the database support for online index creation or use specialized tools designed to reduce locks. The technical details differ between MySQL, PostgreSQL, and other database systems, so do not rely on assumptions from previous projects.

Test migrations before touching production

Migrations should be run in an environment that resembles production. Use a copy of the structure and, if possible, data sizes that closely match real conditions. A migration that completes in one second on an empty database may not be safe when run on large tables.

A simple checklist that can be used:

• Run migrations on a clean local database.
• Run all migrations from the beginning to ensure the order is correct.
• Test migrations on staging with realistic data.
• Check duration, lock usage, and impact on queries.
• Prepare backups and recovery procedures before deployment.
• Determine how to monitor errors after changes are applied.

Do not consider rollback as a substitute for backup. Rolling back structures does not necessarily restore changed data. Backups and testing restores remain an important part of the database change plan.

What does this mean for us?

Migration is not just a formality for large projects. Even in small applications, migration helps reduce reliance on one person's memory and makes setting up new servers more consistent.

Start with simple habits: every schema change should have a migration file, one clear purpose, and be tested before going into production. For risky changes, break the process into several deployments so that both old and new code versions can run simultaneously.

With this approach, database changes are no longer a stressful manual task. They become a planned process that can be reviewed, tested, monitored, and if necessary, fixed without causing the entire application to stop.

– Rio Yotto @rioyotto