Netsuite, a powerful cloud-based business management suite, offers various methods for running complex SQL queries to retrieve, filter, and sort data. In this guide, we'll explore different approaches, including using the Apideck Proxy API, and Netsuite's native support for SuiteScript and REST Query API.
The Apideck Proxy API enables you to execute direct requests against the Netsuite REST API, providing a flexible and direct way to access and manipulate data according to your specific requirements.
Let's take the example to query the customers from the Netsuite REST API:
GET https://1234.suitetalk.api.netsuite.com/services/rest/record/v1/customer
That would be translated into a CURL request towards the Proxy API where the Netsuite REST API endpoint would be passed as the x-apideck-downstream-url header.
curl --location
--request GET 'https://unify.apideck.com/proxy' \
--header 'x-apideck-consumer-id: YOUR_CONSUMER' \
--header 'x-apideck-app-id: YOUR_APP_ID' \
--header 'x-apideck-service-id: netsuite' \
--header 'x-apideck-downstream-url: https://{account_id}.suitetalk.api.netsuite.com/services/rest/record/v1/customer' \
--header 'accept: application/json' \
--header 'prefer: transient' \
--header 'Authorization: YOUR_API_KEY'
Unify will inject the {account_id} variable in the API endpoint URL based on the connection configured by the consumer.
Netsuite also supports a REST Query API that allows users to execute SQL SELECT queries against the Netsuite instance. This API supports a SQL-like syntax, making it easier for users familiar with SQL to seamlessly integrate their querying skills into the Netsuite environment.
Let's take the same example to query the customers by dateCreated, which would result in a SQL query like SELECT * FROM customer ORDER BY dateCreated DESC
The CURL request towards the Netsuite REST Query API using the Proxy API, would look like this:
curl --location
--request POST 'https://unify.apideck.com/proxy' \
--header 'x-apideck-consumer-id: YOUR_CONSUMER' \
--header 'x-apideck-app-id: YOUR_APP_ID' \
--header 'x-apideck-service-id: netsuite' \
--header 'x-apideck-downstream-url: https://{account_id}.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql?offset=0&limit=5' \
--header 'accept: application/json' \
--header 'prefer: transient' \
--header 'Authorization: YOUR_API_KEY' \
--data-raw '{
"q": "SELECT * FROM customer ORDER BY dateCreated DESC"
}'
The same endpoint also supports SQL queries with linking tables using JOIN.
Example to pair Sales Orders With Customers: SELECT c.email AS email, c.companyName AS company, t.tranId AS document, t.tranDate AS date FROM customer c, transaction t WHERE t.entity = c.id AND t.type = 'SalesOrd'
Proxy API request:
curl --location
--request POST 'https://unify.apideck.com/proxy' \
--header 'x-apideck-consumer-id: YOUR_CONSUMER' \
--header 'x-apideck-app-id: YOUR_APP_ID' \
--header 'x-apideck-service-id: netsuite' \
--header 'x-apideck-downstream-url: https://{account_id}.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql?offset=0&limit=5' \
--header 'accept: application/json' \
--header 'prefer: transient' \
--header 'Authorization: YOUR_API_KEY' \
--data-raw '{
"q": "SELECT c.email AS email, c.companyName AS company, t.tranId AS document, t.tranDate AS date FROM customer c, transaction t WHERE t.entity = c.id AND t.type = 'SalesOrd'"
}'