WP-CLI beats SQL

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

# WP CLI?

WP CLI is handy when it comes to request 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';

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';

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:{}"

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

wp plugin deactivate --all

# Read options

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

wp option get active_plugins --format=json

which displays something like :

["gutenberg/gutenberg.php"]

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

which displays something like that :

[
  {
    "name": "gutenberg",
    "status": "active",
    "update": "available",
    "version": "2.1.0"
  }
]

# Getting useful information

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

wp db size

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

wp db size --tables

Please read the official documentation : wp db size

# Tabula rasa

A nice command is the reset command :

wp db reset

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

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');

Source

but now I prefer the following :

wp search-replace 'trucmuche' 'bidule'  table=wp_posts --dry-run

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

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

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

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

wp db export dump.sql --tables=wp_users,wp_usermeta

Awesome !

# Import

To import a dump into your database just run :

wp db import dump.sql

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.

See also blog post K.Lampert for more details.

# 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.