Why I wrote my own migrations system instead of using dbDelta

WordPress’s built-in dbDelta() is fine for simple plugins. The moment your schema gets non-trivial — foreign keys, ALTER TABLE, ordered migrations — it becomes a liability. Here’s the lightweight migrations runner I wrote in 100 lines that solved the problem.

WordPress plugins manage their own database tables via a function called dbDelta(). You feed it a CREATE TABLE statement, it figures out what’s already there, and it adjusts. Add a column? dbDelta will add it. Remove a column? dbDelta… usually doesn’t. Change a column type? Maybe. Add a foreign key? Almost certainly not in a way you can rely on.

For a plugin with three tables and a flat schema, dbDelta is fine. For anything serious, it’s a footgun.

I’m building a plugin with around twenty domain tables, foreign-key relationships between most of them, and a roadmap that includes adding columns, changing indices, and renaming things. I tried dbDelta for the first migration. By the second, I’d written my own runner.

This article shows the runner — about 100 lines of PHP — and explains why each piece exists.

Why dbDelta isn’t enough

Before I show what I built, let me be specific about where dbDelta fails. The official WordPress documentation lists some quirks. The ones that bit me:

Foreign keys are unreliable. dbDelta parses your CREATE TABLE statement to understand what should exist, then issues ALTER TABLE statements as needed. But its parser is fragile when faced with FOREIGN KEY ... REFERENCES ... ON DELETE RESTRICT syntax. On second runs, it sometimes tries to recreate the foreign key, fails because it already exists, and writes a vague error to the WordPress error log. The behaviour is environment-dependent — works on MariaDB 10.6, breaks on MySQL 8.0, or vice versa.

No version concept. dbDelta looks at your “desired schema” string and the current state of the database, and tries to converge them. If you change the desired schema between deploys, it might catch the change, or might not. There’s no record of which migrations have run. No way to write a migration that backfills data and only runs once.

Ordering is implicit. If migration B depends on migration A having run, dbDelta has no concept of “first run A, then B”. You either bundle them together (and lose granularity) or hope your activation hook runs them in source-code order (which is fragile).

No down migrations. If you add a column and then need to remove it, dbDelta won’t drop it. You have to write the ALTER TABLE DROP COLUMN yourself, and remember to remove it from the CREATE TABLE statement, and hope nobody’s running the old version.

For my project, I needed:

• An ordered list of migrations, each with a unique version
• A registry of which migrations have already run
• Real ALTER TABLE for adding foreign keys after table creation
• The ability to backfill or transform data inside a migration
• Integration tests that can rebuild the schema from scratch deterministically

So I wrote it.

The migrations runner

Here’s the entire Migrations class:

declare(strict_types=1);

namespace Avaliar\Database;

use wpdb;

final class Migrations
{
    private wpdb $db;
    private string $registryTable;

    public function __construct()
    {
        global $wpdb;
        $this->db = $wpdb;
        $this->registryTable = $wpdb->prefix . 'avaliar_migrations';
    }

    /**
     * Ensure the migration registry table exists. Safe to call repeatedly.
     */
    public function ensureRegistry(): void
    {
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        $charset = $this->db->get_charset_collate();
        dbDelta(
            "CREATE TABLE {$this->registryTable} (
                version BIGINT UNSIGNED NOT NULL,
                name VARCHAR(191) NOT NULL,
                applied_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (version)
            ) {$charset};"
        );
    }

    /**
     * Run all pending migrations in version order.
     */
    public function runPending(): void
    {
        $this->ensureRegistry();
        $applied = $this->appliedVersions();
        foreach ($this->discover() as $migration) {
            if (in_array($migration->version(), $applied, true)) {
                continue;
            }
            $migration->up();
            $this->markApplied($migration);
        }
    }

    /**
     * @return Migration[]
     */
    public function discover(): array
    {
        $dir = __DIR__ . '/migrations';
        if (!is_dir($dir)) {
            return [];
        }
        $files = glob($dir . '/*.php') ?: [];
        sort($files);

        $migrations = [];
        foreach ($files as $file) {
            /** @var Migration $instance */
            $instance = require $file;
            if (!$instance instanceof Migration) {
                continue;
            }
            $migrations[$instance->version()] = $instance;
        }
        ksort($migrations);
        return array_values($migrations);
    }

    /**
     * @return int[]
     */
    private function appliedVersions(): array
    {
        $rows = $this->db->get_col("SELECT version FROM {$this->registryTable}");
        return array_map('intval', $rows ?: []);
    }

    private function markApplied(Migration $m): void
    {
        $this->db->insert(
            $this->registryTable,
            [
                'version' => $m->version(),
                'name' => $m->name(),
                'applied_at' => current_time('mysql', true),
            ],
            ['%d', '%s', '%s']
        );
    }
}

That’s it. About 80 lines. Let me walk through the design choices.

The migration interface

Each migration is a class implementing this tiny interface:

