Adding a Connection to Shopify

1. In the application console, navigate to the Connections page.
2. At the Add Connections panel, select the icon for the connection you want to add.
3. If the Shopify icon is not available, click the Add More icon to download and install the Shopify connector from the CData site.

For required properties, see the Settings tab.

For connection properties that are not typically required, see the Advanced tab.

Connecting to Shopify

Connecting via Access Token

Connecting via an access token takes two steps:

1. Use the Shopify admin page to create the access token
2. Use your access token to authenticate

Create the Access Token

1. Log in to your Shopify from the admin page and navigate to Apps > Apps and sales channels.
2. Click Develop apps and select Create an app.
3. In the Overview tab, click Configure Admin API scopes and select the access permissions for your store that you want to grant to your application. The permissions required by our provider to use all the tables and views are:
   • read_assigned_fulfillment_orders, write_assigned_fulfillment_orders
   • read_content, write_content
   • read_customers, write_customers
   • read_draft_orders, write_draft_orders
   • read_fulfillments, write_fulfillments
   • read_gift_cards, write_gift_cards
   • read_inventory, write_inventory
   • read_marketing_events, write_marketing_events
   • read_orders, write_orders
   • read_price_rules, write_price_rules
   • read_product_listings, write_product_listings
   • read_products, write_products
   • read_reports, write_reports
   • read_script_tags, write_script_tags
   • read_shopify_payments_payouts
   • read_themes, write_themes
   • read_markets
   • read_shopify_payments_accounts
   • read_publications
   • unauthenticated_read_content (Storefront API)
4. Click Save.
5. Select API Credentials.
6. Under "Access tokens" click Install app. This creates your access token.
7. Copy your access token under Admin API Access token. NOTE: The token can be revealed and copied only once.

Authenticate via Access Token

• AuthScheme: Set to AccessToken.
• AccessToken: Set to the access token value you copied from the custom app.

Connecting via OAuth

This section details a selection of advanced features of the Shopify Sync App.

User Defined Views

The Sync App supports the use of user defined views, virtual tables whose contents are decided by a pre-configured user defined query. These views are useful when you cannot directly control queries being issued to the drivers. For an overview of creating and configuring custom views, see User Defined Views .

SSL Configuration

Use SSL Configuration to adjust how Sync App handles TLS/SSL certificate negotiations. You can choose from various certificate formats;. For further information, see the SSLServerCert property under "Connection String Options" .

Firewall and Proxy

Configure the Sync App for compliance with Firewall and Proxy, including Windows proxies and HTTP proxies. You can also set up tunnel connections.

Query Processing

For further information, see Query Processing.

Logging

Customizing the SSL Configuration

By default, the Sync App attempts to negotiate TLS with the server. The server certificate is validated against the default system trusted certificate store. You can override how the certificate gets validated using the SSLServerCert connection property.

To specify another certificate, see the SSLServerCert connection property.

Connecting Through a Firewall or Proxy

HTTP Proxies

To authenticate to an HTTP proxy, set the following:

• ProxyServer: the hostname or IP address of the proxy server that you want to route HTTP traffic through.
• ProxyPort: the TCP port that the proxy server is running on.
• ProxyAuthScheme: the authentication method the Sync App uses when authenticating to the proxy server.
• ProxyUser: the username of a user account registered with the proxy server.
• ProxyPassword: the password associated with the ProxyUser.

Other Proxies

Set the following properties:

• To use a proxy-based firewall, set FirewallType, FirewallServer, and FirewallPort.
• To tunnel the connection, set FirewallType to TUNNEL.
• To authenticate, specify FirewallUser and FirewallPassword.
• To authenticate to a SOCKS proxy, additionally set FirewallType to SOCKS5.

The CData Sync App models entities in the Shopify API as tables, views, and stored procedures. These are defined in schema files, which are simple, text-based configuration files that are easy to customize.

Key Features

• Include Custom Fields connection property allows you to retrieve custom fields for Products and ProductVariants table. Set this property to True, to enable this feature.
• The Row Scan Depth connection property sets the number of rows that are filtered for custom fields. The default value for this property is 50. There is a trade-off: setting a low value may result in not getting all the custom fields, but setting a high value decreases the driver's performance. To use this feature set the Include Custom Fields connection property to True.
• The Flexible Schema retrieves custom fields for the Products and ProductVariants table. This feature is enabled when the Include Custom Fields connection property is set to True. Flexible schema searches at most 250 rows for custom fields. To retrieve more fields, set the Row Scan Depth to a higher value.

Using Rest API

See REST Data Model for the available entities in the REST API.

Using GraphQL API

See GRAPHQL Data Model for the available entities in the GraphQL API.

The CData Sync App models entities in the Shopify API as tables, views, and stored procedures. These are defined in schema files, which are simple, text-based configuration files that are easy to customize.

Key Features

