Plugin database tables in Wordpress

Introduction

Plugins often need to store their own data. Creating custom database tables helps keep plugin data organized and separate from WordPress core data.

When a plugin needs to save complex or large amounts of data that don't fit well into existing WordPress tables.

When you want to improve performance by avoiding overloading default tables like wp_options.

When you need to store structured data with relationships, like orders, bookings, or custom logs.

When plugin data must be isolated for easier backup, migration, or removal.

When you want to run custom queries efficiently on plugin-specific data.

Syntax

Wordpress

<?php
function myplugin_create_table() {
    global $wpdb;
    $table_name = $wpdb->prefix . 'myplugin_table';
    $charset_collate = $wpdb->get_charset_collate();

    $sql = "CREATE TABLE $table_name (
        id mediumint(9) NOT NULL AUTO_INCREMENT,
        name varchar(255) NOT NULL,
        created datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
        PRIMARY KEY  (id)
    ) $charset_collate;";

    require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
    dbDelta( $sql );
}
add_action( 'plugins_loaded', 'myplugin_create_table' );

Use $wpdb->prefix to get the correct table prefix for the site.

Use dbDelta() to safely create or update tables without losing data.

Examples

This example creates a table to store user messages with an ID, text, user reference, and timestamp.

Wordpress

<?php
// Create a simple table for storing messages
function create_messages_table() {
    global $wpdb;
    $table_name = $wpdb->prefix . 'messages';
    $charset_collate = $wpdb->get_charset_collate();

    $sql = "CREATE TABLE $table_name (
        message_id bigint(20) NOT NULL AUTO_INCREMENT,
        message_text text NOT NULL,
        user_id bigint(20) NOT NULL,
        sent_at datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
        PRIMARY KEY  (message_id)
    ) $charset_collate;";

    require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
    dbDelta( $sql );
}
add_action( 'plugins_loaded', 'create_messages_table' );

This example shows how to add a new column to an existing plugin table using a direct SQL query.

Wordpress

<?php
// Add a new column to an existing plugin table
function update_myplugin_table() {
    global $wpdb;
    $table_name = $wpdb->prefix . 'myplugin_table';

    $status_column = $wpdb->get_results( $wpdb->prepare("SHOW COLUMNS FROM $table_name LIKE %s", 'status') );
    if ( empty( $status_column ) ) {
        $sql = "ALTER TABLE $table_name ADD COLUMN status varchar(20) DEFAULT 'active' NOT NULL;";
        $wpdb->query($sql);
    }
}
add_action( 'plugins_loaded', 'update_myplugin_table' );

Sample Program

This plugin creates a custom table, inserts a sample row, and displays the stored data in the WordPress admin area.

Wordpress

<?php
/**
 * Plugin Name: Simple Plugin Table Example
 */

function spt_create_table() {
    global $wpdb;
    $table_name = $wpdb->prefix . 'spt_data';
    $charset_collate = $wpdb->get_charset_collate();

    $sql = "CREATE TABLE $table_name (
        id mediumint(9) NOT NULL AUTO_INCREMENT,
        data varchar(100) NOT NULL,
        created_at datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
        PRIMARY KEY  (id)
    ) $charset_collate;";

    require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
    dbDelta( $sql );
}
add_action( 'plugins_loaded', 'spt_create_table' );

function spt_insert_sample_data() {
    global $wpdb;
    $table_name = $wpdb->prefix . 'spt_data';

    $row_count = $wpdb->get_var( "SELECT COUNT(*) FROM $table_name" );
    if ( $row_count == 0 ) {
        $wpdb->insert(
            $table_name,
            [
                'data' => 'Hello World'
            ]
        );
    }
}
add_action( 'plugins_loaded', 'spt_insert_sample_data' );

function spt_show_data() {
    global $wpdb;
    $table_name = $wpdb->prefix . 'spt_data';

    $results = $wpdb->get_results( "SELECT * FROM $table_name" );

    foreach ( $results as $row ) {
        echo "ID: {$row->id}, Data: {$row->data}, Created At: {$row->created_at}<br>";
    }
}
add_action( 'admin_notices', 'spt_show_data' );

Important Notes

Always use dbDelta() for creating or updating tables to avoid data loss.

Use the $wpdb object for safe database access and to respect WordPress table prefixes.

Remember to sanitize and validate any data before inserting it into your tables.

Summary

Plugins use custom database tables to store their own data separately from WordPress core.

Use $wpdb->prefix and dbDelta() to create and update tables safely.

Custom tables help organize data, improve performance, and keep plugin data isolated.