namespace Avaliar\Database;

interface Migration
{
    public function version(): int;
    public function name(): string;
    public function up(): void;
}

A migration file looks like this:

// includes/Database/migrations/001_initial_schema.php

declare(strict_types=1);

use Avaliar\Database\Migration;

return new class implements Migration {
    public function version(): int
    {
        return 1;
    }

    public function name(): string
    {
        return 'initial_schema';
    }

    public function up(): void
    {
        global $wpdb;
        $charset = $wpdb->get_charset_collate();

        $wpdb->query("
            CREATE TABLE {$wpdb->prefix}avaliar_schools (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                school_id BIGINT UNSIGNED NOT NULL,
                name VARCHAR(191) NOT NULL,
                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                KEY idx_school (school_id)
            ) {$charset};
        ");

        // ... more CREATE TABLE statements
    }
};

Notice three things:

The migration is a returned anonymous class. This avoids polluting any namespace, gives each file complete isolation, and allows multiple migrations in the same project without name collisions.

up() only — no down(). I made a deliberate choice not to support reversible migrations. In the real world, the production data is the production data. Rollbacks happen by writing a new “fix it” migration, not by running down() and hoping the database returns to a previous state. The simplicity buys clarity.

Direct $wpdb->query() calls, not dbDelta(). I use raw CREATE TABLE and ALTER TABLE statements. They run once, they’re predictable, and wpdb->query returns false on failure (which I check in production code).

Why the version number lives in the file content, not the filename

You might notice the file is called 001_initial_schema.php but the class returns version() === 1. Why both?

The filename prefix (001_) is for ordering. glob() returns files in alphabetical order, so 001_* comes before 002_* comes before 010_*. This makes the order obvious to anyone reading the directory.

The integer in version() is the canonical version. It’s what gets stored in the registry table. The filename could change (e.g. someone renames 001_initial_schema.php to 001_create_initial_tables.php) and the registry wouldn’t break — the version is still 1.

This separation matters because filenames can drift. Versions can’t.

Registry table: the source of truth

The registry table is a single table with three columns:

CREATE TABLE wp_avaliar_migrations (
    version BIGINT UNSIGNED NOT NULL,
    name VARCHAR(191) NOT NULL,
    applied_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (version)
);

After running, it looks like:

version	name	applied_at
1	initial_schema	2026-04-18 14:32:01
2	add_config_overrides	2026-04-18 14:32:02
3	class_owner	2026-04-19 09:15:33

When runPending() is called, it queries this table for the list of applied versions, then iterates discovered migrations, skipping any that are already in the registry.

The PRIMARY KEY (version) matters. If for some reason the same migration runs twice (concurrent activation, bug in the runner), the second INSERT INTO ... VALUES (1, ...) fails with a duplicate key error, preventing data corruption.

When the runner is invoked

Three triggers run the migrations:

// 1. Plugin activation (first install + upgrade)
register_activation_hook(__FILE__, function () {
    (new \Avaliar\Database\Migrations())->runPending();
});

// 2. Plugin update (when WordPress detects a new version)
add_action('upgrader_process_complete', function ($upgrader, $hook_extra) {
    if ($hook_extra['type'] === 'plugin'
        && in_array(plugin_basename(AVALIAR_FILE), $hook_extra['plugins'] ?? [])) {
        (new \Avaliar\Database\Migrations())->runPending();
    }
}, 10, 2);

// 3. Manual via WP-CLI (for staging environments without UI)
WP_CLI::add_command('avaliar migrate', function () {
    (new \Avaliar\Database\Migrations())->runPending();
    WP_CLI::success('Migrations complete');
});

The activation hook handles new installs. The upgrader_process_complete hook handles updates pushed via the WordPress admin or WP-CLI plugin commands. The custom WP-CLI command lets a sysadmin force-run migrations on a server (useful when you’ve manually copied plugin files and need to provoke the schema update).

All three call the same runPending(). Idempotent — calling it twice is fine, the registry filters out already-applied migrations.

Migrations as data transformations, not just schema changes

Here’s where having a real registry pays off. Sometimes a migration isn’t just ALTER TABLE — it’s also data backfill. For example, when I added the school_id column to the students table (preparing for multi-tenancy), I also needed to populate it with 1 for all existing rows:

// includes/Database/migrations/002_add_school_id_to_students.php

return new class implements Migration {
    public function version(): int { return 2; }
    public function name(): string { return 'add_school_id_to_students'; }

    public function up(): void
    {
        global $wpdb;
        $table = "{$wpdb->prefix}avaliar_students";

        // 1. Add the column, nullable initially
        $wpdb->query("ALTER TABLE {$table} ADD COLUMN school_id BIGINT UNSIGNED NULL");

        // 2. Backfill: every existing student belongs to school_id = 1
        $wpdb->query("UPDATE {$table} SET school_id = 1 WHERE school_id IS NULL");

        // 3. Now make it NOT NULL with default for safety
        $wpdb->query("ALTER TABLE {$table} MODIFY COLUMN school_id BIGINT UNSIGNED NOT NULL DEFAULT 1");

        // 4. Add an index
        $wpdb->query("ALTER TABLE {$table} ADD KEY idx_school (school_id)");
    }
};

This is a multi-step migration that has to run exactly once. With dbDelta, you couldn’t do step 2 (the backfill) — it only handles schema. With my runner, the migration is just PHP code; whatever you can do in PHP, you can do in a migration.

Foreign keys: the elephant in the room

I’ll be honest: I haven’t added foreign keys yet, even though the architecture document calls for them. The reason is that I’m still in early-stage development where schema changes are frequent, and FKs make changes harder. Adding a column to table A that’s referenced by table B’s FK requires dropping the FK first.

My plan, documented as conscious technical debt:

Architecture decision (April 2026):
- v0.1 to v0.5: no FKs in schema. Integrity enforced at application
  layer via Repositories + Domain Services.
- v0.6+: introduce migration `0NN_add_foreign_keys.php` that uses
  `ALTER TABLE ... ADD CONSTRAINT` directly. This works where dbDelta
  fails. Justification: DB-level integrity is more robust than app-level
  in scenarios involving bulk imports, manual data manipulation via
  WP-CLI/Adminer, or repository-layer bugs.

The migration when I add them will look like:

return new class implements Migration {
    public function version(): int { return 15; }
    public function name(): string { return 'add_foreign_keys'; }

    public function up(): void
    {
        global $wpdb;
        $p = $wpdb->prefix;

        $wpdb->query("
            ALTER TABLE {$p}avaliar_students
            ADD CONSTRAINT fk_students_school
            FOREIGN KEY (school_id) REFERENCES {$p}avaliar_schools (id)
            ON DELETE RESTRICT ON UPDATE CASCADE
        ");

        $wpdb->query("
            ALTER TABLE {$p}avaliar_classes
            ADD CONSTRAINT fk_classes_subject
            FOREIGN KEY (subject_id) REFERENCES {$p}avaliar_subjects (id)
            ON DELETE RESTRICT ON UPDATE CASCADE
        ");

        // ... etc
    }
};

Critically: this works because each ALTER TABLE ADD CONSTRAINT runs once, in a known sequence, after all the referenced tables exist. dbDelta would have struggled because it can’t easily reason about cross-table dependencies.

Integration tests: the killer app

The biggest payoff of having a deterministic, ordered migrations system: I can build the schema from scratch in test setup, run my domain logic against it, and tear it down. No relying on a “test fixtures” file that drifts from production schema.

abstract class IntegrationTestCase extends \WP_UnitTestCase
{
    protected function setUp(): void
    {
        parent::setUp();
        $this->dropAllAvaliarTables();
        (new \Avaliar\Database\Migrations())->runPending();
    }

    private function dropAllAvaliarTables(): void
    {
        global $wpdb;
        $tables = $wpdb->get_col(
            "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
             WHERE TABLE_SCHEMA = DATABASE()
               AND TABLE_NAME LIKE '{$wpdb->prefix}avaliar_%'"
        );
        $wpdb->query('SET FOREIGN_KEY_CHECKS = 0');
        foreach ($tables as $t) {
            $wpdb->query("DROP TABLE IF EXISTS {$t}");
        }
        $wpdb->query('SET FOREIGN_KEY_CHECKS = 1');
    }
}

Every integration test starts with a fresh schema generated by running every migration in order. This catches a class of bugs where a migration works against an existing schema but breaks against a clean install (or vice versa).

What I learned

Simple is robust. The runner is 80 lines and has zero dependencies beyond $wpdb and the filesystem. There’s nothing in it I can’t debug in five minutes. I’ve seen migration libraries with hundreds of lines of magic — I don’t want that responsibility in my plugin.

The registry is non-negotiable. Without it, you don’t have migrations — you have hopeful schema synchronisation. The whole value of migrations comes from “we know what’s been applied”.

Down migrations are usually a bad idea. I’ve never reverted a production database via a down() method that worked. In real incidents, you write a new migration that fixes the broken state. Building a down() mechanism gives you the false comfort of thinking you can roll back, when in practice you can’t safely.

Idempotency is everything. runPending() should be safe to call any number of times. The registry guarantees this. A migration that’s been applied is skipped silently. Activation hooks fire multiple times on multi-site installs — you cannot afford a migration that fails the second time.

Plain SQL beats dbDelta for non-trivial schema. Once you have control over migration ordering and registry, raw ALTER TABLE is more predictable than dbDelta‘s heuristics. You write what you mean. The database does what you wrote.

If you’re building a WordPress plugin with more than three tables and any kind of schema evolution, write your own migrations runner. It’s an afternoon of work that pays back across the entire lifetime of the project. And if you want help thinking through your specific schema and migration strategy, I’m available.