Database Schema Is More Than Just Tables: How to Change MySQL Structure Without Stopping the Application

Adding a column to a table may seem like a simple task. However, in a web application that is currently in use, changes to the database schema can impact many aspects simultaneously: old queries, API endpoints, reports, background processes, and even code that hasn't been updated yet.

The issue often isn't that MySQL can't change the table structure. The problem arises when changes are made without considering the order. The application reads the new column first, even though it doesn't exist yet. Or the old column is deleted while parts of the system are still using it.

Therefore, database changes should be treated like code changes: documented, tested, reviewable, and with a clear rollback path.

What is migration and why is it important?

Migration is a record of changes to the database structure that can be executed in an orderly manner. It can include creating tables, adding columns, changing indexes, or removing unused structures.

Without migration, database changes are usually made manually through phpMyAdmin or the terminal. This method is quick for experimentation but difficult to track when a project has more than one environment, such as local, staging, and production.

With migration, teams can know:

• What changes have been made.
• The correct order of changes.
• The version of the schema currently in use.
• The steps needed to set up a new server.

Migration is not just a tool for large developers. Even small projects benefit because the database no longer relies on one person's memory.

Do not directly change or delete old columns

One of the riskiest mistakes is making incompatible changes in one step. For example, if an application has a column name, and that column is immediately changed to full_name. If there is still old code running queries against name, the application may encounter errors after deployment.

A safer pattern is usually done in several stages.

1. Add the new structure without deleting the old structure.
2. Update the application to use the new structure.
3. Move or copy old data to the new structure.
4. Ensure that all parts of the application no longer use the old structure.
5. Delete the old structure in a separate migration.

This pattern is often referred to as the expand and contract approach. The system is first expanded to support the new form, then contracted once all parts are ready.

Example: adding order status

Suppose the orders table only has a column is_paid of numeric or boolean type. The team wants to replace it with clearer statuses like pending, paid, shipped, and cancelled.

Do not immediately delete is_paid. The first step can be to add a new column:

ALTER TABLE orders
ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'pending';

After that, the old data needs to be mapped. Orders with is_paid = 1 can be given the status paid, while the rest remain pending.

UPDATE orders
SET status = 'paid'
WHERE is_paid = 1;

The application code is then updated to read status. During the transition period, some processes may still write to is_paid. Therefore, the team needs to clearly define the source of truth. Do not allow two columns to change without rules, as their values could differ.

Once all processes are using status, the is_paid column can be deleted through another migration. This separation makes changes easier to test and reduces risks during phased deployments.

Pay attention to old data, not just the new structure

Migrations are often tested on empty databases and then considered safe. However, problems usually arise in production databases containing hundreds of thousands or millions of rows.

Operations such as filling values for all rows, creating large indexes, or changing data types can take a long time. During this process, tables may be locked or queries may slow down.

Before executing changes, check a few things:

• How many rows will be processed?
• Are the change queries using the correct conditions?
• Do the added columns have safe default values?
• Are the new indexes really necessary?
• Can the changes be executed in stages?

For large data, the backfill process—filling old data into the new structure—can be done in small batches. For example, the application processes a few thousand rows at a time, rather than executing one massive update that burdens the database.

Migration is not a substitute for backup

Migration helps change the schema consistently, but it does not automatically protect data from errors. Before significant changes, ensure that backups can be made and are truly recoverable.

Backups that have never been tested only provide a false sense of security. The team should at least know when the last backup was made, how to restore it, and how long the recovery process takes.

For risky changes, also prepare a rollback plan. However, keep in mind that rolling back a structure does not always mean data can be restored. If a column is deleted, the data within it may not be recoverable just by executing the reverse command.

A good rollback is not just an “undo” button. It must answer: what is being restored, from which backup, and how does the application continue to run during the process?

Test migration in a production-like environment

Local databases are often too small and too clean to represent real conditions. Before deployment, run migrations on staging with a structure and data size close to production.

In addition to checking if the migration is successful, also test the application after the changes:

• Can the old pages still be opened?
• Does the API still return the expected format?
• Do login, payment, search, and reporting processes still work?
• Has query time changed significantly?
• Can the old version of the application still run temporarily?

The last point is important for systems using multiple servers. During deployment, not all servers always switch versions at the same second. The new schema should remain temporarily compatible with the old version of the code.

What you can do now

Start with a simple rule: every database change must have a migration stored in the repository. Name migrations based on their purpose, not just numbers, such as add_status_to_orders or create_audit_logs_table.

Then, use a checklist before deployment:

1. Is the change compatible with the running code?
2. Is the old data correctly mapped?
3. Has the migration been tested on large data?
4. Are there backups and recovery procedures?
5. Have monitoring steps after deployment been determined?

Safe schema changes do not mean they never fail. It means failures can be detected more quickly, impacts are limited, and the team knows the next steps. By treating the database as an essential part of the application architecture—not just a place to store data—the development process becomes calmer and more predictable.

– Rio Yotto @rioyotto