How to Create Custom Database Tables in a WordPress Plugin (dbDelta Tutorial)

Most WordPress data belongs in posts, terms and options. But sooner or later you hit a feature where post meta simply falls apart: analytics events, form submissions, license keys, sync logs, pricing rules, imports with hundreds of thousands of rows. That is when a WordPress plugin custom database table stops being an anti-pattern and starts being the only sane architecture.

This guide is not a toy snippet. It is the exact pattern we ship in client plugins at Pixel Perfect Portfolios: a versioned schema, dbDelta() on activation and on upgrade, multisite handling, repository methods that always run through $wpdb->prepare(), object caching, and a real uninstall.php that cleans up after itself.

When a custom table beats post meta (and when it does not)

Before writing a single CREATE TABLE, be honest about your data. Custom post types plus meta give you the admin UI, revisions, the REST API, search and permissions for free. A custom table gives you speed, correct data types and real indexes, but you build everything else yourself.

Criteria	Custom post type + post meta	Custom database table
Row volume	Fine up to a few thousand items	Best above ~50k rows or high write throughput
Querying by several fields at once	Multiple self JOINs on wp_postmeta, slow	One row, one index, one query
Data types and sorting	Everything is LONGTEXT, numeric sorting needs CAST	Real INT, DECIMAL, DATETIME columns
Admin UI, REST, search	Provided by core	You build list tables and endpoints yourself
Backups, migrations, staging tools	Handled everywhere	Usually fine, but test your migration plugin
Bloat impact on the site	Pollutes wp_posts / wp_postmeta for everyone	Isolated, easy to truncate or prune

Quick decision checklist

• Do you need to filter or sort on 3+ fields at once? Custom table.
• Is the data append-only and high volume (logs, events, hits)? Custom table.
• Does an editor need to write it in the block editor? Custom post type.
• Fewer than a few hundred rows, edited by hand? Options or CPT, do not over-engineer.
• Do you need relations between two entities (many-to-many with attributes)? Custom table.

Plugin structure we will build

Example plugin: ppp-leads, storing lead submissions with a score, a status and a JSON payload.

ppp-leads/
├── ppp-leads.php            (bootstrap, hooks)
├── uninstall.php            (cleanup)
└── includes/
    ├── class-ppp-leads-schema.php      (dbDelta + versioning)
    └── class-ppp-leads-repository.php  (all $wpdb access)

Rule number one: no $wpdb call anywhere else in the plugin. Every read and write goes through the repository, so escaping and caching are enforced in a single file.

Step 1: the bootstrap file and its hooks

<?php
/**
 * Plugin Name: PPP Leads
 * Description: Stores lead submissions in a dedicated custom database table.
 * Version:     1.1.0
 * Requires at least: 6.4
 * Requires PHP: 7.4
 * Author:      Pixel Perfect Portfolios
 */

defined( 'ABSPATH' ) || exit;

define( 'PPP_LEADS_FILE', __FILE__ );
define( 'PPP_LEADS_PATH', plugin_dir_path( __FILE__ ) );

require_once PPP_LEADS_PATH . 'includes/class-ppp-leads-schema.php';
require_once PPP_LEADS_PATH . 'includes/class-ppp-leads-repository.php';

// Runs once, only on manual activation.
register_activation_hook( __FILE__, array( 'PPP_Leads_Schema', 'on_activation' ) );

// Runs on every load: catches auto-updates, WP-CLI updates, Git deploys.
add_action( 'plugins_loaded', array( 'PPP_Leads_Schema', 'maybe_upgrade' ) );

// Multisite: a brand new site must get the table too.
add_action( 'wp_initialize_site', array( 'PPP_Leads_Schema', 'on_new_site' ), 20, 1 );

The single most common bug in custom table plugins: the activation hook does not fire when a plugin is updated. If your 2.0 release adds a column and you only call dbDelta() in register_activation_hook(), existing sites will throw “Unknown column” errors. That is why maybe_upgrade() exists.

Step 2: describe the schema in one place

<?php
defined( 'ABSPATH' ) || exit;

