Custom Tables & dbDelta

When a custom table beats post meta, dbDelta's unforgiving formatting rules, and versioning your schema.

SyntaxdbDelta( string[]|string $queries = '', bool $execute = true ): string[]

Post meta is the default answer for data attached to a post, and it is the right one until it is not. A custom table earns its place when the data is not really a post — event log rows, subscription records, imported line items — when you need to query or sort by more than one column at once, when row counts run to hundreds of thousands, or when you need a real index and a unique constraint. The cost is that you lose everything for free: no admin UI, no REST route, no revisions, no caching, and you must write the delete path yourself.

dbDelta

dbDelta() diffs your CREATE TABLE statement against the live table and issues the ALTERs needed to reconcile them, so one function both installs and migrates. It lives in wp-admin/includes/upgrade.php, which is not loaded on the front end — require_once it first.

It parses with regular expressions, so the formatting rules are real constraints, not style:

  • Two spaces between PRIMARY KEY and the opening parenthesis.
  • One field or key per line.
  • Lowercase type names — bigint(20), not BIGINT(20).
  • KEY, not INDEX, and every key must be named.
  • No IF NOT EXISTS; it stops the diff from working.
  • End with $wpdb->get_charset_collate() so the table matches the site.

Versioning

Store a schema version in an option and compare it on load. Activation hooks do not fire when a plugin is updated, so the version check is what actually ships your migrations.

Example

Example · php
const ACME_DB_VERSION = '1.2';

function acme_install_tables(): void {
    global $wpdb;

    $table   = $wpdb->prefix . 'acme_events'; // Never hard-code "wp_".
    $charset = $wpdb->get_charset_collate();  // Matches the site's charset.

    // Formatting is load-bearing: one field per line, lowercase types,
    // KEY not INDEX, and TWO spaces after PRIMARY KEY.
    // varchar(191) is the safe indexed length under utf8mb4.
    $sql = "CREATE TABLE {$table} (
        id bigint(20) unsigned NOT NULL auto_increment,
        user_id bigint(20) unsigned NOT NULL default 0,
        event_name varchar(191) NOT NULL default '',
        payload longtext NULL,
        created datetime NOT NULL default '0000-00-00 00:00:00',
        PRIMARY KEY  (id),
        KEY user_id (user_id),
        KEY event_created (event_name,created)
    ) {$charset};";

    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    dbDelta( $sql ); // Creates the table, or alters it to match.

    update_option( 'acme_db_version', ACME_DB_VERSION, false );
}
register_activation_hook( __FILE__, 'acme_install_tables' );

// Activation does NOT fire when the plugin is updated in place, so the
// version check is what actually applies migrations on existing sites.
add_action( 'plugins_loaded', function (): void {
    if ( get_option( 'acme_db_version' ) !== ACME_DB_VERSION ) {
        acme_install_tables();
    }
} );

When to use it

  • Storing high-volume event or audit rows that would bloat wp_postmeta.
  • Holding relational data that needs a composite index or a unique constraint.
  • Shipping a schema change in plugin version 2 without a manual migration step.
  • Importing third-party records that are never edited through the post editor.

More examples

Migrating: what dbDelta will and will not do for you

How a second release of the same plugin adds a field. Inspecting dbDelta's return value is the fastest way to find out whether your formatting was accepted — an empty result when you expected an ALTER means the parser did not see your change.

Example · php
// Version 1.3 adds a column and an index. Edit the SAME CREATE TABLE
// statement - dbDelta diffs it against the live table and ALTERs the gap.
// Keep comments OUT of the SQL: dbDelta parses it line by line with
// regexes, so a trailing "-- note" is copied into the generated ALTER
// (a syntax error) and stops a KEY line from being recognised at all.
const ACME_DB_VERSION = '1.3';

// New in 1.3: the "source" column and the KEY on it.
$sql = "CREATE TABLE {$table} (
    id bigint(20) unsigned NOT NULL auto_increment,
    user_id bigint(20) unsigned NOT NULL default 0,
    event_name varchar(191) NOT NULL default '',
    source varchar(50) NOT NULL default 'web',
    payload longtext NULL,
    created datetime NOT NULL default '0000-00-00 00:00:00',
    PRIMARY KEY  (id),
    KEY user_id (user_id),
    KEY event_created (event_name,created),
    KEY source (source)
) {$charset};";

require_once ABSPATH . 'wp-admin/includes/upgrade.php';
$log = dbDelta( $sql ); // Returns the statements it ran, keyed by table.
error_log( print_r( $log, true ) );

// dbDelta cannot drop a column, rename one, or change a column to a
// narrower type safely. Do those by hand, guarded by the version check:
if ( version_compare( get_option( 'acme_db_version', '0' ), '1.3', '<' ) ) {
    $wpdb->query( "ALTER TABLE {$table} DROP COLUMN legacy_ref" );
}

Reading and writing the table

The payoff for the extra work: one indexed table scan instead of an EAV join. Compare the query plan against the postmeta version before committing to a custom table — if your data is small and only ever fetched by post ID, postmeta is still the better trade.

Example · php
function acme_log_event( int $user_id, string $name, array $payload ): int {
    global $wpdb;

    $wpdb->insert(
        $wpdb->prefix . 'acme_events',
        array(
            'user_id'    => $user_id,
            'event_name' => $name,
            'payload'    => wp_json_encode( $payload ),
            'created'    => current_time( 'mysql', true ), // GMT.
        ),
        array( '%d', '%s', '%s', '%s' )
    );

    return (int) $wpdb->insert_id;
}

function acme_recent_events( string $name, int $limit = 50 ): array {
    global $wpdb;
    $table = $wpdb->prefix . 'acme_events';

    // The composite KEY event_created makes this an index range scan.
    // The postmeta equivalent would be a join plus an unindexed compare.
    return $wpdb->get_results(
        $wpdb->prepare(
            "SELECT * FROM {$table}
              WHERE event_name = %s
           ORDER BY created DESC
              LIMIT %d",
            $name,
            $limit
        )
    );
}

Cleaning up on uninstall

Custom tables are not removed when a plugin is deleted unless you remove them. Ship this from the first release — retrofitting it later means every site that ever tried your plugin is carrying an orphan table.

Example · php
// uninstall.php, in the plugin root. WordPress runs this on delete,
// never on deactivation, and only if the plugin has no uninstall hook.
if ( ! defined( 'WP_UNINSTALL_PLUGIN' ) ) {
    exit; // Refuse to run if loaded directly.
}

global $wpdb;

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

// Identifiers cannot be %s. The name is built from $wpdb->prefix and a
// literal, so there is nothing untrusted in it.
$wpdb->query( "DROP TABLE IF EXISTS {$table}" );

delete_option( 'acme_db_version' );
delete_option( 'acme_settings' );

Related

wp wpdbwp wpdb preparewp post metawp plugin lifecyclewp db schema

Discussion

  • Be the first to comment on this lesson.