Having a nice GUI to make direct database queries can be really helpful when working with a complex WordPress site. TablePlus is the best one one I’ve found for the Mac.

Using TablePlus with WP Engine

Connecting to a local database and most remote databases should be pretty straightforward, but there’s a few extra steps if the database is hosted with WP Engine.

WP Engine has remote database access instructions here. Make sure to get your IP address whitelisted, include the cert, and get the ports right. Here’s a screenshot of what the settings should look like.

Useful Queries

These days I do a lot of work with WooCommerce. Here’s the types of queries I run on a regular basis.

Get progress of ActionScheduler during subscription renewal periods:

select status,
	count(*) from wp_actionscheduler_actions where 
	status in ('failed','complete','pending')
	and scheduled_date_gmt > '2021-01-01 00:00:00' 
	and scheduled_date_gmt < '2021-01-05 00:00:00' 
	and hook in ('woocommerce_scheduled_subscription_payment')
group by status;

Update order statuses in bulk:

UPDATE wp_posts
SET post_status = 'wc-pending'
WHERE ID IN (101,102,103);

Get some order/post meta for a specific set of IDS:

SELECT
	orders.ID,
	orders.post_date as order_date,
	orders.post_status as status,
	shipping.meta_value as shipping_date
FROM
	wp_posts orders
JOIN
	wp_postmeta shipping ON orders.ID=shipping.post_id
WHERE
	orders.post_status = 'wc-processing' AND
	shipping.meta_key = 'shipping_date' AND
	orders.ID IN ( 100, 101, 102 )
);

Even for little things, fetching a piece of data can be way quick using TablePlus than even just looking at the order entry itself in WordPress.