Custom Tables and dbDelta in Practice

A plugin that stores nothing new needs no tables. The moment it has to remember a courier company, a tracking number and a log of status changes per parcel, somebody has to decide where that data lives, and the decision outlives the request that made it. Get it wrong and you find out two years later, when a store with eighty thousand orders discovers that every status lookup reads the whole log table.

Sifero Courier Timeline answered the question with two custom tables, named through helpers and created with dbDelta(). This lesson runs that install path on a real WordPress and takes dbDelta apart far enough to know which changes it will make and which it silently refuses.

dbDelta is a schema differ, not a migration engine

The name in the WordPress codebase is dbDelta(), and the delta is real: you hand it the CREATE TABLE statement your code wants, and it compares that statement against the table that exists. Columns that are missing get added, indexes that are missing get added, and a column whose type or default has changed gets an ALTER TABLE. It returns an array describing what it did, and runs those statements itself.

That is the whole feature. It is not versioned and has no concept of a change that removes something, so every surprise below comes from expecting a migration tool and getting a differ.

Here is the plugin's install function, abridged to the parts that matter (the full version is in includes/database.php, lines 232 to 299):

function sfct_install_tables() {
	global $wpdb;

	require_once ABSPATH . 'wp-admin/includes/upgrade.php';

	$charset_collate = $wpdb->get_charset_collate();
	$companies_table = sfct_companies_table();
	$logs_table      = sfct_logs_table();

	$sql_companies = "CREATE TABLE {$companies_table} ( ... ) {$charset_collate};";
	$sql_logs      = "CREATE TABLE {$logs_table} ( ... ) {$charset_collate};";

	dbDelta( $sql_companies );
	dbDelta( $sql_logs );
}

function sfct_logs_table() {
	global $wpdb;
	return $wpdb->prefix . 'sfct_delivery_logs';
}

Two details in those lines decide whether the site works. The require_once is there because dbDelta() lives in wp-admin/includes/upgrade.php, which is not loaded on a front-end request, so an activation hook that calls it without that line dies with a fatal error. The second is that sfct_install_tables() returns nothing, so the array dbDelta() built is thrown away.

The real companies table, column by column

Running the plugin's own install path on a clean WordPress 7.0.7 and dumping the table is more useful than reading the source, because the database normalises a few things on the way in. Here is the first table as MySQL 9.6 stores it:

