loading...

When WordPress CLI beats SQL

jmau111 profile image Julien Maury Updated on ・3 min read

Useful tricks to make the most of WordPress tools and save time.

WP CLI?

WP CLI is handy to make requests to the database.

Deactivate all plugins

The following SQL statement is often used to debug :

UPDATE wp_options SET option_value = 'a:0:{}'WHERE option_name = 'active_plugins';
Enter fullscreen mode Exit fullscreen mode

But WP CLI allows you to run the same statement :

wp db cli
UPDATE wp_options SET option_value = 'a:0:{}'WHERE option_name = 'active_plugins';
Enter fullscreen mode Exit fullscreen mode

wp db cli allows for making all kinds of SQL requests, besides, credentials are already loaded from the wp-config file so it’s super fast :

wp option update active_plugins "a:0:{}"
Enter fullscreen mode Exit fullscreen mode

But it gets better! You can use a specific command to deactivate all plugins :

wp plugin deactivate --all
Enter fullscreen mode Exit fullscreen mode

Read options

We already saw the UPDATE part, here is the GET part :

wp option get active_plugins --format=json
Enter fullscreen mode Exit fullscreen mode

which displays something like :

["gutenberg/gutenberg.php"]
Enter fullscreen mode Exit fullscreen mode

So much faster but it gets better! You can use a specific command to get plugin list here :

wp plugin list --status=active --format=json
Enter fullscreen mode Exit fullscreen mode

which displays something like that :

[{"name":"gutenberg","status":"active","update":"available","version":"2.1.0"}]
Enter fullscreen mode Exit fullscreen mode

Getting useful information

You can google « how to get db size » or you can simply run :

wp db size
Enter fullscreen mode Exit fullscreen mode

You can even use the –tables option to get each table size. Nice!

wp db size --tables
Enter fullscreen mode Exit fullscreen mode

Please read the official documentation : wp db size

Tabula rasa

A nice command is the reset command :

wp db reset
Enter fullscreen mode Exit fullscreen mode

This erase database but it keeps the miminum keys such as admin user account. Pretty useful when developing plugins, it allows for testsing all kind of situations, especially when your option page is not set which prevents bad warnings and notices.

This will prompt a message because this operation is kinda dangerous, for example on a production server, but you can use the global WP CLI option –yes to skip that :

wp db reset --yes
Enter fullscreen mode Exit fullscreen mode

See db reset

Search and replace

Search and replace in posts

Before WP CLI, I used to do that :

    UPDATE wp_posts SET POST_CONTENT = replace(POST_CONTENT, 'trucmuche', 'bidule');
Enter fullscreen mode Exit fullscreen mode

but now I prefer the following :

wp search-replace 'trucmuche' 'bidule'  table=wp_posts --dry-run
Enter fullscreen mode Exit fullscreen mode

I always add a « –dry-run » to make sure my request has good results and when I’m sure I remove the –dry-run :

wp search-replace 'trucmuche' 'bidule'  table=wp_posts
Enter fullscreen mode Exit fullscreen mode

the table option is pretty cool, I don’t have to run the entire database.

Search and replace and export

The following WP CLI command is gold :

wp search-replace 'local.wordpress.test' 'mon-site.com' --export=dump.sql
Enter fullscreen mode Exit fullscreen mode

Everything is done in 1 command. I get a nice and clean SQL export.

See wp search-replace for all options.

Export/import

Export

The wp db export works like that :

wp db export dump.sql
Enter fullscreen mode Exit fullscreen mode

but you can be more specific and select only the tables you need :

wp db export dump.sql --tables=wp_users,wp_usermeta
Enter fullscreen mode Exit fullscreen mode

Awesome!

Import

To import a dump into your database just run :

wp db import dump.sql
Enter fullscreen mode Exit fullscreen mode

import use the credentials set in wp-config.php so it’s smooth. I hate typing credentials in my terminal.

It does not work for me!

Sometimes, there are incompatible plugins that mess all the fun but you can skip them! See skip plugins.

Wrap up

WP-CLI is awesome. It can even generate (scaffold) Gutenberg blocks, themes, and plugins with extra features (tests, config files).

WP-CLI works pretty much like plugins, you can extend it with useful packages.

This post was originally published on https://blog.julien-maury.com/

Posted on by:

jmau111 profile

Julien Maury

@jmau111

Practise what you preach.

Discussion

pic
Editor guide