Shopify to MySQL: 2 Easy Methods

Shopify is an eCommerce platform that enables businesses to sell their products in an online store without spending time and effort on developing the store software.

Even though Shopify provides its suite of analytics reports, it is not always easy to combine Shopify data with the organization’s on-premise data and run analysis tasks. Therefore, most organizations must load Shopify data into their relational databases or data warehouses. In this post, you can explore how to load from Shopify to MySQL, one of the most popular relational databases today.

Why You Should Migrate Data From Shopify to MySQL?

1. Better Data Control – Store and manage Shopify data in your own database for more flexibility.
2. Advanced Analytics – Run complex queries and generate custom reports easily.
3. Faster Performance – Optimize data retrieval for quicker insights.
4. Integration with Other Systems – Seamlessly connect with BI tools, CRMs, and other apps.
5. Cost Efficiency – Reduce reliance on Shopify’s API limits and lower data access costs.

Method 1: Using Hevo to Connect Shopify to MySQL

Step 1.1: Configure Shopify as Your Source

Step 1.2: Configure MySQL as Your Destination

You have now successfully established a connection between Shopify and MySQL.

Method 2: Using Custom ETL Code to Connect Shopify to MySQL

Step 2.1: Using Shopify Export Option

This method provides a simple click-to-export function that allows you to export products, orders, or customer data into “CSV files.” The caveat is that this will be a completely manual process and cannot be done programmatically. 

This method provides simple click-and-export solutions to get the product, orders, and customer data into CSV. This CSV can then be used to load to a MySQL instance. The steps below detail how Shopify customers’ data can be loaded into MySQL.

• Go to Shopify admin and go to the customer’s tab.
• Click Export.
• Select whether you want to export all customers or a specified list of customers. Shopify allows you to select or search customers if you only want to export a specific list.
• After selecting customers, select ‘plain CSV’ as the file format.
• Click Export Customers, and Shopify will provide you with a downloadable CSV file.
• Login to MySQL and use the below statement to create a table according to the Shopify format.

CREATE TABLE customers ( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, firstname VARCHAR(30) NOT NULL, lastname VARCHAR(30) NOT NULL, email VARCHAR(50), company VARCHAR(50), address1 VARCHAR(50), address2 VARCHAR(50), city VARCHAR(50), province VARCHAR(50), province_code VARCHAR(50), country VARCHAR(50), country_code VARCHAR(50), zip VARCHAR(50), phone VARCHAR(50), accepts_markting VARCHAR(50), total_spent DOUBLE, email VARCHAR(50), total_orders INT, tags VARCHAR(50), notes VARCHAR(50), tax_exempt VARCHAR(50)

• Load data using the following command:

LOAD DATA INFILE'customers.csv' INTO TABLE customers FIELDS TERMINATED BY ','  ENCLOSED BY '"' LINES TERMINATED BY 'rn'  IGNORE 1 LINES

Now, that was very simple. But, the problem here is that this is a manual process, and programmatically doing this is impossible. This method will not be helpful if you want to set up a continuous syncing process. For that, you will need to use the Shopify APIs.

Step 2.2: Using Shopify REST APIs to Access Data 

Shopify APIs provide access to products, orders, sales, and customer data but have request limits using a leaky bucket algorithm. Exceeding limits results in errors.

Shopify offers various APIs, with product APIs allowing access to product-related data. Private apps enable store-specific API access via credentials from Shopify Admin. For broader use across multiple stores, public apps require OAuth authentication.

Before starting, ensure you have Shopify Admin access and the generated API credentials.

Once you have access to the credentials, accessing the APIs is very easy, and is done using basic HTTP authentication. Let’s look into how the most basic API can be called using the generated username and password.

curl --user:password GET https://shop.myshopify.com/admin/api/2019-10/shop.json

To get a list of all the products in Shopify, use the following command:

curl --user user:password GET /admin/api/2019-10/products.json?limit=100

Please note this endpoint is paginated and will return only a maximum of 250 results per page. The default pagination limit is 50 if the limit parameter is not given.

From the initial response, users need to store the id of the last product they received and then use it with the next request to get to the next page:

curl --user user:password GET /admin/api/2019-10/products.json?limit=100&since_id=632910392 -o products.json

Where since_id is the last product ID that was received on the previous page.

The response from the API is a nested JSON that contains all the information related to the products, such as title, description, images, etc., and more importantly, the variants sub-JSON, which provides all the variant-specific information like barcode, price,inventory_quantity, and much more information.

Users need to parse this JSON output and convert the JSON file into a CSV file of the required format before loading it to MySQL.

For this, use the Linux command-line utility called jq. You can read more about this utility from the Ubuntu documentation. For simplicity, you can extract only the id, product_type, and product title from the result. Assuming your API response is stored in products.json

Cat products.json | jq '.data[].headers | [.id .product_type product_title] | join(", ")' >> products.csv

Please note you will need to write complicated JSON parsers if you need to retrieve more fields. 

Once the CSV files are obtained, create the required MYSQL command beforehand and load data using the LOAD DATA INFILE command shown in the previous section. 

LOAD DATA INFILE'products.csv' INTO TABLE customers
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY 'rn' ;

Now, you have your Shopify product data in your MySQL. 

Limitations of Using Custom ETL Code to Connect Shopify to MySQL

Shopify provides two easy methods to retrieve the data into files. But, both these methods are easy only when the requests are one-off, and the users do not need to execute them continuously in a programmatic way. Some of the limitations and challenges that you may encounter are as follows: 

• The above process works fine if you want to bring a limited set of data points from Shopify MySQL. You will need to write a complicated JSON parser if you need to extract more data points
• This approach fits well if you need a one-time or batch data load from Shopify. If you are looking at a real-time data sync between Shopify and MySQL, the above method will not work.

An easier way would be to use a fully managed data pipeline solution like Hevo, which can mask all these complexities and deliver a seamless data integration experience.

Use Cases of Shopify to MySQL Integration

1. Advanced Analytics: MySQL’s extensive data processing capabilities allow you to run complicated queries and data analysis on your Shopify data, resulting in insights that would not be achievable with Shopify alone.
2. Data Consolidation: If you’re using various sources in addition to Shopify, syncing to MySQL allows you to centralize your data for a more complete picture of your operations, as well as set up a change data capture process to ensure that there are no data conflicts in the future.
3. Historical Data Analysis: Shopify has limitations with historical data. Syncing data to MySQL enables long-term data retention and trend monitoring over time.
4. Data Security and Compliance: MySQL offers sophisticated data security measures. Syncing Shopify data to MySQL secures your data and enables advanced data governance and compliance management.
5. Scalability: MySQL can manage massive amounts of data without compromising performance, making it a perfect alternative for growing enterprises with expanding Shopify data.

Conclusion

This blog talks about the different methods you can use to connect Shopify to MySQL seamlessly: using a third-party tool, Hevo, and custom ETL scripts.

The second method to set up Shopify MySQL integration involves manually building custom code using RESTful APIs. This method has certain limitations, which were discussed in the previous section.

Implementing Hevo Data can avoid the challenges of the second method. 

Hevo can additionally connect to a variety of data sources (databases, cloud applications, sales and marketing tools, etc.), making it easy to scale your data infrastructure at will. It helps transfer data from Shopify to a destination of your choice for free. Sign up for our 14-day free trial and seamlessly migrate your data from Shopify to MySQL.

FAQs

Sign up for a 14-day free trial. Sign up today to explore how Hevo makes Shopify to MySQL a cakewalk for you!

What are your thoughts about the different approaches to moving data from Shopify to MySQL? Let us know in the comments.