Business systems

NetSuite REST web services and SuiteQL

How NetSuite's REST API works and how to test it — account IDs and sandboxes, token-based and OAuth 2.0 sign-in, records and the metadata catalog, SuiteQL queries and their pages, errors, and the concurrency limit that load tests run into.

NetSuite runs a company's accounts, orders, stock and customers. Online shops, payment tools, warehouses and reporting tools all read and write it through SuiteTalk REST web services. To test one of those integrations, you need to know how NetSuite addresses an account, how it signs in, its two ways of reading (records and SuiteQL), and its concurrency limit.

One account, one address

Every NetSuite customer has an account ID, such as 1234567, and a sandbox of it has a suffix, 1234567_SB1. The API address is built from it, in lower case, with a hyphen for the underscore:

https://1234567.suitetalk.api.netsuite.com/services/rest/        production
https://1234567-sb1.suitetalk.api.netsuite.com/services/rest/    its first sandbox

A test that points at the wrong one is a classic, and expensive, mistake. Put the account address in an environment, and keep production's in an environment that nobody runs by accident.

Signing in

An administrator turns on REST Web Services (and Token-Based Authentication) under Setup → Company → Enable Features → SuiteCloud, and creates an integration record. It gives a consumer key and secret, shown once. Then there are two ways to sign in:

  • Token-based authentication (TBA): OAuth 1.0. Each request is signed with HMAC-SHA256 using four values: the integration's consumer key and secret, and an access token ID and secret made for a user and a role. Nothing is exchanged first, so every request carries its own signature. The realm is the account ID.
  • OAuth 2.0: either authorization code (a person signs in through a browser) or client credentials, where the integration signs a short token with a certificate you upload to NetSuite. The token address is …/services/rest/auth/oauth2/v1/token, and you then send Authorization: Bearer ….

Whichever you use, the role decides what the integration may see and do. A test user with the Administrator role hides permission problems that the real role will hit.

Records, and the metadata catalog

Each kind of thing (customer, salesOrder, invoice, employee, custom records) is a record type, under /services/rest/record/v1/:

RequestGives
GET …/record/v1/metadata-catalogthe record types the account offers
GET …/record/v1/metadata-catalog/customera type's fields, with Accept: application/schema+json
GET …/record/v1/customer/107one record, by its internal ID
POST …/record/v1/customercreate: 204, and the new record's address in the Location header
PATCH …/record/v1/customer/107change fields: 204
DELETE …/record/v1/customer/107delete: 204

A create answers with no body. The new ID is at the end of the Location header, so a test that creates a record and reads it back takes the ID from there (Chaining shows how to save it).

SuiteQL: reading many records

Reading lists uses SuiteQL, SQL over NetSuite's tables, sent as a POST:

POST …/services/rest/query/v1/suiteql?limit=100&offset=0
Prefer: transient
Content-Type: application/json

{ "q": "SELECT id, companyname, balance FROM customer WHERE isinactive = 'F' ORDER BY companyname" }
{ "count": 100, "hasMore": true, "offset": 0, "totalResults": 4512,
  "items": [ { "links": [], "id": "107", "companyname": "Northwind Traders", "balance": "1250" } ] }

The header Prefer: transient is required. A few things surprise people:

  • Values come back as text: "balance": "1250", not 1250. A check that a balance is a number fails, so check that it's text you can read as a number, or compare it as text.
  • Check boxes are 'T' and 'F', not true and false.
  • Dates come in your account's date format, such as 3/14/2024. In a query, write them with TO_DATE('2024-03-14', 'YYYY-MM-DD').
  • A lookup holds an ID: a customer's salesrep is the employee's internal ID. BUILTIN.DF(salesrep) gives the name it shows on screen.

Pages

limit is at most 1,000 a page. While hasMore is true, ask again with offset moved on by limit (it must be a multiple of limit), until you've read totalResults. One SuiteQL query can read at most 100,000 rows this way. For more, split the query, for example by date.

When it goes wrong

Errors follow one shape: what went wrong, and a code, in o:errorDetails:

{ "type": "https://www.rfc-editor.org/rfc/rfc9110.html", "title": "Bad Request", "status": 400,
  "o:errorDetails": [ { "detail": "Invalid search query. … Field 'companynam' for record 'customer' was not found.", "o:errorCode": "…" } ] }
StatusUsually means
400a mistake in the SuiteQL, a field that doesn't exist, or a value in the wrong format
401the signature, token or realm is wrong. For TBA, also check the computer's clock: signatures carry a time
403the role isn't allowed this record or action
404wrong record type or internal ID
429too many requests at once: see the next section

The concurrency limit

NetSuite doesn't count requests per day. It limits how many may run at the same time for the whole account, shared by every integration. The limit depends on the account's service tier and SuiteCloud Plus licenses. Go over it and requests fail straight away with 429.

That changes how you load-test NetSuite:

  • A performance test with 50 simulated users doesn't test NetSuite: most requests get 429 and the report measures the queue. Test with about as many users as the integration really runs at once, and check what the account's limit is first.
  • The other integrations share the limit: a test in production can make the company's real orders fail. Use a sandbox, with the owner's agreement.
  • Test the integration's retry: when it gets 429, it should wait and try again, not lose the order.

What to test

  • Sign-in with the real role, and a 401 with a wrong token.
  • Your SuiteQL: the fields the integration reads exist and come back in the format it parses (text, 'T'/'F', your date format).
  • Paging: with more rows than limit, the integration follows hasMore and offset to the end.
  • Creates: the new ID is read from Location, and the record reads back with the values sent.
  • Behavior at the concurrency limit: 429, then a successful retry.

Key takeaways

  • The account ID is in the address; a sandbox is 1234567-sb1. Keep production in its own environment.
  • Sign in with TBA (OAuth 1.0 signatures) or OAuth 2.0, as a role like the integration's own.
  • Read lists with SuiteQL: POST, Prefer: transient, at most 1,000 rows a page. Values are text, check boxes 'T'/'F'.
  • NetSuite limits requests at the same time, not per day: 429 means wait and retry, and load tests must stay under the account's limit.

In the app: Connect a NetSuite account and browse it · Templates · Chaining: save the new ID

NetSuite is a trademark of Oracle. APITestCenter works with its API and isn't endorsed by Oracle.

Next: The SAP SuccessFactors OData API

A question these lessons don’t answer? Write to support@apitestcenter.com — we add lessons for the questions people ask.