class PPP_Leads_Schema {

	/** Bump this every time the CREATE TABLE statement changes. */
	const DB_VERSION = '1.1.0';

	const VERSION_OPTION = 'ppp_leads_db_version';

	public static function table_name() {
		global $wpdb;
		return $wpdb->prefix . 'ppp_leads';
	}

	public static function get_schema() {
		global $wpdb;

		$table           = self::table_name();
		$charset_collate = $wpdb->get_charset_collate();

		return "CREATE TABLE $table (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			user_id bigint(20) unsigned NOT NULL DEFAULT 0,
			email varchar(191) NOT NULL DEFAULT '',
			source varchar(60) NOT NULL DEFAULT '',
			score smallint(5) unsigned NOT NULL DEFAULT 0,
			status varchar(20) NOT NULL DEFAULT 'new',
			payload longtext NULL,
			created_at datetime NOT NULL,
			updated_at datetime NOT NULL,
			PRIMARY KEY  (id),
			KEY user_id (user_id),
			KEY email (email),
			KEY status_created (status, created_at)
		) $charset_collate;";
	}
}

The dbDelta formatting rules you cannot ignore

dbDelta() parses your SQL with regular expressions instead of a real SQL parser. Break the formatting and it silently does nothing, or worse, it runs an ALTER on every page load.

1. Put each field on its own line.
2. Use two spaces between PRIMARY KEY and the parenthesis: PRIMARY KEY (id).
3. Use the keyword KEY, never INDEX.
4. Give every key a name: KEY status_created (status, created_at).
5. Do not wrap column names in backticks, and keep the type keywords lowercase and consistent with what MySQL reports.
6. Always append $wpdb->get_charset_collate().
7. With utf8mb4, an indexed varchar must stay at 191 characters or less on older MySQL setups.
8. Avoid DEFAULT '0000-00-00 00:00:00': strict mode on MySQL 5.7+ and 8.x rejects zero dates. Use NOT NULL without default and always set the value in PHP, or make the column nullable.

Step 3: install and version the table

	public static function install() {
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		dbDelta( self::get_schema() );

		update_option( self::VERSION_OPTION, self::DB_VERSION );
	}

	public static function on_activation( $network_wide = false ) {
		if ( is_multisite() && $network_wide ) {
			$site_ids = get_sites( array( 'fields' => 'ids', 'number' => 0 ) );

			foreach ( $site_ids as $site_id ) {
				switch_to_blog( $site_id );
				self::install();
				restore_current_blog();
			}

			return;
		}

		self::install();
	}

	public static function on_new_site( $new_site ) {
		if ( ! function_exists( 'is_plugin_active_for_network' ) ) {
			require_once ABSPATH . 'wp-admin/includes/plugin.php';
		}

		if ( ! is_plugin_active_for_network( plugin_basename( PPP_LEADS_FILE ) ) ) {
			return;
		}

		switch_to_blog( (int) $new_site->blog_id );
		self::install();
		restore_current_blog();
	}

	public static function maybe_upgrade() {
		$installed = get_option( self::VERSION_OPTION, '0' );

		if ( version_compare( $installed, self::DB_VERSION, '>=' ) ) {
			return;
		}

		// 1. Bring the structure up to date (adds new columns and keys).
		self::install();

		// 2. Run data migrations that dbDelta cannot do for you.
		if ( version_compare( $installed, '1.1.0', '<' ) && '0' !== $installed ) {
			self::migrate_to_110();
		}
	}

	protected static function migrate_to_110() {
		global $wpdb;
		$table = self::table_name();

		// Example: backfill the status column introduced in 1.1.0.
		$wpdb->query(
			$wpdb->prepare(
				"UPDATE {$table} SET status = %s WHERE status = ''",
				'new'
			)
		);
	}

What dbDelta will and will not do

Change	Handled by dbDelta?
Create the table	Yes
Add a new column	Yes
Add a new KEY	Yes
Widen a column type	Yes (it issues an ALTER)
Drop a column or an index	No, write your own ALTER in a migration
Rename a column	No, it will add a second column
Foreign keys	No, and core does not use them either
Data migration	No, that is your job

