From wp-dev
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.
How this skill is triggered — by the user, by Claude, or both
Slash command
/wp-dev:wordpress-databaseThe summary Claude sees in its skill listing — used to decide when to auto-load this skill
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.
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.
$wpdb->prepare() for any query containing user-supplied or variable data — no exceptions.$wpdb->prefix for table names — never hardcode wp_.WP_Query, meta API, and options API before reaching for raw SQL.dbDelta() for table creation and schema changes — it handles CREATE TABLE idempotently.wp_options and compare on plugin load.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
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' )
);
| Placeholder | Type | Example |
|---|---|---|
%d | Integer | 42 |
%f | Float | 19.99 |
%s | String | 'pending' |
%i | Identifier (table/column name, WP 6.2+) | order_id |
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 |
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.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\_%'" );
$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();
}
$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.
$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.
$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' => '>=',
),
),
) );
$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.
) );
// 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.
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' );
} );
// 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 |
// 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->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.
Recommended for development. Provides admin toolbar panel showing:
For advanced query patterns and indexing strategies, see references/advanced-queries.md. For the full custom tables reference, see references/custom-tables.md.
Provides CDSS development patterns for drug interaction checking, dose validation, clinical scoring (NEWS2, qSOFA), and alert classification integrated into EMR workflows.
npx claudepluginhub iwritec0de/claude-plugin-marketplace --plugin wp-dev