Shopify Connector Examples
The ZappySys API Driver is a user-friendly interface designed to facilitate the seamless integration of various applications with the Shopify API. With its intuitive design and robust functionality, the ZappySys API Driver simplifies the process of configuring specific API endpoints to efficiently read or write data from Shopify.
On this page you will find some SQL examples which can be used for API ODBC Driver or Data Gateway API Connector.
Get list of products [Read more...]
SELECT * FROM ProductsGet a specific product by its ID [Read more...]
SELECT * FROM Products WITH Id=1111111111111Get multiple specific products by their IDs [Read more...]
SELECT * FROM Products WITH(ids='1111111111111,2222222222222,3333333333333')Create a new product [Read more...]
This example shows how to insert a new Shopify product. It also sets Variants
INSERT INTO Products
(
Title
,Status
,BodyHtml
,UrlHandle
,Vendor
,ProductType
,Variants
,Options
,Tags
,Metafields
,Images
)
VALUES
('Ice Cream'
,'draft'
,'<strong>Very yummy ice cream!</strong>'
,'ice-cream'
,'Burton'
,'Snowboard'
,'[
{"price":10.5, "option1":"Chocolate","option2":"Small","sku":"ICE-CHO-SML","inventory_quantity":100},
{"price":10.5, "option1":"Chocolate","option2":"Medium","sku":"ICE-CHO-MED","inventory_quantity":100},
{"price":11.5, "option1":"Vanilla","option2":"Small","sku":"ICE-VNL-MED","inventory_quantity":210}
]'
--you must set variants and use atlease one value from the below list in option1, option2 or option3 in any variant entry else it will fail.
,'[
{"name":"Color","values":["Chocolate","Vanilla"]},
{"name":"Size","values":["Small","Medium"]}
]'
,'["Frozen","Seasonal","Dad''s Fav"]'
--adding metadata (custom fields) - metadata fields must be created before setting it
--below are 2 system fields for SEO Title / SEO Description (you dont need to create them unlike custom metadata). These values appears on SEO section
,'[
{"key":"title_tag","value":"Yum Ice Cream SEO Title", "namespace":"global","type":"single_line_text_field"},
{"key":"description_tag","value":"Yum Ice Cream SEO description", "namespace":"global","type":"single_line_text_field"}
]'
--first image becomes main image if you supply multiple images
--upload multiple images from URL (set "src")
, '[
{"src":"https://zappysys.com/images/tech/google-analytics-logo.png"},
{"src":"https://zappysys.com/images/tech/web-api-logo.png"}
]'
--OR upload multiple local image files (set "attachment")
--, '[
-- {"attachment":"<<c:\temp\icecream_1.png,FUN_FILE_BASE64ENC>>"},
-- {"attachment":"<<c:\temp\icecream_2.png,FUN_FILE_BASE64ENC>>"}
-- ]'
)Update an existing product [Read more...]
This example shows how to update an existing product. Update product title, description (body html), images, variants and more
UPDATE Products
SET Title='Ice Cream - Updated'
, Status='draft' --active, archived, draft
, BodyHtml='<strong>Very yummy ice cream - updated!</strong>'
--first image becomes main image if you supply multiple images
--upload multiple images from URL (set "src")
, Images='[
{"src":"https://zappysys.com/images/tech/google-analytics-logo.png"},
{"src":"https://zappysys.com/images/tech/web-api-logo.png"}
]'
--OR upload multiple local image files (set "attachment")
--, Images='[
-- {"attachment":"<<c:\temp\icecream_1.png,FUN_FILE_BASE64ENC>>"},
-- {"attachment":"<<c:\temp\icecream_2.png,FUN_FILE_BASE64ENC>>"}
-- ]'
, Variants='[
{"price":20.5, "option1":"Chocolate","option2":"Small","sku":"ICE-CHO-SML","inventory_quantity":300},
{"price":21.5, "option1":"Vanilla","option2":"Small","sku":"ICE-VNL-MED","inventory_quantity":110}
]'
, PublishedScope='global' --or web
, Vendor ='IceGlobal'
, ProductType ='Cold Food'
, Tags ='["Frozen","Seasonal","Dad''s Fav"]'
--Update SEO URL
,UrlHandle='ice-cream-51'
--Update SEO title / description
, SEOTitle='Yum Ice Cream SEO Title-update'
, SEODescription='Yum Ice Cream SEO description-update'
Where Id=7348335771748Delete an existing product [Read more...]
This example shows how to delete an existing product.
DELETE FROM Products
Where Id=7348335771748Get list of all product variants [Read more...]
SELECT * FROM ProductVariantsGet all product variants by a specific product ID [Read more...]
SELECT * FROM ProductVariants Where ProductId='1111111111111'Get all product variants by multiple specific product IDs [Read more...]
SELECT * FROM ProductVariants WITH(ids='1111111111111,2222222222222,3333333333333')Create a new product variant [Read more...]
This example shows how to create a new product variant.
INSERT INTO ProductVariants (ProductId, Option1, Option2,SKU,Price,CompareAtPrice,Position,Weight,WeightUnit,ImageId)
Values(7348335771748, 'Chocolate', 'Medium', 'ICE-CHO-MED', 195.5, 200.5, 3, 20.5, 'lb', 31900013854820)Update product variant price, image, weight [Read more...]
This example shows how to update product variant price, image, weight and other attributes.
Update ProductVariants
SET
,Option1='Chocolate'
,Option2='Large'
,SKU='ICE-CHO-SML'
,Price=90.45
,CompareAtPrice=100.45
,Position=2
,Weight=10.5
,WeightUnit='lb'
,ImageId=31900013854820 --use available images from Products table
Where Id=42564507992164Delete an existing product variant [Read more...]
This example shows how to delete an existing product variant by Variant Id.
DELETE FROM ProductVariants
WHERE Id=31900013854820Get list of customers [Read more...]
SELECT * FROM CustomersGet a specific customer by its ID [Read more...]
SELECT * FROM Customers Where Id=12345Get multiple specific customers by their IDs [Read more...]
SELECT * FROM Customers
WITH (ids='1111111111111,2222222222222,3333333333333')Insert a new customer record [Read more...]
INSERT INTO Customers
(FirstName, LastName, Email, Phone, Password, PasswordConfirmation, SendWelcomeEmail, MultipassIdentifier, Note, Tags, TaxExempt, TaxExemptions, DefaultAddressFirstName, DefaultAddressLastName, DefaultAddressCompany, DefaultAddressLine1, DefaultAddressLine2, DefaultAddressCity, DefaultAddressProvince, DefaultAddressCountry, DefaultAddressZip, DefaultAddressPhone, DefaultAddressName, DefaultAddressProvinceCode, DefaultAddressCountryCode, DefaultAddressCountryName)
VALUES
('John', 'Doe', 'john.doe@gmail.com', '7705553543', 'myNewP@ssword123', 'myNewP@ssword123', 1, null, 'This is a note on the customer account.', null, 0, null, 'John', 'Doe', 'John Doe Corp.', '123 Main Street', null, 'Atlanta', 'Georgia', 'United States', '30135', '7705553543', 'John Doe', 'GA', 'US', 'United States')Insert a new customer record using RAW JSON Body (special column _rawdoc_) [Read more...]
Sometimes you have need to INSERT or UPDATE certain arrtibutes for which input columns not defined. In this case you can supply entire BODY JSON as input using special column name _rawdoc_
INSERT INTO Customers(_rawdoc_)
VALUES('{"customer":{"first_name":"John","last_name":"Doe","email":"a.doe@gmail.com","phone":"7705553111"}}')Insert customers in BULK (read from external MS SQL database) [Read more...]
In this example we are reading customer Name, Email, Phone from external source system (Microsoft SQL Server) and sending it to Shopify. Your column name must match with Input columns of the table. See other BULK examples to learn more about reading from other systems using ODBC or OLEDB connection.
INSERT INTO Customers(FirstName, LastName, Email, Phone)
SOURCE('MSSQL'
,'Data Source=localhost;Initial Catalog=tempdb;Integrated Security=true'
,'select ''John'' as FirstName, ''Doe'' as LastName, ''a.doe@gmail.com'' as Email, ''7705553111'' as Phone'
)Insert customers in BULK using RAW JSON Body (read from external MS SQL database) [Read more...]
In this example we are reading customer Name, Email, Phone from external source system (Microsoft SQL Server) and sending it to Shopify. Your column name must match with Input columns of the table. See other BULK examples to learn more about reading from other systems using ODBC or OLEDB connection.
INSERT INTO Customers
SOURCE('MSSQL'
,'Data Source=localhost;Initial Catalog=tempdb;Integrated Security=true'
,'select ''{"customer":{"first_name":"Cust1","last_name":"Doe1","email":"a.doe@gmail.com","phone":"7705553111"}}'' as _rawdoc_
UNION
select ''{"customer":{"first_name":"Cust2","last_name":"Doe2","email":"b.doe@gmail.com","phone":"7705553222"}}'' as _rawdoc_
'
)Update an existing customer record [Read more...]
UPDATE Customers SET
Email = 'john.doe2@gmail.com',
Phone = '7705553445',
Note= 'This is a new note that needed to be added later.'
WHERE Id=1111111111111Update an existing customer record using RAW JSON Body (special column _rawdoc_) [Read more...]
Sometimes you have need to INSERT or UPDATE certain arrtibutes for which input columns not defined. In this case you can supply entire BODY JSON as input using special column name _rawdoc_
UPDATE Customers
SET _rawdoc_='{"customer":{"first_name":"John_new","last_name":"Doe_new","email":"a_new.doe@gmail.com","phone":"7705553111"}}'
WHERE Id=1111111111111Update an existing customer record [Read more...]
UPDATE Orders SET
FulfillmentStatus = 'john.doe5@gmail.com',
Phone = '7705553111',
Note= 'This is a new note that needed to be added to the order later.'
WHERE Id=1111111111111Update customers in BULK (read from external MS SQL database) [Read more...]
In this example we are reading customer Ids, Email, Notes from external source system (Microsoft SQL Server) and sending it to Shopify. Your column name must match with Input columns of the table you trying to update. See other BULK examples to learn more about reading from other systems using ODBC or OLEDB connection.
UPDATE Customers
SOURCE('MSSQL'
,'Data Source=localhost;Initial Catalog=tempdb;Integrated Security=true'
,'select 111 as Id, ''a@a.com''Email , ''SOLD'' as Note,0 as [$$ContineOn404Error]
UNION
select 222 as Id, ''b@b.com''Email , ''SOLD'' as Note,0 as [$$ContineOn404Error]
'
)Delete a customer record [Read more...]
DELETE Customers WHERE Id=1111111111111Delete a customer record (throw error if not found) [Read more...]
DELETE Customers WHERE Id=1111111111111 (ContineOn404Error=0)Delete customers in BULK (read Id from external MS SQL database) [Read more...]
In this example we are reading customer Ids from external source system (Microsoft SQL Server) and sending it to Shopify. See other BULK examples to learn more about reading from other systems using ODBC or OLEDB connection.
DELETE FROM Customers
SOURCE('MSSQL'
,'Data Source=localhost;Initial Catalog=tempdb;Integrated Security=true'
,'select 111 as Id,1 as [$$ContineOn404Error]
UNION
select 222 as Id,1 as [$$ContineOn404Error]
'
)Get all orders [Read more...]
SELECT * FROM OrdersGet open orders [Read more...]
SELECT * FROM Orders WITH (Status='open') --also try 'any', 'open', 'closed', 'cancelled'Get a specific order by its ID [Read more...]
SELECT * FROM Orders Where Id=1111111111111Get multiple specific orders by their IDs [Read more...]
SELECT * FROM Orders WITH(ids='1111111111111,2222222222222,3333333333333')Delete an order record [Read more...]
DELETE Orders WHERE Id=1111111111111Delete an order record (throw error if not found) [Read more...]
DELETE Orders WHERE Id=1111111111111 (ContineOn404Error=0)Get line items for all orders [Read more...]
SELECT * FROM OrderItemsGet line items for a specific order by the order ID [Read more...]
SELECT * FROM OrderItems Where OrderId=1111111111111Get line items for multiple specific orders by their order IDs [Read more...]
SELECT * FROM OrderItems WITH(ids='1111111111111,2222222222222,3333333333333')Insert a new order record [Read more...]
INSERT INTO Orders (BillingAddressLine1, BillingAddressLine2, BillingAddressCity, BillingAddressCompany, BillingAddressCountry, BillingAddressFirstName, BillingAddressLastName, BillingAddressPhone, BillingAddressProvince, BillingAddressZip, BillingAddressName, BillingAddressProvinceCode, BillingAddressCountryCode, BuyerAcceptsMarketing, LineItems, CustomerId, Email, EstimatedTaxes, FinancialStatus, FulfillmentStatus, Name, Note, Phone, Currency, PresentmentCurrency, ProcessedAt, ReferringSite, ShippingAddressLine1, ShippingAddressLine2, ShippingAddressCity, ShippingAddressCompany, ShippingAddressCountry, ShippingAddressFirstName, ShippingAddressLastName, ShippingAddressPhone, ShippingAddressProvince, ShippingAddressZip, ShippingAddressName, ShippingAddressProvinceCode, ShippingAddressCountryCode, Tags, TaxesIncluded, TotalWeight, SendReceipt, SendFulfillmentReceipt)
VALUES
('123 Main Street', 'Suite #54', 'Memphis', 'Acme, Inc.', 'United States', 'John', 'Doe', '4045559876', 'Tennessee', '38101', 'John Doe', 'GA', 'US', 1, '[{"title":"Super Strong Glue","price":24.99,"grams":"100","quantity":1,"tax_lines":[{"price":13.5,"rate":0.06,"title":"State tax"}]}]', 5945175474276, 'johndoe2@gmail.com', 1, 'pending', null, '#40294', 'This order needs to be expedited, so register it in the system as so.', '4045559876', 'USD', 'USD', '2023-02-27T11:00:00', 'https://referringsite.com', '123 Main Street', 'Suite #54', 'Memphis', 'Acme, Inc.', 'United States', 'John', 'Doe', '4045559876', 'Tennessee', '38101', 'John Doe', 'GA', 'US', NULL, 1, 20, 1, 1)Get inventory levels for all locations [Read more...]
Query inventory levels for all locations. If you get URL Too long error then manually supply location ids in the query (see other example)
select * from InventoryLevels
--WITH(location_ids='43512280416356, 44648752676964, ..... upto 300 to 500 more - until you hit URL limit error')Get inventory level for multiple item inventory id(s) [Read more...]
If you get URL Too long error then reduce inventory_item ids in the query (approx 300-400 ids per call allowed)
select * from InventoryLevels WITH (inventory_item_ids='43512280416356, 44648752676964')Get inventory level for specific location id(s) (i.e. Physcical Store / POS ) [Read more...]
If you get URL Too long error then reduce location ids in the query (approx 300-400 ids per call allowed)
select * from InventoryLevels WITH (location_ids='43512280416356, 44648752676964')Get inventory level for specific inventory / location id(s) [Read more...]
select * from InventoryLevels WITH (inventory_item_ids='43512280416356, 44648752676964' , location_ids='111100034, 111100055')Adjust inventory level for a specific inventory / location id(s) [Read more...]
Adjusts the inventory level of an inventory item at a single location
UPDATE InventoryLevels
SET AvailableAdjustment=488,
LocationId=25801916516
WHERE InventoryItemId=43512276942948
WITH(
Action='Adjust' --or set or connect
, ContineOn404Error=0
)Set / insert inventory with a specific inventory item and location id [Read more...]
Sets the inventory level for an inventory item at a location. If the specified location is not connected, it will be automatically connected first. When connecting inventory items to locations
UPDATE InventoryLevels
SET LocationId=25801916516
,Available=488
WHERE InventoryItemId=43512276942948
WITH(
Action='set' --or adjust or connect
, ContineOn404Error=0
)
--OR--
/*
INSERT INTO InventoryLevels (InventoryItemId,LocationId,Available)
VALUES(43512276942948, 25801916516, 488)
--WITH( ContineOn404Error=0 )
*/Connects an inventory item to a location [Read more...]
Connects an inventory item to a location by creating an inventory level at that location.
UPDATE InventoryLevels
SET LocationId=25801916516
WHERE InventoryItemId=43512276942948
WITH(
Action='connect' --or adjust or set
, ContineOn404Error=0
)Set inventory with a specific inventory item and location id - generic API [Read more...]
If you get URL Too long error then reduce location ids in the query (approx 300-400 ids per call allowed)
SELECT * FROM generic_request
WITH (
URL='/inventory_levels/set.json'
--OR Use full URL
--URL='https://MY-STORE-HERE.myshopify.com/admin/api/2023-01/inventory_levels/set.json'
, RequestMethod='POST'
, Body='{"location_id":25801916516,"inventory_item_id":43512280416356,"available":42}' --needed if you call PUT, POST
, Filter='$.inventory_level' --change table name here
, Headers='Content-Type: application/json'
, Meta='inventory_item_id:long; location_id:long; available:int; updated_at: datetime'
)Get inventory item by id [Read more...]
You can find Inventory Item Id in ProductVariants table. ProductVariant has One-to-One mapping with InventoryItems table
select * from InventoryItems Where Id=43512280416356Get inventory item by id [Read more...]
You can find Inventory Item Id in ProductVariants table. ProductVariant has One-to-One mapping with InventoryItems table
select * from InventoryItems Where Id=43512280416356Get inventory items by multiple Ids [Read more...]
Query multiple InventoryItems by Ids (Comma separated list). You can find Inventory Item Id in ProductVariants table. ProductVariant has One-to-One mapping with InventoryItems table
select * from InventoryItems WITH(Ids='43512280416356, 43512280449124')Update an existing inventory item cost and other attributes [Read more...]
UPDATE InventoryItems
SET Cost='25.55'
WHERE Id=43512280416356Generic Query using Shopify admin GraphQL API [Read more...]
This example shows how to invoke GraphQL query for very generic data read/write. For more information on GraphQL API visit this link https://shopify.dev/docs/api/admin/getting-started
SELECT * FROM generic_request
WITH (
URL='/graphql.json'
--OR Use full URL
--URL='https://MY-STORE-HERE.myshopify.com/admin/api/2023-10/graphql.json'
, RequestMethod='POST'
, Filter='$.data.products.nodes' --change table name here e.g. products
, Headers='Content-Type: application/json'
--change table name and columns below here e.g. products... and id, title etc
-- change pagesize if needed (i.e. max 250)
, Body='{
"query" : "<<{
products(first: 250 [$tag$])
{
nodes {
id
title
createdAt
}
pageInfo {
hasNextPage
endCursor
}
}
},FUN_JSONENC>>"
}'
, NextUrlAttributeOrExpr='$.data.products.pageInfo.endCursor' --change table name
, NextUrlEndIndicator='false'
, StopIndicatorAttributeOrExpr='$.data.products.pageInfo.hasNextPage' --change table name
, UseConnection='True'
, EnablePageTokenForBody='True'
, HasDifferentNextPageInfo='True'
, NextPageBodyPart='after: \"[$pagetoken$]\"'
--Use metadata to speed up execution. To get Metadata Run query without Meta clause.
-- Then click View Metadata button found in Botttom Result Grid Toolbar. Get Compact format and paste below
--, Meta='id:String(255); title:String(255); createdAt:DateTime; '
)Generic Query using Shopify admin REST API [Read more...]
This example shows how to invoke pretty much any REST API for generic data read/write. For more information on REST API visit this link https://shopify.dev/docs/api/admin/getting-started
SELECT * FROM generic_request
WITH (
URL='/products.json'
--OR Use full URL
--URL='https://MY-STORE-HERE.myshopify.com/admin/api/2023-01/products.json'
, RequestMethod='GET'
, Body='{}' --needed if you call PUT, POST
, Filter='$.products[*]' --change table name here
, Headers='Content-Type: application/json'
, PagingMode='ByResponseHeaderRfc5988'
--Use metadata to speed up execution. To get Metadata Run query without Meta clause.
--Then click View Metadata button found in Botttom Result Grid Toolbar. Get Compact format and paste below
, Meta='id:String(255); title:String(255); created_at:DateTime; '
)Getting Started with Examples
ZappySys API Driver is a powerful software solution designed to facilitate the extraction and integration of data from a wide range of sources through APIs. Its intuitive design and extensive feature set make it an essential asset for any organization dealing with complex data integration tasks.
To get started with examples using ZappySys API Driver, please click on the following applications:
Key features of the ZappySys API Driver include:
The API ODBC driver facilitates the reading and writing of data from numerous popular online services (refer to the complete list here) using familiar SQL language without learning complexity of REST API calls. The driver allows querying nested structure and output as a flat table. You can also create your own ODBC / Data Gateway API connector file and use it with this driver.
Intuitive Configuration: The interface is designed to be user-friendly, enabling users to easily set up the specific API endpoints within Shopify without requiring extensive technical expertise or programming knowledge.
Customizable Endpoint Setup: Users can conveniently configure the API endpoint settings, including the HTTP request method, endpoint URL, and any necessary parameters, to precisely target the desired data within Shopify.
Data Manipulation Capabilities: The ZappySys API Driver allows for seamless data retrieval and writing, enabling users to fetch data from Shopify and perform various data manipulation operations as needed, all through an intuitive and straightforward interface.
Secure Authentication Integration: The driver provides secure authentication integration, allowing users to securely connect to the Shopify API by inputting the necessary authentication credentials, such as API tokens or other authentication keys.
Error Handling Support: The interface is equipped with comprehensive error handling support, ensuring that any errors or exceptions encountered during the data retrieval or writing process are efficiently managed and appropriately communicated to users for prompt resolution.
Data Visualization and Reporting: The ZappySys API Driver facilitates the seamless processing and presentation of the retrieved data from Shopify, enabling users to generate comprehensive reports and visualizations for further analysis and decision-making purposes.
Overall, the ZappySys API Driver serves as a powerful tool for streamlining the integration of applications with Shopify, providing users with a convenient and efficient way to access and manage data, all through a user-friendly and intuitive interface.