Create a connection to Shopify by navigating to the Connections page in the Sync App application and selecting the corresponding icon in the Add Connections panel. If the Shopify icon is not available, click the Add More icon to download and install the Shopify connector from the CData site.

Required properties are listed under the Settings tab. The Advanced tab lists connection properties that are not typically required.

Connecting to Shopify

In all cases, you must set ShopUrl to the full URL of your Shopify shop (https://yourshopname.myshopify.com). The rest of this document assumes that you have done so.

Access Token

Create a Custom App

Before authenticating, you need to create a custom application via your Shopify admin page. Follow the steps below to register an application and obtain 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
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.

Access Token

To authenticate using an access token, specify the following:

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

OAuth

To connect using OAuth, you must create a public or custom application in the Shopify Partner Dashboard. During this process, API key and API secret key values are generated. You need these keys when you connect using a custom application. See Creating a Custom OAuth App for the procedure.

Note: You must set AuthScheme to OAuth. All the sections below assume that you have done so. Otherwise, you cannot connect.

Desktop Authentication

You must create a custom application and specify Client Id and Client Secret properties.

After setting the following, you are ready to connect:

• InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken connection property.
• OAuthClientId: Set this to the app's API key value.
• OAuthClientSecret: Set this to the app's API secret key value.
• CallbackURL: Set this to the redirect URI defined when you registered your app.

When you connect, the Sync App opens the OAuth endpoint in your default browser. Log in and grant permissions to the application. The Sync App then completes the OAuth process by saving OAuth values in OAuthSettingsLocation that persists across connections and obtaining a new access token when the old one expires.

Web Authentication with a Custom OAuth App

When connecting via a Web application, you need to register a custom OAuth app with Shopify. You can then use the Sync App to get and manage the OAuth token values.

Get an OAuth Access Token

Set the following connection properties to obtain the OAuthAccessToken:

• OAuthClientId: Set this to the app's API key value.
• OAuthClientSecret: Set this to the app's API secret key value.

You can then call stored procedures to complete the OAuth exchange:

1. Call the GetOAuthAuthorizationURL stored procedure. Set the AuthMode input to WEB and set the CallbackURL input to the Redirect URI you specified in your app settings. The stored procedure returns the URL to the OAuth endpoint.
2. Log in and authorize the application. You are redirected back to the callback URL.
3. Call the GetOAuthAccessToken stored procedure. Set the AuthMode input to WEB. Set the Verifier input to the "code" parameter in the query string of the callback URL.

Headless Machines

To create Shopify data sources on headless servers or other machines on which the Sync App cannot open a browser, you need to authenticate from another machine. Authentication is a two-step process.

1. Instead of installing the Sync App on another machine, you can follow the steps below to obtain the OAuthVerifier value. Or, you can install the Sync App on another machine and transfer the OAuth authentication values, after you authenticate through the usual browser-based flow.
2. You can then configure the Sync App to automatically refresh the access token from the headless machine.

You can follow the headless OAuth authentication flow using the OAuth credentials for your custom OAuth app.

Create a Custom OAuth App

See Creating a Custom OAuth App for the procedure. You can then follow the steps below to authenticate and connect to data.

Obtain a Verifier Code

Set the following properties on the headless machine:

• InitiateOAuth: Set this to OFF.
• OAuthClientId: Set this to the API key.
• OAuthClientSecret: Set this to the API secret key.

You can then follow the steps below to authenticate from another machine and obtain the OAuthVerifier connection property.

1. Call the GetOAuthAuthorizationURL stored procedure with the CallbackURL input parameter set to the exact Redirect URI you specified in your app settings.
2. Open the returned URL in a browser. Log in and grant permissions to the Sync App. You are then redirected to the callback URL, which contains the verifier code.
3. Save the value of the verifier code. You will set this in the OAuthVerifier connection property.

On the headless machine, set the following connection properties to obtain the OAuth authentication values:

• OAuthClientId: Set this to the API key.
• OAuthClientSecret: Set this to the API secret key.
• OAuthVerifier: Set this to the verifier code.
• OAuthSettingsLocation: Set this to persist the encrypted OAuth authentication values to the specified file.
• InitiateOAuth: Set this to REFRESH.

After the OAuth settings file is generated, set the following properties to connect to data:

• OAuthClientId: Set this to the API key.
• OAuthClientSecret: Set this to the API secret key.
• OAuthSettingsLocation: Set this to the file containing the encrypted OAuth authentication values. Make sure this file gives read and write permissions to the Sync App to enable the automatic refreshing of the access token.
• InitiateOAuth: Set this to REFRESH.

Transfer OAuth Settings

Follow the steps below to install the Sync App on another machine, authenticate, and then transfer the resulting OAuth values.

On a second machine, install the Sync App and connect with the following properties set:

• OAuthSettingsLocation: Set this to the path of a writable text file.
• OAuthClientId:Set this to the app's API key value.
• OAuthClientSecret: Set this to the app's API secret key value.
• CallbackURL: Set this to the Redirect URI you specified in your app settings.

Test the connection to authenticate. The resulting authentication values are encryped and writtento the path specified by OAuthSettingsLocation. After you have successfully tested the connection, copy the OAuth settings file to your headless machine. On the headless machine, set the following connection properties to connect to data:

• InitiateOAuth: Set this to REFRESH.
• OAuthClientId: Set this to the app's API key value.
• OAuthClientSecret: Set this to the app's API secret key value.
• OAuthSettingsLocation: Set this to the path to your OAuth settings file. Make sure this file gives read and write permissions to the Sync App to enable the automatic refreshing of the access token.

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

User Defined Views

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

SSL Configuration

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

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

The Sync App offloads as much of the SELECT statement processing as possible to Shopify and then processes the rest of the query in memory (client-side).

See Query Processing for more information.

Logging

See Logging for an overview of configuration settings that can be used to refine CData logging. For basic logging, you only need to set two connection properties, but there are numerous features that support more refined logging, where you can select subsets of information to be logged using the LogModules connection property.

Customizing the SSL Configuration

By default, the Sync App attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.

To specify another certificate, see the SSLServerCert property for the available formats to do so.

Connecting Through a Firewall or Proxy

HTTP Proxies

To connect through the Windows system proxy, you do not need to set any additional connection properties. To connect to other proxies, set ProxyAutoDetect to false.

In addition, to authenticate to an HTTP proxy, set ProxyAuthScheme, ProxyUser, and ProxyPassword, in addition to ProxyServer and ProxyPort.

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.
• Row Scan Depth connection property allows you to set the number of rows that will be filtered for custom fields. The default value for this property is 50. Setting a low value may result in not getting all the custom fields, but setting a high value will decrease the performance of the driver. To use this feature set the Include Custom Fields connection property to True.
• Flexible Schema is used to retrieve custom fields for Products and ProductVariants table. This feature is enabled when Include Custom Fields connection property is set to True. Flexible schema will search 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, Reports. Typically, model data that is read-only and cannot be updated are shown as views.

Stored Procedures

Stored Procedures are function-like interfaces to the data source. They can be used to search, update, and modify information in the data source.

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

Generally, querying Shopify tables is the same as querying a table in a relational database. Sometimes there are special cases, for example, including a certain column in the WHERE clause might be required to get data for certain columns in the table. This is typically needed for situations where a separate request must be made for each row to get certain columns. These types of situations are clearly documented at the top of the table page linked below.

Shopify Connector for CData Sync Tables

Name	Description
ApplicationCharges	Create or view Application Charges for Billing.
ApplicationCredit	Create or view Application Credit for Billing.
Articles	Create, read, update or delete articles
Assets	Create, read, update or delete assets
Blogs	Create, read, update or delete blogs
CollectionListings	Query and delete information regarding different collects.
Collects	Query, insert, or delete information regarding different collects.
Comments	Create, read, update and delete the comments.
Countries	Create, select, update, and delete information regarding countries.
CustomCollections	Query, insert, update, or delete information regarding different custom collections.
CustomerAddresses	Create, update, delete, and query customer addresses.
Customers	Create, update, delete, and query customers.
DiscountCodes	Create, select, update, and delete information regarding discount codes.
DraftOrders	Create, update, delete, and query draft orders.
FulfillmentEvents	Create, delete, and query information regarding fulfillment events.
Fulfillments	Create, update, and query fulfillments.
FulfillmentServices	Query, create, update, and delete information regarding different fulfillment services.
InventoryItems	Query and update information regarding different inventory items.
InventoryLevels	Query, create, and update information regarding different inventory levels.
MarketingEvents	Create, update, delete, and query marketing events.
Metafields	Retrieves a list of metafields that belong to a resource.
OrderRisks	Create, update, delete, and query order risks.
Orders	Create, update, delete, and query orders.
OrderTransactions	Create and query transactions.
Pages	Create, read, update or delete pages
PriceRules	Create, update, delete, and query price rules.
ProductImages	Query, Update and Delete Product Images
ProductListings	Query and delete product listings.
Products	Create, update, delete, and query products.
ProductVariants	Query and delete product listings.
Provinces	Query and update information regarding different provinces.
RecurringApplicationCharges	Create, update, delete, and query Recurring Application Charges.
Redirects	Create, read, update or delete redirects.
Refunds	Create and query refunds.
ScriptTags	Create, read, update or delete script tags.
SmartCollections	Query, insert, update, or delete information regarding different smart collections.
Themes	Create, read, update or delete themes
UsageCharges	Create or view Usage Charges for Recurring Application Charges.

Create or view Application Charges for Billing.

Table-Specific Information

Select

The Sync App processes all filters client-side within the Sync App. The following queries are the only ones processed server side:

SELECT * FROM ApplicationCharges

SELECT * FROM ApplicationCharges WHERE Id='123'

You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.

Insert

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

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Long	False		The Unique Identifier of the Application Charge
Name	String	False		The Order Number
ApiClientId	Long	False		The API Client ID
Price	Decimal	False		The price of the application charge
Status	String	False		The status of the application charge. Valid Values are: pending, active, declined, expired
ReturnUrl	String	False		The URL where the merchant is redirected after accepting a charge.
Test	String	False		Whether the application charge is a test transaction. Valid values:true,null
CreatedAt	Datetime	False		The date and time (ISO 8601 format) when the application charge was created.
UpdatedAt	Datetime	False		The date and time (ISO 8601 format) when the application charge was last updated.
ChargeType	String	False		The type of the application charge
DecoratedReturnUrl	String	False		The decorated return url

Create or view Application Credit for Billing.

Table-Specific Information

Select

The Sync App processes all filters client-side within the Sync App. The following queries are the only ones processed server side:

SELECT * FROM ApplicationCredit

SELECT * FROM ApplicationCredit WHERE Id='123'

You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.

Insert

Insert Into ApplicationCredit (Amount, Description, Test) VALUES ('100', 'succes or failure', 'true')

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Long	False		The ID of the application credit.
Amount	Decimal	False		The amount refunded by the application credit.
Description	String	False		The description of the application credit.
Test	String	False		Whether the application credit is a test transaction. Valid values: true,null

Create, read, update or delete articles

Table-Specific Information

Select

The Sync App processes all filters client-side within the Sync App. The following queries are the only ones processed server side:

SELECT * FROM Articles

SELECT * FROM Articles WHERE Id='123'

You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.

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

Name	Type	ReadOnly	References	Description
Id [KEY]	Long	True		The ID of the article.
BlogId [KEY]	Long	False	Blogs.Id	The ID of the blog containing the article.
AdminGraphqlApiId	String	True		Displays the Admin Graphql API id.
Author	String	False		The name of the author of the article.
BodyHtml	String	False		The text of the body of the article, complete with HTML markup.
CreatedAt	Datetime	True		The date and time (ISO 8601 format) when the article was created.
Handle	String	False		A human-friendly unique string for the article that's automatically generated from the article's title. The handle is used in the article's URL.
Image.alt	String	False		Alternative text that describes the image.
Image.createdAt	Datetime	True		The date and time when image is created.
Image.height	Integer	False		Height of the image.
Image.src	String	False		A source URL that specifies the location of the image.
Image.width	Integer	False		Width of the image.
Image.attachment	String	False		An image attached to article returned as Base64-encoded binary data.
PublishedAt	Datetime	False		The date and time (ISO 8601 format) when the article was published.
SummaryHtml	String	False		A summary of the article, complete with HTML markup.
Tags	String	False		Tags are additional short descriptors formatted as a string of comma-separated values.
Template_suffix	String	False		The name of the template an article is using if it's using an alternate template. If an article is using the default article.liquid template, then the value returned is null.
Title	String	False		The title of the article.
UpdatedAt	Datetime	True		The date and time (ISO 8601 format) when the article was last updated.
UserId	Long	True		A unique numeric identifier for the author of the article.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
published_status	String	Filter articles by their publish status. Valid values are: published, unpublished and any ; default: any.

Create, read, update or delete assets

Table-Specific Information

Select

The Sync App processes all filters client-side within the Sync App. The following queries are the only ones processed server side:

SELECT * FROM Assets

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

You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.

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

Name	Type	ReadOnly	References	Description
Key [KEY]	String	False		The path to the asset within a theme.
ThemeId	Long	True	Themes.Id	The ID for the theme that an asset belongs to.
Attachment	String	False		A base64-encoded image.
Checksum	String	True		The MD5 representation of the content, consisting of a string of 32 hexadecimal digits. May be null if an asset has not been updated recently.
ContentType	String	True		The MIME representation of the content, consisting of the type and subtype of the asset.
CreatedAt	Datetime	True		The date and time (ISO 8601 format) when the asset was created.
PublicUrl	String	True		The public-facing URL of the asset.
Size	Integer	True		The asset size in bytes.
UpdatedAt	Datetime	True		The date and time (ISO 8601 format) when an asset was last updated.
Value	String	False		The text content of the asset, such as the HTML and Liquid markup of a template file.

Create, read, update or delete blogs

Table-Specific Information

Select

The Sync App processes all filters client-side within the Sync App. The following queries are the only ones processed server side:

SELECT * FROM Blogs

SELECT * FROM Blogs WHERE Id='123'

You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.

Insert

You must specify the Title to create a Blog.

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

Update

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

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

Delete

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

DELETE FROM Blogs WHERE Id = '555695'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Long	True		A unique numeric identifier for the blog.
AdminGraphqlApiId	String	True		Displays the Admin Graphql API id.
Commentable	String	False		Indicates whether readers can post comments to the blog and if comments are moderated or not. The allowed values are no, yes, moderate. The default value is no.
CreatedAt	Datetime	True		The date and time when the blog was created.
Feedburner	String	False		Feedburner is a web feed management provider and can be enabled to provide custom RSS feeds for Shopify bloggers. This property will default to blank or null unless feedburner is enabled through the shop admin.
FeedburnerLocation	String	False		URL to the feedburner location for blogs that have enabled feedburner through their store admin. This property will default to blank or null unless feedburner is enabled through the shop admin.
Handle	String	False		A human-friendly unique string for a blog automatically generated from its title.
Metafields	String	True		Attaches additional metadata to a store's resources.
Tags	String	False		Tags are additional short descriptors formatted as a string of comma-separated values.
TemplateSuffix	String	False		States the name of the template a blog is using if it is using an alternate template. If a blog is using the default blog.liquid template, the value returned is null.
Title	String	False		The title of the blog.
UpdatedAt	Datetime	True		The date and time when changes were last made to the blog's properties.

Query and delete information regarding different collects.

Table-Specific Information

Select

The Sync App processes all filters client-side within the Sync App. The following queries are the only ones processed server side:

SELECT * FROM CollectionListings

SELECT * FROM CollectionListings WHERE Id='123'

You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.

Delete

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

DELETE FROM CollectionListings WHERE id ='123'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Long	False	CustomCollections.Id	The Id of the collection this listing belongs to.
Title	String	True		The titke of the collection listing.
BodyHtml	String	True		The HTML body/description for this listing.
DefaultProductImage	String	True		The default product image in this listing.
ImageCreatedAt	String	True		The date and time the image for this listing was created at.
ImageSource	String	True		The source/link of the image for this listing.
Handle	String	True		The handle of this listing.
UpdatedAt	Datetime	True		The date and time when the collection listing was last modified.
PublishedAt	Datetime	True		The date and time when the collection listing was created.

Query, insert, or delete information regarding different collects.

Table-Specific Information

Select

The Sync App processes all filters client-side within the Sync App. The following queries are the only ones processed server side:

SELECT * FROM Collects

SELECT * FROM Collects WHERE Id='123'

You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.

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

Name	Type	ReadOnly	References	Description
Id [KEY]	Long	True		A unique numeric identifier for the collect.
CollectionId	Long	False	CustomCollections.Id	The Id of the collection this collection is linked with.
ProductId	Long	False	Products.Id	The Id of the product this collection is linked with.
Position	Int	True		The position of this product in a manually sorted custom collection. The first position is 1. This value is applied only when the custom collection is sorted manually.
SortValue	String	True		This is the same value as position but padded with leading zeroes to make it alphanumeric-sortable.
CreatedAt	Datetime	True		The date and time when the collect was created.
UpdatedAt	Datetime	True		The date and time when the collect was last modified.

Create, read, update and delete the comments.

Table-Specific Information

Select

The Sync App processes all filters client-side within the Sync App. The following queries are the only ones processed server side:

SELECT * FROM Comments

SELECT * FROM Comments WHERE Id='123'

You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.

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

Name	Type	ReadOnly	References	Description
Id [KEY]	Long	True		A unique numeric identifier for the comment.
BlogId	Long	False	Blogs.Id	A unique numeric identifier for the blog containing the article that the comment belongs to.
ArticleId	Long	False	Articles.Id	A unique numeric identifier for the article that the comment belongs to.
Author	String	False		The name of the author of the comment.
Body	String	False		The basic Textile markup of a comment.
BodyHtml	String	False		The text of the comment, complete with HTML markup.
CreatedAt	Datetime	True		The date and time (ISO 8601 format) when the comment was created.
Email	String	False		The email address of the author of the comment.
Ip	String	False		The IP address from which the comment was posted.
PublishedAt	String	False		The date and time (ISO 8601 format) when the comment was published.
Status	String	True		The status of the comment. The allowed values are pending, approved, unapproved, spam, published, removed.
UpdatedAt	Datetime	True		The date and time (ISO 8601 format) when the comment was last modified.
UserAgent	String	False		The user agent string provided by the software used to create the comment.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
published_status	String	Filter comments by their publish status. Valid values are: published, unpublished and any ; default: any.
status	String	Filter comments by their status. Valid values are: published, pending and unapproved

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

Table-Specific Information

Select

The Sync App processes all filters client-side within the Sync App. The following queries are the only ones processed server side:

SELECT * FROM Countries

SELECT * FROM Countries WHERE Id='123'

You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.

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

Name	Type	ReadOnly	References	Description
Id [KEY]	Long	True		A unique numeric identifier for the country.
Name	String	True		The name of the country.
Code	String	False		The code of the country.
Tax	Decimal	False		The amount of tax which should be applied for this country.
Provinces	String	False		The sub-regions of a country, such as its provinces or states. Each sub-region has the following properties: code: The two letter province or state code, country_id: The ID for the country to which this sub-region belongs, id: The ID for the particular sub-region, name: The name of the sub-region, tax: The tax value in decimal format, tax_name: The name of the tax as it is referred to in the applicable sub-region. For example, in Canada, the sales tax in the province Ontario is referred to as HST, tax_type: The tax type. Valid values: null, normal, or harmonized. If the value is harmonized, then the tax is compounded of the provincial and federal sales taxes, tax_percentage: The tax value in percent format

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

Table-Specific Information

Select

The Sync App uses the Shopify API to process search criteria that refer to the Id, ProductId, Title, Handle, PublishedStatus, UpdatedAt, and PublishedAt columns. The supported SQL operators are '=' and 'IN' for Id, '=' for the ProductId, Title, Handle, and PublishedStatus columns, and '>' and '<' for UpdatedAt and PublishedAt. The Sync App processes other filters client-side within the Sync App. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.

For example, the following queries are processed server side.

SELECT * FROM CustomCollections

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 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

Name	Type	ReadOnly	References	Description
Id [KEY]	Long	True		A unique numeric identifier for the custom collection.
Title	String	False		The title of the custom collection.
BodyHtml	String	False		The body/description of the custom collection.
Image	String	False		A JSON aggregate with information regarding the image of custom collection.
Metafields	String	False		Attaches additional metadata to a shop's resources: key: An identifier for the metafield. (maximum: 30 characters), namespace: A container for a set of metadata. Namespaces help distinguish between metadata created by different apps. (maximum: 20 characters), value: Information to be stored as metadata, value_type: The information type being stored. Valid values: string or integer, description (optional): Additional information about the metafield .
Handle	String	False		A human-friendly unique string for the custom collection automatically generated from its title.
SortOrder	String	False		The order in which products in the custom collection appear.
TemplateSuffix	String	False		The suffix of the liquid template being used.
PublishedScope	String	False		The sales channels in which the custom collection is visible.
UpdatedAt	Datetime	True		The date and time when the custom collection was last modified.
PublishedAt	Datetime	True		The date and time when the custom collection was published to the Online Store channel.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
ProductId	String	Show custom collections that include a given product.
PublishedStatus	String	Show custom collection with a given published status: published: show only published custom collections, unpublished: show only unpublished custom collections, any: show custom collections of any published status. (default: any).

Create, update, delete, and query customer addresses.

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.

• CustomerId supports the '=,IN' comparison.
• CustomerUpdatedAt supports the '=' comparison.
• FirstName supports the '=' comparison.
• LastName supports the '=' comparison.
• Company supports the '=' comparison.
• Address1 supports the '=' comparison.
• Address2 supports the '=' comparison.
• City supports the '=' comparison.
• Province supports the '=' comparison.
• Country supports the '=' comparison.
• Phone supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM CustomerAddresses

SELECT * FROM CustomerAddresses WHERE CustomerId='123'

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

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

SELECT * FROM CustomerAddresses WHERE FirstName='abc'

SELECT * FROM CustomerAddresses WHERE LastName='xyz'

SELECT * FROM CustomerAddresses WHERE Company='cdata'

SELECT * FROM CustomerAddresses WHERE Address1='Electronic city'

SELECT * FROM CustomerAddresses WHERE City='Bangalore'

SELECT * FROM CustomerAddresses WHERE Country='India'

SELECT * FROM CustomerAddresses WHERE Phone='9769879890'

You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.

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

Name	Type	ReadOnly	References	Description
Id [KEY]	Long	True		A unique numeric identifier for the address.
CustomerId [KEY]	Long	False	Customers.Id	A unique numeric identifier for the customer.
CustomerUpdatedAt	Datetime	True		The date and time when the customer information was last updated.
Name	String	False		The customer's name.
FirstName	String	False		The customer's first name.
LastName	String	False		The customer's last name.
Company	String	False		The customer's company.
Address1	String	False		The customer's mailing address.
Address2	String	False		An additional field for the customer's mailing address.
City	String	False		The customer's city.
Province	String	False		The customer's province or state name.
Country	String	False		The customer's country.
Zip	String	False		The customer's zip or postal code.
Phone	String	False		The customer's phone number for this mailing address.
ProvinceCode	String	False		The two-letter pcode for the customer's province or state.
CountryCode	String	False		The two-letter country code corresponding to the customer's country.
CountryName	String	False		The customer's normalized country name.
Default	Boolean	True		Indicates whether this address is the default address for the customer.

Create, update, delete, and query customers.

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

For example, the following queries are processed server side.

SELECT * FROM Customers

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'

You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.

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

Name	Type	ReadOnly	References	Description
Id [KEY]	Long	True		A unique numeric identifier for the customer.
LastOrderId	Long	True	Orders.Id	The id of the customer's last order.
LastOrderName	String	True		The name of the customer's last order.
FirstName	String	False		The customer's first name.
LastName	String	False		The customer's last name.
Email	String	False		The unique email address of the customer.
Phone	String	False		The unique phone number for this customer.
TaxExempt	Boolean	False		Indicates whether the customer should be charged taxes when placing orders.
TotalSpent	Decimal	True		The total amount of money that the customer has spent at the shop.
OrdersCount	Integer	True		The number of orders associated with this customer.
AcceptsMarketing	Boolean	True		Indicates whether the customer has consented to be sent marketing material via email.
MultipassIdentifier	String	True		The customer's identifier used with Multipass login.
Note	String	False		A note about the customer.
State	String	True		The state of the customer's account in a shop.
Tags	String	False		The tags for this customer. Separate with comma for multiple tags.
VerifiedEmail	Boolean	True		States whether or not the email address has been verified.
CreatedAt	Datetime	True		The date and time when the customer was created.
UpdatedAt	Datetime	True		The date and time when the customer information was updated.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
Password	String	Password of the customer
PasswordConfirmation	String	Password confirmation of the customer.
SendEmailWelcome	Boolean	Whether to send a welcome email to the customer or not.
SendEmailInvite	Boolean	Whether to send an invite email to the customer or not.

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

Table-Specific Information

Select

The Sync App uses the Shopify API to process search criteria that refer to the PriceRuleId and Id columns. The supported SQL operator is '='. The Sync App processes other filters client-side within the Sync App.

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

Name	Type	ReadOnly	References	Description
Id [KEY]	Long	True		A unique numeric identifier for the discount code.
PriceRuleId [KEY]	Long	False	PriceRules.Id	The Id for the price rule that this discount code belongs to.
Code	String	False		The case-insensitive discount code that customers use at checkout.
UsageCount	Int	True		The number of times that the discount code has been redeemed.
CreatedAt	Datetime	True		The date and time when the discount code was created.
UpdatedAt	Datetime	True		The date and time when the discount code was last modified.

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. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.

For example, the following queries are processed server side:

SELECT * FROM DraftOrders

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

Name	Type	ReadOnly	References	Description
Id [KEY]	Long	True		A unique numeric identifier for the order.
CustomerId	Long	False	Customers.Id	A unique numeric identifier for the customer.
Currency	String	False		The three letter code (ISO 4217) for the currency used for the payment.
Email	String	False		The customer's email address.
Name	String	True		The customer's order name as represented by a number.
TotalPrice	Decimal	True		The sum of all the prices of all the items in the order, taxes and discounts included.
SubtotalPrice	Decimal	True		Price of the order before shipping and taxes.
TotalTax	Decimal	False		The sum of all the taxes applied to the order.
TaxesIncluded	Boolean	False		States whether or not taxes are included in the order subtotal.
InvoiceSentAt	Datetime	True		This auto-generated property is the date and time when the invoice was emailed to the customer. Only available for draft orders.
Note	String	False		The text of an optional note that a shop owner can attach to the order.
Tags	String	False		Additional short descriptors.
InvoiceUrl	String	True		The URL for the invoice. Only available for draft orders.
TaxExempt	Boolean	True		States whether or not taxes are exempt for this order. Only available for draft orders.
Status	String	True		The status of the order. Valid values: open: all open orders (default), invoice_sent: show only closed orders, completed: Show only cancelled orders.
LineAggregate	String	False		A JSON aggregate of line items associated with the draft order.
BillingAddressFirstName	String	False		The first name of the person associated with the payment method.
BillingAddressLastName	String	False		The last name of the person associated with the payment method.
BillingAddressAddress1	String	False		The street address of the billing address.
BillingAddressAddress2	String	False		An optional additional field for the street address of the billing address.
BillingAddressPhone	String	False		The phone number at the billing address.
BillingAddressCity	String	False		The city of the billing address.
BillingAddressCompany	String	False		The company of the person associated with the billing address.
BillingAddressZip	String	False		The zip or postal code of the billing address.
BillingAddressProvince	String	False		The name of the state or province of the billing address.
BillingAddressCountry	String	False		The name of the country of the billing address.
BillingAddressLatitude	Double	False		The latitude of the billing address.
BillingAddressLongitude	Double	False		The longitude of the billing address.
BillingAddressName	String	False		The full name of the person associated with the payment method.
BillingAddressCountryCode	String	False		The two-letter code for the country of the billing address.
BillingAddressProvinceCode	String	False		The two-letter abbreviation of the state or province of the billing address.
BillingAddressDefault	Boolean	False		Whether this address is the default one or not.
ShippingAddressFirstName	String	False		The first name of the person associated with the shipping method.
ShippingAddressLastName	String	False		The last name of the person associated with the shipping method.
ShippingAddressAddress1	String	False		The street address of the shipping address.
ShippingAddressAddress2	String	False		An optional additional field for the street address of the shipping address.
ShippingAddressPhone	String	False		The phone number at the shipping address.
ShippingAddressCity	String	False		The city of the shipping address.
ShippingAddressCompany	String	False		The company of the person associated with the shipping address.
ShippingAddressZip	String	False		The zip or postal code of the shipping address.
ShippingAddressProvince	String	False		The name of the state or province of the shipping address.
ShippingAddressCountry	String	False		The name of the country of the shipping address.
ShippingAddressLatitude	Double	False		The latitude of the shipping address.
ShippingAddressLongitude	Double	False		The longitude of the shipping address.
ShippingAddressName	String	False		The full name of the person associated with the shipping method.
ShippingAddressCountryCode	String	False		The two-letter code for the country of the shipping address.
ShippingAddressProvinceCode	String	False		The two-letter abbreviation of the state or province of the shipping address.
ShippingAddressDefault	Boolean	False		Whether this address is the default one or not.
AppliedDiscountTitle	String	False		The title of the applied discount for this order. Only available for draft orders.
AppliedDiscountDescription	String	False		The description of the applied discount for order. Only available for draft orders.
AppliedDiscountValue	Decimal	False		The value of the applied discount for this order. Only available for draft orders.
AppliedDiscountValueType	String	False		The value type of the applied discount for order. Only available for draft orders.
AppliedDiscountAmount	Decimal	False		The amount of the applied discount for this order. Only available for draft orders.
CreatedAt	Datetime	True		The date and time when the order was created.
UpdatedAt	Datetime	True		The date and time when the order was last modified.
CompletedAt	Datetime	True		The date and time when the order was completed at.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
UseCustomerDefaultAddress	Boolean	Optional boolean that you can send as part of a draft order object to load customer shipping information. Valid values: true or false.

Create, delete, and query information regarding fulfillment events.

Table-Specific Information

Select

The OrderId and FulfillmentId are required to query this table.

The Sync App uses the Shopify API to process search criteria that refer to the OrderId, FulfillmentId, and Id columns. The supported SQL operator is '='. The Sync App processes other filters client-side within the Sync App. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.

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

Name	Type	ReadOnly	References	Description
Id [KEY]	Long	True		A numeric unique identifier for the fulfillment event.
OrderId [KEY]	Long	False	Orders.Id	The id of the order the fulfillment event belongs to.
FulfillmentId [KEY]	Long	False	Fulfillments.Id	A numeric unique identifier for the fulfillment to which the fulfillment event belongs.
ShopId	Long	True	Shop.Id	A numeric unique identifier for the shop to which the fulfillment event belongs.
Status	String	False		The status of the fulfillment event. Valid values are: confirmed, in_transit, out_for_delivery, delivered, failure.
EstimatedDeliveryAt	Datetime	False		The status of the fulfillment event. Valid values are: confirmed, in_transit, out_for_delivery, delivered, failure.
Message	String	False		An arbitrary message describing the status. Can be provided by a shipping carrier.
City	String	False		The city in which the fulfillment event occurred.
Province	String	False		The province in which the fulfillment event occurred.
Zip	String	False		The zip code in the location in which the fulfillment event occurred.
Country	String	False		The country in which the fulfillment event occurred.
Address1	String	False		The fulfillment event's street address.
Latitude	Double	False		Geographic coordinate specifying the north/south location of a fulfillment event.
Longitude	Double	False		Geographic coordinate specifying the east/west location of a fulfillment event.
CreatedAt	Datetime	True		The date and time when the fulfillment event was created.
UpdatedAt	Datetime	True		The date and time when the fulfillment event was updated.
HappenedAt	Datetime	True		The date and time when the fulfillment event occurred.

Create, update, and query fulfillments.

Table-Specific Information

Select

The Sync App uses the Shopify API to process search criteria that refer to the Id, OrderId, CreatedAt, and UpdatedAt columns. The Sync App processes other filters client-side within the Sync App.

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.
  

  SELECT * FROM Fulfillments
  
  SELECT * FROM Fulfillments WHERE OrderId = '123'
  
  SELECT * FROM Fulfillments WHERE OrderId = '123' AND Id='567'

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

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

You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false. When SupportEnhancedSQL is false, you must specify at least OrderId. The Shopify API requires that queries contain this filter.

Insert

You must specify the OrderId column to insert a fulfillment.

• Fulfill one line item using aggregates.
  

  INSERT INTO Fulfillments(OrderId, LineAggregate) VALUES('123', '[{\"id\":\"123\"}]')

• 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) Values ('123')

  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 Fulfillments (OrderId, LinesAggregate) VALUES ('123', '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 (OrderId, LocationId, LinesAggregate) VALUES ('123', '456', 'OrdersItems#Temp')

• Fulfill many line items using aggregates.
  

  INSERT INTO Fulfillments(OrderId, LineAggregate) VALUES('123', '[{\"id\":\"123\"},{\"id\":\"456\"},{\"id\":\"789\"}]')

• Fulfill many line items using temporary table.
  

  INSERT INTO OrdersItems#Temp (ItemId) VALUES ('123')
  INSERT INTO OrdersItems#Temp (ItemId) VALUES ('456')
  INSERT INTO OrdersItems#Temp (ItemId) VALUES ('789')
  INSERT INTO Fulfillments (OrderId, LineAggregate) VALUES ('123', 'OrdersItems#Temp')

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

  INSERT INTO Fulfillments(OrderId, TrackingNumbers, NotifyCustomer) VALUES('123', 'FEDEX1', true)

• Partially fulfill a single line item by explicitly specifying the line item and quantity to be fulfilled using aggregates.
  

  INSERT INTO Fulfillments(OrderId, LineAggregate) VALUES('123', '[{\"id\":\"123\",\"quantity\":5}]')

• Partially fulfill a single line item by explicitly specifying the line item and quantity to be fulfilled using temporary table.
  

  INSERT INTO OrdersItems#Temp (ItemId,ItemQuantity) VALUES ('123',5)
  INSERT INTO Fulfillments (OrderId,LineAggregate) VALUES ('456','OrdersItems#Temp')

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

  INSERT INTO Fulfillments(OrderId, Id, Operation) VALUES('123', '456', 'Complete')    

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

  INSERT INTO Fulfillments(OrderId, Id, Operation) VALUES('123', '456', 'Open')

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

  INSERT INTO Fulfillments(OrderId, Id, Operation) VALUES('123', '456', 'Cancel')

Update

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

UPDATE Fulfillments SET TrackingNumbers='FedEx123,UPS123' WHERE OrderId='123' AND Id='456'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Long	True		A unique numeric identifier for the fulfillment.
OrderId [KEY]	Long	False	Orders.Id	A unique numeric identifier for the order.
LocationId [KEY]	Long	False		A unique numeric identifier for the Location.
NotifyCustomer	Boolean	False		A flag indicating whether the customer should be notified
Status	String	True		The status of the fulfillment.
Receipt	String	True		Provides information about the receipt of this fulfillment.
TrackingCompany	String	False		The name of the tracking company.
TrackingNumbers	String	False		A list of comma-separated tracking numbers, provided by the shipping company.
TrackingUrls	String	True		The sum of all the prices of all the items in the fulfillment.
VariantInventoryManagement	String	True		States the name of the inventory management service.
CreatedAt	Datetime	True		The date and time when the fulfillment was created.
UpdatedAt	Datetime	True		The date and time when the fulfillment was last modified.
LineAggregate	String	False		A JSON aggregate of line items associated with the fulfillment.
OrderUpdatedAt	Datetime	True		The date and time when the order was last modified.
OrderCreatedAt	Datetime	True		The date and time when the order was last created.
Operation	String	False		An operation to apply to the fulfillment. Complete, Open, or Cancel. The allowed values are Complete, Open, Cancel.

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

Table-Specific Information

Select

The Sync App uses the Shopify API to process search criteria that refer to the Id column. The supported SQL operator is '='. The Sync App processes other filters client-side within the Sync App. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.

For example, the following queries are processed server side:

SELECT * FROM FulfillmentServices

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

Name	Type	ReadOnly	References	Description
Id [KEY]	Long	True		A unique numeric identifier for the fulfillment service.
LocationId	Long	True	Locations.Id	The unique identifier of the location tied to the fulfillment service.
ProviderId	String	True		A unique identifier for the fulfillment service provider.
Name	String	False		The name of the fulfillment service as seen by merchants and their customers.
Handle	String	True		A human-friendly unique string for the fulfillment service generated from its title.
ServiceName	String	True		The name of the fulfillment service.
Email	String	False		The email of the fulfillment service.
IncludePendingStock	Boolean	False		States if the fulfillment service includes a pending stock.
RequiresShippingMethod	Boolean	False		States if the fulfillment service requires products to be physically shipped. Valid values are 'true' and 'false'.
TrackingSupport	Boolean	False		States if the fulfillment service provides tracking numbers for packages. Valid values are
InventoryManagement	Boolean	False		States if the fulfillment service tracks product inventory and provides updates to Shopify. Valid values are
FulfillmentOrdersOptIn	Boolean	True		States if the fulfillment orders is opt in.
CallbackUrl	String	False		States the URL endpoint that Shopify needs to retrieve inventory and tracking updates. This field is necessary if either inventory_management or tracking_support is set to
Format	String	False		Specifies the format of the API output. Valid values are json and xml.

Query and update information regarding different inventory items.

Table-Specific Information

Select

The Sync App uses the Shopify API to process search criteria that refer to the Id column. 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 InventoryItems

SELECT * FROM InventoryItems WHERE Id='123'

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

You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false; the Shopify APIs require Id in the search criteria, and any other search criteria will cause an error.

Update

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

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

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Long	True		A unique numeric identifier for the fulfillment.
Cost	Decimal	False		The unit cost of the inventory item.
CountryCodeOfOrigin	String	False		The two-digit code for the country where the inventory item was made.
CountryHarmonizedSystemCodes	String	False		An array of country-specific Harmonized System (HS) codes for the item.
HarmonizedSystemCode	String	False		The general Harmonized System (HS) code for the inventory item. Used if a country-specific HS code is not available.
ProvinceCodeOfOrigin	String	False		The two-digit code for the province where the inventory item was made. Used only if the shipping provider for the inventory item is Canada Post.
SKU	String	False		The unique SKU (stock keeping unit) of the inventory item.
Tracked	Boolean	False		Whether the inventory item is tracked. If true, then inventory quantity changes are tracked by Shopify.
RequiresShipping	Boolean	True		Whether a customer needs to provide a shipping address when placing an order containing the inventory item.
CreatedAt	Datetime	True		The date and time when the inventory item was created.
UpdatedAt	Datetime	True		The date and time when the inventory item was last updated.

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. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.

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

Name	Type	ReadOnly	References	Description
InventoryItemId [KEY]	Long	False	InventoryItems.Id	The ID of the inventory item that the inventory level belongs to.
LocationId [KEY]	Long	False	Locations.Id	The ID of the location that the inventory level belongs to. To find the ID of the location, use the Locations view.
Available	Int	False		The quantity of inventory items available for sale. Returns null if the inventory item is not tracked.
UpdatedAt	Datetime	True		The date and time (ISO 8601 format) when the inventory level was last modified.
Operation	String	False		An operation to apply to the InventoryLevel. Valid values for order: Adjust, Connect, Set. The allowed values are Adjust, Connect, Set.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
RelocateIfNecessary	Boolean	Whether inventory for any previously connected locations will be relocated. This property is ignored when no fulfillment service location is involved. Used in insertions.
DisconnectIfNecessary	Boolean	Whether inventory for any previously connected locations will be set to 0 and the locations disconnected. This property is ignored when no fulfillment service is involved. Used in insertions.

Create, update, delete, and query marketing events.

Table-Specific Information

Select

The Sync App processes all filters client-side within the Sync App. The following queries are the only ones processed server side:

SELECT * FROM MarketingEvents

SELECT * FROM MarketingEvents WHERE Id='123'

You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.

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

Name	Type	ReadOnly	References	Description
Id [KEY]	Long	True		A unique numeric identifier for the marketing event.
RemoteId	String	False		An optional remote identifier for a marketing event.
BreadcrumbId	String	True		An optional identifier for the breadcrumb of a marketing event.
UTMCampaign	String	True		A unique name for the UTM campaign.
UTMSource	String	True		The source of the UTM campaign.
UTMMedium	String	True		The medium of the UTM campaign.
UTMContent	String	True		The content of the UTM campaign.
UTMTerm	String	True		The term of the UTM campaign.
EventTarget	String	True		The target of the event.
Description	String	True		A description for the marketing event.
MarketingChannel	String	False		A broader marketing event type that is focused only on the channel. Must be one of the allowed values (`search`, `display`, `social`, `email`, `referral`).
EventType	String	False		The specific type of marketing event. Must be one of the allowed values (`ad`, `post`, `message`, `retargeting`, `transactional`, `affiliate`, `loyalty`, `newsletter`, `abandoned_cart`, `receipt`).
Budget	Decimal	False		The budget of the ad campaign.
Paid	Boolean	False		A boolean field to specify whether this event is paid or organic.
BudgetType	String	False		The type of the budget; must be either `daily` or `lifetime`.
Currency	String	False		The currency for the budget.
ManageUrl	String	True		A link to manage the marketing event, generally in the Shopify app's interface.
PreviewUrl	String	True		A link to view the live version of the post/ad, or to view a rendered preview of the post/ad/email in the Shopify app.
ReferringDomain	String	False		The destination domain of the marketing event. Required unless MarketingChannel is one of email/referral/display.
MarketedResources	String	True		A list of the items that were marketed in the marketing event. It's a list of dictionaries with type keys and id keys. Valid values for type are: (`product`, `collection`, `price_rule`, `page`, `article`, `homepage`). All types, other than homepage, also require an id.
StartedAt	Datetime	False		The timestamp when the marketing action was started, or when the email was sent, or when the Facebook post was made live, etc.
EndedAt	Datetime	False		For events with a duration, when the event actually ended. This may differ from ScheduledToEndAt, if the ad was stopped early, etc.
ScheduledToEndAt	Datetime	False		For events with a duration, when the event was supposed to end.

Retrieves a list of metafields that belong to a resource.

View-Specific Information

Select

The Sync App uses the Shopify API to process search criteria that refer to the Id, OwnerId and OwnerResource columns. The supported SQL operators are '=,IN' for the Id, OwnerResource and OwnerId columns. The Sync App processes other filters client-side within the Sync App. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.

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);

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 = 19766614851607

