Paged SuiteQL in SuiteScript

query.runSuiteQL() returns at most 5,000 rows. For more, use runSuiteQLPaged() and iterate the pages. Page size is between 5 and 1,000.

lib/suiteql.js

/**
 * @NApiVersion 2.1
 * @NModuleScope Public
 */
define(['N/query', 'N/runtime'], (query, runtime) => {
  // Calls onRow for each mapped row. Stops early when governance is low.
  function forEachRow(sql, params, onRow, { pageSize = 1000, minUnits = 200 } = {}) {
    const paged = query.runSuiteQLPaged({ query: sql, params, pageSize })

    for (const range of paged.pageRanges) {
      if (runtime.getCurrentScript().getRemainingUsage() < minUnits) {
        return { complete: false, lastPage: range.index }
      }
      const rows = paged.fetch({ index: range.index }).data.asMappedResults()
      for (const row of rows) {
        if (onRow(row) === false) return { complete: false, lastPage: range.index }
      }
    }
    return { complete: true }
  }

  return { forEachRow }
})

Usage:

const { complete } = suiteql.forEachRow(
  `SELECT id, itemid, BUILTIN.DF(class) AS class_name
   FROM item WHERE isinactive = 'F' AND itemtype = ?`,
  ['InvtPart'],
  (row) => log.debug(row.itemid, row.class_name)
)

Always pass user values through params, not string concatenation. For very large jobs, prefer a Map/Reduce script that returns the SuiteQL as its input.