N/query Module

Complete guide to executing SuiteQL queries in SuiteScript using the N/query module.

---

Module Overview

The N/query module provides methods to run SuiteQL queries and process results in SuiteScript.

/**
 * @NApiVersion 2.1
 */
define(['N/query'], (query) => {
    // query module is now available
});

---

Core Methods

Method	Description	Returns
query.runSuiteQL()	Execute SuiteQL query	ResultSet
query.runSuiteQLPaged()	Execute with paging	PagedData
query.create()	Build query programmatically	Query object

---

runSuiteQL: Basic Usage

Simple Query

/**
 * @NApiVersion 2.1
 * @NScriptType Suitelet
 */
define(['N/query'], (query) => {

    const onRequest = (context) => {
        // Execute simple query
        const results = query.runSuiteQL({
            query: `
                SELECT id, companyname, email
                FROM customer
                WHERE isinactive = 'F'
                LIMIT 10
            `
        });

        // Get all results as array of objects
        const customers = results.asMappedResults();

        customers.forEach(cust => {
            log.debug('Customer', `${cust.companyname} - ${cust.email}`);
        });
    };

    return { onRequest };
});

Result Processing Methods

const resultSet = query.runSuiteQL({ query: sqlString });

// Method 1: As mapped results (array of objects)
const mapped = resultSet.asMappedResults();
// [ { id: 1, companyname: 'Acme', email: 'a@a.com' }, ... ]

// Method 2: Iterate with each()
resultSet.each(result => {
    log.debug('Row', result);
    return true; // Continue iteration
});

// Method 3: Get column metadata
const columns = resultSet.columns;
columns.forEach(col => {
    log.debug('Column', `${col.name} (${col.type})`);
});

---

Parameterized Queries

Always use parameters to prevent SQL injection.

Test Case: Safe Parameter Usage

/**
 * Search customers by name and state
 */
const searchCustomers = (searchName, stateCode) => {
    // CORRECT: Use parameterized query
    const results = query.runSuiteQL({
        query: `
            SELECT id, companyname, email, state
            FROM customer
            WHERE companyname LIKE ?
              AND state = ?
              AND isinactive = 'F'
            ORDER BY companyname
        `,
        params: [`%${searchName}%`, stateCode]
    });

    return results.asMappedResults();
};

// Usage
const caCustomers = searchCustomers('Tech', 'CA');

Multiple Parameters Example

/**
 * Get transactions in date range for specific customer
 */
const getCustomerOrders = (customerId, startDate, endDate, minAmount) => {
    const results = query.runSuiteQL({
        query: `
            SELECT
                t.id,
                t.tranid,
                t.trandate,
                t.amount,
                t.status
            FROM transaction t
            WHERE t.entity = ?
              AND t.type = 'SalesOrd'
              AND t.trandate BETWEEN TO_DATE(?, 'YYYY-MM-DD')
                                 AND TO_DATE(?, 'YYYY-MM-DD')
              AND t.amount >= ?
            ORDER BY t.trandate DESC
        `,
        params: [customerId, startDate, endDate, minAmount]
    });

    return results.asMappedResults();
};

// Usage
const orders = getCustomerOrders(123, '2024-01-01', '2024-12-31', 1000);

---

Pagination with runSuiteQLPaged

For large result sets, use paged queries to manage memory and governance.

Test Case: Process Large Dataset

/**
 * Process all customers in batches
 */
const processAllCustomers = () => {
    const pagedData = query.runSuiteQLPaged({
        query: `
            SELECT id, companyname, email, balance
            FROM customer
            WHERE isinactive = 'F'
            ORDER BY id
        `,
        pageSize: 100  // Process 100 at a time
    });

    log.audit('Total', `${pagedData.count} customers to process`);

    let processed = 0;

    // Iterate through pages
    pagedData.pageRanges.forEach(pageRange => {
        const page = pagedData.fetch({ index: pageRange.index });

        page.data.asMappedResults().forEach(customer => {
            // Process each customer
            processCustomer(customer);
            processed++;
        });

        log.debug('Progress', `Processed ${processed} of ${pagedData.count}`);
    });

    return processed;
};

Paged Query with Iterator

/**
 * Alternative: Use iterator pattern
 */
const processWithIterator = () => {
    const pagedData = query.runSuiteQLPaged({
        query: `SELECT id, companyname FROM customer WHERE isinactive = 'F'`,
        pageSize: 50
    });

    const pageIterator = pagedData.iterator();

    let pageNum = 0;
    while (pageIterator.hasNext()) {
        const page = pageIterator.next();
        pageNum++;

        log.debug('Page', `Processing page ${pageNum}`);

        page.value.data.asMappedResults().forEach(row => {
            // Process row
        });
    }
};

---

Error Handling

Test Case: Robust Error Handling

/**
 * Execute query with proper error handling
 */
