Understanding dbDelta() Function in WordPress: Full Explanation with Example

ByMr. HR

WordPress provides several powerful tools to work with the database, and one of the most important among them is the dbDelta() function. It is designed to create or update database tables safely, making it an essential function for plugin and theme developers who need to manage custom tables.

What is dbDelta()?

The dbDelta() function is part of the WordPress core and resides in the wp-admin/includes/upgrade.php file. It is a helper function that can:

  • Create new tables
  • Modify existing table structure (add/remove/change columns)
  • Handle index changes

This is safer than executing raw SQL queries using $wpdb->query(), especially when it comes to updating existing tables.

When Should You Use dbDelta()?

You should use dbDelta() when:

  • You’re creating a custom plugin or theme that requires its own database tables.
  • You want to safely update a table structure when the plugin is updated.

Syntax

dbDelta( string|array $queries );

$queries: A single SQL string or an array of SQL strings. These should be CREATE TABLE statements.

Important: SQL must follow specific formatting for dbDelta() to work correctly (e.g., PRIMARY KEY, KEY, and UNIQUE KEY must be written exactly that way, not PRIMARY index, etc.)

Example: Creating a Custom Table

Here’s a complete example of how to use dbDelta() inside a plugin.

Hook into Plugin Activation

register_activation_hook(__FILE__, 'myplugin_create_table');

Define Table Creation Function

function myplugin_create_table() {
    global $wpdb;

    // Set the table name with the prefix
    $table_name = $wpdb->prefix . 'custom_data';

    // Character set and collation
    $charset_collate = $wpdb->get_charset_collate();

    // SQL to create the table
    $sql = "CREATE TABLE $table_name (
        id mediumint(9) NOT NULL AUTO_INCREMENT,
        name varchar(100) NOT NULL,
        email varchar(100) DEFAULT '' NOT NULL,
        created_at datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
        PRIMARY KEY  (id),
        KEY email (email)
    ) $charset_collate;";

    // Include upgrade functions
    require_once(ABSPATH . 'wp-admin/includes/upgrade.php');

    // Call dbDelta to create or update the table
    dbDelta($sql);
}

Key Points to Remember

Field Formatting: Every line (except the last) in the SQL should end with a comma.

KEY & PRIMARY KEY: Must be written exactly like that — capitalization and spacing matter.

Field Types: Use standard MySQL types (varchar, int, datetime, etc.)

Collation & Charset: Always include get_charset_collate() to match WordPress DB settings.

Require Upgrade File: Always load upgrade.php using:

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

Updating an Existing Table

If you update your table definition (e.g., add a column), simply change the SQL and rerun dbDelta(). WordPress will compare and alter the table structure as needed.

Example.

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

Just adding phone column will update the existing table if dbDelta() is run again.

Testing Your Table

You can verify the table creation by using:

global $wpdb;
$table = $wpdb->prefix . 'custom_data';

if($wpdb->get_var("SHOW TABLES LIKE '$table'") === $table) {
    echo 'Table created successfully.';
}

Conclusion

The dbDelta() function is an incredibly useful tool for safely managing custom database tables in WordPress. Whether you are releasing your first plugin or maintaining a professional suite, using dbDelta() ensures that your database structure stays in sync with your code.

Using it correctly helps avoid direct SQL issues, ensures compatibility across different environments, and makes your plugin more future-proof.

Discover More