The Developers Guide to the WordPress Database (and Writing Better Code)

The Developers Guide to the WordPress Database (and Writing Better Code)

The WordPress database may seem like a necessary evil to many developers, yet understanding how it works is a great way to start writing better code.

Knowing exactly what happens when you create a taxonomy or add a post will help take the “magic” factor out of programming – where you don’t quite know why something works.

In this article, I’ll shed some light on each table in the database, what you can expect to be there, how functions affect it and what the bare essentials are.

Let’s jump right in.

The wp-config file is a core WordPress file and contains information about your database.
The wp-config file is a core WordPress file and contains information about your database.

How Table Names in WordPress Work

The name of each table in WordPress is composed of two parts: a prefix and the table name. The prefix is wp_ by default, but it can be set to anything you like using the wp-config.php file. In fact, changing your prefix to something unpredictable like hwuBedFs83_ is a good idea, it will make your site that much safer.

Prefixes exist because in some cases you may want to put multiple installations of WordPress within the same database. Prefixes make this easy, all you need to do is make sure each site has a different prefix.

When used in a multisite environment, WordPress actually does the same thing, it uses different prefixes (incrementing them) for different blogs. For example: wp_posts wp_1_posts and wp_2_posts.

Throughout the article, I will refer to the tables with their names prefixed by the default wp_ prefix.

The Posts Table Explained

The posts table, unsurprisingly, holds your posts. Not just your actual blog posts, but anything that is considered to be a post in WordPress. This includes pages, revisions, custom post types and attachments. To differentiate between the different content, WordPress uses a post_type column.

The post type for pages is page, the post type for an uploaded image or video is attachment. The post type of your run-of-the-mill blog posts is post – which is where all the confusion comes from. There are two terms, both named “post”, but one means something completely different.

In one sense “post” is an umbrella term for content on your website, be that an image, a revision or a page. In the other sense, “post” is the post type assigned to a post, which is meant as a regular article on your website.

The posts table has a total of 23 columns. Let’s take a short look at each to see what type of data is stored there:

  • ID: The identification number of the post. Each post has a unique ID which allows us to identify a post conclusively using a single number
  • post_author: The ID of the author who wrote the post
  • post_date: The date and time the post was published on
  • post_date_gmt: The date and time in the GMT time zone the post was published on
  • post_content: The full and unchanged content of the post
  • post_title: The title of the post
  • post_excerpt: The handwritten excerpt
  • post_status: The current status of the post. WordPress currently has 8: publish, future, draft, pending, private, trash, auto-draft and inherit. See the post status documentation to learn more
  • comment_status: Determines whether the post is open to commenting
  • ping_status: Determines whether the post is open to pingbacks
  • post_password: The password needed to access the post. Beware, this is stored as plain text!
  • post_name: The name of the post, generated from the title by replacing spaces with dashes and removing special characters
  • to_ping: Contains a list of URLs to ping when the post is published
  • pinged: Contains a list of URLs that have already been pinged
  • post_modified: The last modification date and time of the post
  • post_modified_gmt: The last modification date and time in GMT
  • post_content_filtered: Stores a modified version of the post content. It can essentially act as a cache for plugins which run very resource intensive modifications on content. You’ll need to take care as this field is cleared on a number of user actions like updating the post.
  • post_parent: Stores the ID of the parent post if it has one
  • guid: Contains the full URL of the post
  • menu_order: Stores the menu order that can be set for pages
  • post_type: The post type of the post
  • post_mime_type: The MIME type of uploaded media elements is stored in this field. A JPG image would have a MIME type of image/jpeg for example
  • comment_count: The number of comments related to this post

That’s all the information WordPress has in the main posts table. Any other bits and pieces, like what the featured image is for the post, any custom fields you’ve set are stored elsewhere in the post meta table, but more on that in a moment.

One important thing to note is that since the guid contains the full URL of the post, it needs to be changed if the website is moved to another domain.

The Post Meta Table Explained

The post meta table – wp_postmeta – uses a key-value system to create an easily searchable store of data associated with a post. The table has only four fields:

  • meta_id: The ID of the particular row of post meta data
  • post_id: The ID of the post the meta data is associated with
  • meta_key: The name of the data added
  • meta_value: The value of the data added

