Wordpress Database

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

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:

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 );
}

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.