Custom Database Tables in WordPress Plugins: dbDelta, $wpdb, Uninstall

This article is a localized edition of the Japanese original. Read the Japanese original

When a WordPress plugin handles a large amount of its own data (activity logs, bookings, aggregated results and so on), a dedicated table is easier to search and aggregate than post meta (wp_postmeta) or options (wp_options). Using a single example plugin, this article explains how to write the SQL for dbDelta(), how to add columns when you update the plugin, how to read and write with $wpdb, and how to clean up on uninstall. The function behavior was checked against the Plugin Handbook on developer.wordpress.org and the WordPress source code (wp-admin/includes/upgrade.php) as of November 2026.

When a custom table makes sense

• For a small amount of data attached to posts (a few values per post), post meta is enough. The admin screens, the REST API and the export tools all work with it as is.
• For logs that grow past tens of thousands of rows, or data you filter and aggregate by date or user, a custom table with indexes on the columns you need is faster.
• Custom tables may be left out of the standard WordPress export and many migration tools, so check that your backup and migration procedures include them.

How to write SQL that dbDelta accepts

dbDelta() reads a CREATE TABLE statement, creates the table if it does not exist, and adds any missing columns or indexes if it does. Because its SQL parser is simple, the Plugin Handbook requires the following formatting.

If you break these rules, the table may still be created, but the next time dbDelta() runs it may decide that the same columns or indexes are "different" and try to change them, or do nothing at all.

Create the table and add columns on plugin updates

This example goes in the plugin's main file. The table definition lives in one place, and the same function runs both on activation and when a plugin update changes the version number.

<?php
/**
 * Plugin Name: My Logs Example
 */

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

// Bump this number whenever you change the table definition
const MYPLUGIN_DB_VERSION = '1.1';

function myplugin_install_table() {
	global $wpdb;

	$table_name      = $wpdb->prefix . 'my_logs';
	$charset_collate = $wpdb->get_charset_collate();

	// Version 1.1 added the ip_address column
	$sql = "CREATE TABLE {$table_name} (
		id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
		user_id bigint(20) unsigned NOT NULL DEFAULT 0,
		action varchar(100) NOT NULL DEFAULT '',
		description text NOT NULL,
		ip_address varchar(45) NOT NULL DEFAULT '',
		created_at datetime NOT NULL,
		PRIMARY KEY  (id),
		KEY user_id (user_id),
		KEY created_at (created_at)
	) {$charset_collate};";

	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
	dbDelta( $sql );

	update_option( 'myplugin_db_version', MYPLUGIN_DB_VERSION );
}

// On activation
register_activation_hook( __FILE__, 'myplugin_install_table' );

// On plugin update (the activation hook does not run on updates)
add_action( 'plugins_loaded', function () {
	if ( get_option( 'myplugin_db_version' ) !== MYPLUGIN_DB_VERSION ) {
		myplugin_install_table();
	}
} );

Key points:

• register_activation_hook() runs only when the plugin is activated in the admin. It does not run when the plugin is updated to a new version, so, as in the Plugin Handbook example, you compare the stored version number on plugins_loaded and rebuild the table only when it differs.
• Use $wpdb->prefix rather than hard-coding wp_ in the table name, because the prefix can be changed per site.
• $wpdb->get_charset_collate() gives the table the same character set and collation as the other WordPress tables.
• created_at has no default value; you set it when inserting (see below). Which types accept DEFAULT CURRENT_TIMESTAMP depends on the MySQL or MariaDB version and settings, so filling in the date from WordPress avoids differences between environments.

What dbDelta does and does not do

In the implementation in wp-admin/includes/upgrade.php, dbDelta behaves as follows on an existing table.

If you need to drop or rename a column, run ALTER TABLE yourself inside the version-check routine. Back up the production database before you do.

Insert, select, update and delete with $wpdb

$wpdb->insert(), update() and delete() escape values automatically. When you write SQL yourself, as with get_results(), pass values through $wpdb->prepare() placeholders (%d integer, %s string, %f float).

<?php
global $wpdb;
$table = $wpdb->prefix . 'my_logs';

// Insert
$ip = isset( $_SERVER['REMOTE_ADDR'] ) ? filter_var( wp_unslash( $_SERVER['REMOTE_ADDR'] ), FILTER_VALIDATE_IP ) : '';

