# Wordpress Database

> 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.

- Skill: `iwritec0de/wordpress-database` (Agent Skill, multi-file: 3 files)
- Install (CLI): `npx skillmds@latest add iwritec0de/wordpress-database`
- Raw SKILL.md: https://api.skillmd.com/api/skills/iwritec0de/wordpress-database/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- License: MIT
- Author: iwritec0de (https://skillmd.com/u/iwritec0de)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/iwritec0de/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

```php
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:

```php
// 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

```php
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

```php
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

```php
// 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

```php
$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

```php
$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

```php
$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

```php
$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

```php
$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

```php
// 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)

```php
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)

```php
// 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

```php
// 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

```php
$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`.