Tip while developing: dbDelta( $sql, false ) returns the queries it would run without executing them. If it keeps returning the same ALTER on every run, your formatting or your column types do not match what MySQL reports.

Step 4: the repository, where every query is prepared

Table names cannot be passed as %s because they would be quoted. Two safe options: build the name from $wpdb->prefix yourself (never from user input), or use the %i identifier placeholder available since WordPress 6.2. Create WordPress HTML tables from database content is a useful companion to this.

<?php
defined( 'ABSPATH' ) || exit;

class PPP_Leads_Repository {

	const CACHE_GROUP = 'ppp_leads';

	protected static function table() {
		return PPP_Leads_Schema::table_name();
	}

	/**
	 * Insert a lead. Returns the new ID or a WP_Error.
	 */
	public static function insert( array $data ) {
		global $wpdb;

		$now = current_time( 'mysql', true );

		$row = array(
			'user_id'    => isset( $data['user_id'] ) ? (int) $data['user_id'] : 0,
			'email'      => sanitize_email( $data['email'] ?? '' ),
			'source'     => sanitize_key( $data['source'] ?? 'website' ),
			'score'      => isset( $data['score'] ) ? (int) $data['score'] : 0,
			'status'     => sanitize_key( $data['status'] ?? 'new' ),
			'payload'    => wp_json_encode( $data['payload'] ?? array() ),
			'created_at' => $now,
			'updated_at' => $now,
		);

		$formats = array( '%d', '%s', '%s', '%d', '%s', '%s', '%s', '%s' );

		$inserted = $wpdb->insert( self::table(), $row, $formats );

		if ( false === $inserted ) {
			return new WP_Error( 'ppp_leads_insert_failed', $wpdb->last_error );
		}

		wp_cache_set_last_changed( self::CACHE_GROUP );

		return (int) $wpdb->insert_id;
	}

	public static function get( $id ) {
		global $wpdb;

		$id    = (int) $id;
		$cache = wp_cache_get( 'lead_' . $id, self::CACHE_GROUP );

		if ( false !== $cache ) {
			return $cache;
		}

		$table = self::table();

		$row = $wpdb->get_row(
			$wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d", $id )
		);

		wp_cache_set( 'lead_' . $id, $row, self::CACHE_GROUP, 300 );

		return $row;
	}

	public static function update_status( $id, $status ) {
		global $wpdb;

		$updated = $wpdb->update(
			self::table(),
			array( 'status' => sanitize_key( $status ), 'updated_at' => current_time( 'mysql', true ) ),
			array( 'id' => (int) $id ),
			array( '%s', '%s' ),
			array( '%d' )
		);

		wp_cache_delete( 'lead_' . (int) $id, self::CACHE_GROUP );
		wp_cache_set_last_changed( self::CACHE_GROUP );

		return $updated;
	}

	public static function delete( $id ) {
		global $wpdb;

		$deleted = $wpdb->delete( self::table(), array( 'id' => (int) $id ), array( '%d' ) );

		wp_cache_delete( 'lead_' . (int) $id, self::CACHE_GROUP );
		wp_cache_set_last_changed( self::CACHE_GROUP );

		return $deleted;
	}
}

Filtered, paginated and still safe

