dbdelta-dryrun

When schemas never settle

A plugin can call dbDelta() during installation, during a version check, or even on every request. The expectation is that once a table matches the plugin's CREATE TABLE statement, later calls have nothing left to change.

That can fail when the SQL and the database describe the same column differently. dbDelta() compares the text it parsed from each column line with the form reported by the server. If those forms never match, it can produce the same ALTER TABLE again on the next call. That means a schema change and its metadata lock can repeat every time the code reaches dbDelta().

The exact behavior depends on the database server. SQL that settles on MySQL 8.4 can keep producing changes on MariaDB. dbdelta-dryrun exists to test that behavior against the server your plugin supports instead of guessing from the SQL alone.

How it checks

dbdelta-dryrun is a WP-CLI command for CREATE TABLE statements. It rewrites the table names to a scratch prefix, creates those scratch tables, and then runs dbDelta() with the later writes blocked through WordPress's query filter.

The command first lets the scratch schema reach the state that dbDelta() wants. It then asks dbDelta() again. A statement still produced on that second pass is one that would run on every call. A statement that disappears is reported as "stops after one run".

The report identifies the affected column or index where possible, assigns a cause, shows what the SQL says and what the server reports, and gives the form the SQL should use. If it cannot name the cause, it still reports the mismatch with both sides shown.

The built-in cause checks cover cases such as int-display-width, enum-case, space-in-type, type-alias, default-format, two-columns-one-line, not-a-column, index-syntax, if-not-exists, and other column or index mismatches.

Decisions that matter

The command uses scratch tables because dbDelta() behavior cannot be reproduced from source text alone. The database server decides how types, defaults, aliases, indexes, and other schema details are stored and reported.

Scratch table names use the prefix selected by --prefix=<prefix>. The default is dbdd followed by six random characters and _. A table name beginning with the site's own prefix is moved to the scratch prefix too, so SQL copied from a plugin does not need its table names rewritten first.

The command refuses to start when a scratch table already exists. This keeps it from changing a table that it did not create. Unless --keep is used, the scratch tables are dropped before the command exits, including after a failure.

It also refuses to run when wp_get_environment_type() returns production. WordPress uses production by default when nothing sets an environment type. The README recommends a development or staging copy on the same database server version, or setting WP_ENVIRONMENT_TYPE appropriately.

Exit code 1 means a statement still runs on every call, a table could not be created, or, with --strict, that anything at all was reported.

Data and network use

The work happens against the WordPress database you point WP-CLI at. The command creates scratch tables, lets dbDelta() alter those tables once, checks the result, and removes the scratch tables unless --keep was requested.

Every other statement that dbDelta() would execute during the check is blocked through the query filter. There is one important exception to understand when using --from=<callable>. That option runs the plugin function or static method you name so the command can capture the SQL it passes to dbDelta(). Calls to dbDelta() are stopped before they run, but other writes the function makes, such as options, capabilities or rows, still happen. Use --from on a scratch site.

The tool makes no network requests. It uses no model and no API key.

Where it stops

The command checks CREATE TABLE statements only. INSERT and UPDATE statements supplied to dbDelta() are designed to run on every call, so the tool lists them as not checked.

Its diagnosis comes from the statement produced by dbDelta() and the schema information reported by the server. It does not claim that every mismatch fits a named category. Unknown cases still appear in the report.

The command tells you how the SQL should be written to match the database representation. It does not modify the plugin's source code.

Using --from=<callable> has a wider effect than checking a schema file because the callable itself runs. Only its call to dbDelta() is intercepted. Other database writes from that function remain possible.

The repository's tests/fixtures/pitfalls.sql example is synthetic. It contains nine small tables written to demonstrate the documented mistakes. Its output therefore shows the tool's reporting format rather than findings from a production plugin.

Install and run

You need PHP 7.4 or later, WP-CLI, and a WordPress installation connected to the database server you want to test. There are no other dependencies.

wp package install https://github.com/hamzaahmadaslam/dbdelta-dryrun.git
wp dbdelta-dryrun check schema.sql
wp dbdelta-dryrun check --from='Acme\Install::create_tables'

A schema file can use {prefix} for table names and {charset_collate} where the plugin normally appends $wpdb->get_charset_collate(). The --from form checks the SQL a callable sends to dbDelta() instead of reading files.

For CI, the README recommends running the check in a job that already starts a database for plugin tests. Run it once for each database server the plugin supports because results can differ between MySQL and MariaDB.