• Include Custom Fields connection property allows you to retrieve custom fields for Products and ProductVariants table. Set this property to True, to enable this feature.
• The Row Scan Depth connection property sets the number of rows that are filtered for custom fields. The default value for this property is 50. There is a trade-off: setting a low value may result in not getting all the custom fields, but setting a high value decreases the driver's performance. To use this feature set the Include Custom Fields connection property to True.
• The Flexible Schema retrieves custom fields for the Products and ProductVariants table. This feature is enabled when the Include Custom Fields connection property is set to True. Flexible schema searches at most 250 rows for custom fields. To retrieve more fields, set the Row Scan Depth to a higher value.

Using Rest API

See REST Data Model for the available entities in the REST API.

Using GraphQL API

See GRAPHQL Data Model for the available entities in the GraphQL API.

The CData Sync App models the Shopify API as relational views, and stored procedures.

To use REST Data Model, simply set Schema to REST.

Tables

Tables are tables that can be modified, such as Orders, Products.

Views

Views are tables that cannot be modified, such as Events, Patouts and Reports. Typically, read-only model data is shown as views.

Stored Procedures

Stored Procedures are function-like interfaces to the data source. Use these interfaces to search, update, and modify information in the data source.

The Sync App models the data in Shopify as a list of tables in a relational database that can be queried using standard SQL statements.

Shopify Connector for CData Sync Tables

Create or view Application Charges for Billing.

Table-Specific Information

Select

SELECT * FROM ApplicationCharges WHERE Id = '123'

Insert

INSERT INTO ApplicationCharges ( Test ) VALUES ( 'true' )

Columns

Create or view Application Credit for Billing.

Table-Specific Information

Select

SELECT * FROM ApplicationCredit WHERE Id = '123'

Insert

INSERT INTO ApplicationCredit (Amount, Description, Test) VALUES ('100', 'success or failure', 'true')

Columns

Create, read, update or delete articles

Table-Specific Information

Select

SELECT * FROM Articles

SELECT * FROM Articles WHERE Id = '123'

Insert

You must specify the Title to create an Article.

INSERT INTO Articles (BlogId, Author, Title) VALUES ('599421749', 'Adam', 'New Article')

Update

You must specify the blog id and id to update an article. For example:

UPDATE Articles SET Author = 'Harry' WHERE BlogId = '599421749' AND Id = '5557081211'

Delete

You must specify the Id and the Blog Id of the article to delete it.

DELETE FROM Articles WHERE BlogId = '599421749' AND Id = '5556952105'

Columns

Create, read, update or delete assets. CUD support is no longer accessible for public apps.

Table-Specific Information

Select

SELECT * FROM Assets WHERE Key = 'assets/abc.gif'

Insert

You must specify the Theme Id and Key to insert an Asset. For example:

