Deploy New Features Without Damaging the Database: A Practical Guide to Database Migration

Adding new features to a website often seems straightforward: create a new column, modify a table, then deploy the code. Problems usually arise when database changes are made directly on the production server without clear documentation. The application may fail to read data, the rollback process becomes complicated, or developers may no longer remember what changes have been applied.

Database migration is a way to store changes to the database structure in the form of files or scripts that have a clear sequence. With this approach, database changes are treated like application code: they can be reviewed, tested, documented in version control, and applied consistently across different environments.

Why should database changes be managed like code?

Imagine a team has a local database, a testing database, and a production database. If changes are made manually using phpMyAdmin or SQL commands typed directly, it is easy for those three environments to have different structures.

On the developer's computer, the phone_number column may already exist. However, on the testing server, that column has not been created. The application looks fine on the local computer but fails when tested elsewhere. Without migration, the cause is often only discovered after an error occurs.

Migration provides several practical benefits:

• Clear change history: each change has a name, timestamp, and purpose that can be traced.
• More consistent environments: local, staging, and production databases can follow the same sequence of changes.
• Easier reviews: SQL changes can be reviewed alongside application code before being applied.
• More repeatable deployments: new developers or new servers do not have to guess the latest database structure.

Simple example: adding a new column

For example, an online store application wants to store the last time a customer logged in. This change can be written as a migration:

ALTER TABLE users
ADD COLUMN last_login_at DATETIME NULL;

In modern migration systems, that command is usually wrapped in a file with an up function to apply the change and a down function to revert it:

public function up()
{
    Schema::table('users', function ($table) {
        $table->dateTime('last_login_at')->nullable();
    });
}

public function down()
{
    Schema::table('users', function ($table) {
        $table->dropColumn('last_login_at');
    });
}

This example uses PHP framework syntax. The function names and file formats may differ in Laravel, Symfony, Phinx, Doctrine, or other tools. The principle remains the same: a significant change is stored as a single trackable step.

Migration is not just a collection of SQL commands

A common mistake is to think of migration merely as a place to store SQL. In fact, migration also needs to be part of the application change strategy.

If a new column is created directly as NOT NULL, while the old code still inserts without filling that column, the deployment may fail. Therefore, database changes and application changes need to be planned in a safe sequence.

Two-stage pattern for risky changes

1. Add new structure first. Create the new column as nullable or provide a safe default value.
2. Deploy compatible code. The new code starts writing data to that column but can still work if the column is not yet populated.
3. Gradually fill in old data. If necessary, run a backfill process to populate values in the old rows.
4. Tighten rules once safe. After all data is complete, only then can the column be changed to required or given a specific index.

This pattern is often referred to as expand and contract. The expand phase adds structure without breaking compatibility. The contract phase removes old structure once it is no longer in use.

Be cautious when deleting or renaming columns

Adding columns is usually relatively safe. In contrast, deleting or renaming columns can have an immediate impact on old code, reports, scheduled jobs, and other integrations.

For example, if the name column wants to be changed to full_name, a safer approach is not to delete name directly. Instead, add full_name, modify the application to write to both temporarily, copy old data, and ensure no code reads name. After the transition period is over, the old column can be deleted through a separate migration.

Rollback also needs to be understood realistically. The down command can indeed restore the structure, but it may not be able to restore data that has already been deleted or changed. Therefore, rolling back migration is not a substitute for backups.

Index: small in scripts, big impact

Migration is also often used to add indexes, which are additional structures that help the database find data faster. For example, a frequently searched email column can be given a unique index:

CREATE UNIQUE INDEX users_email_unique
ON users (email);

However, indexes are not an automatic solution for all queries. Too many indexes can increase the size of the database and make insert or update processes heavier because the database has to update those indexes. Before adding an index, examine the query patterns that are actually used and check the execution plan if available.

Checklist before running migration in production

• Test migration on a copy of the database or staging environment.
• Ensure there is a backup that has been tested for recovery, not just a backup file that has never been opened.
• Check if the changes could lock tables for too long.
• Ensure the old version of the application remains safe during the phased deployment.
• Measure the amount of data that needs to be changed if the migration performs backfill.
• Document time estimates, risks, and recovery steps.
• Run changes during low-traffic times if the impact is hard to predict.

What does this mean for us?

Database migration is not a feature exclusive to large companies. Simple PHP websites, small online stores, and internal applications will be easier to maintain if database changes have a clear history.

What can be done now is to review the latest database changes. Are there any changes that are only stored in personal notes? Is the local database different from the production server? If the answer is yes, start by creating a migration for the next change. There is no need to immediately transfer the entire old history. The important thing is to start building the habit that changes to the database structure should be readable, testable, and repeatable.

In this way, deployment no longer relies on the memory of one person. The team has the same change log, risks can be discussed before execution, and database issues are easier to handle as the application continues to evolve.

– Rio Yotto @rioyotto