NetSuite SuiteQL Capabilities

TL;DR

• Bottom line: SuiteQL is NetSuite's SQL-92/Oracle SQL query language available via N/query module (SuiteScript), REST Web Services, and SuiteAnalytics Connect — supports SELECT, JOIN, GROUP BY, HAVING, UNION, and subqueries. [src1]
• Key limit: 100,000 row ceiling via REST Web Services; 5,000 rows per runSuiteQL() call; 1,000 rows per page for paged queries. [src2, src6]
• Watch out for: Cannot mix SQL-92 and Oracle SQL syntax in the same query — always use Oracle SQL syntax to avoid critical performance issues and timeouts. [src4]
• Best for: Complex ad-hoc queries, multi-table JOINs, aggregations, and analytics that saved searches cannot handle. [src7]
• Authentication: Token-Based Authentication (TBA) or OAuth 2.0 for REST API; SuiteScript runs in authenticated context automatically. OAuth 2.0 is now the recommended flow. [src2]

System Profile

SuiteQL is Oracle NetSuite's SQL-based query language, introduced in 2020.1. It queries the SuiteAnalytics data source — the same data exposed in SuiteAnalytics Workbook — with role-based access restrictions enforced automatically. SuiteQL supports both SQL-92 and Oracle SQL syntax (but not mixed in a single query) and is available across all NetSuite editions that have SuiteCloud enabled. SuiteQL is read-only — it queries records but cannot insert, update, or delete them. [src1, src5]

API Surfaces & Capabilities

SuiteQL is accessible through three primary interfaces, each with different record limits and pagination behavior. [src1, src2, src5, src6]

Rate Limits & Quotas

Per-Request Limits

Rolling / Daily Limits

Transaction / Governor Limits

NetSuite SuiteScript governance applies to SuiteQL when executed via the N/query module. Each script type has a total governance budget. [src6]

Authentication

Authentication Gotchas

• TBA tokens are role-scoped — the integration's permissions are determined by the role assigned to the token, not the user. Always create a dedicated integration role with minimal permissions. [src2]
• SuiteQL enforces SuiteAnalytics Workbook role-based access, not record-level security — a role with access to the Customer record type sees ALL customers, regardless of subsidiary restrictions on saved searches. [src1]
• REST API requests require the Prefer: transient header — omitting it returns a 400 error with no useful message. [src2]

Constraints

• 100,000 row ceiling on REST Web Services — queries exceeding this return only the first 100K rows with no error or warning. Use SuiteAnalytics Connect for larger datasets. [src2]
• 5,000 record maximum per runSuiteQL() call — no built-in pagination; switch to runSuiteQLPaged() for larger result sets. [src6]
• SuiteQL is read-only — cannot INSERT, UPDATE, or DELETE records. Use N/record or SuiteTalk REST for write operations. [src1]
• Cannot mix SQL-92 and Oracle SQL syntax in the same query — using SQL-92 risks critical performance issues and timeouts. [src4]
• Custom fields in Analytic APIs are not supported for listing application data. [src4]
• Square bracket notation [ ] is not supported in SuiteQL syntax. [src4]
• 10 governance units per query execution in SuiteScript — budget carefully in User Event scripts (1,000 unit budget). [src6]

Integration Pattern Decision Tree

START — User needs to query NetSuite data
├── Where is the query executed?
│   ├── Inside NetSuite (SuiteScript)
│   │   ├── Result set < 5,000 rows?
│   │   │   ├── YES → query.runSuiteQL() — simplest, 10 governance units
│   │   │   └── NO → query.runSuiteQLPaged() — 1,000 rows/page, iterate
│   │   └── Need complex SQL (JOINs, subqueries, UNION)?
│   │       ├── YES → SuiteQL (this card)
│   │       └── NO, simple field lookups → search.lookupFields (5 units, faster)
│   ├── External system (middleware, app)
│   │   ├── Result set < 100,000 rows?
│   │   │   ├── YES → SuiteTalk REST /query/v1/suiteql — POST, paginate
│   │   │   └── NO → SuiteAnalytics Connect (JDBC/ODBC) — no row ceiling
│   │   └── Need real-time results?
│   │       ├── YES → REST API (sub-second for simple queries)
│   │       └── NO → SuiteAnalytics Connect (better for bulk)
│   └── BI/Reporting tool
│       └── SuiteAnalytics Connect → JDBC/ODBC driver, unlimited rows
├── What SQL features are needed?
│   ├── JOINs across multiple record types → SuiteQL
│   ├── UNION queries → SuiteQL only (not in saved searches)
│   ├── Subqueries → SuiteQL only
│   ├── GROUP BY + HAVING → SuiteQL
│   └── Simple filters + sort → Saved search may suffice
└── Need to write data?
    ├── YES → Use SuiteTalk REST/SOAP or N/record module
    └── NO → SuiteQL is the right choice

