What $wpdb Is, and Why I Try Not To Use It

More than half of what matters about a WordPress site is stored in its (typically MySQL) database. Why? Because WordPress stores all its “content” in the database. For most people, that makes it even more important (and harder to replace) than their plugins and themes, and possibly even media files.

In the simplest terms possible $wpdb is how the database is accessed 99% of the time on a WordPress site. It’s WordPress’s database interacting agent.

In the simplest terms possible $wpdb is how the database is accessed 99% of the time on a WordPress site. It’s WordPress’s database interacting object. And it’s quite good, rather useful, and more than a bit interesting. It’s also not nearly as necessary to understand as a new developer might expect. I rarely use it outside of a small number of cases where it’s worthwhile to make and maintain my own database tables in a plugin. For most of my data creation and fetching needs, WordPress offers other functions which are a great deal easier.

So our goal is to get into a bit more detail about $wpdb is and some of its capabilities, and then we’ll answer the more important issue: when you should and shouldn’t use it.

Understanding the Basics of Database Interactions in PHP

SQL stands for “structured query language” and its the general syntax that people use to put data into and retrieve data from relational databases like MySQL.

As we talked about at some length in the LAMP discussion, PHP and MySQL aren’t actually “stacked” when used in WordPress. They’re peers, and they talk to each other through an agreed upon channel. But generally, a PHP developer doesn’t need to even think about that channel, someone has abstracted that channel away and they can just get to the business of doing their database stuff.

When I say “database stuff,” I basically mean running SQL queries and commands and seeing their results. If you’re not familiar, SQL stands for “structured query language” and its the general syntax that people use to put data into and retrieve data from relational databases like MySQL. Though every database software — MySQL, Postgres, SQLServer, Oracle Database, etc — generally has a slightly divergent version, all relational databases speak some dialect of SQL.

If you’re totally comfortable with SQL, go ahead and skip on. If not, we’re about to run through a few-minute introduction to what a basic query looks likes.

Your First SQL Query

If you’ve never peered into a database before, the most approachable analogy I know of is that of a spreadsheet.

People usually reach for $wpdb when they want to write their own SQL queries or commands. The syntax of SQL that’s necessary and useful to be good at using databases is far too big for me to explain here. (Plus, I’m not going to claim vast SQL expertise. I know enough to understand JOINs intuitively, but not so well that I can tell you the syntax and specific meaning without reference materials.)

If you’ve never peered into a database before, the most approachable analogy I know of is that of a spreadsheet. Like a Microsoft Excel (Google Sheets, etc) document, a database — think of it as the main file — contains many tables, which are like the individual sheets inside your Excel document. So you give WordPress access to a single database, and inside of that it creates its nine tables, each of which consists of rows and rows of data separated into columns that store your values/content. Make sense?

SQL is how we interrogate these database tables. And most people’s first SQL statement will looks something like this:

SELECT * FROM table WHERE `column` = 'value';

Before we get too far, I’ve capitalized three whole words in that statement: SELECT, FROM, and WHERE. This is a common convention when writing SQL — all-caps-ing your keywords — but it’s not strictly required. Anyway, what do these keywords do?

  • SELECT tells the database what data we want to receive back from it. By saying SELECT * we’re telling it that we want “everything” — that’s the star’s meaning — which means each value from each column. You can also just specify one, two, or twelve column names. That’s look something like SELECT `col1`, `col2`. (Backticks are optional in SQL, but are used to surround column names.)
  • FROM tells the database which table to look in. Again, this is saying go to the table table. (Not a good name, but it makes the SQL fragment stand alone well.)
  • WHERE is our condition(s) for pulling values from the table. If you don’t have a WHERE clause, you’re effectively asking for the whole table. Here we’re looking for only rows where the value in the column column is 'value'.

There are lots more things about SQL that are useful to know. We’ve not covered ordering, or JOINs, or INSERT statements or more. But if we did, this article would be well over 3000 words long. Hopefully this has given you a basic sense of the terms and ideas at play when you actually set out to write some SQL.

Your First $wpdb Query

Ok, so lets assume that we want to get all posts from our database which has a numeric ID below 45. Using raw SQL, we’d write something like:

SELECT * FROM wp_posts WHERE `id` < 45;

To run that query in a world where $wpdb exists, you’ll do something like this in our PHP:

global $wpdb;
$posts = $wpdb->get_results( "SELECT * FROM $wpdb->posts WHERE `id` < 45", OBJECT );

The first line here is important and uses the fact that WordPress sets up early on it its process a global instance of $wpdb so what we’re doing when we’re saying global $wpdb is telling PHP that from now on when we talk about $wpdb in this code, we’re referring to that global one WordPress set up. (I’ve written a little more about globals in PHP here, but it’s a big area.)