Delete

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

DELETE FROM Metafields WHERE Id = 19766614851607

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Long	True		The unique ID of the metafield.
Namespace	String	False		A container for a set of metafields. You need to define a custom namespace for your metafields to distinguish them from the metafields used by other apps.
Key	String	False		The name of the metafield.
Value	String	False		The information to be stored as metadata.
Type	String	False		The metafield's information type.
Description	String	False		A description of the information that the metafield contains.
OwnerId	Long	False		The unique ID of the resource that the metafield is attached to.
OwnerResource	String	False		The type of resource that the metafield is attached to. The allowed values are shop, draft_order, product, variant, page, article, order, customer, collection, blog, product_image.
CreatedAt	Datetime	True		The date and time (ISO 8601 format) when the metafield was created.
UpdatedAt	Datetime	True		The date and time (ISO 8601 format) when the metafield was last updated.
AdminGraphqlApiId	String	True		The admin graphql api id

Create, update, delete, and query order risks.

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Long	True		A unique numeric identifier for the order risk.
CauseCancel	Boolean	False		Whether this order risk is severe enough to force the cancellation of the order.
CheckoutId	Long	True		The ID of the checkout that the order risk belongs to.
Display	Boolean	False		Whether the order risk is displayed on the order details page in the Shopify admin.
Message	String	False		The message that's displayed to the merchant to indicate the results of the fraud check.
OrderId	Long	True	Orders.Id	The ID of the order that the order risk belongs to.
Recommendation	String	False		The recommended action given to the merchant. The allowed values are cancel, investigate, accept.
Score	Decimal	False		For internal use only. A number between 0 and 1 that's assigned to the order. The closer the score is to 1, the more likely it is that the order is fraudulent.
Source	String	False		The source of the order risk.