Quick Reference

SuiteQL Supported SQL Syntax

Step-by-Step Integration Guide

1. Execute a SuiteQL query via SuiteScript (N/query module)

Use query.runSuiteQL() for result sets under 5,000 rows. This is the simplest approach for in-script data access. [src5, src6]

/**
 * @NApiVersion 2.1
 * @NScriptType ScheduledScript
 */
define(['N/query', 'N/log'], (query, log) => {
    const execute = (context) => {
        const results = query.runSuiteQL({
            query: `
                SELECT id, companyname, email, datecreated
                FROM customer
                WHERE isinactive = 'F'
                ORDER BY datecreated DESC
            `
        });
        const customers = results.asMappedResults();
        log.debug('Customer count', customers.length);

        // Parameterized query — prevents SQL injection
        const filtered = query.runSuiteQL({
            query: `
                SELECT id, companyname FROM customer
                WHERE subsidiary = ? AND datecreated > TO_DATE(?, 'YYYY-MM-DD')
            `,
            params: [2, '2025-01-01']
        });
        log.debug('Filtered count', filtered.asMappedResults().length);
    };
    return { execute };
});

Verify: Check script execution log → expected: Customer count: [N]

2. Handle pagination with runSuiteQLPaged()

For result sets exceeding 5,000 rows, use paged execution with page sizes between 5 and 1,000. [src5]

/**
 * @NApiVersion 2.1
 * @NScriptType MapReduceScript
 */
define(['N/query', 'N/log'], (query, log) => {
    const getInputData = (context) => {
        const pagedData = query.runSuiteQLPaged({
            query: `
                SELECT t.id, t.tranid, t.trandate, tl.item, tl.amount
                FROM transaction t
                INNER JOIN transactionLine tl ON t.id = tl.transaction
                WHERE t.type = 'SalesOrd'
                  AND t.trandate >= TO_DATE('2025-01-01', 'YYYY-MM-DD')
                ORDER BY t.trandate DESC
            `,
            pageSize: 1000
        });
        const allResults = [];
        pagedData.pageRanges.forEach((pageRange) => {
            const page = pagedData.fetch({ index: pageRange.index });
            allResults.push(...page.data.asMappedResults());
        });
        return allResults;
    };
    const map = (context) => { /* process each row */ };
    const summarize = (summary) => { /* log results */ };
    return { getInputData, map, summarize };
});

Verify: Map/Reduce deployment log → expected: Total rows: [N]

3. Query via REST Web Services (external integration)

POST to /services/rest/query/v1/suiteql with limit and offset URL parameters. The Prefer: transient header is required. [src2]

curl -X POST \
  'https://ACCOUNT_ID.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql?limit=1000&offset=0' \
  -H 'Content-Type: application/json' \
  -H 'Prefer: transient' \
  -H 'Authorization: OAuth realm="ACCOUNT_ID",oauth_consumer_key="...",oauth_token="...",...' \
  -d '{"q": "SELECT id, companyname, email FROM customer WHERE isinactive = '\''F'\'' ORDER BY id"}'

Verify: Response contains totalResults, hasMore, and items array

4. Paginate REST results beyond 1,000 records

Loop through pages using offset until hasMore is false or you hit the 100K ceiling. [src2, src8]

import requests
from requests_oauthlib import OAuth1

auth = OAuth1(client_key='KEY', client_secret='SECRET',
              resource_owner_key='TOKEN', resource_owner_secret='TOKEN_SECRET',
              realm='ACCOUNT_ID', signature_method='HMAC-SHA256')
base_url = 'https://ACCOUNT_ID.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql'
sql = "SELECT id, companyname FROM customer WHERE isinactive = 'F' ORDER BY id"

all_records, offset, limit = [], 0, 1000
while True:
    resp = requests.post(f'{base_url}?limit={limit}&offset={offset}',
                         json={'q': sql}, auth=auth,
                         headers={'Prefer': 'transient', 'Content-Type': 'application/json'})
    resp.raise_for_status()
    data = resp.json()
    all_records.extend(data['items'])
    if not data.get('hasMore', False) or offset >= 100000:
        break
    offset += limit

Verify: len(all_records) matches totalResults (if under 100K)

Code Examples

Python: Paginated SuiteQL via REST API

