Tutorial - Custom Database Tables

[jonathanbossenger]

Topic Description

While the default WordPress database schema is perfect for most content-related data types, there may be instances where you need to create custom tables in a WordPress database. One example of this is when a plugin has a specific use case that extends beyond what the custom post-type functionality of WordPress offers. In this tutorial, you will learn how to create and manage custom WordPress database tables, how to interact with these tables, and some maintenance considerations.

Related Resources

Links to related content on Learn, HelpHub, DevHub, GitHub Gutenberg Issues, DevNotes, etc.

• Custom tables in WordPress online workshop

Guidelines

Review the team guidelines

Tutorial Development Checklist

• Vetted by instructional designers for content idea
• Provide feedback of the idea
• Gather links to Support and Developer Docs
• Review any related material on Learn
• Define several SEO keywords to use in the article and where they should be prominently used
• Description and Objectives finalized
• Tutorial created and announced to the team for Q/A review
• Tutorial reviewed and ready to publish
• Tutorial submitted and published to WPTV
• Tutorial published on WPTV
• Tutorial captioned
• Tutorial created on Learn.WordPress.org
• Tutorial post reviewed for grammar, spelling, etc.
• Tutorial published on Learn.WordPress.org
• Tutorial announced to Marketing Team for promotion

[SierraTR]

You might consider making the title more specific - such as Custom Database tables - so that it is not confused with html tables, the table block in site content or selecting a table-specific plugin. Or direct it toward developers rather than users.

[jonathanbossenger]

That's a good point @SierraTR, edited.

[jonathanbossenger]

WIP Script - https://github.com/jonathanbossenger/wp-content/blob/main/tutorials/fundamentals-02-custom-tables.md

[jonathanbossenger]

WIP video for review:

Custom.Database.Tables.mp4

In preparing this video for review, I realized there are two fixes I need to make

1. at 5:06, in the example code to update the record, the ID field should be id
2. at 6:37 I say plugin install not plugin uninstall

I will make sure to fix those before the video is published.

[TinaCollier]

Hello @jonathanbossenger ,

I thoroughly enjoyed your tutorial and here is what I noted during the video:

1. You clearly and concisely met all objectives in this video including Where to find information about creating custom database tables, How to create custom database tables, and How to interact with these custom database tables.
2. Your example code and the resources mentioned in the video were very helpful in understanding the process of creating custom database tables. I was easily able to follow along with your explanations and points
3. At the end of the video, you quickly reviewed table updates. I appreciate that you mentioned this process.

Great tutorial and I wouldn't change a thing about it other than the two items you mentioned above.

[digitalchild]

Great tutorial @jonathanbossenger my only question is why did the custom table docs not get migrated from codex?

[jonathanbossenger]

I honestly don't know @digitalchild, you'd have to ask the docs team. If I had to guess, probably because no one knew to migrate certain Codex docs, or there wasn't a logical place for them in the new developer docs.

[jonathanbossenger]

Thank you @TinaCollier and @digitalchild for the reviews.

[github-actions]

This content is ready to be reviewed. Please follow the steps listed under Guidelines for reviewing content. Thank you for your contribution! ✨

[juanmaguitar]

@jonathanbossenger I have watched the preview and it looks great. I don't have much feedback to add. I think the tutorial is clear and serves its purpose.
Good job! 👍

[ryanwelcher]

@jonathanbossenger I just watch the preview and I think it's great. There were a few thing that came to mind as I was watching.

1. ~4:48 you mention inserting data on the register_activation_hook which is the same hook as creating the table. I was wondering if perhaps it's worth showing/mentioning how to ensure that the table actually exists before inserting?
2. ~6:40 You mention sanitization when talking about deleting a table. First, THANK YOU :), second, is it beyond the scope to talk about properly escaping using functions like prepare()? I only mention it because when done incorrectly, custom tables and unprepared statements can be a massive security risk. For the record, there is nothing that you're doing that looks dangerous to me.
3. It may be work looking at what happens when a query/command fails. For example, trying to create a table that already exists or deleting one that doesn't. It's been a while since I did custom tables so I am not sure how it's handed in core. I DO remember that I messed it up a few times and got some weird results around the activation/deletion of the plugin
4. I'm not sure I'd recommend deleting the table on plugin deactivation. I know I've deactivated plugins accidentally and if I did so and lost all of my orders, I'd be pretty upset. Doing on uninstall can be done with a hook or file and deleting a plugin requires a confirmation step.

