Contents
Introduction
This article is a complete, in-depth guide to creating and managing WordPress database tables from a plugin activation using dbDelta. It covers everything from the required includes and SQL structure to versioned upgrades, multisite handling, common pitfalls, and practical examples you can copy and adapt. The code examples are presented in ready-to-use PHP blocks.
What is dbDelta and why use it?
dbDelta is a WordPress helper function (in wp-admin/includes/upgrade.php) that parses CREATE TABLE SQL statements and creates or alters database tables to match that SQL. It is the recommended method when a plugin needs to create or update database schema because it attempts to add missing columns and indexes while preserving existing data.
High-level workflow
- On plugin activation call a function that builds your CREATE TABLE SQL.
- Include the upgrade.php file: require_once( ABSPATH . wp-admin/includes/upgrade.php ).
- Call dbDelta(sql) with one or more CREATE TABLE statements.
- Store a schema version in the options table so later plugin loads can run upgrade routines if the schema changed.
Core requirements and best practices
- Always use wpdb->prefix for table names (not hard-coded wp_). Use a variable like table_name = wpdb->prefix . your_table.
- Get correct charset/collation via charset_collate = wpdb->get_charset_collate() and append it to your CREATE TABLE statement.
- Include upgrade.php: dbDelta is defined in wp-admin/includes/upgrade.php. Include it before calling dbDelta.
- SQL formatting matters: dbDelta parses SQL with regex. Use backticks around field and table names and keep conventional spacing and newlines for better reliability.
- Store a DB version in the options table (eg. add_option(my_plugin_db_version,1.0)) and compare it on plugin load to run upgrades only when needed.
- Test on a backup: schema changes can break things test on a development or staging database before production.
- DB privileges: the DB user must have CREATE and ALTER privileges for dbDelta to work fully.
dbDelta limitations and gotchas
- dbDelta focuses on CREATE TABLE statements. It can add columns and indexes and sometimes modify column definitions, but it cannot reliably remove columns or fully replace complex column-type changes. For destructive changes use explicit ALTER TABLE queries or a controlled migration.
- Use PRIMARY KEY and KEY (or INDEX/UNIQUE KEY) syntax in standard forms. Inconsistent formatting can prevent dbDelta from recognizing keys.
- For utf8mb4 InnoDB unique keys, prefer varchar(191) or use appropriate column prefixes to avoid key length issues on older MySQL versions.
- dbDelta expects the table name in the CREATE TABLE statement to match the real table name (including prefix). Use the actual resulting name variable in the SQL.
- dbDelta ignores comments and certain nonstandard SQL. Keep the CREATE TABLE statements simple and standard.
- If you see nothing happening, check wpdb->last_error and enable WP_DEBUG to capture SQL errors.
Minimal, complete example: create table on activation
The example below shows a simple plugin activation routine that creates a table and saves the plugin DB version in the options table. The SQL uses wpdb->get_charset_collate() and dbDelta.
prefix . my_items
charset_collate = wpdb->get_charset_collate()
sql = CREATE TABLE {table_name} (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
name varchar(191) NOT NULL,
description text NOT NULL,
status tinyint(1) NOT NULL DEFAULT 0,
created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
KEY name (name),
KEY status (status)
) {charset_collate}
require_once( ABSPATH . wp-admin/includes/upgrade.php )
dbDelta( sql )
add_option( my_plugin_db_version, my_plugin_db_version )
}
register_activation_hook( __FILE__, my_plugin_install )
?>
Why varchar(191)?
When using utf8mb4 with older MySQL/InnoDB versions, an indexed VARCHAR needs to be limited to 191 characters to avoid index size limits. If you need longer indexed text, use a prefix index (e.g. KEY name (name(191))) or change server settings.
Detailed explanation of the example
- wpdb->prefix ensures correct table name for installations with custom prefixes or multisite.
- wpdb->get_charset_collate() yields proper ENGINE=… DEFAULT CHARSET=… COLLATE=… part so new tables match WP DB settings.
- CREATE TABLE statement includes explicit backticks and a semicolon at the end. dbDelta supports multiple CREATE TABLE statements in the same string if separated by semicolons and newlines.
- require_once upgrade.php loads dbDelta.
- add_option(my_plugin_db_version) keeps track of schema version for upgrades.
Versioned upgrades — how to change schema later
Always compare a stored DB version option with the current plugin schema version. If they differ, run dbDelta again with the new CREATE TABLE statement(s), then update the option. This approach allows adding columns or indexes safely without losing data.
prefix . my_items
charset_collate = wpdb->get_charset_collate()
// Add new column updated_at and an index on status
sql = CREATE TABLE {table_name} (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
name varchar(191) NOT NULL,
description text NOT NULL,
status tinyint(1) NOT NULL DEFAULT 0,
created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at datetime NULL DEFAULT NULL,
PRIMARY KEY (id),
KEY name (name),
KEY status (status)
) {charset_collate}
require_once( ABSPATH . wp-admin/includes/upgrade.php )
dbDelta( sql )
update_option( my_plugin_db_version, my_plugin_db_version )
}
}
add_action( plugins_loaded, my_plugin_update_check )
?>
Notes on upgrades
- dbDelta will add new columns and new indexes. It may attempt to change existing column definitions but not reliably for all cases.
- When you need to change a column type or drop columns, use explicit ALTER TABLE statements executed via wpdb->query().
- Always back up user data before applying destructive schema changes.
Multisite considerations
On multisite installs, register_activation_hook runs once for the network activation or for the single site activation. For network activates, you need to iterate all sites and create the table in each blog (unless you intentionally want a single global table).
get_col( SELECT blog_id FROM {wpdb->blogs} )
foreach ( blog_ids as blog_id ) {
switch_to_blog( blog_id )
my_plugin_install() // call the same installer that uses wpdb->prefix
restore_current_blog()
}
} else {
my_plugin_install()
}
}
register_activation_hook( __FILE__, my_plugin_network_activate )
?>
Uninstall and cleanup
If you want the plugin to remove its database tables on uninstall, implement an uninstall routine. Use register_uninstall_hook or an uninstall.php file. Be careful — uninstall should be intentional and documented.
prefix . my_items
// Drop the table
wpdb->query( DROP TABLE IF EXISTS {table_name} )
// Remove version option and any plugin options
delete_option( my_plugin_db_version )
// delete_option( my_plugin_some_other_option ) // etc.
}
// Register the uninstall hook (this file must be in the main plugin file)
register_uninstall_hook( __FILE__, my_plugin_uninstall )
?>
How to verify the table exists and basic CRUD with wpdb
Use wpdb to check for the table, and to insert / update / query safely.
prefix . my_items
// Check table exists
exists = wpdb->get_var( wpdb->prepare( SHOW TABLES LIKE %s, table ) )
if ( exists ) {
// Insert
wpdb->insert(
table,
array(
name => Example,
description => Demo record,
status => 1,
),
array( %s, %s, %d )
)
// Query rows
rows = wpdb->get_results( SELECT FROM {table} WHERE status = 1, ARRAY_A )
// Update
wpdb->update(
table,
array( status => 0 ),
array( id => 1 ),
array( %d ),
array( %d )
)
}
?>
Index, primary keys, unique constraints and composite keys
Examples and tips for indexes in a CREATE TABLE used by dbDelta:
- Primary key: PRIMARY KEY (id)
- Simple index: KEY name (name)
- Unique key: UNIQUE KEY slug (slug)
- Composite key: KEY post_type_status (post_type, status)
- Prefer keys on short columns or use prefix lengths for text columns (e.g. name(100)).
CREATE TABLE wp_example ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, slug varchar(191) NOT NULL, post_type varchar(20) NOT NULL, status tinyint(1) NOT NULL DEFAULT 0, PRIMARY KEY (id), UNIQUE KEY slug (slug), KEY post_type_status (post_type,status) ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
When dbDelta fails: debugging tips
- Enable WP_DEBUG and WP_DEBUG_LOG in wp-config.php to capture DB errors.
- Check wpdb->last_error and wpdb->print_error() after dbDelta or wpdb->query() calls.
- Make sure ABSPATH is defined and you included the correct path for upgrade.php.
- Verify the SQL statement: bad SQL syntax breaks parsing. Run the CREATE TABLE SQL directly in a DB client to test.
- Confirm the plugins table name uses the same prefix as the existing table mismatched names mean dbDelta will create a new table rather than update an existing one.
- Check DB user privileges (CREATE/ALTER/DROP) — insufficient privileges prevent schema changes.
Advanced: altering column types and destructive changes
dbDelta is not ideal for destructive migrations (removing columns, renaming columns or complex type changes). For such actions:
- Create a migration that performs controlled ALTER TABLE statements using wpdb->query().
- Take and require backups before running destructive migrations.
- Consider creating a temporary column, migrating content, then dropping the old column as a two-step safe migration.
Best practices summary
- Use wpdb->prefix and wpdb->get_charset_collate().
- Include upgrade.php and call dbDelta with carefully formatted CREATE TABLE SQL.
- Store a schema version option and run upgrade checks on plugins_loaded or init.
- Handle multisite activations explicitly, iterating blogs when network-activated.
- Use explicit ALTER TABLE queries for destructive or complex changes.
- Always test on development/staging and keep backups.
Further reading and references
- dbDelta() reference — WordPress Developer Resources
- wpdb — WordPress Database Access Abstraction Object
- utf8mb4 and collation (background on column length recommendations)
Closing notes
This article contains the key details, practical examples, and the most common pitfalls for creating and maintaining plugin tables using dbDelta. Use the examples as a starting point, adjust column definitions to your plugins needs, and always test schema changes in a safe environment before deploying.
|
|
Acepto donaciones de BAT's mediante el navegador Brave 🙂 |