Shopify to MySQL: 2 Easy Methods

Shopify is an eCommerce platform offered as a service that enables businesses to sell their products in an online store without having to spend 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 will need to load Shopify data into their relational databases or data warehouses. In this post, we will discuss how to load from Shopify to MySQL, one of the most popular relational databases in use today.

Introduction to Shopify

Shopify is one of the most popular e-commerce platforms allowing businesses and users to sell their products/services online. Using Shopify, you can set up an online store and sell your products with the help of the Point of Sale (POS) system.

Shopify provides users with secure payment gateways, marketing, and shipping facilities, along with various customer engagement techniques, to help you get started.

With Shopify’s interactive interface, you can also analyze various customer trends and metrics, to draw crucial insights about their customers.

Various products or services that you can sell on Shopify:

• Physical Products: Shopify supports door-step delivery of your manufactured products such as jewelry, t-shirts, mugs, etc. You can schedule the delivery of various products using Shopify.
• Digital Products: With Shopify, you can provide digital services such as e-books, audio files, course materials, etc. to your customers.   
• Services and Consultation: If you provide services like Life consultation, home-cooked delicacies, event planning, or anything else, Shopify has got you covered.
• Memberships: Various memberships such as gym memberships, yoga classes memberships, event memberships, etc. can be sold to customers using Shopify.
• Experiences: Event-based experiences like adventurous sports and travel, mountain trekking, wine tasting, events, and hands-on workshops. You can use Shopify to sell tickets for these events as well.
• Rentals: You can also promote and advertise various types of rental services such as rental taxis, furniture, gadgets, etc. using Shopify and engage with customers in a better and more efficient way.
• Classes: You can advertise online tutorials on Shopify, allowing customers to subscribe to various online courses such as studies, fitness, yoga, etc. 

Introduction to MySQL

MySQL was used by more than 39% of developers in the world back in 2019, making it the world’s most popular Database. It certainly lacks the extensive features that are provided by PostgreSQL, but it is still useful for a large number of applications like web applications. 

Since it comes standard in the LAMP stack, where LAMP stack is an Open-Source suite of web applications that consists of Linux, Apache HTTP Server, MySQL, and PHP; MySQL is the go-to choice for scalable web applications.

Let’s talk about a few salient features that make MySQL such a great catch.    

Key Features of MySQL

• Maintained by Oracle: Oracle owns and maintains MySQL. It also offers premium versions of MySQL with additional services, proprietary plugins, user support, and extensions. 
• Long History: MySQL has been around for over 20 years since its first release in 1995.
• Frequent Updates: MySQL is made more robust with frequent updates with new features and security improvements. The latest release is Version 8.0.23 released on 18 January 2021.
• MVCC Features: MySQL recently started offering MVCC (Multi-Version Concurrency Control) features. 
• A Supportive Community: A devoted community of developers is available to help with troubleshooting as and when needed.
• Open-Source: MySQL is also a free and Open-Source Relational Database Management System (RDBMS).
• Users: MySQL is used widely by Google, NASA, Flickr, GitHub, Netflix, Tesla, Twitter, Uber, Wikipedia, YouTube, and Zendesk to name a few.

This blog covers the first approach in great detail. The blog also covers the limitations of this approach and also highlights the workarounds to mitigate them.

Understanding the Methods to connect Shopify to MySQL

These are the methods you can use to establish a connection from Shopify to MySQL seamlessly:

• Method 1: Using Hevo to connect Shopify to MySQL
• Method 2: Using Custom ETL Code to connect Shopify to MySQL

Method 1: Using Hevo to connect Shopify to MySQL

The best way to avoid the above limitations is to use a fully managed Data Pipeline platform as Hevo works out of the box. It will automate your data flow in minutes without writing any line of code. Its fault-tolerant architecture makes sure that your data is secure and consistent.

Hevo provides a truly efficient and fully automated solution to manage data in real-time and always has analysis-ready data at MySQL.

With Hevo’s point-and-click interface, loading data from Shopify to MySQL comes down to 2 simple steps:

• Step 1: Connect and configure your Shopify data source by providing the Pipeline Name, Shop Name, and Admin API Password.

• Step 2: Input credentials to the MySQL destination where the data needs to be loaded. These include the Destination Name, Database Host, Database Port, Database User, Database Password, and Database Name.

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

Shopify provides two options to access its product and sales data:

1. Use the export option in the Shopify reporting dashboard: This method provides a simple click-to-export function that allows you to export products, orders, or customer data into CSV files. The caveat here is that this will be a completely manual process and there is no way to do this programmatically. 
2. Use Shopify rest APIs to access data: Shopify APIs provide programmatic access to products, orders, sales, and customer data. APIs are subject to throttling for higher request rates and use a leaky bucket algorithm to contain the number of simultaneous requests from a single user. The leaky bucket algorithm works based on the analogy of a bucket that leaks at the bottom. The leak rate is the number of requests that will be processed simultaneously and the size of the bucket is the number of maximum requests that can be buffered. Anything over the buffered request count will lead to an API error informing the user of the request rate limit in place. 

Let us now move into how data can be loaded to MySQL using each of the above methods:

• Step 1: Using Shopify Export Option 
• Step 2: Using Shopify REST APIs to Access Data 

Step 1: Using Shopify Export Option

The first 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 below steps detail how Shopify customers’ data can be loaded to MySQL this way.

• 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 not possible.

If you are looking to set up a continuous syncing process, this method will not be helpful. For that, we will need to use the Shopify APIs.

Step 2: Using Shopify REST APIs to Access Data 

Shopify provides a large set of APIs that are meant for building applications that interact with Shopify data. Our focus today will be on the product APIs allowing users to access all the information related to products belonging to the specific user account.

We will be using the Shopify private apps mechanism to interact with APIs. Private Apps are Shopify’s way of letting users interact with only a specific Shopify store.

Authentication, in this case, is done by generating a username and password from the Shopify Admin. If you need to build an application that can be used by any Shopify store, you will need a public app configuration with OAuth authentication.

Before beginning the steps, ensure you have gone to Shopify Admin and have access to the generated username and password.

Once you have access to the credential, 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, I am using the Linux command-line utility called jq. You can read more about this utility here. For simplicity, I am only extracting 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 to MySQL. You will need to write a complicated JSON parser if you need to extract more data points
• This approach fits well if your need is a one-time or batch data load from Shopify to MySQL. In case you are looking at real-time data sync from Shopify to MySQL, the above method will not work.

An easier way of accomplishing this 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 from Shopify to MySQL.

Conclusion

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

Visit our Website to Explore Hevo

That’s it! No Code, No ETL. Hevo takes care of loading all your data in a reliable, secure, and consistent fashion from Shopify to MySQL. 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 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.