This page provides a guide to Establishing a Connection to Shopify in CData Cloud, as well as information on the available resources, and a reference to the available connection properties.

Connecting to Shopify

Establishing a Connection shows how to authenticate to Shopify and configure any necessary connection properties to create a database in CData Cloud

Accessing Data from CData Cloud Services

Accessing data from Shopify through the available standard services and CData Cloud administration is documented in further details in the CData Cloud Documentation.

Connect to Shopify by selecting the corresponding icon in the Database tab. Required properties are listed under Settings. The Advanced tab lists connection properties that are not typically required.

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, under Configuration, click Admin API integration and select the access permissions for your store that you want to grant to your application. The Admin API permissions required by our provider are:
   • read_assigned_fulfillment_orders, write_assigned_fulfillment_orders
   • read_content, write_content
   • read_customer_merge
   • read_customers, write_customers
   • read_discounts, write_discounts
   • read_draft_orders, write_draft_orders
   • read_files, write_files
   • read_fulfillments, write_fulfillments
   • read_gift_card_transactions, write_gift_card_transactions
   • read_gift_cards, write_gift_cards
   • read_inventory, write_inventory
   • read_locations, write_locations
   • read_marketing_events, write_marketing_events
   • read_markets
   • read_metaobject_definitions
   • read_metaobjects
   • read_online_store_navigation, write_online_store_navigation
   • read_orders, write_orders
   • read_payment_terms
   • read_price_rules, write_price_rules
   • read_product_listings, write_product_listings
   • read_products, write_products
   • read_publications
   • read_reports, write_reports
   • read_resource_feedbacks, write_resource_feedbacks
   • read_returns, write_returns
   • read_script_tags, write_script_tags
   • read_shipping, write_shipping
   • read_shopify_payments_accounts
   • read_shopify_payments_bank_accounts
   • read_shopify_payments_payouts
   • read_store_credit_account_transactions, write_store_credit_account_transactions
   • read_store_credit_accounts
   • read_themes, write_themes
4. Click Save.
5. In the Overview tab, under Configuration, click Storefront API integration and select the access permissions for your store that you want to grant to your application. The Storefront API permission required by our provider is:
   • unauthenticated_read_content
6. Click Save.
7. Select API Credentials.
8. Under "Access tokens" click Install app. This creates your access token.
9. 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

GraphQL API Rate Limiting

Point-Based Rate Limits

GraphQL was created to overcome shortcomings that REST APIs were not designed to address. Working with GraphQL has a number of advantages, one of which being the reduced number of HTTP calls required to obtain all of the data available through REST API.

However, calls to the GraphQL Admin API are limited based on estimated query costs, which means that the cost of queries over time should be considered rather than the amount of requests.

The points function in a very straightforward manner. A bucket of 1000 cost points is given to each app and store combination, with a leak rate of 50 cost points per second. This means that at any given time, the total cost of your queries cannot exceed 1,000 points, and that space in the app's bucket is created at a rate of 50 points per second. Plainly put, every second, you're given 50 points in a typical plan. Any mutation that requires you to edit, create, or delete data costs ten points. The cost of obtaining an object, on the other hand, is merely one point. Let's imagine you needed an order, but you also wanted each and every line item from that order. That isn't going to be a one-point penalty. If your order contains 10 line items, each of those line items will cost one point, and the order itself will cost one point, totaling 11 points.

The requested and actual query costs are combined to set the limit. Before the query can be executed, the app's bucket must have enough space to accommodate the desired cost. When the query is finished, the bucket is repaid the difference between the requested and actual query costs.

The cost of the request and the state of the throttle are included in the response. The following information is returned under the extensions key:

"extensions": {
    "cost": {
      "requestedQueryCost": 101,
      "actualQueryCost": 46,
      "throttleStatus": {
        "maximumAvailable": 1000,
        "currentlyAvailable": 954,
        "restoreRate": 50
      }
    }
  }
  

Taking these into consideration, our driver must calculate the appropriate quantity of data that can be retrieved each request to avoid throttling. The pagesize of a table is calculated automatically based on the number of fields and data in the table to conform to GraphQL API restrictions. Due to a low computed pagesize, performance for large tables in GraphQL may be lacking. An alternative is to increase the MaxPointsPerCall, which forces an increase in the pageisze, but we do not encourage this as it will most certainly result in throttling.

GraphQL Bulk API

Bulk operations, rather than single queries, are recommended for data replication tasks.

Bulk operations are intended to handle massive amounts of data and do not have the same cost or rate constraints as single queries. You'll have to paginate your data sets if you don't utilize the bulk query. For REST, for example, a response can only include 250 elements. GraphQL is cost-based, but you're still limited to a specific number per request.

With a Bulk Operation API, this is not the case. Therefore, when using the GraphQL schema, we suggest setting UseBulkAPI to TRUE in the connection string to retrieve massive amounts of data without concerns about pagination or throttling.

Customizing the SSL Configuration

By default, the Cloud 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 Cloud 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 Cloud models entities in the Shopify API as an easy-to-use SQL database, using tables, views, and stored procedures. These are defined in schema files, which are simple, easy-to-read text files that define the structure and organization of data.

The driver exposes two schemas, which you select in Schema:

• See REST Data Model for the available entities in the REST API Version 2025-04.
  • Note: As of October 1, 2024, Shopify has designated the REST API as legacy.
• See GRAPHQL Data Model for the available entities in the GraphQL API Version 2025-04.

Note: Shopify typically releases new API versions quarterly. If a stable API version is no longer supported, Shopify automatically defaults to the oldest supported stable version to respond to your API request. For more information on Shopify's API versioning, click this link.

Flexible Schema

The CData Cloud is a semi-dynamic driver. This means that it exposes a static schema but can also retrieve dynamic columns from custom fields (for example, in the Products and ProductVariants tables). To enable this feature for either API schema (REST or GraphQL), follow the instructions in IncludeCustomFields. Note that the search for custom fields is limited to 250 rows.

The CData Cloud models entities in the Shopify API as an easy-to-use SQL database, using tables, views, and stored procedures. These are defined in schema files, which are simple, easy-to-read text files that define the structure and organization of data.

Note: Shopify typically releases new API versions quarterly. If a stable API version is no longer supported, Shopify automatically defaults to the oldest supported stable version to respond to your API request. For more information on Shopify's API versioning, click this link.

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 Cloud models the Shopify API as relational tables, views, and stored procedures.

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

Note: As of October 1, 2024, Shopify has designated the REST API as legacy.

Tables

The Tables section, which details standard SQL tables, and the Views section, which lists read-only SQL tables, contain samples of what you might have access to in your Shopify account.

Common tables include:

Stored Procedures

Stored Procedures are actions that are invoked via SQL queries. They perform tasks beyond standard CRUD operations, including managing order cancellations and fulfillments, sending invoices, and handling authentication flows.

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

CData Cloud - Shopify 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-columns are fields that can only be used in the types of statements under which they are explicitly listed. They are not standard columns but instead provide additional functionality for specific operations.

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 Cloud 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 Cloud processes other filters client-side within the Cloud.

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-columns are fields that can only be used in the types of statements under which they are explicitly listed. They are not standard columns but instead provide additional functionality for specific operations.

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 Cloud uses the Shopify API to process WHERE clause conditions built with the following column and operator. The Cloud processes other filters client-side within the Cloud.

• 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

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
• page
• article
• order
• customer
• collection
• blog

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 = 'order'

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

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

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

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

SELECT * FROM Metafields WHERE OwnerResource = 'order' 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 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