Create and Upgrade Custom WordPress Database Tables with dbDelta

WordPress’s post/meta/option tables cover the majority of storage needs for themes and plugins. But sometimes you genuinely need a custom relational table — for high-volume event logs, complex many-to-many relationships, analytics data, or any structure where the EAV (entity-attribute-value) pattern of wp_postmeta would produce hundreds of joins. WordPress provides dbDelta() for exactly this: a function that compares a desired table definition against the current database schema and applies only the necessary changes — creating the table if it does not exist, adding missing columns, and changing column definitions, all without dropping existing data. Understanding how to use it correctly — including its strict whitespace requirements that trip up most developers on first use — is the key to writing reliable plugin installers.

Problem: Your plugin needs a dedicated database table to store structured data that does not fit the post/meta model. The table must be created on activation, updated safely when the plugin upgrades, and removed on uninstall.

Solution: Use dbDelta() inside the activation hook to create and migrate the table. Call it again on upgrade by comparing stored and current schema versions.

<?php
function my_plugin_create_tables() {
    global $wpdb;

    $table   = $wpdb->prefix . 'event_log';
    $charset = $wpdb->get_charset_collate();

    // CRITICAL: dbDelta requires exactly TWO spaces between the column name
    // and its definition, and a comma at the END of every line except the last.
    // Each KEY line must be on its own line. Any deviation silently fails.
    $sql = "CREATE TABLE $table (
  id         bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id    bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  event_type varchar(100)        NOT NULL,
  object_id  bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  extra_data longtext                     DEFAULT NULL,
  created_at datetime            NOT NULL,
  PRIMARY KEY  (id),
  KEY user_id (user_id),
  KEY event_type (event_type)
) $charset;";

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

register_activation_hook( __FILE__, 'my_plugin_activate' );

function my_plugin_activate() {
    my_plugin_create_tables();
    add_option( 'my_plugin_db_version', '1.0' );
}

// Run upgrade check on every admin page load
add_action( 'plugins_loaded', 'my_plugin_upgrade_check' );

function my_plugin_upgrade_check() {
    $current = get_option( 'my_plugin_db_version', '0' );
    if ( version_compare( $current, '1.1', '<' ) ) {
        // dbDelta will ADD the new column without touching existing data
        my_plugin_create_tables();
        update_option( 'my_plugin_db_version', '1.1' );
    }
}

Querying the custom table safely with $wpdb->prepare():

<?php
function get_user_events( $user_id, $limit = 20 ) {
    global $wpdb;
    $table = $wpdb->prefix . 'event_log';

    return $wpdb->get_results(
        $wpdb->prepare(
            "SELECT * FROM $table WHERE user_id = %d ORDER BY created_at DESC LIMIT %d",
            absint( $user_id ),
            absint( $limit )
        )
    );
}

NOTE: dbDelta() can only add columns and indexes — it never removes them. If you need to drop a column in a plugin upgrade, you must run the ALTER TABLE statement separately after calling dbDelta(). Also, dbDelta() is strict about the SQL format: the PRIMARY KEY definition must have two spaces between the words, and there must be no trailing comma after the last column definition before the closing parenthesis.