Here at DoltHub, we strive to ensure the Dolt database can be a drop in replacement for MySQL. There are almost 3 decades of development on MySQL, and we occasionally hear that we are crazy to attempt a full re-write. We're fine being called crazy; by our measure we are 99.87% of the way there. That's a nice number - but it's meaningless if we can't actually be dropped in as a replacement.

WordPress is one of the most widely used Content Management Systems around. It's open source, and has existed since 2003. It has only two specific dependencies - PHP 7.4 and up, and a running MySQL or MariaDB database. They list MySQL 5.7+ or MariaDB 10.4+ as the only two databases they support. We wanted to know if it was time for them to add Dolt to their list.

Does WordPress Work with Dolt?

Yes! In fact, this was so easy that documenting that alone would be too short for a blog post. WordPress already prides itself on its easy installation process, and they say it should take just 5 minutes. That may be the case for people who already have PHP installed (I didn't). After I installed PHP, I followed their instructions pretty much to the letter, with the exception of using Dolt instead of MySQL.

Here are the steps I followed to run a local WordPress instance on my machine. These instructions are very similar to the WordPress instructions, so be sure to check them out. Full disclosure: They suggest a tool, phpMyAdmin, to create the user and grant permissions. That didn't work with Dolt (yet), so I followed the manual instructions which were far more straightforward than installing the tool anyway.

1. Install PHP. On my Mac, I used brew, but I'll leave it to the reader to figure out the best approach:

$ brew install php

2. Install Dolt. Again, each system is a little different. On a Mac:

$ brew install dolt

3. Run a local Dolt Database

$ mkdir wordpress_db
$ cd wordpress_db
wordpress_db$ dolt init
wordpress_db$ dolt sql-server

You should see output like the following:

Starting server with Config HP="localhost:3306"|T="28800000"|R="false"|L="info"|S="/tmp/mysql.sock"
2023-08-02T11:43:18-07:00 INFO [no conn] Server ready. Accepting connections. {}

Leave this running in its own terminal window. You'll be able to see the logs and can verify the server is receiving traffic.

4. Create the wp-admin user.

wordpress_db$ dolt sql -q "CREATE USER 'wp-admin'@'%' IDENTIFIED BY 'w0rdpress'"

5. Grant admin privileges to wp-admin

wordpress_db$ dolt sql -q "GRANT ALL PRIVILEGES ON wordpress_db.* TO 'wp-admin'@'%'"

6. Download and untar WordPress. Do this in a different directory from your DB, and be aware that the tarball unpacks into the wordpress directory.

$ wget https://wordpress.org/latest.tar.gz
$ tar -xzvf latest.tar.gz
$ cd wordpress

7. Configure your WordPress instance to talk to your DB. There are a lot of details here, but the short instructions are that you need to copy the wp-config-sample.php file to wp-config.php, and edit it to have your database details. There are a lot of settings in that file that you don't need to worry about, but the database section should be modified to look like this:

/** The name of the database for WordPress */
define( 'DB_NAME', 'wordpress_db' );

/** Database username */
define( 'DB_USER', 'wp-admin' );

/** Database password */
define( 'DB_PASSWORD', 'w0rdpress' );

/** Database hostname */
define( 'DB_HOST', 'localhost' );

8. Start your WordPress server:

wordpress$ php -S localhost:8000

You should see output like the following.

[Wed Aug  2 15:41:56 2023] PHP 8.2.8 Development Server (http://localhost:8000) started

Leave this running in its own terminal window as well. Watch for logs showing traffic.

9. Then connect to the WordPress server with your browser at: http://localhost:8000/

You'll be directed to install.php which will ask for the name of your site and set up your admin user. Note that this user is different from the DB user. If you look at the logs of your two open terminals, dolt sql-server and php -S localhost:8000, you'll see messages about installing javascript files, creating users, creating tables, and so forth.

Once the install is complete, you'll be directed to a page that looks something like this:

There are numerous WordPress hosting services (lmgtfy), and it's quite possible that one or more of them gives you the freedom to specify your database. Dolt should work in that case, but I'm going to leave that exercise to the reader. Since I'm new to WordPress, I just wanted to know if I could run it locally against a local instance of Dolt. If an adventurous reader finds such a service, I suggest you point it to a HostedDolt instance. Serverless™!

Be More Dolty

Wordpress is up and running, but there is nothing particularly different about this installation than any other. Since the application knows nothing about Dolt, it's not taking advantage of the version control features at all. Regardless of how many posts you create, your Dolt status will remain the same:

wordpress_db$ dolt status
On branch main
Untracked tables:
  (use "dolt add <table>" to include in what will be committed)
        new table:        wp_comments
        new table:        wp_termmeta
        new table:        wp_terms
        new table:        wp_commentmeta
        new table:        wp_term_relationships
        new table:        wp_term_taxonomy
        new table:        wp_posts
        new table:        wp_usermeta
        new table:        wp_users
        new table:        wp_links
        new table:        wp_options
        new table:        wp_postmeta

This is telling us that there have been several new tables created, but none of them are being tracked in the Dolt version history. Furthermore, the HEAD of your history is never changing:

wordpress_db$ dolt show
commit bnv5u1gh2b1cg5nqnous52ulote0upo2 (HEAD -> main)
Author: Neil Macneale IV <neil@dolthub.com>
Date:  Wed Aug 02 13:27:47 -0700 2023

        Initialize data repository

Dolt has the ability to create a commit for every SQL transaction, so let's enable that with the following command:

wordpress_db$ dolt sql -q "set @@global.dolt_transaction_commit=1"

Once that is on, every update to the site will result in changes in your Dolt commit history. I created a post, gave it a title, then saved as a draft. Now the latest change looks like this:

wordpress_db$ dolt show
commit q8j7tkuuvtmfa3a9cafp0hse95l5d80n (HEAD -> main)
Author: Neil Macneale IV <neil@dolthub.com>
Date:  Wed Aug 02 13:53:11 -0700 2023

        Transaction commit

diff --dolt a/wp_postmeta b/wp_postmeta
--- a/wp_postmeta
+++ b/wp_postmeta
+---+---------+---------+------------+--------------+
|   | meta_id | post_id | meta_key   | meta_value   |
+---+---------+---------+------------+--------------+
| < | 3       | 5       | _edit_lock | 1691009530:1 |
| > | 3       | 5       | _edit_lock | 1691009591:1 |
+---+---------+---------+------------+--------------+

Since your server may still be running, there will be changes to your DB regardless of if you alter the page or not. This can be interesting to reverse engineer. I came across this commit:

wordpress_db$ dolt show --skinny HEAD~4
commit jeescbtrsodtvr5q2p7ojthvt2hua4ai
Author: Neil Macneale IV <neil@dolthub.com>
Date:  Wed Aug 02 13:49:15 -0700 2023

        Transaction commit

diff --dolt a/wp_posts b/wp_posts
--- a/wp_posts
+++ b/wp_posts
+---+----+----------------------------+
|   | ID | guid                       |
+---+----+----------------------------+
| < | 7  |                            |
| > | 7  | http://localhost:8000/?p=7 |
+---+----+----------------------------+

Which was an interesting discovery because it turns out that the full URL is used and the globally unique identifier, and this technique is used in several tables. It's not the design choice I would make, but there are so many ways to skin this cat. As someone who knows nothing about WordPress, it was interesting to do things on the site, then see how the data changed in each individual SQL transaction. This visibility into exactly how and when your data has changed is one of Dolt's super powers – you can track the complete history of any value in any row since that row was initially inserted to a table, and you can see exactly how it changed, when, and who changed it. Being able to see that history was a pretty neat way to reverse engineer how WordPress interacts with the datastore.

Be Even More Dolty?

What can Dolt enable you to do with WordPress that you can't do at all today? It took me a little exploring with WordPress before I felt like I had a good example. It turns out that WordPress is pretty good about having revisions on posts and showing users their history. There are a lot of features to WordPress, which I remind you I know nothing about. Furthermore, in many cases I found that what looked like a single action on the WordPress application would result in a dozen updates to the database.

Taking the reverse engineering theme a little further, I used a little trick to make sense of live application through the lens of its data. Remember that the HEAD commit in Dolt is always changing, thanks to the dolt_transaction_commit flag set above. I decided to use tags start and finish so that I could see stable behavior. The workflow went like this:

1. Create a start tag:

wordpress_db$ dolt tag start

2. Do something on the site. Create a draft post, leave a comment, create a user. I tried all of these things and more. Creating Lisa and Bart Simpson's user accounts was the most compelling:

3. Create a finish tag:

wordpress_db$ dolt tag finish

4. Compare the results

wordpress_db$ dolt diff start..finish

screen capture from my terminal

From this, I can see three tables were altered as a result of my user creations. There were two rows added to the wp_users table, and there were additional details about those users in the wp_usermeta table, details such as their locale preferences. Finally, there is a user_count field updated in the wp_options table. I don't know why that is there, but I assume it's to optimize an access pattern.

Also, I can see there are 32 commits in the range, I'll spare you the details, but if you look at each one, they are mostly adding single rows to the wp_usermeta table. Like this arbitrary commit ~4 from finish:

wordpress_db$ dolt show finish~4
commit 9llk6qiqc35autvsmmdce6kv6g3m3hib
Author: Neil Macneale IV <neil@dolthub.com>
Date:  Thu Aug 03 15:10:33 -0700 2023

        Transaction commit

diff --dolt a/wp_usermeta b/wp_usermeta
--- a/wp_usermeta
+++ b/wp_usermeta
+---+----------+---------+----------+------------+
|   | umeta_id | user_id | meta_key | meta_value |
+---+----------+---------+----------+------------+
| + | 42       | 3       | locale   |            |
+---+----------+---------+----------+------------+

Bart is not great about keeping his key to himself, so we want to set a new password for him. I'm curious how that looks in the database. In order to do the same trick with the tags, delete the existing tags and create a new start tag:

wordpress_db$ dolt tag -d start
wordpress_db$ dolt tag -d finish
wordpress_db$ dolt tag start

Now lets make a change the WordPress. If you hover over the name, you'll get the option to "Edit". There, you can set the password to the user to whatever you would like.

When you've finished the password change, create the tag, and look at what changed:

wordpress_db$ dolt tag finish
wordpress_db$ dolt diff --skinny start..finish
diff --dolt a/wp_users b/wp_users
--- a/wp_users
+++ b/wp_users
+---+----+------------------------------------+
|   | ID | user_pass                          |
+---+----+------------------------------------+
| < | 2  | $P$BdV9KkShgz6vkv4v20OgLmfjy/K/KN/ |
| > | 2  | $P$BXQ93ThWvgZEBDg62nQd3o1KEH6N0l/ |
+---+----+------------------------------------+

Turns out that the users table is pretty simple compared to wp_posts with all of its revisions complexity. Maybe more importantly, we can now tell when a user changes their password and we can reset their password to values from the past.

wordpress_db$ dolt sql -q 'select commit_hash,commit_date,user_pass from  dolt_history_wp_users where user_login = "bart" limit 5'
+----------------------------------+-------------------------+------------------------------------+
| commit_hash                      | commit_date             | user_pass                          |
+----------------------------------+-------------------------+------------------------------------+
| dsttvdn2o6cqbgk92p3naiq50m7ka4u4 | 2023-08-03 23:03:01.332 | $P$BXQ93ThWvgZEBDg62nQd3o1KEH6N0l/ |
| coqurjpu5rp308bpfdifsdurr03bena0 | 2023-08-03 22:47:35.368 | $P$BdV9KkShgz6vkv4v20OgLmfjy/K/KN/ |
| rgfavrnkvvtbgahfgs4q51ibfshb7j6k | 2023-08-03 22:47:01.961 | $P$Bz3KS0OATy2bzqmAvzEpCY2s.VZg4W1 |
| bdfg0auqk608hkjfeb0faqinogek6url | 2023-08-03 22:47:01.941 | $P$Bz3KS0OATy2bzqmAvzEpCY2s.VZg4W1 |
| j3nce5tmgj71hnbl7f4siu83fpag5nr1 | 2023-08-03 22:46:16.817 | $P$Bz3KS0OATy2bzqmAvzEpCY2s.VZg4W1 |
+----------------------------------+-------------------------+------------------------------------+

If we look at the HEAD change, It's simply a update of the user_pass column, which is what we saw int the diff above:

wordpress_db$ dolt show --skinny dsttvdn2o6cqbgk92p3naiq50m7ka4u4
commit dsttvdn2o6cqbgk92p3naiq50m7ka4u4 (tag: finish)
Author: Neil Macneale IV <neil@dolthub.com>
Date:  Thu Aug 03 16:03:01 -0700 2023

        Transaction commit

diff --dolt a/wp_users b/wp_users
--- a/wp_users
+++ b/wp_users
+---+----+------------------------------------+
|   | ID | user_pass                          |
+---+----+------------------------------------+
| < | 2  | $P$BdV9KkShgz6vkv4v20OgLmfjy/K/KN/ |
| > | 2  | $P$BXQ93ThWvgZEBDg62nQd3o1KEH6N0l/ |
+---+----+------------------------------------+

Using the version history of the wp_user table provided by Dolt, we can see when credentials were updated. Otherwise, WordPress doesn't have this ability. In this particular instance, if you wanted to put the password back to its previous value, you would simply revert that particular commit:

wordpress_db$ dolt revert dsttvdn2o6cqbgk92p3naiq50m7ka4u4
commit 8o069445c27vkj20n7e3dfeliaci5542 (HEAD -> main)
Author: Neil Macneale IV <neil@dolthub.com>
Date:  Thu Aug 03 16:37:38 -0700 2023

        Revert "Transaction commit"

Wrapping it Up

This post isn't really about WordPress, per se. It's about how can you use version controlled data to enable you navigate the unknown. I was able to explore the application without ever looking at the source code, learn its data model, and uncover a missing feature that I now magically had - all in a couple hours.

Ultimately applications won't get the full benefits of Dolt unless they are built with Dolt's unique versioning approach in mind. If I were to build an application like WordPress on Dolt, I wouldn't worry about modeling revisions in the DB - I'd just use the Dolt's AS OF mechanism to see old versions. Furthermore, I'd enable multiple people to edit a document rather than using locks, then merge the data together when both authors are done. There are many unique applications you can build if you start with versioned data as a building block. Come join us on discord to tell us what application you're going to build!