Add Custom Database Table to a WordPress Plugin

Adding a custom database table to a WordPress plugin is a simple 2 step process. If you are using the WordPress Plugin Boilerplate, adding a custom database table involves adding the following code to the activate function within the class-plugin-name-activator.php. This code includes utilizing the wpdb global variable and the dbDelta function that allows a custom SQL statement to be run on the database.

For the purposes of this example, we’re using the WordPress Plugin Boilerplate (WPPB) framework when building out WordPress plugins. Check out our post introducing working with the WordPress Plugin Boilerplate if you would like to learn more.

Step 1: Use dbDelta function to Add Custom Database Table to includes/class-plugin-name-activator.php file

The following code needs to be added to the activate method within the includes/class-plugin-name-activator.php file. You’ll notice in the code below that in addition to creating a custom database table, we also establish the plugin’s database version in an option that is saved to the user’s WordPress site. A plugin’s database version is recorded in case future adjustments are made to a database table, to allow you to accommodate those changes in future versions of your plugin.

global $wpdb;
$plugin_name_db_version = '1.0';
$table_name = $wpdb->prefix . "plugin_name_customers"; 
$charset_collate = $wpdb->get_charset_collate();

$sql = "CREATE TABLE $table_name (
		  id mediumint(9) NOT NULL AUTO_INCREMENT,
		  created timestamp NOT NULL default CURRENT_TIMESTAMP,
		  name tinytext NULL,
		  custom_field varchar(255) DEFAULT '' NOT NULL,
		  email varchar(255) DEFAULT '' NOT NULL,
		  UNIQUE KEY id (id)
		) $charset_collate;";

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

Step 2: Use dbDelta function to Delete Custom Database Table to includes/class-plugin-name-deactivator.php file

If you would like to remove the database when someone deactivates a plugin, they need to add corresponding code that deletes the database table. To do this, you need to add the following code to the deactivate method in the includes/class-plugin-name-deactivator.php file.

$table_name = $wpdb->prefix . "plugin_name_customers"; 

$sql = "DROP TABLE IF EXISTS $table_name";

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