This is the pattern for a list table query: build the WHERE clauses in an array, collect the values, then prepare once.

	public static function query( array $args = array() ) {
		global $wpdb;

		$args = wp_parse_args(
			$args,
			array(
				'status'   => '',
				'search'   => '',
				'sources'  => array(),
				'orderby'  => 'created_at',
				'order'    => 'DESC',
				'per_page' => 20,
				'page'     => 1,
			)
		);

		$table  = self::table();
		$where  = array( '1=1' );
		$values = array();

		if ( '' !== $args['status'] ) {
			$where[]  = 'status = %s';
			$values[] = sanitize_key( $args['status'] );
		}

		if ( '' !== $args['search'] ) {
			$where[]  = 'email LIKE %s';
			$values[] = '%' . $wpdb->esc_like( $args['search'] ) . '%';
		}

		if ( ! empty( $args['sources'] ) ) {
			$placeholders = implode( ', ', array_fill( 0, count( $args['sources'] ), '%s' ) );
			$where[]      = "source IN ( $placeholders )";
			$values       = array_merge( $values, array_map( 'sanitize_key', $args['sources'] ) );
		}

		// Never interpolate ORDER BY from user input: whitelist it.
		$allowed_orderby = array( 'id', 'email', 'score', 'created_at' );
		$orderby = in_array( $args['orderby'], $allowed_orderby, true ) ? $args['orderby'] : 'created_at';
		$order   = 'ASC' === strtoupper( $args['order'] ) ? 'ASC' : 'DESC';

		$per_page = max( 1, min( 200, (int) $args['per_page'] ) );
		$offset   = ( max( 1, (int) $args['page'] ) - 1 ) * $per_page;

		$sql = "SELECT * FROM {$table} WHERE " . implode( ' AND ', $where )
			. " ORDER BY {$orderby} {$order} LIMIT %d OFFSET %d";

		$values[] = $per_page;
		$values[] = $offset;

		return $wpdb->get_results( $wpdb->prepare( $sql, $values ) );
	}

Rules that keep this code out of trouble

• Every external value goes through a placeholder: %d, %f, %s, or %i for identifiers (WP 6.2+).
• Do not put quotes around placeholders. '%s' in your SQL string is a classic bug, prepare() adds the quotes.
• ORDER BY, LIMIT direction and column names must be whitelisted, never concatenated from $_GET.
• Use $wpdb->esc_like() before wrapping a term in % for LIKE searches.
• Check return values: $wpdb->insert() and query() return false on error and $wpdb->last_error tells you why.
• Count rows with a separate SELECT COUNT(*) query, SQL_CALC_FOUND_ROWS is deprecated in MySQL 8.
• Cache read-heavy queries with wp_cache_get() / wp_cache_set() and invalidate with a “last changed” key on every write.

Step 5: cleanup on uninstall (not on deactivation)

Deactivation happens all the time: debugging, plugin conflict testing, staging syncs. Never drop a table on deactivation. Do it in uninstall.php, and let the site owner decide.

<?php
// uninstall.php

defined( 'WP_UNINSTALL_PLUGIN' ) || exit;

function ppp_leads_uninstall_site() {
	global $wpdb;

	$settings = get_option( 'ppp_leads_settings', array() );

	// Respect an explicit "keep my data" setting.
	if ( ! empty( $settings['keep_data_on_uninstall'] ) ) {
		return;
	}

	$table = $wpdb->prefix . 'ppp_leads';

	$wpdb->query( "DROP TABLE IF EXISTS {$table}" );

	delete_option( 'ppp_leads_db_version' );
	delete_option( 'ppp_leads_settings' );

	wp_clear_scheduled_hook( 'ppp_leads_prune_event' );
}

if ( is_multisite() ) {
	$site_ids = get_sites( array( 'fields' => 'ids', 'number' => 0 ) );

	foreach ( $site_ids as $site_id ) {
		switch_to_blog( $site_id );
		ppp_leads_uninstall_site();
		restore_current_blog();
	}

	delete_site_option( 'ppp_leads_network_settings' );
} else {
	ppp_leads_uninstall_site();
}

Also worth adding when you store personal data: hook into wp_privacy_personal_data_exporters and wp_privacy_personal_data_erasers so your custom table participates in WordPress export and erase requests. Custom tables are invisible to those tools unless you register them. MB Custom Table tackles the same question from another angle.

Verify your table in 30 seconds

With WP-CLI:

wp db tables --all-tables | grep ppp_leads
wp db query "DESCRIBE wp_ppp_leads;"
wp db query "SHOW INDEX FROM wp_ppp_leads;"
wp option get ppp_leads_db_version

During development, add define( 'SAVEQUERIES', true ); plus WP_DEBUG and inspect $wpdb->queries, or install Query Monitor to see every prepared statement your plugin runs and how long it takes. The same instinct shows in the work of another team working this way.