const safeQuery = (sqlQuery, params = []) => {
    try {
        const results = query.runSuiteQL({
            query: sqlQuery,
            params: params
        });

        return {
            success: true,
            data: results.asMappedResults()
        };

    } catch (e) {
        log.error('Query Error', {
            query: sqlQuery,
            params: params,
            error: e.message,
            stack: e.stack
        });

        // Check for specific error types
        if (e.message.includes('Invalid search filter')) {
            return { success: false, error: 'Invalid column name' };
        }

        if (e.message.includes('SSS_USAGE_LIMIT_EXCEEDED')) {
            return { success: false, error: 'Governance limit reached' };
        }

        return { success: false, error: e.message };
    }
};

// Usage
const result = safeQuery(
    'SELECT id, companyname FROM customer WHERE id = ?',
    [123]
);

if (result.success) {
    log.debug('Data', result.data);
} else {
    log.error('Failed', result.error);
}

---

Complete Script Examples

Example 1: Suitelet with SuiteQL Report

/**
 * @NApiVersion 2.1
 * @NScriptType Suitelet
 */
define(['N/query', 'N/ui/serverWidget'], (query, serverWidget) => {

    const onRequest = (context) => {
        if (context.request.method === 'GET') {
            showForm(context);
        } else {
            showResults(context);
        }
    };

    const showForm = (context) => {
        const form = serverWidget.createForm({ title: 'Customer Report' });

        form.addField({
            id: 'custpage_state',
            type: serverWidget.FieldType.SELECT,
            label: 'State',
            source: 'state'
        });

        form.addField({
            id: 'custpage_min_balance',
            type: serverWidget.FieldType.CURRENCY,
            label: 'Minimum Balance'
        });

        form.addSubmitButton({ label: 'Run Report' });

        context.response.writePage(form);
    };

    const showResults = (context) => {
        const state = context.request.parameters.custpage_state;
        const minBalance = parseFloat(context.request.parameters.custpage_min_balance) || 0;

        // Build dynamic query
        let sql = `
            SELECT id, companyname, email, state, balance
            FROM customer
            WHERE isinactive = 'F'
              AND balance >= ?
        `;
        const params = [minBalance];

        if (state) {
            sql += ` AND state = ?`;
            params.push(state);
        }

        sql += ` ORDER BY balance DESC LIMIT 100`;

        // Execute query
        const results = query.runSuiteQL({
            query: sql,
            params: params
        }).asMappedResults();

        // Build output form
        const form = serverWidget.createForm({ title: 'Customer Report Results' });

        const sublist = form.addSublist({
            id: 'custpage_results',
            type: serverWidget.SublistType.LIST,
            label: `Results (${results.length} customers)`
        });

        sublist.addField({ id: 'custpage_id', type: serverWidget.FieldType.TEXT, label: 'ID' });
        sublist.addField({ id: 'custpage_name', type: serverWidget.FieldType.TEXT, label: 'Company' });
        sublist.addField({ id: 'custpage_email', type: serverWidget.FieldType.TEXT, label: 'Email' });
        sublist.addField({ id: 'custpage_state', type: serverWidget.FieldType.TEXT, label: 'State' });
        sublist.addField({ id: 'custpage_balance', type: serverWidget.FieldType.CURRENCY, label: 'Balance' });

        results.forEach((row, idx) => {
            sublist.setSublistValue({ id: 'custpage_id', line: idx, value: row.id });
            sublist.setSublistValue({ id: 'custpage_name', line: idx, value: row.companyname || '' });
            sublist.setSublistValue({ id: 'custpage_email', line: idx, value: row.email || '' });
            sublist.setSublistValue({ id: 'custpage_state', line: idx, value: row.state || '' });
            sublist.setSublistValue({ id: 'custpage_balance', line: idx, value: row.balance || 0 });
        });

        form.addButton({ id: 'custpage_back', label: 'Back', functionName: 'history.back()' });

        context.response.writePage(form);
    };

    return { onRequest };
});

Example 2: Map/Reduce with SuiteQL

/**
 * @NApiVersion 2.1
 * @NScriptType MapReduceScript
 */
define(['N/query', 'N/record', 'N/email', 'N/runtime'], (query, record, email, runtime) => {

    const getInputData = () => {
        // Use SuiteQL to get records to process
        return query.runSuiteQL({
            query: `
                SELECT
                    c.id,
                    c.companyname,
                    c.email,
                    SUM(t.amountremaining) AS total_ar
                FROM customer c
                INNER JOIN transaction t ON c.id = t.entity
                WHERE t.type = 'CustInvc'
                  AND t.amountremaining > 0
                  AND c.isinactive = 'F'
                GROUP BY c.id, c.companyname, c.email
                HAVING SUM(t.amountremaining) > 10000
            `
        }).asMappedResults();
    };

    const map = (context) => {
        const customer = JSON.parse(context.value);

        log.debug('Processing', customer.companyname);

        // Get detailed invoice list
        const invoices = query.runSuiteQL({
            query: `
                SELECT tranid, trandate, amountremaining, duedate
                FROM transaction
                WHERE entity = ?
                  AND type = 'CustInvc'
                  AND amountremaining > 0
                ORDER BY duedate
            `,
            params: [customer.id]
        }).asMappedResults();

        context.write({
            key: customer.id,
            value: {
                customer: customer,
                invoices: invoices
            }
        });
    };

    const reduce = (context) => {
        const data = JSON.parse(context.values[0]);

        // Send reminder email
        // ... email logic ...

        context.write({
            key: context.key,
            value: { sent: true, customer: data.customer.companyname }
        });
    };

    const summarize = (context) => {
        let sent = 0;
        context.output.iterator().each((key, value) => {
            sent++;
            return true;
        });

        log.audit('Complete', `Sent ${sent} reminder emails`);
    };

    return { getInputData, map, reduce, summarize };
});

