wordpress-database

name	wordpress-database
description	This skill should be used when the user asks to "create a custom database table", "write a WP_Query", "use $wpdb", "run a meta query", "migrate a database schema", "optimize WordPress queries", "use dbDelta", "add an index", or mentions "custom table", "$wpdb", "WP_Query", "meta_query", "tax_query", "date_query", "dbDelta", "get_results", "prepare", "SAVEQUERIES", "Query Monitor", "object cache", "wp_cache", "database migration", "schema versioning". Provides WordPress database development expertise covering $wpdb, custom tables, schema migrations, WP_Query, meta/taxonomy/date queries, caching layers, and query debugging.
license	MIT
metadata	{"author":"Chris Kelley ([e-mail])","version":"1.0.0"}

WordPress Database Development

This skill covers WordPress database patterns including $wpdb usage, custom table creation, schema migrations, WP_Query advanced queries, meta/taxonomy/date queries, caching layers, and query debugging.

Critical Rules

1. Always use $wpdb->prepare() for any query containing user-supplied or variable data — no exceptions.
2. Always use $wpdb->prefix for table names — never hardcode wp_.
3. Use WordPress APIs first — WP_Query, meta API, and options API before reaching for raw SQL.
4. Use dbDelta() for table creation and schema changes — it handles CREATE TABLE idempotently.
5. Version your schema — store a version number in wp_options and compare on plugin load.
6. Never run schema changes on every page load — gate behind version checks.
7. Cache expensive queries — use transients or the object cache for repeated lookups.

$wpdb Core Methods

global $wpdb;

// Single value:
$count = $wpdb->get_var(
    $wpdb->prepare(
        "SELECT COUNT(*) FROM {$wpdb->prefix}orders WHERE status = %s",
        'completed'
    )
);

// Single row (object by default):
$row = $wpdb->get_row(
    $wpdb->prepare(
        "SELECT * FROM {$wpdb->prefix}orders WHERE id = %d",
        $order_id
    )
);

// Single column (flat array):
$ids = $wpdb->get_col(
    $wpdb->prepare(
        "SELECT id FROM {$wpdb->prefix}orders WHERE user_id = %d",
        $user_id
    )
);

// Multiple rows:
$results = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT id, total FROM {$wpdb->prefix}orders WHERE status = %s ORDER BY created_at DESC LIMIT %d",
        'pending',
        50
    )
);

// Output types for get_row / get_results:
$wpdb->get_row( $sql, OBJECT );   // Default — stdClass
$wpdb->get_row( $sql, ARRAY_A );  // Associative array
$wpdb->get_row( $sql, ARRAY_N );  // Numeric array

CRUD Methods

Prefer these for single-row operations — they handle escaping via format arrays:

// INSERT — returns false on failure.
$wpdb->insert(
    $wpdb->prefix . 'orders',
    array(
        'user_id'    => $user_id,
        'total'      => $total,
        'status'     => 'pending',
        'created_at' => current_time( 'mysql' ),
    ),
    array( '%d', '%f', '%s', '%s' )
);
$new_id = $wpdb->insert_id;

// UPDATE — returns rows affected or false.
$wpdb->update(
    $wpdb->prefix . 'orders',
    array( 'status' => 'completed' ),       // SET
    array( 'id' => $order_id ),             // WHERE
    array( '%s' ),                          // SET formats
    array( '%d' )                           // WHERE formats
);

// DELETE — returns rows affected or false.
$wpdb->delete(
    $wpdb->prefix . 'orders',
    array( 'id' => $order_id ),
    array( '%d' )
);

// REPLACE (insert or update on duplicate key):
$wpdb->replace(
    $wpdb->prefix . 'orders',
    array(
        'id'     => $order_id,
        'status' => 'refunded',
    ),
    array( '%d', '%s' )
);

Format Placeholders

Placeholder	Type	Example
%d	Integer	42
%f	Float	19.99
%s	String	'pending'
%i	Identifier (table/column name, WP 6.2+)	order_id

Custom Table Creation

register_activation_hook( __FILE__, 'myplugin_create_tables' );

function myplugin_create_tables(): void {
    global $wpdb;
    $table_name      = $wpdb->prefix . 'myplugin_orders';
    $charset_collate = $wpdb->get_charset_collate();

    // dbDelta rules:
    // - Each field on its own line.
    // - Exactly two spaces between column name and definition.
    // - KEY, not INDEX.
    // - Key name must be included: KEY status_idx (status).
    // - PRIMARY KEY must be on its own line with two spaces after.
    $sql = "CREATE TABLE {$table_name} (
        id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        user_id bigint(20) unsigned NOT NULL DEFAULT 0,
        total decimal(10,2) NOT NULL DEFAULT 0.00,
        status varchar(20) NOT NULL DEFAULT 'pending',
        notes text NOT NULL DEFAULT '',
        created_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
        updated_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
        PRIMARY KEY  (id),
        KEY user_id_idx (user_id),
        KEY status_idx (status),
        KEY created_at_idx (created_at)
    ) {$charset_collate};";

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

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