Then we’re using the get_results() method on that $wpdb object to pass in our query. You’ll notice that our raw SQL syntax was making an assumption that our posts were in the wp_posts table, but that’s not a safe assumption for all WordPress sites. This query has switched that out for a reference to a public property of the $wpdb object, namely it’s posts value. As you may be able to guess — this is one of those little ways that $wpdb is kind of nice — each of these properties corresponds to full prefix-included table name. The name of the table that is usually wp_posts is accessible at $wpdb->post, wp_users is at $wpdb->users etc. By getting table names this way they’ll continue to work if someone gave their WordPress database tables the prefex rT5Sq_ rather than, wp_.

The second parameter in there is specifying that we want to get back an array of WP_Post objects, which I prefer to the other structures on offer. Not too painful, right?

There a wealth of other methods on $wpdb. get_row, get_var, insert, delete, etc. We’ve also not covered issues of safely parameterizing queries and making sure you don’t let bad actors blow up your site. We could definitely explain them all, but for simplicity’s sake let’s leave you to the Codex for now.

Why I Try My Best To Eschew $wpdb and SQL

The problem that I have with reaching for $wpdb isn’t that I’m opposed to SQL, it’s that it’s a different level of abstraction than most WordPress code. Jumping between writing SQL and PHP is a small but important mental tax you pay.

If you read through that introduction to SQL, there’s a good chance that you kind of already grasp part of my reason for trying to use $wpdb as little as possible: SQL is a new layer of different abstraction and complexity.

There’s nothing wrong with SQL, it’s a really solid, robust, and well-designed language. And $wpdb makes SQL easier than it is without such a nice resource. The problem that I have with reaching for SQL and $wpdb isn’t that I’m opposed to SQL, it’s that it’s a different level of abstraction and thought than most WordPress code. Jumping between writing SQL and PHP is a small but important mental tax you pay.

And it’s an added prerequisite that some other coder must understand before they can contribute to your project. A lot of people who can otherwise do pretty well in WordPress programming are understandably hesitant to get into SQL.

These concerns also leave aside the importance of making sure your SQL queries are safely parameterized to prevent an SQL injection attack, and that you’re able to efficiently and effortlessly serialize and re-hydrate data from the database back into your code.

Controlling complexity is one of the important and never-finished tasks of a good developer. We touched on this a bit in my Genesis article, but it’s always possible to further complicate thing to complete a short-term goal. But it’s both harder and more important to actually keep that complexity as small as it can possibly be so new people joining a project aren’t lost in the weeds.

When To Reach for $wpdb

$wpdb is great for when you really get benefits from writing your own SQL. This is most common when you’re writing a plugin with complex data needs.

$wpdb is great for when you really get benefits from writing your own SQL. This is most common when you’re writing a plugin with complex data needs. If you have complex data structures that don’t have a lot in common with a classic piece of content like a WordPress post or page, you may want to think about making your own tables. The easiest and most common way that a WordPress developer skips the complexity of SQL and $wpdb is to make everything possible a “post.” But for complex systems where a lot of non-post-like data is useful and necessary — e-commerce is the obvious example — simply shoehorning your system to “posts” will hurt a lot as you try to run complex reports on “post meta” data.

Another possibility is that you’re actually running deep performance analysis of your site and you are sure that you can write a better database query than WordPress runs for you by default. I’ve personally never had a case where database access was an important performance bottleneck on a WordPress site, but I do know that it could happen. In that case, going that extra step may be worth it.

Because it’s still pretty integrated with WordPress, $wpdb‘s much preferable to dropping down to the PHP primitives and making those work for you when you need to access the database. But when you’re trying to do something that a new WP_Query() or get_post_meta() will accomplish for you just as fast, I think reaching for $wpdb can easily be an unforced design mistake.

What We’ve Learned

$wpdb is a bit like a chainsaw. It’s a really powerful tool, but it’s also overkill when you just need to trim a few branches on a slightly overgrown bush.

Hopefully you’ve now got a solid understanding of the basics of what $wpdb does in the WordPress ecosystem and how it’s useful. I hope you also know see that it’s a bit like a chainsaw. It’s a really powerful tool, but it’s also overkill when you just need to trim a few branches on a slightly overgrown bush.

Whenever possible, I recommend heartily that you stick to WordPress’s PHP functions which look in the database and hide the SQL layer away from you. It keeps your work simpler and frees you to focus on your problem rather than worrying about this new SQL languages you may not fully understand the power and risk of. Happy hacking!

Image credit: Glen Noble | Upsplash


2 Responses

Comments

Pingbacks