Create a Custom Database Table in a WordPress Plugin with dbDelta

Home » MySQL » Create a Custom Database Table in a WordPress Plugin with dbDelta
August 10, 2022 MySQL
Create a Custom Database Table in a WordPress Plugin with dbDelta

WordPress uses the wp_postmeta table to store arbitrary key-value pairs for posts, users, and terms, but this schema becomes a bottleneck when you need relational queries, complex filtering, or high insertion volume. Creating a dedicated custom table gives you full control over columns, indexes, and query patterns optimised for your specific use case. The dbDelta() function, included in WordPress core, compares an existing table schema against a desired SQL definition and applies only the necessary ALTER TABLE statements. This makes it safe to call dbDelta() on every plugin activation without risk of data loss — it adds missing columns and indexes but never drops existing ones. Naming your table with the $wpdb->prefix value ensures compatibility with multi-site installations where each site has a unique prefix. The $wpdb->get_charset_collate() method returns the correct CHARACTER SET and COLLATE clause for the current WordPress database, preventing character encoding issues. Storing the table version in an option with update_option() lets you detect future schema upgrades and run dbDelta() selectively on plugin update. Always use $wpdb->prepare() for every query that includes user-supplied values to prevent SQL injection, even in internal plugin code. The database bloat cleanup guide explains how orphaned postmeta rows accumulate and why a separate table avoids that problem. For read-heavy workloads, adding a composite index on the columns most frequently used together in WHERE clauses dramatically reduces query time. See the EXPLAIN and composite index post for a step-by-step walkthrough of index selection. Custom tables also enable direct bulk inserts with $wpdb->query(), which is orders of magnitude faster than calling add_post_meta() in a loop.

Problem: Storing high-volume or relational plugin data in wp_postmeta causes expensive full-table scans and row contention that degrades performance for all WordPress queries.

Solution: Use register_activation_hook() with dbDelta() to create a typed, indexed custom table on activation, and always query it with $wpdb->prepare() to prevent SQL injection.

register_activation_hook(__FILE__, 'my_plugin_create_table');

function my_plugin_create_table() {
    global $wpdb;
    $table   = $wpdb->prefix . 'my_custom_data';
    $charset = $wpdb->get_charset_collate();

    $sql = sprintf(
        'CREATE TABLE %s (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            user_id bigint(20) NOT NULL,
            action_type varchar(50) NOT NULL,
            meta_value longtext NOT NULL,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            KEY user_id (user_id),
            KEY action_type (action_type)
        ) %s;',
        $table,
        $charset
    );

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

function get_user_actions(int $user_id): array {
    global $wpdb;
    $table = $wpdb->prefix . 'my_custom_data';
    return $wpdb->get_results(
        $wpdb->prepare(
            'SELECT id, action_type, meta_value, created_at FROM ' . $table
            . ' WHERE user_id = %d ORDER BY created_at DESC',
            $user_id
        )
    );
}

NOTE: dbDelta() requires exactly two spaces after each column's data type in the CREATE TABLE statement, and the PRIMARY KEY line must also have two spaces — deviating from this format causes the function to attempt a full table recreate on every activation.

Related posts