dbDelta() formatting rules — these are strict and will silently fail if violated:

Rule	Correct	Wrong
Spacing after column name	id bigint(20) (two spaces)	id bigint(20)
PRIMARY KEY line	PRIMARY KEY (id) (two spaces)	PRIMARY KEY (id)
Index keyword	KEY name (col)	INDEX name (col)
Named keys	KEY status_idx (status)	KEY (status)
Statement	One CREATE TABLE per call	Multiple statements

Schema Versioning & Migrations

add_action( 'plugins_loaded', 'myplugin_check_db_version' );

function myplugin_check_db_version(): void {
    $installed_version = get_option( 'myplugin_db_version', '0' );
    $current_version   = '1.2.0';

    if ( version_compare( $installed_version, $current_version, '<' ) ) {
        myplugin_run_migrations( $installed_version );
        update_option( 'myplugin_db_version', $current_version );
    }
}

function myplugin_run_migrations( string $from_version ): void {
    global $wpdb;
    $table = $wpdb->prefix . 'myplugin_orders';

    // Migration: 1.0.0 → 1.1.0 — add currency column.
    if ( version_compare( $from_version, '1.1.0', '<' ) ) {
        $col_exists = $wpdb->get_var(
            $wpdb->prepare(
                'SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND COLUMN_NAME = %s',
                DB_NAME,
                $table,
                'currency'
            )
        );

        if ( ! $col_exists ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange
            $wpdb->query( "ALTER TABLE {$table} ADD COLUMN currency varchar(3) NOT NULL DEFAULT 'USD' AFTER total" );
        }
    }

    // Migration: 1.1.0 → 1.2.0 — add composite index.
    if ( version_compare( $from_version, '1.2.0', '<' ) ) {
        $index_exists = $wpdb->get_var(
            $wpdb->prepare(
                'SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND INDEX_NAME = %s',
                DB_NAME,
                $table,
                'user_status_idx'
            )
        );

        if ( ! $index_exists ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange
            $wpdb->query( "ALTER TABLE {$table} ADD INDEX user_status_idx (user_id, status)" );
        }
    }

    // Re-run dbDelta to sync full schema (catches column type changes).
    myplugin_create_tables();
}

Uninstall Cleanup

// uninstall.php — runs when plugin is deleted from admin.
if ( ! defined( 'WP_UNINSTALL_PLUGIN' ) ) {
    exit;
}

global $wpdb;

// Drop custom tables.
$wpdb->query( "DROP TABLE IF EXISTS {$wpdb->prefix}myplugin_orders" );

// Remove options.
delete_option( 'myplugin_db_version' );

// Remove post meta (batch delete).
$wpdb->query( "DELETE FROM {$wpdb->postmeta} WHERE meta_key LIKE '_myplugin\_%'" );

WP_Query

Basic Usage

$query = new WP_Query( array(
    'post_type'      => 'product',
    'post_status'    => 'publish',
    'posts_per_page' => 20,
    'paged'          => get_query_var( 'paged' ) ?: 1,
    'orderby'        => 'date',
    'order'          => 'DESC',
) );

if ( $query->have_posts() ) {
    while ( $query->have_posts() ) {
        $query->the_post();
        the_title();
    }
    wp_reset_postdata();
}

Meta Queries

$query = new WP_Query( array(
    'post_type'  => 'product',
    'meta_query' => array(
        'relation' => 'AND',
        'price_clause' => array(
            'key'     => '_price',
            'value'   => array( 10, 50 ),
            'type'    => 'DECIMAL(10,2)',
            'compare' => 'BETWEEN',
        ),
        array(
            'key'     => '_stock_status',
            'value'   => 'instock',
            'compare' => '=',
        ),
    ),
    // Order by meta value using named clause:
    'orderby' => 'price_clause',
    'order'   => 'ASC',
) );

compare operators: =, !=, >, >=, <, <=, LIKE, NOT LIKE, IN, NOT IN, BETWEEN, NOT BETWEEN, EXISTS, NOT EXISTS, REGEXP, NOT REGEXP.

type cast values: NUMERIC, DECIMAL(10,2), CHAR, DATE, DATETIME, TIME, SIGNED, UNSIGNED, BINARY.

Taxonomy Queries

$query = new WP_Query( array(
    'post_type' => 'product',
    'tax_query' => array(
        'relation' => 'AND',
        array(
            'taxonomy' => 'product_cat',
            'field'    => 'slug',
            'terms'    => array( 'electronics', 'gadgets' ),
            'operator' => 'IN',
        ),
        array(
            'taxonomy' => 'product_tag',
            'field'    => 'term_id',
            'terms'    => array( 42 ),
            'operator' => 'NOT IN',
        ),
    ),
) );