INSERT INTO Assets(Key, Value, ThemeId) VALUES('assets/abc.gif', '
', '282977') 

Update

You must specify the Theme Id and Key to update an Asset. For example:

UPDATE Assets SET value = 'pqrs' WHERE Key = 'assets/abc.gif' AND ThemeId = '282977'

Delete

You must specify the Id and the Theme Id of the Asset to delete it.

DELETE FROM Assets WHERE Key = 'assets/abc.gif' AND ThemeId = '282977'

Columns

Create, read, update or delete blogs

Table-Specific Information

Select

• Id supports the '=,IN' comparison.
• Handle supports the '=,IN' comparison.

SELECT * FROM Blogs WHERE Id = '123'
SELECT * FROM Blogs WHERE Id IN ('123', '345')
SELECT * FROM Blogs WHERE Handle = 'new-blog-5927';
SELECT * FROM Blogs WHERE Handle IN ('new-blog-5927', 'new-blog-5926');

Insert

You must specify the Title to create a Blog.

INSERT INTO Blogs (title) VALUES ('new blog')

INSERT INTO Blogs (Title, Metafields) VALUES ('new blog','[{"key":"sponsor","value":"shopify","type":"single_line_text_field","namespace":"global"}]')

Update

You must specify the id to update a Blog. For example:

UPDATE Blogs SET title = 'new blog' WHERE Id = '77171130'

UPDATE Blogs SET Metafields='[{"key":"sponsor","value":"shopify","type":"single_line_text_field","namespace":"global"}]' WHERE Id='77171130'

Delete

You must specify the Id of the Blog to delete it.

DELETE FROM Blogs WHERE Id = '555695'

Columns

Returns a list of resource feedback objects..

Table-Specific Information

Select

SELECT * FROM CarrierServices

SELECT * FROM CarrierServices WHERE Id = '123'

SELECT * FROM CarrierServices WHERE Id IN ('123', '235');

Insert

You must specify path and target to create a Redirect.

INSERT INTO [CarrierServices] (Name, Type, CallbackUrl) VALUES ('ApiServiceType', 'api', 'https://test.com');

Update

You must specify the id to update a Redirect. For example:

UPDATE [CarrierServices] SET [Active] = 'false' WHERE [Id] = 59103608855;

Delete

You must specify the Id of the Redirect to delete it.

DELETE FROM [CarrierServices] WHERE [Id] = 59103608855;

Columns

Query and delete information regarding different collects.

Table-Specific Information

Select

SELECT * FROM CollectionListings WHERE Id = '123'

Delete

You must specify the Id of the collection listing to delete it.

DELETE FROM CollectionListings WHERE id = '123'

Columns

Query, insert, or delete information regarding different collects.

Table-Specific Information

Select

SELECT * FROM Collects WHERE Id = '123'

Insert

You must specify the ProductId and CollectionId to create a collect.

INSERT INTO Collects (ProductId, CollectionId) VALUES ('512433520663', '22892937239')

Delete

You must specify the Id of the collect to delete it.

DELETE FROM Collects WHERE id = '123'

Columns

Create, read, update and delete the comments.

Table-Specific Information

Select

SELECT * FROM Comments WHERE Id = '123'

Insert

You must specify Article Id, Author, Body, Email to create a Comment.

INSERT INTO Comments (articleid, author, body, email) VALUES ('5557001', 'Harry', 'test', '[email protected]')

Update

You must specify the Id to update a Comment. For example:

UPDATE Comments SET Author = 'Adam' WHERE Id = '77171130'

Delete

You must specify the Id of the Comments to delete it.

DELETE FROM Comments WHERE Id = '555695'

Columns

Create, select, update, and delete information regarding countries.

Table-Specific Information

Select

SELECT * FROM Countries

SELECT * FROM Countries WHERE Id = '123'

Insert

You must specify the Code to create a country.

INSERT INTO Countries (Code) VALUES ("FR")

Update

You must specify the country Id to update a country. For example:

UPDATE Countries SET Tax = 0.1 WHERE Id = '123'

Delete

You must specify the Id of the country to delete it.

DELETE FROM Countries WHERE id = '123'

Columns

Query, insert, update, or delete information regarding different custom collections.

Table-Specific Information

Select

For example, the following queries are processed server-side.

SELECT * FROM CustomCollections WHERE Id = '123'

SELECT * FROM CustomCollections WHERE Id IN ('123', '456')

SELECT * FROM CustomCollections WHERE ProductId = '123'

SELECT * FROM CustomCollections WHERE Title = 'Ducks'

SELECT * FROM CustomCollections WHERE Handle = 'frontpage'

SELECT * FROM CustomCollections WHERE Handle IN ('frontpage', 'lastpage')

SELECT * FROM CustomCollections WHERE PublishedStatus = 'published'

SELECT * FROM CustomCollections WHERE UpdatedAt > '2018-05-02'

SELECT * FROM CustomCollections WHERE PublishedAt < '2017-08-15'

Insert

You must specify the Title to create a custom collection.

INSERT INTO CustomCollections (Title) VALUES ('Macbooks')

INSERT INTO CustomCollections (Title, Published) VALUES ('Macbooks', false)

Update

You must specify the custom collection Id to update a custom collection. For example:

UPDATE CustomCollections SET Title = 'Updated title' WHERE Id = '123'

Delete

You must specify the Id of the custom collection to delete it.

DELETE FROM CustomCollections WHERE Id = '123'

Columns

Create, update, delete, and query customer addresses.

Table-Specific Information

Select

• CustomerId supports the '=,IN' comparison.
• CustomerUpdatedAt supports the '<,>,<=,>=' comparison.

For example, the following queries are processed server-side:

SELECT * FROM CustomerAddresses WHERE CustomerId = '123'

SELECT * FROM CustomerAddresses WHERE CustomerId IN ('123', '456')

SELECT * FROM CustomerAddresses WHERE CustomerUpdatedAt >= '2018-05-04'

Insert

You must specify the customer Id to create an address.

INSERT INTO CustomerAddresses (CustomerId, Address1, City, Company) VALUES ('123', '1 Rue des Carrieres', 'Suite 1234', 'Montreal')

Update

You must specify the customer Id and the Id of the customer address to update an address.

UPDATE CustomerAddresses SET Zip = '90210' WHERE CustomerId = '123' AND Id = '456'

Delete

You must specify the customer Id and the Id of the customer address to delete an address. You cannot delete a customer's default address.

DELETE FROM CustomerAddresses WHERE CustomerId = '183467180055' AND Id = '292265787415'

Columns

Create, update, delete, and query customers.

Table-Specific Information

Select

• Id supports the '=,IN' comparison.
• FirstName supports the '=' comparison.
• LastName supports the '=' comparison.
• Email supports the '=' comparison.
• Phone supports the '=' comparison.
• TotalSpent supports the '=' comparison.
• OrdersCount supports the '=' comparison.
• AcceptsMarketing supports the '=' comparison.
• State supports the '=' comparison.
• VerifiedEmail supports the '=' comparison.
• CreatedAt supports the '>=,<=' comparison.
• UpdatedAt supports the '>=,<=,=' comparison.

SELECT * FROM Customers WHERE Id = '123'

SELECT * FROM Customers WHERE Id IN ('123', '456')

SELECT * FROM Customers WHERE CreatedAt >= '2017-10-25'

SELECT * FROM Customers WHERE CreatedAt <= '2017-10-25'

SELECT * FROM Customers WHERE UpdatedAt <= '2017-10-25'

SELECT * FROM Customers WHERE UpdatedAt >= '2017-10-25'

SELECT * FROM Customers WHERE FirstName = 'abc'

SELECT * FROM Customers WHERE LastName = 'xyz'

SELECT * FROM Customers WHERE Email = '[email protected]'

SELECT * FROM Customers WHERE OrdersCount = 5

SELECT * FROM Customers WHERE Phone = '999999999'

SELECT * FROM Customers WHERE VerifiedEmail = true

SELECT * FROM Customers WHERE UpdatedAt = '2017-10-25'

Insert

The Email field is required to insert.

INSERT INTO Customers (Email, Phone) VALUES ('[email protected]', '+15142546011')

INSERT INTO Customers (Email, Phone, SendEmailInvite) VALUES ('[email protected]', '+15142546011', true)

Update

You must specify the Id of the customer to update a customer.

UPDATE Customers SET Note = 'Customer is a great guy' WHERE Id = '123'

UPDATE Customers SET Tags = 'New Customer, Repeat Customer' WHERE Id = '123'

Delete

You must specify the Id of the customer to delete a customer.

DELETE FROM Customers WHERE Id = '123'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements, to offer a more granular control over the tuples that are returned from the data source, or as parameters in INSERT statements.

Create, select, update, and delete information regarding discount codes.

Table-Specific Information

Select

For example, the following queries are processed server-side:

SELECT * FROM DiscountCodes WHERE PriceRuleId = '123'

SELECT * FROM DiscountCodes WHERE PriceRuleId = '123' AND Id = '456'

Insert

You must specify the PriceRuleId and Code to create a discount code.

INSERT INTO DiscountCodes (PriceRuleId, Code) VALUES ('290807676951', 'SUMMERSALE100OFF')

Update

You must specify the PriceRuleId and Id to update a discount code. For example:

UPDATE DiscountCodes SET Code = 'WINTERSALE500OFF' WHERE PriceRuleId = '123' AND Id = '456'

Delete

You must specify the Id of the custom collection to delete it.

DELETE FROM DiscountCodes WHERE PriceRuleId = '123' AND Id = '456'

Columns

Create, update, delete, and query draft orders.

Table-Specific Information

Select

The Sync App uses the Shopify API to process search criteria that refer to the Id, Status, and UpdatedAt columns. The supported SQL operators are "=" and "IN" for Id, "=" for Status, and ">" and "<" for UpdatedAt. The Sync App processes other filters client-side within the Sync App.

For example, the following queries are processed server-side:

SELECT * FROM DraftOrders WHERE Id = '123'

SELECT * FROM DraftOrders WHERE Id IN ('123', '456')

SELECT * FROM DraftOrders WHERE Status = 'completed'

SELECT * FROM DraftOrders WHERE UpdatedAt > '2018-02-05'

Insert

• Create a simple draft order with only a product variant Id using aggregates.
  

  INSERT INTO DraftOrders (LineAggregate) VALUES ('[{\"variant_id\":\"4236041945111\",\"quantity\":2},{\"variant_id\":\"4236069011479\",\"quantity\":3}]')

• Create a simple draft order with only a product variant Id using temporary table. The temporary table you are populating is dynamic and will be created at run time the first time you insert to it. Temporary tables are denoted by a # appearing in their name. When using a temporary table to insert, the temporary table must be named in the format [TableName]#TEMP, where TableName is the name of the table you will be inserting to. For instance:
  

  INSERT INTO OrdersItems#TEMP (ItemVariantId, ItemQuantity) VALUES ('123', 2)

  Once your temporary table is populated, it is now time to insert to the actual table in Shopify. You can do this by performing an INSERT to the actual table and setting as a value for LinesAggregate the name of the temporary table. For instance:
  

  INSERT INTO DraftOrders (LineAggregate) VALUES ('OrdersItems#TEMP')

• Create a custom draft order using aggregates.
  

  INSERT INTO DraftOrders (LineAggregate, CustomerId, UseCustomerDefaultAddress) VALUES ('[{\"title\":\"Custom Tee\",\"price\":20.15,\"quantity\":5}]', '709015339031', true)

• Create a custom draft order using temporary table.
  

  INSERT INTO OrdersItems#TEMP (ItemTitle, ItemPrice, ItemQuantity) VALUES ('Custom Tee', 20.15, 5)
  INSERT INTO DraftOrders (LineAggregate, CustomerId, UseCustomerDefaultAddress) VALUES ('OrdersItems#TEMP', '709015339031', true)

Update

• Add a Note to a draft order:
  

  UPDATE DraftOrders SET Note = 'Customer contacted us about a custom engraving on this iPod' WHERE Id = '123'

• Set a discount on a draft order:
  

  UPDATE DraftOrders SET AppliedDiscountDescription = 'Custom discount', AppliedDiscountValueType = 'percentage', AppliedDiscountValue = 10.0, AppliedDiscountAmount = 19.90, AppliedDiscountTitle = 'Custom' WHERE Id = '123'

Delete

You must specify the Id of the draft order to delete it.

DELETE FROM DraftOrders WHERE Id = '123'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements, to offer a more granular control over the tuples that are returned from the data source, or as parameters in INSERT statements.

Create, delete, and query information regarding fulfillment events.

Table-Specific Information

Select

For example, the following queries are processed server-side:

SELECT * FROM FulfillmentEvents WHERE OrderId = '123' AND FulfillmentId = '456'

SELECT * FROM FulfillmentEvents WHERE OrderId = '123' AND FulfillmentId = '456' AND Id = '789'

Insert

You must specify the OrderId, FulfillmentId, and Status to create a fulfillment event.

INSERT INTO FulfillmentEvents (Orderid, FulfillmentId, Status, Message, EstimatedDeliveryAt) VALUES ('202520330263', '206541914135', 'in_transit', 'test', '2018-08-02 10:15:25 PM')

Delete

You must specify the OrderId, FulfillmentId, and Id of the fulfillment event to delete it.

DELETE FROM FulfillmentEvents WHERE Orderid = '202520330263' AND FulfillmentId = '206541914135' AND Id = '2374424264727'

Columns

Create, update, and query fulfillments.

Table-Specific Information

Select

For example, the following queries are processed server-side.

• If you specify the unique identifier of the order, then this view will only list fulfillment information concerning that order. You can also retrieve a specific fulfillment by specifying OrderId and Id. To retrieve fulfillments associated with a fulfillment order you need to specify the FulfillmentOrderId.
  

  SELECT * FROM Fulfillments WHERE OrderId = '5729988903171'
  
  SELECT * FROM Fulfillments WHERE OrderId = '5729988903171' AND Id = '5165361791235'
  
  SELECT * FROM Fulfillments WHERE FulfillmentOrderId = '6817622622467'

• This view supports filtering by the CreatedAt and UpdatedAt columns on the server-side.
  

  SELECT * FROM Fulfillments WHERE CreatedAt > '2017-10-25'

Insert

You must specify the FulfillmentOrderId column to insert a fulfillment.

• Fulfill all line items.
  

  INSERT INTO Fulfillments (FulfillmentOrderId) VALUES ('6817622622467')

• Fulfill one line item using aggregates.

  You must specify the fulfillment order line item id and the quantity to be fulfilled in the aggregate.

  Note: To obtain the fulfillment order line item id, you can query the Id column in the FulfillmentOrderLineItems table.
  

  INSERT INTO Fulfillments (FulfillmentOrderId, LineAggregate) VALUES ('6817622622467','[{\"id\":14179445244163,\"quantity\":5}]')

• Fulfill one line item using temporary table. The temporary table you are populating is dynamic and will be created at run time the first time you insert to it. Temporary tables are denoted by a # appearing in their name. When using a temporary table to insert, the temporary table must be named in the format [TableName]#TEMP, where TableName is the name of the table you will be inserting to. For instance:
  

  INSERT INTO OrdersItems#Temp (ItemId, ItemQuantity) Values ('14179445244163', '5')

  Once your temporary table is populated, it is now time to insert to the actual table in Shopify. You can do this by performing an INSERT to the actual table and setting as a value for LineAggregate the name of the temporary table. For instance:
  

  Insert INTO Fulfillments (FulfillmentOrderId, LineAggregate) VALUES ('6817622622467', 'OrdersItems#TEMP')

  In cases that two ore more shop locations are specified in your Shopify account the LocationId needs to be added. For instance:
  

  Insert INTO Fulfillments (FulfillmentOrderId, LocationId, LineAggregate) VALUES ('6817622622467', '1448280087', 'OrdersItems#TEMP')

• Fulfill many line items using aggregates.
  

  INSERT INTO Fulfillments (FulfillmentOrderId, LineAggregate) VALUES ('6817622622467', '[{\"id\":14179445244163,\"quantity\":5},{\"id\":14179445276931,\"quantity\":2}]')

• Fulfill many line items using temporary table.
  

  INSERT INTO OrdersItems#Temp (ItemId, ItemQuantity) Values ('14179445244163', '5')
  INSERT INTO OrdersItems#Temp (ItemId, ItemQuantity) Values ('14179445276931', '2')
  INSERT INTO Fulfillments (FulfillmentOrderId, LineAggregate) VALUES ('6817622622467', 'OrdersItems#TEMP')

• Fulfill all line items, notify the customer, and set a tracking number.
  

  INSERT INTO Fulfillments (FulfillmentOrderId, TrackingNumbers, NotifyCustomer) VALUES ('6817622622467', 'FEDEX1', true)

• Complete a fulfillment (you must specify the Id of the fulfillment as well).
  

  INSERT INTO Fulfillments (FulfillmentOrderId, Id, Status) VALUES ('6817622622467', '5165361791235', 'Complete')

• Transition a fulfillment from pending to open (you must specify the Id of the fulfillment as well).
  

  INSERT INTO Fulfillments (FulfillmentOrderId, Id, Status) VALUES ('6817622622467', '5165361791235', 'Open')

• Cancel a fulfillment (you must specify Id of the fulfillment as well).
  

  INSERT INTO Fulfillments (FulfillmentOrderId, Id, Status) VALUES ('6817622622467', '5165361791235', 'Cancel')

Update

You must specify the OrderId and Id of the fulfillment to fulfill an order.

UPDATE Fulfillments SET TrackingNumbers = 'FedEx123,UPS123' WHERE OrderId = '5729988903171' AND Id = '5165361791235'

Columns

Query, create, update, and delete information regarding different fulfillment services.

Table-Specific Information

Select

For example, the following queries are processed server-side:

SELECT * FROM FulfillmentServices WHERE Id = '123'

Insert

You must specify the Name, CallbackUrl, and Format to create a fulfillment service.

INSERT INTO FulfillmentServices (Name, CallbackUrl, Format) VALUES ('testing fulfillment services', 'http://google.com', 'json')

Update

You must specify the Id to update a fulfillment service. For example:

UPDATE FulfillmentServices SET Name = 'test32', IncludePendingStock = true WHERE Id = '123'

Delete

You must specify the Id of the fulfillment service to delete it.

DELETE FROM FulfillmentServices WHERE Id = '123'

Columns

Create, update, delete, and query gift cards.

Table-Specific Information

Select

The Sync App uses the Shopify API to process WHERE clause conditions built with the following column and operator. The Sync App processes other filters client-side within the Sync App.

• Id supports the '=,IN' comparison operators.

For example, the following queries are processed server side:

  SELECT * FROM GiftCards
  SELECT * FROM GiftCards WHERE Id = 123

Columns

Query and update information regarding different inventory items.

Table-Specific Information

Select

For example, the following queries are processed server-side:

SELECT * FROM InventoryItems WHERE Id = '123'

SELECT * FROM InventoryItems WHERE Id IN ('123', '456')

Update

You must specify the Id to update an inventory item. For example:

UPDATE InventoryItems SET Sku = 'lamo', Tracked = false WHERE Id = '123'

UPDATE InventoryItemsSET Cost = 45.95, CountryCodeOfOrigin = 'AU', CountryHarmonizedSystemCodes ='[{"harmonized_system_code":"701912","country_code":"CA"},{"harmonized_system_code":"701912","country_code":"US"}]', HarmonizedSystemCode = '701912', ProvinceCodeOfOrigin = 'NC', SKU = 'Test1', Tracked = FALSE WHERE Id = '123'

Columns

Query, create, and update information regarding different inventory levels.

Table-Specific Information

Select

To query this table InventoryItemId, LocationId or both should be specified. The supported SQL operators are '=' and 'IN'. The Sync App processes other filters client-side within the Sync App.

For example, the following queries are processed server-side:

SELECT * FROM InventoryLevels WHERE InventoryItemId = '123' 

SELECT * FROM InventoryLevels WHERE InventoryItemId = '123' AND LocationId = '456'

SELECT * FROM InventoryLevels WHERE InventoryItemId IN ('123', '456') AND LocationId IN ('123', '456')

Insert

• Adjust the available quantity of an inventory item at a single location:
  

  INSERT INTO InventoryLevels (InventoryItemId, LocationId, Available, Operation) VALUES (534169354263, 1448280087, 5, 'Adjust')

• Connects an inventory item to a location by creating an inventory level at that location:
  

  INSERT INTO InventoryLevels (InventoryItemId, LocationId, Operation) VALUES (534169354263, 4484497431, 'Connect')

• Sets the inventory level for an inventory item at a location:
  

  INSERT INTO InventoryLevels (InventoryItemId, LocationId, Available, Operation, DisconnectIfNecessary) VALUES (534169354263, 4484497431, 42, 'Set', true)

Delete

You must specify the InventoryItemId and LocationId to delete an inventory level of an inventory item at a location.

DELETE FROM InventoryLevels WHERE InventoryItemId = '153681943' AND LocationId = '45678'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements, to offer a more granular control over the tuples that are returned from the data source, or as parameters in INSERT statements.

Create, update, delete, and query marketing events.

Table-Specific Information

Select

SELECT * FROM MarketingEvents

SELECT * FROM MarketingEvents WHERE Id = '123'

Insert

You must specify the MarketingChannel, EventType, Paid, ReferringDomain, and StartedAt columns to create a marketing event. For example:

INSERT INTO MarketingEvents (MarketingChannel, EventType, Paid, ReferringDomain, StartedAt) VALUES ('social', 'ad', true, 'facebook.com', '2018-12-15')

Update

You must specify the Id to update a marketing event. You can modify only timestamps, RemoteId, and budget/currency. For example:

UPDATE MarketingEvents SET RemoteId = '1000:2000', StartedAt = '2018-02-02T00:00 +00:00', EndedAt = '2018-02-03T00:00 +00:00', ScheduledToEndAt = '2018-02-04T00:00 +00:00', Budget = 11.1, BudgetType = 'daily', Currency = 'USD' WHERE Id = '123'

Delete

You must specify the Id of the marketing event to delete it.

DELETE FROM MarketingEvents WHERE Id = '123'

Columns

Retrieves a list of metafields that belong to a resource.

View-Specific Information

OwnerResource or the metafield Id is required for any query against Metafields.

OwnerResource must be one of the following values:

• shop
• draft_order
• product
• variant
• page
• article
• order
• customer
• collection
• blog
• product_image

Any other value will run the query against "shop" instead.

Select

For example, the following queries are processed server-side:

SELECT * FROM Metafields WHERE Id = 721389482

SELECT * FROM Metafields WHERE ID IN (SELECT Id FROM Customers)

SELECT * FROM Metafields WHERE ID IN (19422939054103, 19422939185175, 19422939250711, 19431415152663, 19431415185431)

SELECT * FROM Metafields WHERE OwnerId = 64146833431 AND OwnerResource = 'product'

SELECT * FROM Metafields WHERE OwnerId = 64146833431 AND OwnerResource IN ('product', 'customer')

SELECT * FROM Metafields WHERE OwnerId IN (64146833430, 64146833431) AND OwnerResource = 'product'

SELECT * FROM Metafields WHERE OwnerResource = 'customer' AND OwnerId IN (SELECT Id FROM Customers);

SELECT * FROM Metafields WHERE OwnerResource = 'product' AND OwnerId = '64146735127' AND UpdatedAt >= '2021-10-13T05:55:08-04:00';

SELECT * FROM Metafields WHERE OwnerResource = 'product' AND OwnerId = '64146735127' AND Type = 'multi_line_text_field';

Insert

You must specify the NameSpace, Key, and Value to create Metafields.

INSERT INTO Metafields (NameSpace, Key, Value, Type, Description, OwnerResource) VALUES ('inventory', 'warehouse', 25, 'number_integer', 'This is description about data', 'factory')

Update

You must specify the Id to update Metafields. For example:

UPDATE Metafields SET Value = 26 WHERE Id = 19712314851607

Delete

You must specify the Id of the Metafields to delete it.

DELETE FROM Metafields WHERE Id = 19712314851607

Columns

Create, update, delete, and query order risks.

Table-Specific Information

Select

The Sync App uses the Shopify API to process WHERE clause conditions built with the following column and operator. The Sync App processes other filters client-side within the Sync App.

• Id supports the '=,IN' comparison operators.
• OrderId supports the '=,IN' comparison operators.

For example, the following queries are processed server side:

  SELECT * FROM OrderRisks
  SELECT * FROM OrderRisks WHERE Id = 123
  SELECT * FROM OrderRisks WHERE OrderId = 123

Columns

Create, update, delete, and query orders.

Table-Specific Information

Select

For example, the following queries are processed server-side:

SELECT * FROM Orders WHERE Id = '123'

SELECT * FROM Orders WHERE FinancialStatus = 'pending'

SELECT * FROM Orders WHERE CreatedAt > '2017-10-25'

Insert

• Create a simple order with only a product variant Id using aggregates.
  

  INSERT INTO Orders (LineAggregate) VALUES ('[{\"variant_id\":\"4236041945111\",\"quantity\":2}]')

• Create a simple order with only a product variant Id using temporary table. The temporary table you are populating is dynamic and will be created at run time the first time you insert to it. Temporary tables are denoted by a # appearing in their name. When using a temporary table to insert, the temporary table must be named in the format [TableName]#TEMP, where TableName is the name of the table you will be inserting to. For instance:
  

  INSERT INTO OrdersItems#TEMP (ItemVariantId, ItemQuantity) VALUES ('123', 2)

  Once your temporary table is populated, it is now time to insert to the actual table in Shopify. You can do this by performing an INSERT to the actual table and setting as a value for LinesAggregate the name of the temporary table. For instance:
  

  INSERT INTO Orders (LineAggregate) VALUES ('OrdersItems#TEMP')

• Create a simple order, sending the order receipt and fulfillment receipt to the customer using aggregates.
  

  INSERT INTO Orders (Email, FulfillmentStatus, SendReceipt, SendFulfillmentReceipt, LineAggregate) VALUES ('[email protected]', 'fulfilled', true, true, '[{\"variant_id\":\"4236041945111\",\"quantity\":2},{\"variant_id\":\"4236069011479\",\"quantity\":3}]')

• Create a simple order, sending the order receipt and fulfillment receipt to the customer using temporary table.
  

  INSERT INTO OrdersItems#TEMP (ItemVariantId, ItemQuantity) VALUES ('4236041945111', 2)
  INSERT INTO OrdersItems#TEMP (ItemVariantId, ItemQuantity) VALUES ('4236069011479', 3)
  INSERT INTO Orders (Email, FulfillmentStatus, SendReceipt, SendFulfillmentReceipt, LineAggregate) VALUES ('[email protected]', 'fulfilled', true, true, 'OrdersItems#TEMP')

• Create a simple order, with taxes using aggregates.
  

  INSERT INTO Orders (LineAggregate, TaxAggregate) VALUES ('[{\"variant_id\":\"4236041945111\",\"quantity\":2},{\"variant_id\":\"4236069011479\",\"quantity\":3}]', '[{\"price\":10.2,\"rate\":0.06,\"title\":\"State Tax\"}]')

• Create a simple order, with taxes using temporary table.
  

  INSERT INTO OrdersItems#TEMP (ItemVariantId, ItemQuantity) VALUES ('4236041945111', 2)
  INSERT INTO OrdersItems#TEMP (ItemVariantId, ItemQuantity) VALUES ('4236069011479', 3)
  INSERT INTO TaxItems#TEMP (TaxItemPrice, TaxItemRate, TaxItemTitle) VALUES (10.2, 0.06, 'State Tax')
  INSERT INTO Orders (LineAggregate, TaxAggregate) VALUES ('OrdersItems#TEMP', 'TaxItems#TEMP')

• Create a simple order, with multiple taxes using aggregates.
  

  INSERT INTO Orders (LineAggregate, TaxAggregate) VALUES ('[{\"variant_id\":\"4236041945111\",\"quantity\":2},{\"variant_id\":\"4236069011479\",\"quantity\":3}]', '[{\"price\":10.2,\"rate\":0.06,\"title\":\"State Tax\"},{\"price\":4.25,\"rate\":0.025,\"title\":\"County Tax\"}]')

• Create a simple order, with multiple taxes using temporary table.
  

  INSERT INTO OrdersItems#TEMP (ItemVariantId, ItemQuantity) VALUES ('4236041945111', 2)
  INSERT INTO OrdersItems#TEMP (ItemVariantId, ItemQuantity) VALUES ('4236069011479', 3)
  INSERT INTO TaxItems#TEMP (TaxItemPrice, TaxItemRate, TaxItemTitle) VALUES (10.2, 0.06, 'State Tax')
  INSERT INTO TaxItems#TEMP (TaxItemPrice, TaxItemRate, TaxItemTitle) VALUES (4.25, 0.025, 'County Tax')
  INSERT INTO Orders (LineAggregate, TaxAggregate) VALUES ('OrdersItems#TEMP', 'TaxItems#TEMP')

• Create a more comprehensive order using aggregates.
  

  INSERT INTO Orders (LineAggregate) VALUES ([{\"title\":\"Big Brown Bear Boots\",\"price\":74.99,\"grams\":1300,\"quantity\":3}])

• Create a more comprehensive order using temporary table.
  

  INSERT INTO OrdersItems#TEMP (ItemTitle, ItemPrice, ItemGrams, ItemQuantity) VALUES ('Big Brown Bear Boots', 74.99, 1300, 3)
  INSERT INTO Orders (LineAggregate) VALUES ('OrdersItems#TEMP')

• Create a pending order with an existing customer using aggregates.
  

  INSERT INTO Orders (LineAggregate, CustomerId, FinancialStatus) VALUES ('[{\"variant_id\":\"123\",\"quantity\":3}]', '456', 'pending')

• Create a pending order with an existing customer using temporary table.
  

  INSERT INTO OrdersItems#TEMP (ItemVariantId, ItemQuantity) VALUES ('123', 3)
  INSERT INTO Orders (LineAggregate, CustomerId, FinancialStatus) VALUES ('OrdersItems#TEMP', '456', 'pending')

• Create a partially paid order with a new customer and addresses using aggregates.
  

  INSERT INTO Orders (LineAggregate, CustomerFirstName, CustomerLastName, CustomerEmail, FinancialStatus) VALUES ('[{\"variant_id\":\"123\",\"quantity\":3}]', 'Paul', 'Norman', '[email protected]', 'partially_paid')

• Create a partially paid order with a new customer and addresses using temporary.
  

  INSERT INTO OrdersItems#TEMP (ItemVariantId, ItemQuantity) VALUES ('123', 3)
  INSERT INTO Orders (LineAggregate, CustomerFirstName, CustomerLastName, CustomerEmail, FinancialStatus) VALUES ('OrdersItems#TEMP', 'Paul', 'Norman', '[email protected]', 'partially_paid')

• Close an order (you must specify the Id of the order).
  

    INSERT INTO Orders (Id, Operation) VALUES ('123', 'Close')
    

• Reopen an order (you must specify the Id of the order).
  

    INSERT INTO Orders (Id, Operation) VALUES ('123', 'Open')
    

• Cancel an order (you must specify the Id of the order).
  

    INSERT INTO Orders (Id, Operation) VALUES ('123', 'Cancel')
    

Update

Only columns BuyerAcceptsMarketing, Email, Phone, Note, Tags, ShippingAddressFirstName, ShippingAddressLastName, ShippingAddressAddress1, ShippingAddressAddress2, ShippingAddressPhone, ShippingAddressCity, ShippingAddressCompany, ShippingAddressZip, ShippingAddressProvince, ShippingAddressCountry, ShippingAddressLatitude, ShippingAddressLongitude, ShippingAddressName, ShippingAddressCountryCode, ShippingAddressProvinceCode and ShippingAddressDefault can be updated.

• Update the shipping address of an existing order.
  

  UPDATE Orders SET ShippingAddressAddress1 = '123 Ship Street', ShippingAddressCity = 'Shipsville' WHERE Id = '123'

• Update an order's tags.
  

  UPDATE Orders SET Tags = 'External, Inbound, Outbound' WHERE Id = '123'

Delete

You must specify the Id of the order when deleting an item from this table.

DELETE FROM Orders WHERE Id = '123'

Columns