CREATE TABLE `wp_sfct_delivery_companies` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(190) COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `logo` text COLLATE utf8mb4_unicode_520_ci,
  `color` varchar(20) COLLATE utf8mb4_unicode_520_ci DEFAULT '#2563eb',
  `api_url` text COLLATE utf8mb4_unicode_520_ci,
  `api_key` text COLLATE utf8mb4_unicode_520_ci,
  `token` text COLLATE utf8mb4_unicode_520_ci,
  `auth_type` varchar(50) COLLATE utf8mb4_unicode_520_ci DEFAULT 'none',
  `status` varchar(20) COLLATE utf8mb4_unicode_520_ci DEFAULT 'active',
  `notes` text COLLATE utf8mb4_unicode_520_ci,
  `tracking_base_url` text COLLATE utf8mb4_unicode_520_ci,
  `tracking_button_label` varchar(190) COLLATE utf8mb4_unicode_520_ci DEFAULT '',
  `tracking_instructions` text COLLATE utf8mb4_unicode_520_ci,
  `external_tracking_enabled` tinyint(1) DEFAULT '1',
  `timeout` int DEFAULT '15',
  `custom_headers` longtext COLLATE utf8mb4_unicode_520_ci,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `status` (`status`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

name and tracking_button_label are varchar(190). The 190 is not a WordPress rule. It is the number people settled on because utf8mb4 uses four bytes per character and an index on the column had to fit inside the 767-byte limit older MySQL builds imposed on a single key. The plugin indexes name, so a wider column would have been a problem on those servers.

The credential columns, api_url, api_key and token, are text rather than varchar: the honest choice for a value whose length you do not control. It is worth naming what that does not do. A courier API key sits in this table in plain text, readable by anything that can run a query. custom_headers goes further and is longtext, because it holds JSON with room to spare.

created_at and updated_at are not nullable and have no default, so every insert supplies them. The plugin passes current_time( 'mysql' ), which is the site's local time rather than UTC, and that is the first thing that will bite if those rows ever move between sites in different timezones.

get_charset_collate() supplies the last line, and the call is not decoration. It builds the clause from the site's configured charset and, where the server advertises the capability, upgrades utf8mb4_unicode_ci to utf8mb4_unicode_520_ci. That is why the collation above reads 520_ci here and would read differently on a MariaDB host. Hard-code a collation and you own a bug that appears on one customer's server.

Four rules that decide whether dbDelta works at all

The parsing in dbDelta() is a set of regular expressions over your SQL string, and a statement that does not match those expressions fails in ways you will not see.

One column per line. The function splits the body of your statement on newlines and treats each line as one column definition or one index. Two column definitions on one line means the second is not parsed as a column at all. Nothing errors; the column is absent from the table.

At least one space before the bracket on an index line. This is the rule people half-remember as "two spaces after PRIMARY KEY". Two spaces is a style the WordPress core tables use, not a requirement. The parser accepts PRIMARY\s+KEY followed by \s+, so one space and a tab both work on WordPress 7.0.7, and they have worked since at least 4.9. What fails is writing the statement the way MySQL itself allows:

// Works: one or more spaces between KEY and the bracket.
"PRIMARY KEY (id)"    => dbDelta adds "PRIMARY KEY (`id`)"
"PRIMARY KEY  (id)"   => dbDelta adds "PRIMARY KEY (`id`)"

// Fails, silently, and returns as if it had succeeded:
"PRIMARY KEY(id)"     => dbDelta emits ALTER TABLE ... ADD KEY `` (``)
                          MySQL rejects it, dbDelta keeps going

Measured on the plugin's own logs table: with PRIMARY KEY(id), the ALTER TABLE that dbDelta() built was ADD KEY `` (``), the database answered You have an error in your SQL syntax, and the table was left with a uniq_id index where the primary key should have been. PHP also emitted Undefined array key "index_type" from inside upgrade.php, which is the only outward sign you get. This is why the spacing matters, and it has nothing to do with two spaces.

Field definitions have to match what is already in the table. A column that exists with a type the statement does not describe gets an ALTER TABLE ... CHANGE COLUMN. Narrowing varchar(190) to varchar(100) is applied, on a table with rows in it.

Every path you write has to be re-runnable. dbDelta() is idempotent: two calls in a row returned an empty array the second time and changed nothing, and running the install against a logs table holding five thousand rows left all five thousand rows in place. That is what makes a version-gated upgrade routine safe to write.

The two things dbDelta will not do

Both of them are about removal, and both of them matter on a plugin that ships updates.

It will not drop a column. Add legacy_flag to the table by hand, leave it out of the CREATE TABLE statement, call sfct_install_tables(), and legacy_flag is still there.

It will not drop an index. Same for a key, and this is the more expensive half, because an index you no longer need keeps charging for every insert and update. When you need a removal, you write the ALTER TABLE ... DROP COLUMN yourself and guard it so it runs once. That is the version gate, and lesson 05 builds one.

One asymmetry is worth internalising: dbDelta() can add a missing index and a missing primary key, so a drifted table is usually repairable by re-running the install. But when it executes a statement the database rejects, it does not stop and does not throw. Dropping log_type_created from the logs table and re-running the real install function did restore the composite key, and the returned array said so. The plugin discards that array, so on a live site the only record is the table itself.

The table name belongs in a function

Both tables are named through helpers rather than a constant:

function sfct_companies_table() {
	global $wpdb;
	return $wpdb->prefix . 'sfct_delivery_companies';
}

function sfct_logs_table() {
	global $wpdb;
	return $wpdb->prefix . 'sfct_delivery_logs';
}

On a default install those return wp_sfct_delivery_companies and wp_sfct_delivery_logs. The point is the $wpdb->prefix in the middle. On a multisite network every site has its own prefix, so a site with an id of seven gets wp_7_sfct_delivery_logs, and the same function returns a different string depending on which site is current. A define() would freeze whichever value was correct when the file loaded, and every subsite would share the main site's rows. Whether that is a correctness bug or a data leak depends on what the table holds; for a table of courier API keys it is both.

The cost is a global lookup on every call, which is free in practice, and once $wpdb has been switched to another site the helper follows it without any code change. Lesson 06 uses that behaviour to install the tables on a new subsite.

When a custom table beats post meta or a taxonomy

WordPress gives you three places to put plugin data before you write any SQL, and choosing badly is expensive to undo.

Reach for wp_postmeta or wp_usermeta when the value belongs to one object, you read it with the object, and you rarely query across objects. Two meta rows per order is fine. Every extra row lands in a table the whole site shares, meta values are strings, and filtering on them means a slow LIKE or an index you cannot add without touching a core table.

Reach for a taxonomy when the thing you are storing is a set of terms that other things get assigned to, that editors should be able to browse and rename, and that you might want to join in a query. A courier as a taxonomy would be defensible if couriers were managed through the normal term screens.

Reach for a custom table when at least two of these are true. You write many rows per object over time, as a log does. You need columns with real types, because a datetime that sorts correctly is worth more than a string that does not. You need indexes that match the queries you run. You need the data out of a table editors browse. Or the volume is high enough that adding rows to a shared table would hurt everyone.

The logs table satisfies four of those. The companies table is a closer call: a store carries a handful of courier companies, not thousands, and a custom post type with meta would have worked. The plugin chose a table because the logs table references companies through company_id, which wants an integer column and an index, so the two decisions are one decision.

CREATE TABLE wp_sfct_lab (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  label varchar(190) NOT NULL, note text NULL,
  PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8mb4;

CREATE TABLE wp_sfct_lab (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  label varchar(190) NOT NULL,
  note text NULL,
  created_at datetime NOT NULL,
  PRIMARY KEY  (id),
  KEY label (label)
) DEFAULT CHARACTER SET utf8mb4;