Skip to main content
All CollectionsApideck Unify
Netsuite: How to execute complex SQL queries using the Proxy API?
Netsuite: How to execute complex SQL queries using the Proxy API?
Updated over a week ago

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'"
}'
Did this answer your question?