If this structure is new to you, here’s an example of why this structure is great. All posts contain a bunch of data, 23 different columns to be exact. These are all stored in the main posts table. You’re building a personal website and you’d love to indicate your current weight within each post. You could create a new column in the main posts table, it would work just fine, but you would break the WordPress database structure.

In addition, you may want to add more, such as fat percentage, minutes of sports completed and so on. The problem is that someone else may want to add completely different columns. We would end up in a situation where some users would have 45 columns in the posts table, some would have 28 and everyone would have completely different data at that.

This is just one of the many reasons the post meta table exists. It allows you to add rows to an already existing table – no structural changes necessary. If you want to add your weight you can create a row, assign it the correct post_id, use “weight” as the meta_key and 75 as the meta_value. If someone else want’s to add their current mood, no problem – they just use a different meta_key.

One neat little trick is prefixing your meta_key names with an underscore. These meta keys are considered “protected” and can not be viewed or modified with the custom field UI in the admin. If you would like to use a hidden meta field you will need to enter it using the add_post_meta() function.

Custom fields for a post
Only custom fields without an underscore prefix show up

So, the post meta table is there to store additional information about posts in a reusable and extendable way. WordPress itself only uses a couple of meta fields, chiefly: _edit_last, _edit_lock and _thumbnail_id, _wp_attached_file and _wp_attachment_metadata.

_edit_last stores the ID of the user who edited the post last. _edit_lock is used to determine who can be allowed to edit a post and to allow handovers to other people. The _thumbnail_id field is how the featured image is attached to the post, it contains the ID of the attachment.

_wp_attached_file points to the file associated with a particular attachment. The value contains the absolute path to the media item. The _wp_attachment_metadata field contains a bunch of info about the media item such as width, height, image sizes and so on.

Array Storage Explained

In many cases, we want to store a bunch of information in the database. The aforementioned attachment metadata is a good example. Here’s what WordPress stores there for one of my files:

As we all know, databases don’t store arrays. The way to put an array into the database is to serialize it – that is, use a function to convert it into a string. Then, when you retrieve the value from the DB, you convert it back.

The good news is that WordPress has you covered. As long as you use built in meta functions like update_user_meta() you can pass an array, WordPress will serialize it for you. Use get_[user/post/comment]_meta and the values will be unserialized if they are arrays.

Comments & Comment Meta

The comments are stored in wp_comments. Similarly to posts, they contain a number of columns which hold information about each comment.

  • comment_ID: The ID of the comment
  • comment_post_ID: The ID of the post the comment was made on
  • comment_author: The name of the commenter
  • comment_author_email: The email address of the commenter
  • comment_author_url: The Url of the commenter
  • comment_author_IP: The IP address the user commented from
  • comment_date: The date and time the comment was submitted
  • comment_date_gmt: The date and time of the comment in GMT
  • comment_content: The full text of the comment
  • comment_karma: This field is unused it is a remnant of old functionality. WordPress core contributor Samuel Wood (Otto) has said that it will probably be removed at some point.
  • comment_approved: Stores the approval status of the comment
  • comment_agent: The user agent (identifier of the browser and platform) used, for example: Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/42.0.2311.135 Safari/537.36
  • comment_type: Mostly used to distinguish between regular comments, trackbacks, and pingbacks. Custom types can also be added, like product reviews.
  • comment_parent: The ID of the parent comment
  • user_id: The ID of the user who commented

In vase you’re wondering, the comment_author, comment_author_url, comment_author_email fields are filled out, even if there is a user_id associated with the comment. This ensures that comments remain intact, even if users are removed.

comment_karma is a field which is not used anymore. As a result, you shouldn’t use it either. At some point, the core team will decide to remove the column and you’ll be in a difficult spot if you’re using it.

How Links Are Stored

The table named wp_links was used by the link manager which has been removed from the core code, so it is no longer used. I won’t go into a lot of detail since it is not used by many installations anymore. If you’d like to get the functionality of the link manager back you can, simply install the Link Manager plugin.

Users and User Meta

The users table is where all your administrators, editors, authors and other users reside. It stores the basic details of each user and makes good use of the user meta table for additional info.