Example 3: RESTlet with SuiteQL

/**
 * @NApiVersion 2.1
 * @NScriptType Restlet
 */
define(['N/query'], (query) => {

    const get = (params) => {
        try {
            // Validate parameters
            if (!params.type) {
                return { error: 'Missing required parameter: type' };
            }

            const allowedTypes = ['SalesOrd', 'CustInvc', 'PurchOrd'];
            if (!allowedTypes.includes(params.type)) {
                return { error: 'Invalid transaction type' };
            }

            // Build query
            let sql = `
                SELECT
                    t.id,
                    t.tranid,
                    t.trandate,
                    t.amount,
                    t.status,
                    c.companyname AS customer_name
                FROM transaction t
                LEFT JOIN customer c ON t.entity = c.id
                WHERE t.type = ?
            `;
            const queryParams = [params.type];

            // Optional filters
            if (params.startDate) {
                sql += ` AND t.trandate >= TO_DATE(?, 'YYYY-MM-DD')`;
                queryParams.push(params.startDate);
            }

            if (params.endDate) {
                sql += ` AND t.trandate <= TO_DATE(?, 'YYYY-MM-DD')`;
                queryParams.push(params.endDate);
            }

            if (params.customerId) {
                sql += ` AND t.entity = ?`;
                queryParams.push(parseInt(params.customerId));
            }

            sql += ` ORDER BY t.trandate DESC LIMIT ?`;
            queryParams.push(parseInt(params.limit) || 100);

            // Execute
            const results = query.runSuiteQL({
                query: sql,
                params: queryParams
            }).asMappedResults();

            return {
                success: true,
                count: results.length,
                data: results
            };

        } catch (e) {
            log.error('RESTlet Error', e.message);
            return {
                success: false,
                error: e.message
            };
        }
    };

    return { get };
});

---

Governance Considerations

Query Governance Cost

Operation	Governance Units
runSuiteQL	10 units
runSuiteQLPaged	10 units + 5 per page fetch

Test Case: Monitor Governance

/**
 * Track governance usage
 */
const runQueryWithGovernance = (sql, params) => {
    const script = runtime.getCurrentScript();
    const startUsage = script.getRemainingUsage();

    const results = query.runSuiteQL({
        query: sql,
        params: params
    }).asMappedResults();

    const endUsage = script.getRemainingUsage();
    const used = startUsage - endUsage;

    log.debug('Governance', `Query used ${used} units, ${endUsage} remaining`);

    return results;
};

---

Best Practices

Do's

// DO: Use parameterized queries
query.runSuiteQL({
    query: 'SELECT * FROM customer WHERE id = ?',
    params: [customerId]
});

// DO: Select only needed columns
query.runSuiteQL({
    query: 'SELECT id, companyname FROM customer'  // Not SELECT *
});

// DO: Use LIMIT
query.runSuiteQL({
    query: 'SELECT id FROM transaction ORDER BY id LIMIT 1000'
});

// DO: Handle errors
try {
    const results = query.runSuiteQL({ query: sql });
} catch (e) {
    log.error('Query failed', e.message);
}

Don'ts

// DON'T: Concatenate user input (SQL injection!)
query.runSuiteQL({
    query: 'SELECT * FROM customer WHERE name = \'' + userName + '\''  // WRONG!
});

// DON'T: Query inside loops
records.forEach(rec => {
    query.runSuiteQL({ query: '...' });  // WRONG - query once outside loop
});

// DON'T: Ignore pagination for large sets
const all = query.runSuiteQL({
    query: 'SELECT * FROM transactionline'  // WRONG - could be millions
});

---

Quick Reference

// Basic query
query.runSuiteQL({ query: 'SELECT ...', params: [] }).asMappedResults();

// Paged query
const paged = query.runSuiteQLPaged({ query: 'SELECT ...', pageSize: 100 });
paged.pageRanges.forEach(range => {
    const page = paged.fetch({ index: range.index });
    page.data.asMappedResults().forEach(row => { /* process */ });
});

// With iterator
resultSet.each(row => { /* process */ return true; });