[jonathanbossenger]

@juanmaguitar thank you for the reivew

[jonathanbossenger]

@ryanwelcher thank you for the review, and the detailed feedback.

I was wondering if perhaps it's worth showing/mentioning how to ensure that the table actually exists before inserting?

That is a great point, and worth mentioning in the tutorial.

second, is it beyond the scope to talk about properly escaping using functions like prepare()

That's a great question. It's sort of one of the reasons I didn't want to even mention inserting and updating data using query, but I do get your point. What about if we add this at the end of that section?

You should try to never use the query method to insert or update data, but for whatever reason if you must use it to insert or update data, you should always use the prepare method on the SQL Query. This will ensure that the SQL query is sanitized to prevent any security vulnerabilities.

It may be work looking at what happens when a query/command fails. For example, trying to create a table that already exists or deleting one that doesn't. It's been a while since I did custom tables so I am not sure how it's handed in core. I DO remember that I messed it up a few times and got some weird results around the activation/deletion of the plugin

So I do want to dive more into custom tables in the future, including something about how one might manage the actual upgrade routine, and what to do if queries fail, etc. If it's ok with you, I'll leave this idea for that follow-up tutorial. This one was more of an "introduction to" style tutorial

I'm not sure I'd recommend deleting the table on plugin deactivation.

That's a very valid point, and something that I left out of this tutorial, but included in the workshop. What I actually said in the workshop was that one should typically let the user decide if they want to clean up the data or not, and then use the relevant hook, but this got left out of the tutorial script somewhere along the line.

Based on the other feedback above, what about this as an update to that "Cleaning up" section

Cleaning up

It's also possible to delete your custom tables. To do this, you can use the query method of the $wpdb object, passing a SQL statement to delete the table.

    function delete_table() {
        global $wpdb;

        $table_name = $wpdb->prefix . 'custom_table';

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

query will run any SQL query, but it's best to only use it for queries that don't insert or update data, as those functions include built-in sanitization.

You should try to never use the query method to insert or update data, but for whatever reason if you must use it to insert or update data, you should always use the prepare method on the SQL Query. This will ensure that the SQL query is sanitized to prevent any security vulnerabilities.

Depending on your requirements, or the requirements of your plugin's users, you could delete the table in two ways.

If your plugin users will not need the data in this table if they deactivate the plugin, you could trigger this on the plugin deactivation hook.

register_deactivation_hook( __FILE__, 'delete_table' );

However, if the data in that table is important, and your users might want to keep it, even if the plugin is deactivated, you could delete the table using one of the two uninstall methods available to plugins. For example, if you choose to use the register_uninstall_hook.

register_uninstall_hook( __FILE__, 'delete_table');

It's generally recommended to check with your user and then use one of the uninstall methods over plugin deactivation.

[ryanwelcher]

@jonathanbossenger that sounds great! Thanks for putting up with me :)

[Zebedeu]

The tutorial seems to be excellent, considering that it covers the entire CRUD process. The code examples are all well-organized. However, as I mentioned in the previous review, it is difficult to include all aspects in a short video, including best practices.

For review:

It is important to emphasize and explain to the user that certain practices were used only to illustrate a concept, but are not recommended.

[jonathanbossenger]

Thanks for putting up with me :)

@ryanwelcher not at all, I appreciate the feedback and correction.

[jonathanbossenger]

Thanks for your review @Zebedeu

It is important to emphasize and explain to the user that certain practices were used only to illustrate a concept, but are not recommended.

I'm just checking, do you feel that this needs to be added, or that this idea is already covered in the tutorial?

[Zebedeu]