The main users table, wp_users, contains the following columns:

  • ID: The ID of the user
  • user_login: The username the user logs in with
  • user_pass: The user’s hashed password
  • user_nicename: The generated user identifier based on the user login. This is what is used in the link to the user’s author archive
  • user_email: The user’s email address
  • user_url: The user’s URL
  • user_id: The ID of the user who commented
  • user_registered: The date the user registered on
  • user_activation_key: A temporary key generated by WP to make sure users activate their account before allowing them to log in
  • user_status: This field is not used anymore. You can, in theory, use it for your own purposes, but I would suggest going with post meta if you need to
  • display_name: The displayed name of the user.

As you can see there is a bit of an overlap here, I think there are some fields which should definitely reside in the meta table, like user_url for example. These fields were put in place before meta fields I assume, which is why they are somewhat misplaced.

As for user meta fields, there are quite a few used by WordPress. Many of them list UI settings such as closed meta boxes, positions of various movable elements, etc.

I haven’t found a conclusive list of everything WordPress so I’ll focus on the most frequently used ones.

  • first_name: The user’s first name
  • last_name: The user’s last name
  • description: The user’s bio, as set in the profile
  • rich_editing: The user’s setting for the visual editor, as set in the profile
  • show_welcome_panel: Wether or not to show the welcome panel to the user
  • wp_capabilities: A list of the user’s capabilities
  • wp_user_level: The user’s level

The wp_capabilities is the most important one to understand. First of all, the prefix comes from the database prefix. So if your database prefix is iwenf_32e, the meta key for this field will be iwenf_32e_capabilities. This is important to keep in mind for multisite installations where users may have different capabilities for different sites.

It is also one reason why migrations break. If you rename your tables and forget to rename the meta key of the user capabilities, no one will be able to log in.

Terms, Term Taxonomies, and Term Relationships

Terms and taxonomies are a tad convoluted in WordPress – the result of legacy issues. At the time, shared terms seemed like an awesome idea, but the real World proved this to be a misstep to say the least. A taxonomy roadmap was devised to rectify the situation. We’re not through it yet so I will be dealing with the current state, but a better architecture will eventually be in place.

For now there are three separate tables that govern our much-loved categories, tags, and custom taxonomies. When a term is created it is first added to the wp_terms table which has the following structure:

  • term_id: The term’s ID
  • name: The readable name of the term
  • slug: A term slug used in URLs
  • term_group: A mechanism for creating aliases, this isn’t really used as far as I know

Once a term has been added here, it is also added to the wp_term_taxonomy table which defines the taxonomy the term belongs to. It has the following structure:

  • term_taxonomy_id: The term taxonomy ID
  • term_id: The ID of the term
  • taxonomy: The slug of the taxonomy
  • description: The description of the term
  • parent: The ID of the parent term

Right now, a term could actually be shared among a number of taxonomies, which is what the team originally wanted, but this causes all sorts of issues – this is what they are currently resolving.

As it stands you either need the term_taxonomy_id to identify a term or you need the term_id and the taxonomy name.

Finally, once a term is assigned to an object like a post, the wp_term_relationships table is used to record it. It uses the following structure:

  • object_id: The ID of the object the term is assigned to
  • term_taxonomy_id: The term_taxonomy_id (not the term_id)
  • term_order: The order in which the terms are assigned. I haven’t seen this used heavily, it can be utilized to make sure terms appear in a given order.

Database Options

Options for WordPress are stored in the wp_options table. This includes things like: the date format, the default category, permalink structure, posts per page, current template, avatar rating, and so on. The keys are logically named so you should be able to figure out what most options are for.

WordPress Options
WordPress options in the database

The option table works a lot like meta tables but instead of met_key and meta_value you have option_name and option_value.

Don’t forget to use the add/update/delete_option functions to add values into this table. If you need to add lots of values, consider using only one row by adding an array as the value. A lot of plugins mistakenly take up many-many rows here without much cause.

The options table has an extra autoload row. This determines wether or not an option is loaded on each page automatically. If you’re creating an option which is only used in one specific place – or perhaps just a couple – make sure to set autoload to “no” to save on loading times.

Custom Tables

There may be other tables in your database, created by your plugins. Generally you should stay away from creating additional tables but if it is truly warranted, you can use the wpdb class to interact with it, just like other tables.

Wrapping Up

Knowing what the tables contain, how they fit together and how functions manipulate the data has helped me immensely to become better at programming for WordPress. I hope you’ve learned some of the basics yourself through this article!

If you know of any database quirk, useful tidbit or other database related info please do let us know in the comments below.

Image credit: Eric Norris.