wp-database

Name: wp-database
Author: mralaminahamed

25stars3forks

How to install wp-database?

mralaminahamed/wp-dev-skills/wp-database

$npx -y skills add mralaminahamed/wp-dev-skills --skill wp-database

Installs into the current project.

Summary

Use when a WordPress plugin needs a custom database table — creating with dbDelta (strict SQL format: two spaces before PRIMARY KEY, no trailing comma), writing versioned upgrade routines with version_compare and register_activation_hook, querying with $wpdb prepared statements (

Files · 1

View on GitHub

SKILL.md

1	# WordPress Custom Database Tables
2
3	> Model note: `dbDelta` schema and CRUD patterns are mechanical (`haiku`). Query optimisation and multi-version data migrations require cross-file reasoning — use `sonnet` for those sub-tasks.
4
5	Create and manage custom database tables in WordPress plugins: `dbDelta()` for schema definition, versioned upgrade routines, `$wpdb` CRUD with prepared statements, and data migration strategies.
6
7	## When to use
8
9	- "Create a custom DB table for my plugin", "set up plugin schema with dbDelta".
10	- "Write a migration for plugin upgrade", "run schema changes on update".
11	- "Query a custom table", "insert/update/delete with $wpdb".
12	- "Optimise a slow custom query", "add an index to a plugin table".
13	- "Migrate data from post meta to a custom table".
14
15	Not for: WooCommerce order table operations — use `wp-woocommerce`. General $wpdb query optimisation in core WP tables — use `wp-performance` (official skill).
16
17	## Method
18
19	### 1. Create table with dbDelta
20
21	`dbDelta()` is the only WP-safe way to create and alter tables — it diffs the current schema against the SQL and applies only the necessary changes.
22
23	```php
24	function my_plugin_create_tables() {
25	global $wpdb;
26	$charset_collate = $wpdb->get_charset_collate(); // e.g. DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci
27
28	// $wpdb->prefix respects multisite site prefix automatically
29	$table_log = $wpdb->prefix . 'my_plugin_log';
30	$table_meta = $wpdb->prefix . 'my_plugin_item_meta';
31
32	// IMPORTANT: two spaces before PRIMARY KEY, one space before each KEY
33	// IMPORTANT: no trailing comma on last field before closing paren
34	$sql = "CREATE TABLE {$table_log} (
35	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
36	item_id bigint(20) unsigned NOT NULL,
37	action varchar(100) NOT NULL DEFAULT '',
38	message longtext NOT NULL,
39	user_id bigint(20) unsigned NOT NULL DEFAULT 0,
40	created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
41	PRIMARY KEY (id),
42	KEY item_id (item_id),
43	KEY created_at (created_at)
44	) {$charset_collate};
45
46	CREATE TABLE {$table_meta} (
47	meta_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
48	item_id bigint(20) unsigned NOT NULL,
49	meta_key varchar(255) NOT NULL DEFAULT '',
50	meta_value longtext,
51	PRIMARY KEY (meta_id),
52	KEY item_id (item_id),
53	KEY meta_key (meta_key(191))
54	) {$charset_collate};";
55
56	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
57	dbDelta( $sql );
58	}
59	```
60
61	Critical dbDelta formatting rules (violations cause silent failures):
62	- Two spaces after `PRIMARY KEY` (e.g. `PRIMARY KEY (id)`)
63	- Field definitions must end with a comma except the last field before the closing paren
64	- `KEY` lines go after all field definitions, before the closing paren
65	- Only `CREATE TABLE` statements — no `ALTER TABLE` (dbDelta handles column additions, not removals)
66	- Always include `{$charset_collate}` at the end
67
68	### 2. Versioned upgrade routine
69
70	Track schema version in an option; only re-run dbDelta when the version changes:
71
72	```php
73	define( 'MY_PLUGIN_DB_VERSION', '1.3.0' );
74
75	function my_plugin_maybe_upgrade_db() {
76	$installed = get_option( 'my_plugin_db_version', '0' );
77	if ( version_compare( $installed, MY_PLUGIN_DB_VERSION, '>=' ) ) {
78	return; // already up to date
79	}
80
81	my_plugin_create_tables(); // always safe to re-run dbDelta
82
83	// Version-specific data migrations
84	if ( version_compare( $installed, '1.2.0', '<' ) ) {
85	my_plugin_migrate_to_1_2_0();
86	}
87	if ( version_compare( $installed, '1.3.0', '<' ) ) {
88	my_plugin_migrate_to_1_3_0();
89	}
90
91	update_option( 'my_plugin_db_version', MY_PLUGIN_DB_VER

People who liked this also viewed

6 picks

Type

wp-database

25stars3forks

View on GitHub

How to install wp-database?

mralaminahamed/wp-dev-skills/wp-database

$npx -y skills add mralaminahamed/wp-dev-skills --skill wp-database

Installs into the current project.

Summary

Files · 1

View on GitHub

SKILL.md

1	# WordPress Custom Database Tables
2
3	> Model note: `dbDelta` schema and CRUD patterns are mechanical (`haiku`). Query optimisation and multi-version data migrations require cross-file reasoning — use `sonnet` for those sub-tasks.
4
5	Create and manage custom database tables in WordPress plugins: `dbDelta()` for schema definition, versioned upgrade routines, `$wpdb` CRUD with prepared statements, and data migration strategies.
6
7	## When to use
8
9	- "Create a custom DB table for my plugin", "set up plugin schema with dbDelta".
10	- "Write a migration for plugin upgrade", "run schema changes on update".
11	- "Query a custom table", "insert/update/delete with $wpdb".
12	- "Optimise a slow custom query", "add an index to a plugin table".
13	- "Migrate data from post meta to a custom table".
14
15	Not for: WooCommerce order table operations — use `wp-woocommerce`. General $wpdb query optimisation in core WP tables — use `wp-performance` (official skill).
16
17	## Method
18
19	### 1. Create table with dbDelta
20
21	`dbDelta()` is the only WP-safe way to create and alter tables — it diffs the current schema against the SQL and applies only the necessary changes.
22
23	```php
24	function my_plugin_create_tables() {
25	global $wpdb;
26	$charset_collate = $wpdb->get_charset_collate(); // e.g. DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci
27
28	// $wpdb->prefix respects multisite site prefix automatically
29	$table_log = $wpdb->prefix . 'my_plugin_log';
30	$table_meta = $wpdb->prefix . 'my_plugin_item_meta';
31
32	// IMPORTANT: two spaces before PRIMARY KEY, one space before each KEY
33	// IMPORTANT: no trailing comma on last field before closing paren
34	$sql = "CREATE TABLE {$table_log} (
35	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
36	item_id bigint(20) unsigned NOT NULL,
37	action varchar(100) NOT NULL DEFAULT '',
38	message longtext NOT NULL,
39	user_id bigint(20) unsigned NOT NULL DEFAULT 0,
40	created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
41	PRIMARY KEY (id),
42	KEY item_id (item_id),
43	KEY created_at (created_at)
44	) {$charset_collate};
45
46	CREATE TABLE {$table_meta} (
47	meta_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
48	item_id bigint(20) unsigned NOT NULL,
49	meta_key varchar(255) NOT NULL DEFAULT '',
50	meta_value longtext,
51	PRIMARY KEY (meta_id),
52	KEY item_id (item_id),
53	KEY meta_key (meta_key(191))
54	) {$charset_collate};";
55
56	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
57	dbDelta( $sql );
58	}
59	```
60
61	Critical dbDelta formatting rules (violations cause silent failures):
62	- Two spaces after `PRIMARY KEY` (e.g. `PRIMARY KEY (id)`)
63	- Field definitions must end with a comma except the last field before the closing paren
64	- `KEY` lines go after all field definitions, before the closing paren
65	- Only `CREATE TABLE` statements — no `ALTER TABLE` (dbDelta handles column additions, not removals)
66	- Always include `{$charset_collate}` at the end
67
68	### 2. Versioned upgrade routine
69
70	Track schema version in an option; only re-run dbDelta when the version changes:
71
72	```php
73	define( 'MY_PLUGIN_DB_VERSION', '1.3.0' );
74
75	function my_plugin_maybe_upgrade_db() {
76	$installed = get_option( 'my_plugin_db_version', '0' );
77	if ( version_compare( $installed, MY_PLUGIN_DB_VERSION, '>=' ) ) {
78	return; // already up to date
79	}
80
81	my_plugin_create_tables(); // always safe to re-run dbDelta
82
83	// Version-specific data migrations
84	if ( version_compare( $installed, '1.2.0', '<' ) ) {
85	my_plugin_migrate_to_1_2_0();
86	}
87	if ( version_compare( $installed, '1.3.0', '<' ) ) {
88	my_plugin_migrate_to_1_3_0();
89	}
90
91	update_option( 'my_plugin_db_version', MY_PLUGIN_DB_VER

People who liked this also viewed

6 picks

Type