Common dbDelta problems and their fix

Symptom	Cause	Fix
Table is never created	upgrade.php not required, or no KEY defined	Require ABSPATH . 'wp-admin/includes/upgrade.php' and add a PRIMARY KEY
Same ALTER runs on every page load	Backticks, wrong spacing, or type mismatch	Remove backticks, use two spaces after PRIMARY KEY, match MySQL type output
“Specified key was too long”	Indexed varchar longer than 191 with utf8mb4	Reduce to varchar(191) or index a prefix
“Invalid default value” on a datetime	Zero date under strict SQL mode	Drop the default and set the value in PHP
Column missing after a plugin update	Only relying on the activation hook	Version check on plugins_loaded
Table missing on one multisite subsite	Network activation loop or new-site hook missing	Loop get_sites() and hook wp_initialize_site

Performance notes for large tables

• Index for your queries, not for beauty. A composite key like (status, created_at) serves “pending leads, newest first” in one pass.
• Keep longtext payloads out of list queries: select explicit columns instead of SELECT * when you render a table of 200 rows.
• Batch imports with a single multi-row INSERT built from prepared placeholders instead of 5,000 calls to $wpdb->insert().
• Prune old rows with a scheduled event (wp_schedule_event) and a DELETE ... LIMIT 1000 loop so you never lock the table.
• Run EXPLAIN on your slowest query before adding yet another index.

Pre-release checklist

1. Schema lives in one method, version constant bumped.
2. dbDelta() runs on activation and through a version check.
3. Multisite: network activation loop + wp_initialize_site.
4. All queries go through the repository and $wpdb->prepare().
5. ORDER BY and column names whitelisted.
6. Capability checks and nonces on every admin action that writes.
7. Object caching plus invalidation on write.
8. uninstall.php drops the table and its options, with a “keep data” opt-out.
9. Privacy exporter and eraser registered if personal data is stored.
10. Tested on MySQL 8 and MariaDB, on a fresh install and on an upgrade from the previous version.

FAQ

Is it bad practice to create your own table in a WordPress plugin?

No. Core itself, WooCommerce, and every serious analytics or forms plugin do it. It becomes bad practice only when the data would have been fine as a custom post type, or when the plugin skips versioning, prepared statements and uninstall cleanup.

Does dbDelta run automatically when my plugin updates?

No. register_activation_hook() does not fire on updates. Store a DB version in an option and compare it on plugins_loaded, as shown above.

Can I pass a table name to $wpdb->prepare()?

Since WordPress 6.2 you can, using the %i identifier placeholder: $wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', $table, $id ). On older versions, build the name from $wpdb->prefix in code, never from request data.

Should I use $wpdb->prefix or $wpdb->base_prefix?

Use $wpdb->prefix for per-site data on multisite (one table per subsite). Use $wpdb->base_prefix only when the data is genuinely shared across the whole network, and remember uninstall then runs once.

How many rows before post meta becomes a problem?

There is no hard limit, but pain usually starts when wp_postmeta passes a few hundred thousand rows or when a single query needs three or more meta conditions. A custom table with proper indexes handles millions of rows comfortably.

How do I remove a column that dbDelta added by mistake?

Write an explicit migration guarded by your version check: $wpdb->query( "ALTER TABLE {$table} DROP COLUMN old_field" );, then bump the DB version. dbDelta() will never drop anything for you.

Can users manage a custom plugin table from the WordPress admin?

Not by default. You either build a WP_List_Table screen, expose REST routes with permission callbacks, or rely on a third-party spreadsheet-style editor. Building your own screen is the option we recommend for anything client-facing, because you control validation and capabilities.

Need this built properly?

A custom table is a long-term commitment: every future release has to migrate real client data without downtime. If you want a plugin architecture reviewed, or a slow meta-based feature refactored into an indexed table, the Pixel Perfect Portfolios team does exactly this kind of work. Get in touch with your current schema and query logs, and we will tell you whether a custom table is the right move.