$result = $wpdb->insert(
	$table,
	array(
		'user_id'     => get_current_user_id(),
		'action'      => 'login',
		'description' => 'User logged in',
		'ip_address'  => $ip ? $ip : '',
		'created_at'  => current_time( 'mysql', true ), // Store in UTC
	),
	array( '%d', '%s', '%s', '%s', '%s' )
);

if ( false === $result ) {
	error_log( 'my_logs insert failed: ' . $wpdb->last_error );
} else {
	$new_id = $wpdb->insert_id; // ID of the inserted row
}

// Select (always pass values through placeholders)
$logs = $wpdb->get_results(
	$wpdb->prepare(
		"SELECT id, action, created_at FROM {$table} WHERE user_id = %d ORDER BY created_at DESC LIMIT %d",
		get_current_user_id(),
		10
	)
);

foreach ( $logs as $log ) {
	echo esc_html( $log->action . ' ' . $log->created_at ) . '<br>';
}

// Get a single value
$count = (int) $wpdb->get_var(
	$wpdb->prepare( "SELECT COUNT(*) FROM {$table} WHERE action = %s", 'login' )
);

// Update (2nd argument: values to set, 3rd argument: WHERE conditions)
$wpdb->update(
	$table,
	array( 'description' => 'Updated' ),
	array( 'id' => 42 ),
	array( '%s' ),
	array( '%d' )
);

// Delete
$wpdb->delete( $table, array( 'id' => 42 ), array( '%d' ) );

• The table name is built from $wpdb->prefix and a fixed string, so it is embedded directly in the SQL. If user input is used as a table or column name, use the identifier placeholder %i, available since WordPress 6.2.
• insert(), update() and delete() return false on failure. Check $wpdb->last_error for the cause.
• Store dates in UTC as Y-m-d H:i:s with current_time( 'mysql', true ) and convert to the site time zone when displaying, so the data does not shift if the time zone setting changes.

Other $wpdb methods are covered in "How to run SQL with $wpdb in WordPress".

Drop the table on uninstall

Code that removes the table and options when the plugin is deleted in the admin goes in uninstall.php, placed directly in the plugin folder. It is common practice not to delete data on deactivation alone (so the data is still there if the plugin is reactivated).

<?php
// uninstall.php
if ( ! defined( 'WP_UNINSTALL_PLUGIN' ) ) {
	exit;
}

global $wpdb;

function myplugin_drop_table( $prefix ) {
	global $wpdb;
	$wpdb->query( "DROP TABLE IF EXISTS {$prefix}my_logs" );
}

if ( is_multisite() ) {
	// On multisite, each site has its own table and option
	foreach ( get_sites( array( 'fields' => 'ids', 'number' => 0 ) ) as $blog_id ) {
		myplugin_drop_table( $wpdb->get_blog_prefix( $blog_id ) );
		delete_blog_option( $blog_id, 'myplugin_db_version' );
	}
} else {
	myplugin_drop_table( $wpdb->prefix );
	delete_option( 'myplugin_db_version' );
}

On multisite, $wpdb->prefix differs for each site (for example wp_2_). If you use one table for the whole network, use $wpdb->base_prefix instead. Also note that even on network activation the activation hook runs only for the current site, so each site's table is created by the plugins_loaded version check above when that site is first loaded.

Check the table with WP-CLI

On servers with WP-CLI, you can inspect the table with the following commands. Check the table prefix with wp db prefix; the examples assume wp_.

# Check the table prefix
wp db prefix

# List tables starting with wp_my_ (including custom tables not registered in $wpdb)
wp db tables 'wp_my_*' --all-tables-with-prefix

# Check the column definitions and indexes
wp db query "SHOW CREATE TABLE wp_my_logs\G"

# Check the row count
wp db query "SELECT COUNT(*) FROM wp_my_logs"

# Check the size of each table
wp db size --tables --all-tables-with-prefix

After installing a new version that adds a column, confirm that the column was added with SHOW CREATE TABLE and that the number was updated with wp option get myplugin_db_version.

Cautions

• When dbDelta() changes a column type in production, it can take a long time on a table with many rows, and writes to the table may wait in the meantime. Make changes to large tables during low-traffic hours and take a backup first.
• How to change a table definition step by step (migrations per version) is covered in detail in "Managing WordPress custom tables with SCHEMA_VERSION".

Questions about WordPress? The publisher of this site is based in Japan and replies in Japanese. Contact (Japanese)