# Input:  OAuth 1.0 credentials, SuiteQL query string
# Output: All matching records as list of dicts (up to 100K)

import requests  # requests==2.31.0
from requests_oauthlib import OAuth1  # requests-oauthlib==1.3.1

def run_suiteql(account_id, auth, query, max_rows=100000):
    """Execute SuiteQL via REST with automatic pagination."""
    url = f'https://{account_id}.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql'
    headers = {'Prefer': 'transient', 'Content-Type': 'application/json'}
    results, offset, limit = [], 0, 1000
    while offset < max_rows:
        resp = requests.post(f'{url}?limit={limit}&offset={offset}',
                             json={'q': query}, auth=auth, headers=headers)
        resp.raise_for_status()
        data = resp.json()
        results.extend(data['items'])
        if not data.get('hasMore', False):
            break
        offset += limit
    return results

JavaScript/Node.js: SuiteQL in SuiteScript 2.1

// Input:  SuiteQL query string
// Output: Array of result objects

define(['N/query'], (query) => {
    function runSuiteQLAll(sql, params) {
        const pagedData = query.runSuiteQLPaged({
            query: sql, params: params || [], pageSize: 1000
        });
        const results = [];
        pagedData.pageRanges.forEach((range) => {
            results.push(...pagedData.fetch({ index: range.index }).data.asMappedResults());
        });
        return results;
    }
    return { runSuiteQLAll };
});

cURL: Quick SuiteQL test via REST

# Input:  NetSuite account ID, TBA credentials configured
# Output: JSON array of first 10 customers

curl -X POST \
  'https://ACCOUNT_ID.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql?limit=10' \
  -H 'Content-Type: application/json' \
  -H 'Prefer: transient' \
  -H 'Authorization: OAuth realm="ACCOUNT_ID",...' \
  -d '{"q": "SELECT TOP 10 id, companyname, email FROM customer"}'

Data Mapping

SuiteQL Record and Field Name Reference

Data Type Gotchas

• Boolean fields use 'T' and 'F' strings, not true/false — standard SQL boolean literals do not work. [src7]
• Date fields require Oracle SQL date functions: TO_DATE('2025-01-01', 'YYYY-MM-DD'), not ANSI date literals. [src3]
• Column aliases in asMappedResults() are always returned in lowercase — SELECT id AS myID returns myid. [src4]
• Record type and field name casing may change between NetSuite releases — always normalize to uppercase or lowercase. [src4]
• Numeric IDs are returned as numbers via SuiteScript but as strings via REST API — type-check before comparison. [src2]

Error Handling & Failure Points

Common Error Codes

Failure Points in Production

• Silent 100K truncation: REST queries returning >100K rows silently return only the first 100K with no error flag. Fix: Check totalResults in first response; switch to SuiteAnalytics Connect if >100K. [src2]
• Timeout on complex joins: Multi-table JOINs with SQL-92 syntax can cause unrecoverable timeouts. Fix: Use Oracle SQL syntax; add ROWNUM or TOP N limits during development. [src4]
• Alias casing breaks code: asMappedResults() lowercases all aliases. Fix: Always reference result fields in lowercase. [src4]
• TransactionStatus query anomalies: Using = 'CuTrSale' on TranType returns unexpected results. Fix: Use LIKE: WHERE TranType LIKE 'CuTrSale%'. [src4]
• CLOB field sort ordering: Sorting on large text fields only compares first 250 characters. Fix: Sort on a non-CLOB field or truncate in subquery. [src4]

Anti-Patterns

Wrong: Fetching all records to filter in code

// BAD — wastes governance units and memory
const all = query.runSuiteQL({ query: 'SELECT * FROM customer' });
const active = all.asMappedResults().filter(c => c.isinactive === 'F');

Correct: Filter in the query WHERE clause

// GOOD — database does the filtering, returns fewer rows
const active = query.runSuiteQL({
    query: "SELECT id, companyname, email FROM customer WHERE isinactive = 'F'"
});

Wrong: Using runSuiteQL() for large result sets

// BAD — silently truncates at 5,000 rows with no error
const results = query.runSuiteQL({
    query: 'SELECT id, tranid, amount FROM transaction ORDER BY trandate DESC'
});
// results.asMappedResults() has at most 5,000 rows — data loss!

Correct: Use runSuiteQLPaged() for large sets

// GOOD — iterates all pages, no truncation
const paged = query.runSuiteQLPaged({
    query: 'SELECT id, tranid, amount FROM transaction ORDER BY trandate DESC',
    pageSize: 1000
});
const results = [];
paged.pageRanges.forEach(range => {
    results.push(...paged.fetch({ index: range.index }).data.asMappedResults());
});