Yes, this information is important and should be included in the tutorial. It is essential to emphasize to the user that when creating custom tables in WordPress, it is crucial to version them to control and update the table structure if necessary. Additionally, it is important to note that this technique allows for updates to be applied consistently without compromising the integrity of the stored data in the table. Therefore, versioning is a valuable and indispensable resource for managing custom tables in WordPress.

[jonathanbossenger]

@Zebedeu thanks for your feedback.

As I mentioned to another reviewer in an earlier comment I do think that a follow-up tutorial on custom tables would be good here:

So I do want to dive more into custom tables in the future, including something about how one might manage the actual upgrade routine, what to do if queries fail, etc.

The problem exists in balancing the limitation of creating a tutorial video 5-10 minutes long, keeping the content engaging and easier to follow for viewers, without trying to rush through all the available information about a specific topic.

Quite honestly, the topic of custom tables could be a series of tutorials, even a short course on the subject.

So I just want to make sure I understand your specific review feedback here, as I think there are two action items:

1. explain to the user that certain practices were used only to illustrate a concept, but are not recommended.
2. add details on table versioning

Would you agree that a follow-up tutorial (or tutorials) including things like versioning, what to do if queries fail, and other topics related to custom tables would make sense?

Additionally, if you're interested, this might be content you or another contributor might be interested in working on.

[jonathanbossenger]

@ryanwelcher for the "if table exists check" on inserting data, would you be happy with using get_var and a SHOW TABLES MySQL statement?

    function insert_record_into_table(){
        global $wpdb;

        $table_name = $wpdb->prefix . 'custom_table';

        $table_exists = $wpdb->get_var( "SHOW TABLES LIKE '$table_name'" );

        if ( $table_exists ) {
            $wpdb->insert(
                $table_name,
                array(
                    'time' => current_time( 'mysql' ),
                    'name' => 'John Doe',
                    'text' => 'Hello World!',
                    'url'  => 'https://wordpress.org'
                )
            );
        }
    }

$table_exists would either be the table name, or NULL if it doesn't exist.

[ryanwelcher]

for the "if table exists check" on inserting data, would you be happy with using get_var and a SHOW TABLES MySQL statement?

Explaining the why here is probably more important than the implementation but that looks great to me and demonstrates the steps perfectly.

[jonathanbossenger]

Thanks @ryanwelcher.

I'm wondering now if this item, as well as the items about when to create tables and when to delete them, should be in the follow-up tutorial.

What I'm thinking is:

This tutorial = the basics of creating, inserting data, updating, and deleting custom tables (aka the theory)

Follow-up tutorial = how you would actually implement this with real-world examples (aka in practice)

1. Adding tables on plugin activation and removing them only on uninstall
2. Using the table exists check on inserts and updates. Thinking about this more, running a table exists check every time you insert or update data isn't super performant, maybe an option stored once the table is created is better, although this doesn't solve the problem if the table is deleted some other way. That being said...
3. Implementing proper checks for all queries, with error output to the user if any of them fail. This would prevent the problem at 2) because then at least it can fail gracefully and report an error.
4. When, and how, to use prepare with query for a custom insert or delete (although I can't think of a real-world example at this time)
5. How to implement the upgrade routine, to manage table updates

This would be similar to another two-part series I did recently on plugin vulns:

1. https://learn.wordpress.org/tutorial/introduction-to-securely-developing-plugins/
2. https://learn.wordpress.org/tutorial/extending-wordpress-common-security-vulnerabilities/

I'm thinking of this in terms not only of being able to create courses based on this content, where one small step follows the next, but also in terms of the learning pathways project.

Thoughts?

[ryanwelcher]

@jonathanbossenger that sounds like a good approach to me. It would be very easy to overwhelm the user with all of the nuances in a single tutorial and splitting them out mitigates that and allows you to spend more time on each.

[jonathanbossenger]

Thank you @ryanwelcher I appreciate your experienced feedback on this topic.

[jonathanbossenger]

https://learn.wordpress.org/tutorial/custom-database-tables/