Create, update, delete, and query orders.

Table-Specific Information

Select

The Sync App uses the Shopify API to process search criteria that refer to the Id, FinancialStatus, FulfillmentStatus, CreatedAt, ProcessedAt and UpdatedAt columns. The supported SQL operators are '=' and 'IN' for Id, '=' for FinancialStatus, and FulfillmentStatus, and '>' and '<' for the CreatedAt, ProcessedAt, and UpdatedAt columns. The Sync App processes other filters client-side within the Sync App. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.

For example, the following queries are processed server side:

SELECT * FROM Orders

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 simple partially paid order containing a captured transaction using aggregates.
  

  INSERT INTO Orders(LineAggregate, FinancialStatus, TransactionAggregate) VALUES('[{\"title\":\"Big Brown Bear Boots\",\"price\":25.99,\"quantity\":2}]', 'partially_paid', '[{\"status\":\"success\",\"kind\":\"capture\",\"amount\":52.57,\"gateway\":\"bogus\"}]')

• Create a simple partially paid order containing a captured transaction using temporary table.
  

  INSERT INTO OrdersItems#Temp (ItemTitle,ItemQuantity,ItemPrice) VALUES ('Big Brown Bear Boots',2,25.99)
  INSERT INTO Transactions#Temp (TransactionItemAmount, TransactionItemKind, TransactionItemStatus, TransactionItemGateway) VALUES (52.57, 'capture', 'success', 'bogus')
  INSERT INTO Orders (LineAggregate,FinancialStatus,TransactionAggregate) VALUES ('OrdersItems#Temp','partially_paid','Transactions#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

Name	Type	ReadOnly	References	Description
Id [KEY]	Long	True		A unique numeric identifier for the order.
Number	Long	True		A unique identifier for the order, used by the shop owner and customer.
CustomerId	Long	False	Customers.Id	A unique numeric identifier for the customer.
CustomerFirstName	String	False		The first name of the customer.
CustomerLastName	String	False		The last name of the customer.
Currency	String	False		The three letter code (ISO 4217) for the currency used for the payment.
Email	String	False		The customer's email address.
Name	String	True		The customer's order name as represented by a number.
TotalDiscounts	Decimal	True		The total amount of the discounts to be applied to the price of the order.
TotalOrderItemsPrice	Decimal	True		The sum of all the prices of all the items in the order.
TotalPrice	Decimal	True		The sum of all the prices of all the items in the order, taxes and discounts included.
SubtotalPrice	Decimal	True		Price of the order before shipping and taxes.
TotalTax	Decimal	False		The sum of all the taxes applied to the order.
TotalWeight	Integer	True		The sum of all the weights of the line items in the order, in grams.
BrowserIp	String	True		The IP address of the browser used by the customer when placing the order.
BuyerAcceptsMarketing	Boolean	False		Indicates whether or not the person who placed the order would like to receive email updates from the shop.
CancelReason	String	True		The reason why the order was canceled.
CartToken	String	True		Unique identifier for a particular cart that is attached to a particular order.
OrderStatusUrl	String	True		The URL pointing to the order status web page.
Token	String	True		Unique identifier for a particular order.
TaxesIncluded	Boolean	False		States whether or not taxes are included in the order subtotal.
SourceName	String	True		Where the order originated.
ReferringSite	String	True		The website that the customer clicked on to come to the shop.
ProcessingMethod	String	True		States the type of payment processing method.
Phone	String	False		The customer's phone number.
Note	String	False		The text of an optional note that a shop owner can attach to the order.
LocationId	Long	True		The unique numeric identifier for the physical location at which the order was processed.
LandingSite	String	True		The URL for the page where the buyer landed when entering the shop.
Tags	String	False		Additional short descriptors.
FulfillmentStatus	String	False		The fulfillment status of the order.
FinancialStatus	String	False		The financial status of the order.
CustomerLocale	String	True		The customer locale of the order.
InvoiceSentAt	Datetime	True		This auto-generated property is the date and time when the invoice was emailed to the customer. Only available for draft orders.
InvoiceUrl	String	True		The URL for the invoice. Only available for draft orders.
TaxExempt	Boolean	True		States whether or not taxes are exempt for this order. Only available for draft orders.
DiscountCodes	String	True		Applicable discount codes that can be applied to the order.
LineAggregate	String	False		A JSON aggregate of line items associated with the order.
TaxAggregate	String	False		A JSON aggregate of tax line items associated with the order. Note, these taxes are applied on the order, not on individual items. If you want them to be applied on individual items, use them in LineAggregate. Tax lines must be associated with either order or line item but not both.
ShippingAggregate	String	False		A JSON aggregate of shipping line items associated with the order.
NoteAttributesAggregate	String	False		A JSON aggregate of note attributes associated with the order.
DiscountApplicationsAggregate	String	True		An ordered list of stacked discount applications.
BillingAddressFirstName	String	False		The first name of the person associated with the payment method.
BillingAddressLastName	String	False		The last name of the person associated with the payment method.
BillingAddressAddress1	String	False		The street address of the billing address.
BillingAddressAddress2	String	False		An optional additional field for the street address of the billing address.
BillingAddressPhone	String	False		The phone number at the billing address.
BillingAddressCity	String	False		The city of the billing address.
BillingAddressCompany	String	False		The company of the person associated with the billing address.
BillingAddressZip	String	False		The zip or postal code of the billing address.
BillingAddressProvince	String	False		The name of the state or province of the billing address.
BillingAddressCountry	String	False		The name of the country of the billing address.
BillingAddressLatitude	Double	False		The latitude of the billing address.
BillingAddressLongitude	Double	False		The longitude of the billing address.
BillingAddressName	String	False		The full name of the person associated with the payment method.
BillingAddressCountryCode	String	False		The two-letter code for the country of the billing address.
BillingAddressProvinceCode	String	False		The two-letter abbreviation of the state or province of the billing address.
BillingAddressDefault	Boolean	False		Whether this address is the default one or not.
ShippingAddressFirstName	String	False		The first name of the person associated with the shipping method.
ShippingAddressLastName	String	False		The last name of the person associated with the shipping method.
ShippingAddressAddress1	String	False		The street address of the shipping address.
ShippingAddressAddress2	String	False		An optional additional field for the street address of the shipping address.
ShippingAddressPhone	String	False		The phone number at the shipping address.
ShippingAddressCity	String	False		The city of the shipping address.
ShippingAddressCompany	String	False		The company of the person associated with the shipping address.
ShippingAddressZip	String	False		The zip or postal code of the shipping address.
ShippingAddressProvince	String	False		The name of the state or province of the shipping address.
ShippingAddressCountry	String	False		The name of the country of the shipping address.
ShippingAddressLatitude	Double	False		The latitude of the shipping address.
ShippingAddressLongitude	Double	False		The longitude of the shipping address.
ShippingAddressName	String	False		The full name of the person associated with the shipping method.
ShippingAddressCountryCode	String	False		The two-letter code for the country of the shipping address.
ShippingAddressProvinceCode	String	False		The two-letter abbreviation of the state or province of the shipping address.
ShippingAddressDefault	Boolean	False		Whether this address is the default one or not.
AppliedDiscountTitle	String	False		The title of the applied discount for this order. Only available for draft orders.
AppliedDiscountDescription	String	False		The description of the applied discount for order. Only available for draft orders.
AppliedDiscountValue	Decimal	False		The value of the applied discount for this order. Only available for draft orders.
AppliedDiscountValueType	String	False		The value type of the applied discount for order. Only available for draft orders.
AppliedDiscountAmount	Decimal	False		The amount of the applied discount for this order. Only available for draft orders.
PaymentTermsAmount	Long	True		The amount that is owed according to the payment terms.
PaymentTermsCurrency	String	True		The presentment currency for the payment.
PaymentTermsPaymentTermsName	String	True		The name of the selected payment terms template for the order.
PaymentTermsPaymentTermsType	String	True		The type of selected payment terms template for the order.
PaymentTermsDueInDays	Integer	True		The number of days between the invoice date and due date that is defined in the selected payment terms template.
PaymentTermsPaymentSchedules	String	True		An array of schedules associated to the payment terms.
ProcessedAt	Datetime	True		The date and time when the order was imported, in ISO 8601 format.
CreatedAt	Datetime	True		The date and time when the order was created.
ClosedAt	Datetime	True		The date and time when the order was closed.
CancelledAt	Datetime	True		The date and time when the order was canceled.
UpdatedAt	Datetime	True		The date and time when the order was last modified.
SendReceipt	Boolean	False		Determines whether an order confirmation will be sent to the customer. The default value is false.
SendFulfillmentReceipt	Boolean	False		Determines whether a fulfillment confirmation will be sent to the customer. The default value is false.
InventoryBehaviour	String	False		Determines which inventory updating behavior is used. The following values are available: default, decrement_ignoring_policy, decrementobeying_policy The allowed values are default, decrement_ignoring_policy, decrementobeying_policy. The default value is default.
Operation	String	False		An operation to apply to the Order. Valid values for order: Close, Open, or Cancel. The allowed values are Close, Open, Cancel.
TotalShippingPriceSetShopMoneyAmount	Double	True		The amount of the shop money in the total shipping price set.
TotalShippingPriceSetShopMoneyCurrencyCode	String	True		The currency code of the shop money in the total shipping price set.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
Status	String	Filter orders by their status. Valid values are: open: show only open orders, closed: show only closed orders, cancelled: show only canceled orders, any: show orders of any status,default: open.

Create and query transactions.

Table-Specific Information

Select

The Sync App uses the Shopify API to process search criteria that refer to the OrderId column. The Sync App processes other filters client-side within the Sync App.

The Shopify APIs requires the search criteria to contain OrderId. For example, the following query is processed server side.

SELECT * FROM OrderTransactions WHERE OrderId = '123'

When performing a query without a filter or a filter other then the OrderId column, the Sync App will retrieve the entire list of transactions and perform the filters client-side. The Shopify API requires that an OrderId be specified when retrieving transactions. Therefore to retrieve all the transactions, the Sync App will first retrieve all the OrderIds from the Orders table (essentially performing the query: SELECT Id FROM Orders). Once the OrderId values have been retrieved, the Sync App will issue individual requests to the Shopify API to retrieve all the transactions for each OrderId to build the result set.

SELECT * FROM OrderTransactions

SELECT * FROM OrderTransactions WHERE Kind='Capture'

You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false. Other search criteria will be ignored and an unfiltered response will be returned.

Insert

You must specify the OrderId and Kind when inserting a transaction.

• Capture a specified amount on a previously authorized order.
  

  INSERT INTO OrderTransactions(OrderId, Amount, Kind) VALUES('123', 10.25, 'Capture')

• Capture a previously authorized order for the full amount.
  

  INSERT INTO OrderTransactions(OrderId, Kind) VALUES('123', 'Capture')

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Long	True		A unique numeric identifier for the transaction.
OrderId	Long	False	Orders.Id	A unique numeric identifier for the order.
UserId	Long	True		The unique identifier for the user.
LocationId	Long	True		The ID of the physical location where the transaction was processed.
Amount	Decimal	False		The amount of money that the transaction was for.
Authorization	String	True		The authorization code associated with the transaction.
Currency	String	True		The three letter code (ISO 4217) for the currency used for the payment.
DeviceId	String	True		The unique identifier for the device.
Gateway	String	False		The name of the gateway the transaction was issued through.
SourceName	String	True		The origin of the transaction.
Kind	String	False		The kind of transaction. Can be any of the following: Authorization, Capture, Sale, Void, Refund. The allowed values are Authorization, Capture, Sale, Void, Refund.
Message	String	True		The message associated with this transaction.
ErrorCode	String	True		A standardized error code, independent of the payment provider.
Status	String	False		The status of the transaction.
Test	Boolean	True		The option to use the transaction for testing purposes.
AVSResultCode	String	True		The Response code from the address verification system.
CreditCardBin	String	True		The issuer identification number (IIN).
CVVResultCode	String	True		The Response code from the credit card company.
CreditCardNumber	String	True		The customer's credit card number, with most of the leading digits redacted with Xs.
CreditCardCompany	String	True		The name of the company who issued the customer's credit card.
CreatedAt	Datetime	True		The date and time when the customer was created.
PaymentsRefundsAttributesStatus	String	True		The current status of the refund The allowed values are pending, faliure, success, error.
PaymentsRefundsAttributesAcquirerReferenceNumber	String	True		A unique number associated with the transaction that can be used to track the refund.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
TransactionItemAmount	Decimal	The amount of money that the transaction was for. Use this when inserting transactions into an order or refund.
TransactionItemGateway	String	The name of the gateway the transaction was issued through. Use this when inserting transactions into an order or refund.
TransActionItemKind	String	The kind of transaction. Can be any of the following: Authorization, Capture, Sale, Void, Refund. Use this when inserting transactions into an order or refund. The allowed values are Authorization, Capture, Sale, Void, Refund.
TransactionItemStatus	String	The status of the transaction. Use this when inserting transactions into an order or refund.
TransactionItemParentId	String	The parent id of the transaction. Use this when inserting transactions into a refund.
UpdatedAt	Datetime	The last time the order is updated.

Create, read, update or delete pages

Table-Specific Information

Select

The Sync App processes all filters client-side within the Sync App. The following queries are the only ones processed server side:

SELECT * FROM Pages

SELECT * FROM Pages WHERE Id='123'

You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.

Insert

You must specify the Title to create a Page.

INSERT INTO Pages(title) VALUES ('new Page')

Update

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

UPDATE Pages SET author='Harry' WHERE Id = '77171130'

Delete

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

DELETE FROM Pages WHERE Id = '555695'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Long	True		The unique numeric identifier for the page.
AdminGraphqlApiId	String	True		Displays the Admin Graphql API id.
Author	String	False		The name of the person who created the page.
BodyHtml	String	False		The text content of the page, complete with HTML markup.
CreatedAt	Datetime	True		The date and time (ISO 8601 format) when the page was created.
Handle	String	False		A unique, human-friendly string for the page, generated automatically from its title. In online store themes, the Liquid templating language refers to a page by its handle.
Metafields	String	True		Additional information attached to the Page object.
PublishedAt	Datetime	False		The date and time (ISO 8601 format) when the page was published. Returns null when the page is hidden.
ShopId	Long	True		The ID of the shop to which the page belongs.
TemplateSuffix	String	False		The suffix of the Liquid template being used.
Title	String	False		The page's title.
UpdatedAt	Datetime	True		The date and time (ISO 8601 format) when the page was last updated.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
published_status	String	Filter comments by their publish status. Valid values are: published, unpublished and any ; default: any.

Create, update, delete, and query price rules.

Table-Specific Information

Select

The Sync App processes all filters client-side within the Sync App. The following queries are the only ones processed server side:

SELECT * FROM PriceRules WHERE Id = '123'

Insert

You must specify the Title, TargetType, TargetSelection, ValueType, Value, CustomerSelection, StartsAt, and AllocationMethod columns to create a price rule. For example:

INSERT INTO PriceRules (Title,TargetType,TargetSelection,ValueType,Value,CustomerSelection,StartsAt,AllocationMethod) VALUES ('SUMMERSALE10OFF', 'line_item', 'all', 'fixed_amount', '-10.0', 'all', '2017-01-19T17:59:10Z','across')

Update

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

UPDATE PriceRules SET Title = 'SUMMERSALE10OFF' WHERE Id = '123'

Delete

You must specify the Id of the price rule to delete it.

DELETE FROM PriceRules WHERE Id = '123'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Long	True		A unique numeric identifier for the price rule.
AllocationMethod	String	False		The allocation method of the price rule. Valid values: each: The discount is applied to each of the entitled items. across: The calculated discount amount will be applied across the entitled items. The allowed values are each, across.
CustomerSelection	String	False		The customer selection for the price rule. Valid values: all: The price rule is valid for all customers. prerequisite: The customer must either belong to one of the customer saved searches specified by PrerequisiteSavedSearchIds. The allowed values are all, prerequisite.
EntitledCollectionIds	String	False		A list of Ids of collections whose products will be eligible to the discount. It can be used only with TargetType set to line_item and TargetSelection set to entitled. It can't be used in combination with EntitledProductIds or EntitledVariantIds.
EntitledCountryIds	String	False		A list of Ids of shipping countries that will be entitled to the discount. It can be used only with TargetType set to shipping_line and TargetSelection set to entitled.
EntitledProductIds	String	False		A list of Ids of products that will be entitled to the discount. It can be used only with TargetType set to line_item and TargetSelection set to entitled.
EntitledVariantIds	String	False		A list of Ids of product variants that will be entitled to the discount. It can be used only with TargetType set to line_item and TargetSelection set to entitled.
OncePerCustomer	Boolean	False		Whether the generated discount code will be valid only for a single use per customer. This is tracked using customer Id.
PrerequisiteCustomerIds	String	False		A list of customer Ids which for the price rule to be applicable, the customer must match one of the specified customers. If PrerequisiteCustomerIds is populated, then PrerequisiteSavedSearchIds must be empty.
PrerequisiteQuantityRange	String	False		The minimum number of items for the price rule to be applicable. It has the following property: greater_than_or_equal_to: The quantity of an entitled cart item must be greater than or equal to this value.
PrerequisiteSavedSearchIds	String	False		A list of customer saved search Ids. For the price rule to be applicable, the customer must be in the group of customers matching a customer saved search. If PrerequisiteSavedSearchIds is populated, then PrerequisiteCustomerIds must be empty.
PrerequisiteShippingPriceRange	String	False		The maximum shipping price for the price rule to be applicable. It has the following property: less_than_or_equal_to: The shipping price must be less than or equal to this value.
PrerequisiteSubtotalRange	String	False		The minimum subtotal for the price rule to be applicable. It has the following property: greater_than_or_equal_to: The subtotal of the entitled cart items must be greater than or equal to this value for the discount to apply.
TargetSelection	String	False		The target selection method of the price rule. Valid values: all: The price rule applies the discount to all line items in the checkout, entitled: The price rule applies the discount to selected entitlements only.
TargetType	String	False		The target type that the price rule applies to. Valid values: line_item: The price rule applies to the cart's line items, shipping_line: The price rule applies to the cart's shipping lines.
UsageLimit	Integer	False		The maximum number of times the price rule can be used, per discount code.
PrerequisiteProductIds	String	False		List of product ids that will be a prerequisites for a Buy X Get Y type discount. The PrerequisiteProductIds can be used only with: TargetType set to line_item, TargetSelection set to entitled, AllocationMethod set to each and PrerequisiteToEntitlementQuantityRatio defined. Caution: If a product variant is included in PrerequisiteVariantIds, then PrerequisiteProductIds can't include the ID of the product associated with that variant.
PrerequisiteVariantIds	String	False		List of variant ids that will be a prerequisites for a Buy X Get Y type discount. The EntitledVariantIds can be used only with: TargetType set to line_item, TargetSelection set to entitled, AllocationMethod set to each and PrerequisiteToEntitlementQuantityRatio defined. Caution: If a product is included in PrerequisiteProductIds, then PrerequisiteVariantIds can't include the ID of any variants associated with that product.
PrerequisiteCollectionIds	String	False		List of collection ids that will be a prerequisites for a Buy X Get Y discount. The EntitledCollectionIds can be used only with: TargetType set to line_item, TargetSelection set to entitled, AllocationMethod set to each and PrerequisiteToEntitlementQuantityRatio defined. Cannot be used in combination with PrerequisiteProductIds or PrerequisiteVariantIds.
Value	Decimal	False		The value of the price rule. If if the value of target_type is shipping_line, then only -100 is accepted. The value must be negative.
ValueType	String	False		The value type of the price rule. Valid values: fixed_amount: Applies a discount of value as a unit of the store's currency. For example, if value is -30 and the store's currency is USD, then $30 USD is deducted when the discount is applied, percentage: Applies a percentage discount of value. For example, if value is -30, then 30% will be deducted when the discount is applied. If TargetType is shipping_line, then only percentage is accepted.
PrerequisiteToEntitlementQuantityRatio	String	False		Buy/Get ratio for a Buy X Get Y discount. prerequisite_quantity defines the necessary 'buy' quantity and entitled_quantity the offered 'get' quantity. The PrerequisiteToEntitlementQuantityRatio can be used only with: ValueType set to percentage, TargetType set to line_item, TargetSelection set to entitled, AllocationMethod set to each, PrerequisiteProductIds or PrerequisiteVariantIds or PrerequisiteCollectionIds defined and EntitledProductIds or EntitledVariantIds or EntitledCollectionIds defined.
Images	String	False		A list of image objects.
ProductType	String	False		A categorization that a product can be tagged with, commonly used for filtering and searching.
Options	String	True		Custom product property names.
PublishedScope	String	False		Indicates whether the product is published to the Point of Sale channel.
Tags	String	False		A categorization that a product can be tagged with. Use commas to separate categories.
TemplateSuffix	String	True		The suffix of the liquid template being used.
Title	String	False		The name of the product.
Vendor	String	False		The name of the vendor of the product.
StartsAt	Datetime	False		The date and time when the price rule starts.
EndsAt	Datetime	False		The date and time when the price rule ends. Must be after StartsAt.
PublishedAt	Datetime	True		The date and time when the product was published to the Online Store channel.
CreatedAt	Datetime	True		The date and time when the price rule was created.
UpdatedAt	Datetime	True		The date and time when the price rule was last modified.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
Published	Boolean	Set to true to publish the product or to false to not publish it
MetafieldsGlobalTitleTag	String	The name of the product, to be used for SEO purposes.
MetafieldsGlobalDescriptionTag	String	The description of the product, to be used for SEO purposes.

Query, Update and Delete Product Images

View-Specific Information

The Sync App uses the Shopify API to process search criteria that refer to the ProductId and ImageId. The supported SQL operators are '=' for ProductId and '=' for ImageId. The Sync App processes other filters client-side within the Sync App. For example, the following queries are processed server side.

Select

You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.

SELECT * FROM ProductImages WHERE ProductId = '123'

SELECT * FROM ProductImages WHERE ProductId = '123' AND ImageId = '2342'

Insert

To create a new product image with image data as attachment

INSERT INTO ProductImages (productid,filename,attachment) VALUES ('64146735127','test.png','iVBORw0KGgoAAAANSUhEUgAAAAUAAAAFCAYAAACNbyblAAAAHElEQVQI12P4//8/w38GIAXDIBKE0DHxgljNBAAO9TXL0Y4OHwAAAABJRU5ErkJggg==')")

To create a new product image using a source URL that will be downloaded by Shopify

INSERT INTO ProductImages (productid,filepath) VALUES ('64146735127','http://example.com/rails_logo.gif')")

To create a new product image and make it the main image

INSERT INTO ProductImages (productid,position,filename,filepath) VALUES ('64146735127',1,'test.png','http://example.com/rails_logo.gif')")

To create a new product image and attach it to product variants

INSERT INTO ProductImages (productid,variantids,filename,attachment) VALUES ('64146735127','[808950810,457924702]','test.png','iVBORw0KGgoAAAANSUhEUgAAAAUAAAAFCAYAAACNbyblAAAAHElEQVQI12P4//8/w38GIAXDIBKE0DHxgljNBAAO9TXL0Y4OHwAAAABJRU5ErkJggg==')")

Update

To update a product image you must specify its ProductId and ImageId.

To change the position and alt tag content for an image

UPDATE ProductImages SET Position = 1, Alt = 'This is the main image' WHERE ProductId = '123' AND ImageId = '2342'

To add it to product variants for an image

UPDATE ProductImages SET VariantIds = '[808950810,457924702]'  WHERE ProductId = '123' AND ImageId = '2342'

Delete

To delete a product image you must specify its Id.

DELETE FROM ProductImages WHERE ProductId = '123' AND ImageId = '2342'

Columns

Name	Type	ReadOnly	References	Description
ImageId [KEY]	Long	True		A unique numeric identifier for the product image.
ProductId	Long	True	Products.Id	The id of the product associated with the image.
VariantIds	String	False		The array of variant ids associated with the image.
Position	Integer	False		The order of the product image in the list.
FilePath	String	False		Full path to the image. Can be locally or online. Example: http://example.com/rails_logo.gif. Either this or Base64Content are required.
Width	Integer	True		The width of the image.
Height	Integer	True		The height of the image.
CreatedAt	Datetime	True		The datetime when the image was created.
UpdatedAt	Datetime	True		The datetime when the image was updated.
Alt	String	False		The Alt content of the image.
Src	String	False		Specifies the location of the product image. This parameter supports URL filters that you can use to retrieve modified copies of the image.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
FileName	String	The name that the image will be displayed in the application.
Attachment	String	Base64 encoded bytes of the image. Either this or FilePath are required.

Query and delete product listings.

Table-Specific Information

Select

The Sync App uses the Shopify API to process search criteria that refer to the ProductId, UpdatedAt, and Handle columns. The supported SQL operators are '=' and 'IN' for ProductId, '=' for Handle, and '>' for the UpdatedAt column. The Sync App processes other filters client-side within the Sync App. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.

For example, the following queries are processed server side:

SELECT * FROM ProductListings

SELECT * FROM ProductListings WHERE ProductId='123'

SELECT * FROM ProductListings WHERE ProductId IN ('123','456')

SELECT * FROM ProductListings WHERE UpdatedAt>'2018-02-21'

SELECT * FROM ProductListings WHERE Handle='ipod-nano'

Delete

Delete a product listing to unpublish a product from your app (ProductId is required):

DELETE FROM ProductListings WHERE Productid=512433520663

Columns

Name	Type	ReadOnly	References	Description
ProductId [KEY]	Long	False		The unique identifer of the product this listing is for. The primary key for this resource.
Title	String	False		The name of the product.
BodyHtml	String	False		The description of the product, complete with HTML formatting.
Handle	String	True		A human-friendly unique string for the Product automatically generated from its title.
Variants	String	True		A list of variant objects, each one representing a slightly different version of the product.
Images	String	False		A list of image objects, each one representing an image associated with the product.
ProductType	String	False		A categorization that a product can be tagged with, commonly used for filtering and searching.
Options	String	True		Custom product property names like 'Size', 'Color', and 'Material'.
Tags	String	False		A categorization that a product can be tagged with, commonly used for filtering.
Vendor	String	False		The name of the vendor of the product.
PublishedAt	Datetime	True		The date and time when the product was published to the Online Store channel.
CreatedAt	Datetime	True		The date and time when the product was created.
UpdatedAt	Datetime	True		The date and time when the product was last modified.

Create, update, delete, and query products.

Table-Specific Information

Select

The Sync App uses the Shopify API to process search criteria that refer to the following columns: Id, Title, Vendor, Handle, ProductType, PublishedStatus, CreatedAt, PublishedAt, and UpdatedAt. The Sync App processes other filters client-side within the Sync App. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.

For example, the following queries are processed server side.

SELECT * FROM Products

SELECT * FROM Products WHERE Id = '123'

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

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

SELECT * FROM Products WHERE Title = 'just a title'

SELECT * FROM Products WHERE Vendor = 'just a vendor'

Insert

• Create a new product with the default product variant.
  

  INSERT INTO Products(Title, BodyHtml, Vendor, ProductType, Tags) VALUES('Burton Custom Freestyle 151', 'Good snowboard!', 'Burton', 'Snowboard', 'Barnes & Noble, Johns Fav, Big Air')

• Create a new unpublished product.
  

  INSERT INTO Products(Title, BodyHtml, Vendor, ProductType, Tags, Published) VALUES('Burton Custom Freestyle 151', 'Good snowboard!', 'Burton', 'Snowboard', 'Barnes & Noble, Johns Fav, Big Air', false)

Update

To update a product you must specify its Id.

UPDATE Products SET Published=true  WHERE Id='123'

UPDATE Products SET MetafieldsGlobalTitleTag='Brand new title', MetafieldsGlobalDescriptionTag='Brand new description'  WHERE Id='123'

Delete

To delete a product you must specify its Id.

DELETE FROM Products WHERE Id='123'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Long	True		A unique numeric identifier for the product.
BodyHtml	String	False		The description of the product, complete with HTML formatting.
Handle	String	True		A human-friendly unique string for the Product automatically generated from its title.
Images	String	False		A list of image objects.
ProductType	String	False		A categorization that a product can be tagged with, commonly used for filtering and searching.
Options	String	True		Custom product property names.
PublishedScope	String	False		Indicates whether the product is published to the Point of Sale channel.
Tags	String	False		A categorization that a product can be tagged with. Use commas to separate categories.
Status	String	False		The status of the product. The allowed values are active, archived, draft.
TemplateSuffix	String	True		The suffix of the liquid template being used.
Title	String	False		The name of the product.
Vendor	String	False		The name of the vendor of the product.
PublishedAt	Datetime	True		The date and time when the product was published to the Online Store channel.
CreatedAt	Datetime	True		The date and time when the product was created.
UpdatedAt	Datetime	True		The date and time when the product was last modified.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
PublishedStatus	String	Return products by their published status: published: show only published products;unpublished: show only unpublished products; any: show all products.
Published	Boolean	Set to true to publish the product or to false to not publish it
MetafieldsGlobalTitleTag	String	The name of the product, to be used for SEO purposes.
MetafieldsGlobalDescriptionTag	String	The description of the product, to be used for SEO purposes.

Query and delete product listings.

Table-Specific Information

Select

The Sync App uses the Shopify API to process search criteria that refer to the Id and ProductId column. The Sync App processes other filters client-side within the Sync App. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.

For example, the following queries are processed server side.

SELECT * FROM ProductVariants

SELECT * FROM ProductVariants WHERE Id = '123'

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

SELECT * FROM ProductVariants WHERE ProductId = '456'

Insert

You must specify the ProductId to insert a product variant.

INSERT INTO ProductVariants(ProductId, Option1, Price) VALUES('123', 'Yellow', 3.5)

Update

You must specify the Id of the variant to edit a product variant.

• Update the title and price of an existing variant.
  

  UPDATE ProductVariants SET Option1='Pink', Price=99.99  WHERE Id='123'

Delete

You must specify the ProductId and the Id of the variant to delete a product variant.

DELETE FROM ProductVariants WHERE Id='123' AND ProductId='456'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Long	True		A unique numeric identifier for the product variant.
ProductId	Long	False		A unique numeric identifier for the product this variant belongs to.
InventoryItemId	String	False		The unique identifier for the inventory item, which is used in the Inventory API to query for inventory information.
ImageId	Long	False		A unique numeric identifier for the image this variant belongs to.
Price	Decimal	False		The price of the product variant.
CompareAtPrice	String	False		The competitors prices for the same item.
Title	String	False		The name of the product.
Grams	Integer	False		The weight of the product variant in grams.
Barcode	String	False		A human-friendly unique string for the Product automatically generated from its title.
Weight	Double	False		The weight of the product variant in the unit system specified.
WeightUnit	String	True		The unit of measurement that applies to the product's variant weight.
FulfillmentService	String	False		Service which is doing the fulfillment.
InventoryManagement	String	False		Specifies whether or not Shopify tracks the number of items in stock for this product variant.
InventoryPolicy	String	True		Specifies whether or not customers are allowed to place an order for a product variant when it's out of stock. Valid values are deny or continue.
InventoryQuantity	Integer	True		The number of items in stock for this product variant. Default value 1.
Option1	String	False		Custom properties that a shop owner can use to define product variants.
Option2	String	False		Custom properties that a shop owner can use to define product variants.
Option3	String	False		Custom properties that a shop owner can use to define product variants.
Position	Integer	False		The order of the product variant in the list of product variants.
RequiresShipping	Boolean	False		Specifies whether or not a customer needs to provide a shipping address when placing an order for this product variant.
Sku	String	False		A unique identifier for the product in the shop.
Taxable	Boolean	False		Specifies whether or not a tax is charged when the product variant is sold.
CreatedAt	Datetime	True		The date and time when the product variant was created.
UpdatedAt	Datetime	True		The date and time when the product was last modified.

Query and update information regarding different provinces.

Table-Specific Information

Select

The Sync App uses the Shopify API to process search criteria that refer to the ProductId, UpdatedAt, and Handle columns. The supported SQL operators are '=' and 'IN' for ProductId, '=' for Handle, and '>' for the CreatedAt column. The Sync App processes other filters client-side within the Sync App. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria that refers to other columns will cause an error.

For example, the following queries are processed server side:

SELECT * FROM Provinces

SELECT * FROM Provinces WHERE CountryId='123'

Update

You must specify the CountryId and Id to update a province. For example:

UPDATE Provinces Set Tax='3.4' WHERE CountryId='123' AND Id='456'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Long	True		The ID for the province.
CountryId [KEY]	Long	True	Countries.Id	The ID for the country that the province belongs to.
Name	String	False		The full name of the province.
Code	String	False		The standard abbreviation for the province.
Tax	Decimal	False		The sales tax rate to be applied to orders made by customers from this province..
TaxPercentage	Double	False		The province's tax in percent format.
TaxName	String	False		The name of the tax for this province.
TaxType	String	False		The tax type. Valid values: normal, null, or harmonized. If the value is harmonized, then the tax is compounded of the provincial and federal sales taxes.
ShippingZoneId	Long	True	ShippingZones.Id	The ID for the shipping zone that the province belongs to.

Create, update, delete, and query Recurring Application Charges.

Table-Specific Information

Select

The Sync App processes all filters client-side within the Sync App. The following queries are the only ones processed server side:

SELECT * FROM RecurringApplicationCharges

SELECT * FROM RecurringApplicationCharges WHERE Id = '123'

You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.

Update

UPDATE RecurringApplicationCharges SET Test = 'true', Name = 'cd' WHERE Id ='2'

Delete

You must specify the Id of the recurring application charge to delete it.

DELETE From RecurringApplicationCharges WHERE Id ='2'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Long	False		The ID of the recurring application charge.
Name	String	False		The name of the recurring application charge.
ApiClientId	String	False		The API Client Id
Price	Decimal	False		The price of the recurring application charge. The maximum price is 10,000.
Status	String	False		The status of the recurring charge
ReturnUrl	String	False		The URL where the merchant is redirected after accepting the charge.
BillingOn	Date	False		The date and time (ISO 8601 format) when the customer was billed.
Test	String	False		Whether the application charge is a test transaction.
CreatedAt	Datetime	False		The date and time (ISO 8601 format) when the recurring application charge was created.
UpdatedAt	Datetime	False		The date and time (ISO 8601 format) when the recurring application charge was last updated.
ActivatedOn	Datetime	False		The date and time (ISO 8601 format) when the customer activated the recurring application charge.
CancelledOn	Datetime	False		The date and time (ISO 8601 format) when the merchant canceled their recurring application charge.
TrialDays	Integer	False		The number of days that the customer is eligible for a free trial.
TrialEndsOn	Datetime	False		The date and time (ISO 8601 format) when the free trial ends.
DecoratedReturnUrl	String	False		The decorated return url.
CappedAmount	String	False		The limit a customer can be charged for usage based billing. If this property is provided, then you must also provide the terms property.
ConfirmationUrl	String	False		The URL where the merchant accepts or declines the recurring application charge.
Terms	String	False		The terms and conditions of usage based billing charges. Must be present in order to create usage charges,

Create, read, update or delete redirects.

Table-Specific Information

Select

The Sync App processes all filters client-side within the Sync App. The following queries are the only ones processed server side:

SELECT * FROM Redirects

SELECT * FROM Redirects WHERE Id='123'

You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.

Insert

You must specify path and target to create a Redirect.

INSERT INTO Redirects(path,target) VALUES ('/abc.php','/pqr.jsp')

Update

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

UPDATE Redirects SET Path='/abc.php' WHERE Id = '77171130'

Delete

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

DELETE FROM Redirects WHERE Id = '555695'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Long	True		The ID for the redirect.
Path	String	False		The old path to be redirected.
Target	String	False		The target location where the user will be redirected.

Create and query refunds.

Table-Specific Information

Select

The Sync App uses the Shopify API to process search criteria that refer to the OrderId column. The Sync App processes other filters client-side within the Sync App.

For example, the following queries are processed server side. If you specify the unique identifier of the Order, then this view will only list refund information concerning that order.

SELECT * FROM Refunds

SELECT * FROM Refunds WHERE OrderId = '179098550295'

SELECT * FROM Refunds WHERE OrderId='179098550295' AND Id='7382073367'

You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false. When SupportEnhancedSQL is false, you must specify at least OrderId. The Shopify APIs require that queries contain this filter. Search criteria that refers to other columns will cause an error.

Insert

You must specify the OrderId when inserting a refund.

• Calculate a refund for a line item and shipping using aggregates.
  

  INSERT INTO Refunds(OrderId, CalculateRefund, ShippingFullRefund, LineAggregate) VALUES('123', true, true, '[{\"id\":\"123\",\"quantity\":3,\"restock_type\": \"return\",\"location_id\": \"12346\"}]')

• Calculate a refund for a line item and shipping using temporary table.
  

  INSERT INTO OrdersItems#Temp (ItemId, ItemQuantity) VALUES ('123', 3)
  INSERT INTO Refunds(OrderId, CalculateRefund, ShippingFullRefund, LineAggregate) VALUES('123', true, true, 'OrdersItems#Temp')

• Calculate a refund for a partial amount of shipping.
  

  INSERT INTO Refunds(OrderId, CalculateRefund, ShippingAmount) VALUES('123', true, 2.56)

• Create a new refund for an order using aggregates.
  

  INSERT INTO Refunds(OrderId, Restock, Notify, Note, ShippingFullRefund, LineAggregate) VALUES('123', true, true, 'Wrong size', true, '[{\"id\":\"123\",\"quantity\":3,\"restock_type\": \"return\"}]')

• Create a new refund for an order using temporary table.
  

  INSERT INTO OrdersItems#Temp (ItemId, ItemQuantity) VALUES ('123', 3)
  INSERT INTO Refunds(OrderId, Restock, Notify, Note, ShippingFullRefund, LineAggregate) VALUES('123', true, true, 'Wrong size', true, 'OrdersItems#Temp')

• Create a new refund for an order and attach a transaction to the refund as well using aggregates.
  

  INSERT INTO Refunds(OrderId, LineAggregate, OrderTransactionAggregate) VALUES('123', '[{\"id\":\"123\",\"quantity\":3,\"restock_type\": \"return\"}]', '[{\"kind\":\"refund\",\"amount\":5.5,\"gateway\":\"bogus\",\"parent_id\":\"789\"}]')

• Create a new refund for an order and attach a transaction to the refund as well using temporary table.
  

  INSERT INTO OrdersItems#Temp (ItemId, ItemQuantity) VALUES ('123', 3)
  INSERT INTO Transactions#Temp (TransactionItemParentId, TransactionItemAmount, TransactionItemKind, TransactionItemGateway) VALUES ('789', 5.5, 'refund', 'bogus')
  INSERT INTO Refunds(OrderId, LineAggregate, OrderTransactionAggregate) VALUES('123', 'OrdersItems#Temp','Transactions#Temp')

• Refund a specific amount of shipping using aggregates.
  

  INSERT INTO Refunds(OrderId, ShippingAmount, OrderTransactionAggregate) VALUES('123', 5.3, '[{\"parent_id\":\"456\"}]')

• Refund a specific amount of shipping using temporary table.
  

  INSERT INTO Transactions#Temp (TransactionItemParentId) VALUES ('456')
  INSERT INTO Refunds(OrderId, ShippingAmount, OrderTransactionAggregate) VALUES('123', 5.3, 'Transactions#Temp')

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Long	True		The unique identifier for the refund.
OrderId	Long	False	Orders.Id	The id of the order.
OrderUpdatedAt	Datetime	True	Orders.UpdatedAt	The date and time when the order was last modified.
Note	String	False		The optional note attached to a refund.
Restock	Boolean	True		Whether or not the line items were added back to the store inventory.
UserId	Long	True		The unique identifier of the user who performed the refund.
CreatedAt	Datetime	True		The date and time when the refund was created.
ProcessedAt	Datetime	True		The date and time when the refund was imported.
LineAggregate	String	False		A JSON aggregate of line items associated with the refund.
OrderTransactionAggregate	String	False		A JSON aggregate of transactions associated with the refund.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
Notify	Boolean	Whether or not to send a refund notification to the customer.
DiscrepancyReason	String	An optional comment, used if there is a discrepancy between calculated and actual refund amounts (one of: restock, damage, customer, other)
ShippingAmount	Decimal	Set specific amount of shipping to refund. Takes precedence over FullRefund.
ShippingFullRefund	Boolean	Whether or not to to refund all remaining shipping.
CalculateRefund	Boolean	Set to true to simply calculate the refund but not process it The default value is false.

Create, read, update or delete script tags.

Table-Specific Information

Select

The Sync App processes all filters client-side within the Sync App. The following queries are the only ones processed server side:

SELECT * FROM ScriptTags

SELECT * FROM ScriptTags WHERE Id='123'

You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.

Insert

You must specify event and src to create a ScriptTag.

INSERT INTO Scripttags(event,src) VALUES ('onload','https://js-aplenty.com/abc.js')

Update

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

UPDATE ScriptTags SET src='https://js-aplenty.com/abc.js' WHERE Id = '77171130'

Delete

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

DELETE FROM ScriptTags WHERE Id = '555695'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Long	True		The ID for the script tag.
Cache	Boolean	False		Whether the Shopify CDN can cache and serve the script tag.
CreatedAt	Datetime	True		The date and time (ISO 8601) when the script tag was created.
DisplayScope	String	False		The page or pages on the online store where the script should be included. The allowed values are online_store, order_status, all.
Event	String	False		The DOM event that triggers the loading of the script. Valid values: onload.
Src	String	False		The URL of the remote script.
UpdatedAt	Datetime	True		The date and time (ISO 8601) when the script tag was last updated.