When it comes to working with WordPress-based projects, arguably one of the most frustrating or tedious aspects of deployment is actually getting the databases across your environments in-sync with one another.

Sure, there's something to be said for using test data in development, user data in staging, and actual data in production, but there's no such thing as a silver bullet, right? That means that sometimes test data is going to work; other times, it won't.

For example, let's say that you inherit a project for which you have to pull down a database and then begin working with existing data. Or let's say that you have to migrate an entire site or application from one server to another.

In cases like that, test data doesn't help a whole lot. Instead, you need a tool for it. And sure, the WordPress Importer is a fair tool for basic migrations, and running SQL exports and imports is okay if you're comfortable with database front-ends and working with SQL itself.

But what about those who are somewhere in between?

Making Migrations Easier

The truth is, when it comes to working with WordPress database migrations, it's a mixed bag because many of us have skill levels that vary depending on what part of the stack we work with the most.

By that, I mean:

• Those who are far more comfortable with front-end work will likely be less familiar with the application layer and/or the database layer
• Those who are used to working with the application layer may be just as good with the front end but not the database (or vice versa)
• Those who live in the database may not be comfortable with the layers above

This isn't to say that there aren't full stack developers. Obviously, there are; however, not everyone is in that position.

So when it comes to the case of working on migrating WordPress databases, some have a much harder time than others. Alternatively, despite one's comfort level with SQL, some may be looking for a tool simply to help make the entire process easier.

In this series, we're going to take a look at a utility that does just that, but before we do that, let's have a quick primer on the WordPress database to make sure that we're all on the same page.

The WordPress Database

When it comes to discussing the WordPress database, an entire series of articles could be written about each table, each column, the schema, how to write optimal queries, and so on.

This is not the series for that.

Instead, we're going to do two things in this article:

1. We're going to make sure that we all have a clear, conceptual understanding of what a database is so we know how to picture it in our minds
2. We're going to take a look at each table in the WordPress database in order to understand what kind of data each table holds

Ultimately, this should help explain or demystify some of the underlying work for those who spend more time on the front-end, and it may help those who spend more time at the application layer working with the WordPress API understand what functions match to which table (which can ultimately lead to writing better code).

What's a Database?

Generally speaking, I think the majority of the readers of Wptuts+ know what a database is.

Straight from Wikipedia:

A database is an organized collection of data. The data are typically organized to model relevant aspects of reality (for example, the availability of rooms in hotels), in a way that supports processes requiring this information (for example, finding a hotel with vacancies).

That's a fair definition, but I don't think it does such a good job of illustrating the WordPress database or similar web applications - it's a bit too general. So, from here, let's create our own working definition that we can use throughout the rest of the series.

Let's try this:

A database is made up of at least one table. A table consists of rows and columns each of which store unique pieces of information. Each row is called a record. Multiple tables can exist in a database, and sometimes tables can be related to one another.

Perhaps the most confusing part of what I've shared above is that tables can be related to one another. We'll revisit this idea before the end of the article - but first, let's discuss the WordPress database.

The WordPress Database Schema

In short, the WordPress database consists of eleven tables (unless you're using Multisite, but that's outside the scope of this series).

Now, each table also has its own set of columns that represent a variety of information stored within the table. For example, the wp_posts table has a column called post_content that represents the actual content that's stored in a post.

The tables and their descriptions are as follows:

• wp_users holds the list of users who are registered with the WordPress installation. This includes things like the email address, password, display name, and so on.
• wp_usermeta contains information related to each user. This is where you can store additional information about each user.
• wp_posts is where all post information is stored. The truth is, it doesn't matter if it's a post, a page, or a custom post type - all information such as the title, content, and more are stored here.
• wp_postmeta is where the meta data for each post is stored. This table allows you to save and retrieve more information about each post.
• wp_comments are where the comments for each post (again, regardless of type) are stored.
• wp_commentmeta like the the other "meta" tables allows you to store more information about each comment than what's already saved in the comment table.
• wp_terms is where the categories and tags are stored. Because the relationship between posts, pages, custom post types, categories, and tags can get a bit more complicated, there are a few additional tables.
• wp_term_taxonomy provides a description of the category or the tag (or even the link if you're still using them) in the wp_terms table.
• wp_term_relationship stores the relationships from a given post to its category (or categories) and/or tag (or tags).
• wp_options are where all of the settings are kept - this includes those that ship and are configured with WordPress and those that are created by using the Settings API.
• wp_links is a table that still exists within the WordPress database despite the fact that there's no longer a UI option for the data. If you've ever used this feature, then you're familiar with Links and how they work and this is the table in which they're stored.

And that's all there is to the WordPress database. It's relatively simple and straightforward, right?

Posts are kept in the posts table, Comments in the comments table, Users in the users table, and so on. Sure, there are some subtle nuances (such as the fact that Pages are stored in the Posts table); however, it's a relatively uncomplicated schema to follow.

That's a good thing.

Also, remember how we mentioned earlier that some tables can refer to one another? A good example of this would be the Comments table and the Posts table. Since comments are left on a specific post, then a comment needs to know which post ID it is associated with so that when a post is loaded, comments related to that post's ID can be retrieved.

Anyway, this is more detail than we'll be diving into in this series, but hopefully it's enough to give you an idea. If you're interested in more technical information, the relationships between the tables, the columns, and more, then definitely checkout the WordPress Codex article on the Database Description.

Conclusion

At this point, we've covered all we need to cover in our primer of the WordPress database. Hopefully this helps to pull back the curtain for what goes on under-the-hood whenever you're saving information in WordPress, but now that we've covered this it's time to look at a tool that makes working with data migrations extremely easy.

And considering we now have an understanding of how the database is organized, we should also have an understanding as to how migrations work.