Week 4 • Module 7

N/query Module (SuiteQL)

SQL-like queries for efficient data retrieval in NetSuite

🗃️ What is SuiteQL?

SuiteQL is NetSuite's SQL-like query language. If you know SQL, you can write SuiteQL. It provides a more familiar syntax for developers coming from database backgrounds and often performs better than traditional searches for complex queries.

💡 SuiteQL vs N/search

SuiteQL uses SQL syntax (SELECT, FROM, WHERE, JOIN). N/search uses NetSuite's proprietary filter/column objects. Both retrieve data—choose based on your comfort level and query complexity.

📚 The Records Catalog

Before writing queries, you need to know table and column names. The Records Catalog is your schema browser:

Navigation: Setup → Records Catalog

Search for record types (e.g., "Customer", "Sales Order") to find:

✅ Pro Tip

Bookmark the Records Catalog—you'll reference it constantly when writing SuiteQL queries.

🔧 Basic Query Syntax

runSuiteQL()

Execute a query and get up to 5,000 results:

const query = require('N/query');

const results = query.runSuiteQL({
    query: `
        SELECT id, companyname, email
        FROM customer
        WHERE isinactive = 'F'
    `
});

// Get results as an array of objects
const customers = results.asMappedResults();
customers.forEach(customer => {
    log.audit('Customer', customer.companyname);
});

Result Limit

Method Max Results Use Case
runSuiteQL() 5,000 Simple queries, smaller datasets
runSuiteQLPaged() Unlimited (1,000/page) Large datasets, batch processing

🔗 Joins

Link related tables using implicit inner joins:

const results = query.runSuiteQL({
    query: `
        SELECT 
            c.companyname,
            c.email,
            cur.name AS currency_name
        FROM customer c
        INNER JOIN currency cur ON c.currency = cur.id
        WHERE c.isinactive = 'F'
    `
});

⚠️ Join Syntax

Use table aliases (c, cur) for readability. The Records Catalog shows which fields can be joined to other tables.

📊 Pagination with runSuiteQLPaged()

For large datasets, use pagination:

const pagedQuery = query.runSuiteQLPaged({
    query: `SELECT id, companyname FROM customer`,
    pageSize: 1000
});

// Iterate through pages
pagedQuery.pageRanges.forEach(pageRange => {
    const page = pagedQuery.fetch({ index: pageRange.index });
    const results = page.data.asMappedResults();
    
    results.forEach(row => {
        // Process each row
    });
});

🔄 Processing Results

asMappedResults()

Returns an array of objects with column names as keys:

const results = query.runSuiteQL({...}).asMappedResults();
// [ { id: 1, companyname: 'Acme', email: 'acme@example.com' }, ... ]

Iterator Pattern

Process results one at a time (memory efficient):

const resultSet = query.runSuiteQL({...});
const iterator = resultSet.iterator();

iterator.each(result => {
    const values = result.value.values;
    log.audit('Row', values);
    return true; // Continue iterating
});

📖 Finding This in the Docs

To look up N/query and SuiteQL:

  1. Go to docs.oracle.com → SuiteScript 2.x Modules
  2. Click on N/query Module
  3. Review runSuiteQL() and runSuiteQLPaged() methods

Key pages to bookmark:

🎯 Key Takeaways