Partial Database Export with data specified by a configuration file

A configuration file formatted with valid JSON can be used to specify the data for export in the Partial Database Export file. To use the configuration file, pass the absolute or relative path to the file in the --config-file option when running the VIP-CLI command vip export sql.

For example:

vip @example-app.develop export sql --config-file=~/Users/example-user/Desktop/example-file.json

The configuration file must be located on the user’s local machine and be set to a type of data export: full, tables, site_ids, or wpcli_command.

full

Set the configuration file type to full in order to export a database in its entirety.

// Export the entire database

{
  "type": "full"
}

tables

Set the configuration file type to tables in order to export specific database tables. In this example, the wp_posts, wp_postmeta, wp_users, wp_terms, and wp_options tables will be exported in the Partial Database Export file.

// Export specific database tables

{
  "type": "tables",
  "tables": {
    "wp_posts": {},
    "wp_postmeta": {},
    "wp_users": {},
    "wp_terms": {},
    "wp_options": {}
  }
}

SQL dump options

More specific data from within the tables can be exported by adding SQL dump options to the configuration file.

Options that are allowed to be added to a configuration file set to tables type:

• insert-ignore (boolean) Write INSERT IGNORE rather than INSERT statements. This will prevent the insertion of a row if it potentially creates a duplicate key.
• no-create-info (boolean) Do not write CREATE TABLE statements that re-create each dumped table. Export data with INSERT statements instead.
• no-data (boolean) Do not include table contents. Only include table schema.
• replace (boolean) Write REPLACE statements rather than INSERT statements.
• skip-add-drop-table (boolean) Do not add a DROP TABLE statement before each CREATE TABLE statement.
• where (string) Only include rows selected by the given WHERE condition.

// Export specific data from the database tables

{
  "type": "tables",
  "tables": {
      "wp_posts": {
          "no-create-info": true, // Export data with INSERT statements but do not include CREATE TABLE statements
          "insert-ignore": true // Prevent the insertion of a row if it potentially creates a duplicate key
      },
      "wp_postmeta": {
          "no-data": true // Only table schema, no data
      },
      "wp_users": {
          "insert-ignore": true,
          "where": "ID IN (SELECT DISTINCT post_author FROM wp_posts WHERE post_status = 'publish')" // Any valid SQL WHERE clause
      },
      "wp_terms": {
          "no-data": true
      },
      "wp_options": {
          "skip-add-drop-table": true, // Export data but do not include DROP TABLE IF EXISTS statements
          "replace": true // Replace existing
      }
  }
}

site_ids

Set the configuration file type to site_ids in order to export only the tables related to the network sites of a WordPress multisite that are requested by ID. In this example, the tables related to site IDs 2, 5, and 10 will be exported in the Partial Database Export file.

// Export database tables related to specific network sites

{
  "type": "site_ids",
  "site_ids": [2, 5, 10]
}

Export specific data from network site tables

SQL dump options can also be added to a configuration file set to the site_ids type. This allows specific data from within the tables related to the requested network site(s) to be included in the Partial Database Export file.

// Export specific data from the tables related to the requested network sites

{
  "type": "site_ids",
  "site_ids": [2, 5, 10],
  "tables": {
    "wp_posts": {
      "where": "post_status = 'publish'"
    },
    "wp_options": {
      "insert-ignore": true
    }
  }
}

wpcli_command

Set the configuration file type to wpcli_command in order to apply the logic of a custom WP-CLI command to specify the data to be included in the Partial Database Export file.

The custom WP-CLI command referred to in the configuration file must exist within the branch that deploys to the environment from which the data will be exported. The logic of the custom WP-CLI command should be configured to retrieve the desired set of data for the export.

In this configuration file example, data based on the logic of the custom WP-CLI command my-custom-data-export will be retrieved from the past 180 days and exported.

// Export the entire database

{
  "type": "wpcli_command",
  "wpcli_command": "my-custom-data-export --days=180"
}