Shopify to PostgreSQL: 2 Easy Methods

The advent of the internet and the cloud has paved the way for SaaS companies like Shopify to simplify the cumbersome task of setting up and running a business online. The businesses that use Shopify have crucial data about their customers, products, catalogs, orders, etc. within Shopify and would often need to extract this data out of Shopify into a central database and combine this with their advertising, ads, etc. to derive meaningful insights.

PostgreSQL has emerged as a top ORDBMS (object-relational database management system) that is highly extensible with technical standards compliance. PostgreSQL’s ease of set up and use has made it a very lucrative option to host data from systems like Shopify. In this blog, we are going to learn how to move data from Shopify to PostgreSQL.

Introduction to Shopify

Shopify is an e-commerce platform that helps businesses to create online stores and sell their products. They also have an online retail point of sale system (Shopify POS) where merchants can sell their products in person. It is designed for all businesses regardless of their sizes. Shopify provides a set of tools and services such as payment gateways, shipping, marketing, and customer engagement so that users can grow their business easily.

Shopify has built-in capabilities that allow users to analyze customers’ data. However, businesses/organizations need to store this data in a database because it allows them to gain in-depth insights by combining other organizational data with Shopify. This allows them to leverage the power of a full-fledged database (or sometimes a data warehouse) and overcome the limitation of Shopify analytics. 

Various products or services that you can sell on Shopify:

• Physical Products: Shopify supports door-step delivery of your manufactured products such as jewelry, custom 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 the 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 PostgreSQL

PostgreSQL is an open-source and powerful object-relational database system. It not only uses SQL language but also extends it with many features that stores and scale very complicated data workloads. It has a strong reputation due to its proven architecture, data integrity, reliability, extensibility, robust feature set and dedication to a rich open-source community. 

Key features of Postgres

• ACID-Compliant: Postgres is ACID-compliant and so ensures that your transactions are handled in a timely and efficient manner.
• Open Source: Postgres is fully open-source and has an active community to help with efficiency tips, bug resolution, etc.
• Extensibility: Postgres is very customizable and so can more readily incorporate new functionality into its system.

The above two methods will be covered in detail in this blog. In the end, you will have an option to choose the best based on your needs.

Understanding the Methods to Connect Shopify to PostgreSQL

Here are the methods you can use to connect Shopify to PostgreSQL in a seamless fashion:

• Method 1: Using Hevo Data to Connect Shopify to PostgreSQL
• Method 2: Using Custom ETL Scripts to Connect Shopify to PostgreSQL

Method 1: Using Hevo Data to Connect Shopify to PostgreSQL

Data can be loaded from Shopify to PostgreSQL without having to write any code. This can be done in just two steps:

Step 1: Configure your Shopify data source

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

Step 2: Configure your PostgreSQL As a Destination

• Load data from Shopify to Postgresql by providing your Postgresql databases credentials like Database Host, Port, Username, Password, Schema, and Name along with the destination name.

Advantages of Using Hevo

1. No Data Loss – Hevo has a fault-tolerant architecture that ensures data is reliably moved from Shopify to PostgreSQL.
2. 100’s of Out of the Box Integrations – In addition to Shopify,  Hevo brings data from other sources such as Databases, Cloud Applications, SDKs, and so on into PostgreSQL. This gives you the flexibility and scalability required to grow your business.
3. Minimal Setup – Hevo is easy to set up and use. It has a visual point-and-click interface that will allow you to start moving data from Shopify to PostgreSQL in minutes.
4. Automatic Schema Detection and Mapping – The schema of incoming Shopify data is scanned automatically by Hevo. Hevo handles any changes seamlessly by incorporating them into PostgreSQL. 
5. Exceptional Support –  Hevo provides 24/7 email and chat support so that you always have someone who can reliably navigate you through any issues you may face.

Method 2: Using Custom ETL Scripts to Connect Shopify to PostgreSQL

The Shopify platform is completely exposed to developers through the Shopify API. Businesses can extract data from Shopify using the API endpoints Shopify exposes. 

The Shopify API can be accessed using HTTP/CURL/Postman as the API follows RESTful architecture principles. Here are the steps involved in this method:

• Step 1: Shopify API Authentication
• Step 2: Shopify Endpoints
• Step 3: Preparing Shopify Data for PostgreSQL
• Step 4: Loading Data from Shopify to PostgreSQL

Step 1: Shopify API Authentication

To authenticate with Shopify’s API using a private app, you’ll need to generate API credentials from your Shopify admin or Partner dashboard. Here’s how you can do it:

• Create a Private App: Go to your Shopify admin panel.
  • Click on Apps > Manage private apps > Create a new private app.
  • Set permissions for the app according to your data needs.
