September 2021
« Aug    


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:

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.

How databases are normally represented.

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:

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.

Database Tables
A database and its tables.

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.


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.


Comments 0
There are currently no comments.