Wrong: Using SQL-92 syntax with complex queries

-- BAD — SQL-92 JOIN syntax risks performance issues and timeouts
SELECT t.id, c.companyname
FROM transaction t, customer c
WHERE t.entity = c.id AND t.trandate > DATE '2025-01-01'

Correct: Use Oracle SQL syntax consistently

-- GOOD — Oracle syntax avoids performance issues
SELECT t.id, c.companyname
FROM transaction t
INNER JOIN customer c ON t.entity = c.id
WHERE t.trandate > TO_DATE('2025-01-01', 'YYYY-MM-DD')

Common Pitfalls

• runSuiteQL() 5K silent truncation: Returns at most 5,000 rows with no error. Fix: Always use runSuiteQLPaged() for any query that might exceed 5K rows. [src6]
• Governance budget exhaustion in loops: Running SuiteQL inside a for-loop burns 10 units per iteration. Fix: Write one query with IN clause or JOIN instead of N queries in a loop. [src6]
• REST 100K ceiling with no warning: The limit applies silently. Fix: Always check totalResults; use SuiteAnalytics Connect for >100K datasets. [src2]
• Mixed SQL syntax errors: Mixing TO_DATE() (Oracle) with DATE '...' (SQL-92) causes parse errors. Fix: Standardize on Oracle SQL syntax. [src4]
• Field name discovery: SuiteQL field names differ from UI labels. Fix: Use Records Browser or SELECT * FROM {recordType} WHERE ROWNUM <= 1. [src7]
• Role permission gaps: SuiteQL enforces SuiteAnalytics Workbook permissions, which differ from saved search permissions. Fix: Verify role has SuiteAnalytics Workbook permission enabled. [src1]

Diagnostic Commands

# Test SuiteQL connectivity via REST
curl -X POST \
  'https://ACCOUNT_ID.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql?limit=1' \
  -H 'Content-Type: application/json' -H 'Prefer: transient' \
  -H 'Authorization: OAuth ...' \
  -d '{"q": "SELECT TOP 1 id, companyname FROM customer"}'

# Verify field names for a record type
curl -X POST \
  'https://ACCOUNT_ID.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql?limit=1' \
  -H 'Content-Type: application/json' -H 'Prefer: transient' \
  -H 'Authorization: OAuth ...' \
  -d '{"q": "SELECT * FROM customer WHERE ROWNUM <= 1"}'

# Count total records (check for 100K ceiling risk)
curl -X POST \
  'https://ACCOUNT_ID.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql' \
  -H 'Content-Type: application/json' -H 'Prefer: transient' \
  -H 'Authorization: OAuth ...' \
  -d '{"q": "SELECT COUNT(*) as total FROM transaction"}'

Version History & Compatibility

Deprecation Policy

Oracle NetSuite follows a cloud-only release model with biannual major releases (YYYY.1 and YYYY.2). SuiteQL is the strategic query interface — Oracle has not announced any deprecation timeline. Saved searches remain supported but new features (UNION, complex subqueries) are SuiteQL-only. The NetSuite.com SuiteAnalytics data source was removed in 2026.1 — all SuiteQL queries now run against the NetSuite2.com data source, which has renamed some tables and fields (e.g., standalone Invoice table deprecated in favor of unified Transactions table). [src1, src7]

When to Use / When Not to Use

Important Caveats

• SuiteQL result field casing may change between NetSuite releases — never rely on exact casing; normalize before comparison. [src4]
• The 100K row ceiling is absolute for REST Web Services and cannot be raised by contacting NetSuite support. SuiteAnalytics Connect is the only workaround. [src2]
• SuiteQL governance cost (10 units) is higher than search.lookupFields (5 units) — for simple lookups, saved search APIs are more efficient. [src6, src7]
• Oracle recommends Oracle SQL syntax to avoid "critical performance issues" — this is in the official documentation. [src4]
• SuiteQL queries the SuiteAnalytics data source, which has a slight delay compared to live data. For time-critical reads after a write, use N/record.load() instead. [src1]
• The NetSuite.com data source was removed in 2026.1 — all SuiteQL now uses NetSuite2.com exclusively. Some record types and field names changed (e.g., standalone Invoice table merged into Transactions, TimeEntry moved to TimeBill). Review and test all queries after upgrades.
• OAuth 2.0 is now the recommended authentication flow for REST Web Services; TBA (OAuth 1.0) remains supported but is no longer the default recommendation for new integrations.