WordPress Custom Database Tables with dbDelta

WordPress stores almost everything in wp_posts and wp_postmeta, but high-volume plugins — analytics, logging, event ticketing — need dedicated tables with proper normalisation and indexed columns. The dbDelta() function handles schema creation and migration safely: it creates new tables, adds missing columns, and changes column types without destroying existing data, making it the correct tool for plugin activation and upgrade routines.

Problem: A WordPress plugin stores structured data — event registrations, API usage logs, subscription records — in wp_postmeta or wp_options because creating a custom database table seems complex and fragile across plugin updates.

Solution: Use dbDelta() from wp-admin/includes/upgrade.php in the plugin activation hook to create the custom table safely. dbDelta() compares the desired schema against the existing one and issues only the necessary ALTER TABLE statements — it is safe to run on every activation. Store the schema version in wp_options and call dbDelta() again on plugin update when the version changes.

The code below creates a custom table on plugin activation with dbDelta(), runs schema migrations on upgrade by comparing a stored version number, adds a typed data-access layer, and shows how to clean up on uninstall.

<?php
// ── 1. Create table on activation ────────────
register_activation_hook( __FILE__, 'my_plugin_create_tables' );

function my_plugin_create_tables(): void {
    global $wpdb;
    $table   = $wpdb->prefix . 'my_events';
    $charset = $wpdb->get_charset_collate();

    // dbDelta requires very specific formatting:
    // - Two spaces between column name and type
    // - PRIMARY KEY on its own line (not inline)
    // - No trailing comma on the last column before the closing paren
    $sql = "CREATE TABLE $table (
  id          BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  post_id     BIGINT(20) UNSIGNED NOT NULL,
  event_type  VARCHAR(50)         NOT NULL,
  event_data  LONGTEXT,
  created_at  DATETIME            NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY  (id),
  KEY idx_post  (post_id),
  KEY idx_type  (event_type),
  KEY idx_date  (created_at)
) $charset;";

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

    update_option( 'my_plugin_db_version', '1.0.0' );
}

// ── 2. Run migrations on plugin update ────────────
add_action( 'plugins_loaded', function () {
    $installed = get_option( 'my_plugin_db_version', '0' );
    if ( version_compare( $installed, '1.1.0', '<' ) ) {
        global $wpdb;
        $table = $wpdb->prefix . 'my_events';
        $charset = $wpdb->get_charset_collate();

        // Adding a new column — dbDelta handles this non-destructively
        $sql = "CREATE TABLE $table (
  id          BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  post_id     BIGINT(20) UNSIGNED NOT NULL,
  event_type  VARCHAR(50)         NOT NULL,
  event_data  LONGTEXT,
  user_id     BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
  created_at  DATETIME            NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY  (id),
  KEY idx_post  (post_id),
  KEY idx_type  (event_type),
  KEY idx_user  (user_id),
  KEY idx_date  (created_at)
) $charset;";

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        dbDelta( $sql );
        update_option( 'my_plugin_db_version', '1.1.0' );
    }
} );

// ── 3. Typed data-access layer ────────────
class My_Events_Table {

    private static function table(): string {
        global $wpdb;
        return $wpdb->prefix . 'my_events';
    }

    public static function insert( int $post_id, string $type, array $data = [], int $user_id = 0 ): int|false {
        global $wpdb;
        $result = $wpdb->insert(
            self::table(),
            [
                'post_id'    => $post_id,
                'event_type' => $type,
                'event_data' => wp_json_encode( $data ),
                'user_id'    => $user_id ?: get_current_user_id(),
            ],
            [ '%d', '%s', '%s', '%d' ]
        );
        return $result ? (int) $wpdb->insert_id : false;
    }

    public static function get_for_post( int $post_id, string $type = '', int $limit = 20 ): array {
        global $wpdb;
        $table = self::table();
        $sql   = $wpdb->prepare(
            "SELECT * FROM `$table` WHERE post_id = %d" . ( $type ? ' AND event_type = %s' : '' ) . " ORDER BY created_at DESC LIMIT %d",
            ...array_filter( [ $post_id, $type ?: null, $limit ] )
        );
        return $wpdb->get_results( $sql, ARRAY_A ) ?: [];
    }
}

// ── 4. Remove table on uninstall (uninstall.php) ────────────
// if ( ! defined( 'WP_UNINSTALL_PLUGIN' ) ) exit;
// global $wpdb;
// $wpdb->query( "DROP TABLE IF EXISTS {$wpdb->prefix}my_events" );
// delete_option( 'my_plugin_db_version' );

NOTE: dbDelta() can only add columns and indexes — it cannot drop columns, rename columns, or change a column's position; if your migration requires removing a column, run a raw ALTER TABLE ... DROP COLUMN statement after dbDelta(), and always back up the table before running destructive migrations on production.