Setting Up WooCommerce MySQL Integration: 3 Easy Methods

WooCommerce is an open-source E-Commerce platform that is built on top of WordPress. WooCommerce helps businesses set up eCommerce websites easily. It supports all the standard functionalities needed by small and medium businesses. WooCommerce offers great customizability while creating storefront designs. It provides many plugins that can be used to integrate functionalities like dynamic pricing, subscriptions, etc, easily. 

WooCommerce also provides a number of add-on services to handle other tasks related to running an E-Commerce organization, like shipping, delivery, order tracking, etc. In this article, you will learn about the three methods to set up WooCommerce MySQL Integration.

Methods to Integrate WooCommerce MySQL

Method 1: WooCommerce MySQL Integration using Hevo’s No-Code Data Pipeline
Method 2: Using the REST APIs for WooCommerce SQL Integration 
Method 3: Directly Accessing the WooCommerce Database

Method 1: WooCommerce MySQL Integration using Hevo’s No-Code Data Pipeline

Without the need for manually setting up any connection drivers or the limited-feature MySQL Workbench, you can effortlessly carry out WooCommerce MySQL Integration using Hevo by following the simple steps given below:

Step 1: Configure WooCommerce as the Source

Step 2: Configure MySQL as the Destination

For more information, check out the Hevo documentation:

• WooCommerce Source Connector
• MySQL Destination Connector

Advantages of using Hevo Data Platform:

• Data transformation: Its analyst-friendly data transformation features are well integrated into the platform to streamline analytics tasks further.
• No Data Loss – Hevo architecture is fault-tolerant and allows easy, reliable, and seamless data transfer from WooCommerce MySQL without data loss. 
• Automatic Schema Detection and Mapping – The incoming data schema is scanned automatically.  If changes are detected, they are handled seamlessly, and the changes are incorporated into the Database or Data Warehouse. 
• Multi-region support: Hevo supports maintaining a single account across all Hevo regions, with a maximum of five workspaces.

Method 2: Using REST APIs for WooCommerce SQL Integration

In the case of a completely managed WooCommerce instance, it is better to use the REST APIs exposed by the WooCommerce database. You will now try to access the REST APIs and load the customer data to your MySQL table.

• Head to the “Advanced” section in your WooCommerce dashboard to create an API key to access the REST API.

• Click ‘Add key’ , enter the user details and description, and click ‘Generate API Key’

• Note the generated Consumer Key and Consumer secret to use with your REST API calls.
• Use the below command to access the list of customers from your WooCommerce instance

curl https:/<your_woocommerce_instance>/wp-json/wc/v3/customers 
    -u consumer_key:consumer_secret >> cust_list.json

• Ensure that you replace the consumer_key and secret with what was generated in the previous step.
• Use the below command to extract the id, email and first_name from the JSON file to CSV.

jq -r '.[] | [.id,.email,.first_name] | @csv' cust_list.json >> cust_list.csv

The above command uses a utility called “jq” to convert the JSON to CSV from Linux command line.

• This CSV can then be loaded to a table using the ‘LOAD DATA’ command. Assuming you have already created a table with the required structure, this can be done using the below command in the MySQL shell.

LOAD DATA INFILE '<path_to_csv_file>'
INTO TABLE customers
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY 'n';

Once the last command is successfully executed, you can find the customer data inserted into MySQL.

Also read: Ultimate Guide on How to Use WooCommerce REST APIs Simplified 101

Limitations of REST API Method

While the REST API gave us the data in a structured format, this was still not a seamless exercise. In summary, this method for WooCommerce MySQL Integration has the following limitations.

• The above approach is suitable for a one-off load, but to make this production-ready, a script has to be created using the above building blocks. This will require quite a lot of developer effort.
• Synchronizing only the newly added entries and not the complete customer table every time will require even more steps to be added to this approach. 
• For large tables, this will be a time-consuming process, the script will need logic to restart in case it fails or loses connection. 

Method 3: Directly Accessing WooCommerce Database

Accessing the WooCommerce database directly requires knowing how WooCommerce uses WordPress to get things done.

WordPress uses MySQL as the default database. It uses a table called “wp_posts” to store the blog posts. It may seem counterintuitive to read about storing blog posts while talking about an eCommerce plugin, but the answer to that lies in the fact that WordPress is primarily a content management system.

Hence, its table structure is designed accordingly. Since WooCommerce is built on top of WordPress, the WooCommerce database schema is similar; it uses the same tables WordPress uses. Most WooCommerce entities, like Orders, Products, etc, are stored in the ‘wp_posts’ table with a post type to distinguish between them.

For example, orders are stored in the ‘wp_posts’ table with post_type as ‘shop_order’. If you want the details of your customers, you need to find the order information first, then join the post_id of that order with the ‘wp_meta’ table that stores metadata about posts. The ‘wp_meta’ table is a tall table with ‘meta_key’ and ‘meta_value’ as columns. You will find all the required customer attributes, like first name, last name, address, etc, in this table. 

Now that we understand the table structure, let us explore how data from this MySQL database can be loaded into your own MySQL instance.

1. Use the FTP client that you use to administer your WordPress instance and go to the root folder. Locate the wp-config.php file and open it.
2. Scroll down to find the details of the MySQL instance used by WordPress. It will look as below.

3. Note the values for ‘DB_HOST’, ‘DB_NAME’, ‘DB_USER’, and ‘DB_PASSWORD’ and use the below query. 
4. Use SSH to log in to the instance where WooCommerce is installed and type the following command.

sudo mysqldump -u root --databases 'DB_NAME' --single-transaction --compress --order-by-primary --password='DB_PASSWORD' | sudo mysql -u 'targetDBusername' --host 'targetDBHost' --password

Ensure to replace with the username and hostnames of the source database and target database in the appropriate places. You may also want to add the IP of your WordPress instance to the allowed_hosts list in your target database. 

Once the command is executed, a copy of the WooCommerce database will be created in your target MySQL instance. 

Limitations of Accessing WooCommerce Database Method

As evident from the detailed process, this is not the easiest to execute. This method of WooCommerce MySQL Integration also comes with a number of other limitations

1. This is possible only on self-hosted WooCommerce instances. If you are using a completely managed instance, it will be tough to use this method. 
2. This method takes a complete dump of all the data every time you need to sync WooCommerce MySQL data.

Need for WooCommerce MySQL Integration

Since WooCommerce is built on top of WordPress, it uses the same database as WordPress to MySQL. The question is, if WooCommerce already stores its data in MySQL, what is the need to connect it separately to MySQL? 

This is because the storefront database is transactional, and it is not a good idea to use it for analytics. Having the data in a separate database allows analysts to query the data without worrying about putting a load on the database that powers the storefront. 

Hence, most organizations choose to extract, transform, and load the data from a transactional database to a separate database in a format suitable for analysis. Since MySQL has a strong querying layer, it is used as an analytical database tool in many cases. Hence, the need to integrate WooCommerce with MySQL.

Conclusion

In this article, you learnt about the 3 two approaches to provide basic building blocks to set up WooCommerce MySQL Integration. The last 2 methods to WooCommerce MySQL Integration require a lot of development effort to transform this into a production-ready form. If you want to integrate WooCommerce MySQL through a code-free method, consider using Hevo. It is a completely managed ETL tool that can facilitate data transfer between most common sources and the target database. 

Want to take Hevo for a spin? Sign Up here for a 14-day free trial and experience the feature-rich Hevo suite firsthand.

FAQs