• Obtain API Credentials:
  • After creating the app, you’ll receive an API key and Password. Use these for authentication.
• Basic Authentication Example: Use the API key and password in your API requests as follows:

import requests

SHOPIFY_API_KEY = 'your_api_key'
SHOPIFY_API_PASSWORD = 'your_api_password'
SHOP_NAME = 'your_shop_name'

url = f'https://{SHOPIFY_API_KEY}:{SHOPIFY_API_PASSWORD}@{SHOP_NAME}.myshopify.com/admin/api/2020-01/products.json'

response = requests.get(url)
products = response.json()
print(products)

Step 2: Shopify Endpoints

All possible touchpoints of e-commerce are exposed by the 35+ Shopify API endpoints. Below are some of the most important resources covered by these endpoints: 

• Order Details: These set of APIs allow you to extract data on orders placed, cart abandonments, refunds requested and more
• Customer Details: These APIs allow you to retrieve customer data
• Product Details: These APIs allow you to retrieve the details of the products listed on the Shopify store.

To fetch the product data, use the following API request:

response = requests.get(url)
products = response.json()
print(products)

Step 3: Preparing Shopify Data for PostgreSQL

Before loading data into PostgreSQL, you need to define a schema that corresponds to your Shopify data. Here’s a basic example of how to create a table for products:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    shopify_id BIGINT UNIQUE,
    title VARCHAR(255),
    body_html TEXT,
    vendor VARCHAR(255),
    product_type VARCHAR(255),
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    price DECIMAL(10, 2)
);

Step 4: Loading Data from Shopify to PostgreSQL

• Inserting Data

While you can use the INSERT command for small data loads, it’s more efficient to use the COPY command for larger datasets.

• Using INSERT (for small batches)

import psycopg2
conn = psycopg2.connect("dbname='your_db' user='your_user' password='your_password' host='localhost'")
cur = conn.cursor()

for product in products['products']:
    cur.execute("""
        INSERT INTO products (shopify_id, title, body_html, vendor, product_type, created_at, updated_at, price)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
    """, (
        product['id'],
        product['title'],
        product['body_html'],
        product['vendor'],
        product['product_type'],
        product['created_at'],
        product['updated_at'],
        product['variants'][0]['price']  # Assuming you take the first variant's price
    ))

conn.commit()
cur.close()
conn.close()

• Using COPY (for large datasets)
  • First, save the data to a CSV file:

import csv

with open('products.csv', 'w', newline='') as csvfile:
    fieldnames = ['shopify_id', 'title', 'body_html', 'vendor', 'product_type', 'created_at', 'updated_at', 'price']
    writer = csv.DictWriter(csvfile, fieldnames=fieldnames)

    writer.writeheader()
    for product in products['products']:
        writer.writerow({
            'shopify_id': product['id'],
            'title': product['title'],
            'body_html': product['body_html'],
            'vendor': product['vendor'],
            'product_type': product['product_type'],
            'created_at': product['created_at'],
            'updated_at': product['updated_at'],
            'price': product['variants'][0]['price']
        })

• Then, use the COPY command to load data into PostgreSQL:

COPY products (shopify_id, title, body_html, vendor, product_type, created_at, updated_at, price)
FROM '/path/to/products.csv'
DELIMITER ','
CSV HEADER;

Limitations of using Custom ETL Scripts to Connect Shopify to PostgreSQL

1. Infrastructure Maintenance: Shopify has rich APIs and may continuously update or change them. So, you will need to invest in time and an engineering team to update and maintain the ETL code regularly. 
2. Real-time Data: The above approach works if you have data to be loaded in a batch fashion. Many times, this is not what businesses need. If your use case is to bring data from Shopify and load it to PostgreSQL in real-time, you would need additional lines of code to implement that. 
3. Ability to Transform Data: The current method does not allow you to clean, transform, or enrich the Shopify data. For example, let us say you would want to standardize time zones to effectively calculate sales or if you want to convert currencies to a specific denomination, these tweaks are not achievable using the above code. Also, check out how you can export Shopify orders.

Conclusion

This blog talks about the two different methods you can use to establish a connection from Shopify to PostgreSQL seamlessly, using Custom ETL Scripts and a third-party tool, Hevo. It helps transfer data from Shopify to a destination of your choice for free.

Sign Up for a 14-day risk-free trial with Hevo. This will give you an opportunity to experience Hevo’s simplicity and start moving data from Shopify to PostgreSQL instantly. 

What are your thoughts on moving data from Shopify to PostgreSQL? Let us know in the comments. 

FAQs