Netsuite: How to execute complex SQL queries using the Proxy API?
NetSuite offers several methods for running complex SQL queries to retrieve, filter, and sort data. This guide covers how to use the Apideck Proxy API to execute direct requests against the NetSuite REST API and REST Query API (SuiteQL).
Querying records via the REST 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.
For example, to query customers from the NetSuite REST API:
GET https://1234.suitetalk.api.netsuite.com/services/rest/record/v1/customer
That translates into the following Proxy API request, where the NetSuite endpoint is 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'
Note: Unify will automatically inject the
{account_id}variable in the downstream URL based on the connection configured by the consumer.
Querying data using SuiteQL
NetSuite's REST Query API allows you to execute SQL SELECT queries against your NetSuite instance using a SQL-like syntax (SuiteQL).
Example: Simple query
To query customers ordered by date created:
SELECT * FROM customer ORDER BY dateCreated DESC
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 * FROM customer ORDER BY dateCreated DESC"
}'
Example: JOIN query
SuiteQL also supports JOIN queries for linking tables. 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'\''"
}'