When a user installs your plugin for the first time, everything is clean. You create tables, add options, and move on.

But when a user updates your plugin, the database already has data. You can’t just overwrite things.

Here’s what can go wrong if you don’t handle upgrades properly:

  • Old data becomes incompatible with new code
  • Features stop working silently
  • Users lose data
  • Support tickets explode
  • Your plugin gets bad reviews

An upgrade script solves one problem:
It safely moves the database from the old structure to the new one.

Think of it like renovating a house while people still live inside.

The Core Idea Behind Upgrade Scripts

Every plugin should know which version of the database is currently installed.

When the plugin updates:

  1. Check the current database version
  2. Compare it with the plugin’s latest version
  3. Run only the necessary changes
  4. Update the stored database version

That’s it. Everything else builds on this idea.

Step 1: Store a Database Version

Never rely on the plugin version alone.
Code versions and database structures don’t always change together.

You store a database version using get_option() and update_option().

Example:

  • Database version: 1.0
  • Plugin version: 1.3.2

Add a constant in your plugin:

define( 'MY_PLUGIN_DB_VERSION', '1.2' );

Store it during installation:

add_option( 'my_plugin_db_version', MY_PLUGIN_DB_VERSION );

This option becomes your source of truth.

Step 2: Create an Install Function

Your install function runs when the plugin is activated for the first time.

This function should:

  • Create tables
  • Add default options
  • Save the database version

Example:

function my_plugin_install() {
    global $wpdb;

    $table_name = $wpdb->prefix . 'my_plugin_data';

    $charset_collate = $wpdb->get_charset_collate();

    $sql = "CREATE TABLE $table_name (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        user_id BIGINT UNSIGNED NOT NULL,
        meta_value TEXT NOT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id)
    ) $charset_collate;";

    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    dbDelta( $sql );

    add_option( 'my_plugin_db_version', MY_PLUGIN_DB_VERSION );
}
register_activation_hook( __FILE__, 'my_plugin_install' );

This handles fresh installs.
Now let’s deal with updates.

Step 3: Detect When an Upgrade Is Needed

Every time WordPress loads, your plugin should check:

  • Does the stored database version match the latest version?

If not, run the upgrade logic.

Example:

function my_plugin_check_upgrade() {
    $installed_version = get_option( 'my_plugin_db_version' );

    if ( $installed_version !== MY_PLUGIN_DB_VERSION ) {
        my_plugin_run_upgrade( $installed_version );
    }
}
add_action( 'plugins_loaded', 'my_plugin_check_upgrade' );

This ensures upgrades happen automatically, without user action.

Step 4: Write Version-Based Upgrade Steps

This is the most important part.

Never write one big upgrade function.
Always upgrade step by step, based on versions.

Why?

  • Users might skip versions
  • You need predictable upgrades
  • Debugging becomes easier

Example structure:

function my_plugin_run_upgrade( $installed_version ) {
    if ( version_compare( $installed_version, '1.1', '<' ) ) {
        my_plugin_upgrade_to_1_1();
    }

    if ( version_compare( $installed_version, '1.2', '<' ) ) {
        my_plugin_upgrade_to_1_2();
    }

    update_option( 'my_plugin_db_version', MY_PLUGIN_DB_VERSION );
}

Each function handles one change.

Step 5: Example Upgrade — Adding a New Column

Let’s say version 1.2 adds a new column called status.

Upgrade function:

function my_plugin_upgrade_to_1_2() {
    global $wpdb;

    $table_name = $wpdb->prefix . 'my_plugin_data';

    $wpdb->query(
        "ALTER TABLE $table_name ADD status VARCHAR(20) DEFAULT 'active'"
    );
}

That’s it.
Simple. Clear. Controlled.

Step 6: Updating Data, Not Just Structure

Sometimes you don’t change tables—you change how data works.

Example:

  • Old meta values: yes / no
  • New format: 1 / 0

Upgrade function:

function my_plugin_upgrade_to_1_3() {
    global $wpdb;

    $table = $wpdb->prefix . 'my_plugin_data';

    $wpdb->query(
        "UPDATE $table SET meta_value = '1' WHERE meta_value = 'yes'"
    );

    $wpdb->query(
        "UPDATE $table SET meta_value = '0' WHERE meta_value = 'no'"
    );
}

This kind of upgrade is common—and dangerous if skipped.

Step 7: Handling Large Data Safely

Never assume small datasets.

For large sites:

  • Avoid running heavy queries on every page load
  • Use batch processing if needed
  • Consider background processing for very big updates

Simple batching example:

$limit = 500;
$offset = 0;

do {
    $rows = $wpdb->get_results(
        $wpdb->prepare(
            "SELECT id FROM $table LIMIT %d OFFSET %d",
            $limit,
            $offset
        )
    );

    foreach ( $rows as $row ) {
        // Update each row
    }

    $offset += $limit;
} while ( count( $rows ) === $limit );

This keeps the site responsive.

Step 8: Always Make Upgrade Scripts Safe to Re-run

Upgrades should be idempotent.
That means running them twice should not break anything.

Bad example:

  • Blindly adding the same column again

Better approach:

  • Check before changing

Example:

$column_exists = $wpdb->get_results(
    "SHOW COLUMNS FROM $table_name LIKE 'status'"
);

if ( empty( $column_exists ) ) {
    $wpdb->query(
        "ALTER TABLE $table_name ADD status VARCHAR(20)"
    );
}

This avoids fatal errors.

Step 9: Never Break Old Sites

Some users:

  • Haven’t updated WordPress in years
  • Are running older PHP versions
  • Use custom database setups

Best practices:

  • Avoid strict assumptions
  • Use WordPress helper functions
  • Test upgrades on old data

Backward compatibility matters more than elegance.

Step 10: Testing Upgrade Scripts Properly

Before releasing:

  1. Install version 1.0
  2. Add real data
  3. Update directly to latest version
  4. Test skipped versions
  5. Check for data loss
  6. Check performance

Never trust fresh installs only.
Most bugs appear during upgrades.

Common Mistakes to Avoid

Let’s be direct.

Avoid these mistakes:

  • Running upgrade logic on every page load
  • Not tracking database versions
  • Writing one big upgrade function
  • Assuming users update every version
  • Ignoring large datasets
  • Forgetting to update the DB version

These mistakes cause most plugin failures.

A Simple Mental Model

When you think about database upgrades, remember this:

  • Code changes instantly
  • Data changes slowly and carefully
  • Users never forgive data loss

Your upgrade script is not optional.
It’s part of your product quality.

Final Thoughts

Writing upgrade scripts isn’t glamorous.
Users won’t see it.
Marketing won’t talk about it.

But this is where professional plugins separate themselves from hobby projects.

If you handle database upgrades well:

  • Your plugin scales
  • Users trust updates
  • Support load drops
  • Your product lives longer

Do it once. Do it cleanly.
Your future self will thank you.