field options: term_id (default), name, slug, term_taxonomy_id. operator options: IN, NOT IN, AND, EXISTS, NOT EXISTS.

Date Queries

$query = new WP_Query( array(
    'post_type'  => 'post',
    'date_query' => array(
        'relation' => 'AND',
        array(
            'after'     => '2024-01-01',
            'before'    => array(
                'year'  => 2024,
                'month' => 12,
                'day'   => 31,
            ),
            'inclusive' => true,
        ),
        array(
            'hour'    => 9,
            'compare' => '>=',
        ),
    ),
) );

Performance Tips for WP_Query

$query = new WP_Query( array(
    'post_type'              => 'product',
    'posts_per_page'         => 100,
    'no_found_rows'          => true,   // Skip SQL_CALC_FOUND_ROWS when no pagination needed.
    'update_post_meta_cache' => false,  // Skip meta cache priming if not reading meta.
    'update_post_term_cache' => false,  // Skip term cache priming if not reading terms.
    'fields'                 => 'ids',  // Return only post IDs instead of full objects.
) );

Meta API

// Post meta:
update_post_meta( $post_id, '_myplugin_price', '29.99' );
$price = get_post_meta( $post_id, '_myplugin_price', true );
delete_post_meta( $post_id, '_myplugin_price' );

// User meta:
update_user_meta( $user_id, 'myplugin_preference', 'dark' );
$pref = get_user_meta( $user_id, 'myplugin_preference', true );

// Term meta:
update_term_meta( $term_id, 'myplugin_color', '#ff0000' );
$color = get_term_meta( $term_id, 'myplugin_color', true );

// Prefix private meta with underscore to hide from Custom Fields UI.
// Third param true = single value, false = array of all values.

Caching Layers

Transients (Persistent, Survives Page Loads)

function myplugin_get_top_products(): array {
    $cache_key = 'myplugin_top_products';
    $products  = get_transient( $cache_key );

    if ( false !== $products ) {
        return $products;
    }

    global $wpdb;
    $products = $wpdb->get_results(
        "SELECT p.ID, p.post_title, pm.meta_value AS total_sales
         FROM {$wpdb->posts} p
         INNER JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id
         WHERE p.post_type = 'product'
           AND p.post_status = 'publish'
           AND pm.meta_key = 'total_sales'
         ORDER BY CAST(pm.meta_value AS UNSIGNED) DESC
         LIMIT 10"
    );

    set_transient( $cache_key, $products, HOUR_IN_SECONDS );

    return $products;
}

// Invalidate when a product is updated:
add_action( 'save_post_product', function (): void {
    delete_transient( 'myplugin_top_products' );
} );

Object Cache (Per-Request Unless Persistent Cache Plugin Installed)

// wp_cache_* uses the in-memory object cache.
// With a persistent backend (Redis, Memcached), it survives across requests.
$result = wp_cache_get( 'my_data', 'myplugin' );

if ( false === $result ) {
    $result = expensive_calculation();
    wp_cache_set( 'my_data', $result, 'myplugin', 300 );
}

Layer	Persists Across Requests	Requires Plugin	Best For
wp_cache_* (no backend)	No	No	Deduplicating queries within a single request
wp_cache_* (Redis/Memcached)	Yes	Yes	Frequently accessed data, low-latency reads
Transients	Yes (in wp_options)	No	API responses, computed aggregates
Transients (with object cache)	Yes (in cache backend)	Yes	Same as above, but avoids wp_options bloat

Query Debugging

SAVEQUERIES

// In wp-config.php (development only):
define( 'SAVEQUERIES', true );

// Then inspect:
global $wpdb;
echo '<pre>';
print_r( $wpdb->queries ); // Array of [ query, elapsed, caller ]
echo '</pre>';
echo 'Total queries: ' . count( $wpdb->queries );

$wpdb Error Checking

$wpdb->show_errors();    // Enable WP database error display (dev only).
$wpdb->suppress_errors(); // Suppress errors for expected failures.

// After any query:
if ( '' !== $wpdb->last_error ) {
    error_log( 'DB error: ' . $wpdb->last_error );
    error_log( 'Query: ' . $wpdb->last_query );
}

// Row counts:
$wpdb->num_rows;          // Rows returned by last SELECT.
$wpdb->rows_affected;     // Rows affected by last INSERT/UPDATE/DELETE.

Query Monitor Plugin

Recommended for development. Provides admin toolbar panel showing:

• All database queries with timing and caller
• Duplicate queries
• Slow queries
• HTTP API calls, hooks, and conditionals

For advanced query patterns and indexing strategies, see references/advanced-queries.md. For the full custom tables reference, see references/custom-tables.md.