Reusable SuiteQL Query Template

Most SuiteQL queries I write need the same foundations: explicit columns, documented inputs, bind parameters, predictable aliases and a deterministic order.

Starting from a consistent template makes the important business logic easier to see and reduces mistakes in the parameter array.

The template

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

interface QueryInput {
  subsidiaryId: number;
  startDate: string;
  includeInactive?: boolean;
}

function getRows(input: QueryInput) {
  const conditions = [
    'record.subsidiary = ?',
    "record.trandate >= TO_DATE(?, 'YYYY-MM-DD')",
  ];

  const params = [
    input.subsidiaryId,
    input.startDate,
  ];

  if (!input.includeInactive) {
    conditions.push("record.isinactive = 'F'");
  }

  const suiteql = `
    SELECT
      record.id AS id,
      record.name AS name,
      record.lastmodifieddate AS last_modified_date
    FROM
      customrecord_example record
    WHERE
      ${conditions.join('\n      AND ')}
    ORDER BY
      record.lastmodifieddate,
      record.id
  `;

  return query.runSuiteQL({
    query: suiteql,
    params,
    customScriptId: 'custscript_example_query',
  }).asMappedResults();
}

Replace the illustrative record and field IDs with values confirmed in your account’s Records Catalog.

Document inputs beside the builder

Keep the parameter contract close to the query:

/**
 * @param subsidiaryId NetSuite internal ID.
 * @param startDate Inclusive date in YYYY-MM-DD format.
 * @param includeInactive When true, do not add the inactive filter.
 */

This makes the order and expected type of every ? placeholder visible before the SQL is edited.

Add conditions and parameters together

The most common parameter bug is changing the SQL without changing the parameter array in the same order.

Keep both mutations adjacent:

if (input.departmentId != null) {
  conditions.push('record.department = ?');
  params.push(input.departmentId);
}

Do not interpolate a value into the query string:

// Avoid this.
conditions.push(`record.department = ${input.departmentId}`);

Bind parameters preserve types, avoid quoting mistakes and separate the query structure from its values.

Handle optional inputs in JavaScript

It is possible to express an optional filter in SQL by binding the same value twice:

AND (? IS NULL OR record.department = ?)

But the parameter must then appear twice:

params.push(departmentId, departmentId);

For a growing query, conditionally adding the entire predicate is usually clearer and produces fewer placeholder-order mistakes.

See Build Dynamic SuiteQL WHERE Clauses Without Breaking Bind Parameters for a reusable builder pattern.

Use explicit columns

Prefer:

SELECT
    record.id,
    record.name,
    record.lastmodifieddate

over:

SELECT *

Explicit columns make result mappings stable and avoid retrieving large or calculated fields that the script never uses. Oracle includes this in its SuiteQL performance guidance.

Give mapped results predictable aliases

asMappedResults() uses column aliases as object keys:

SELECT
    record.id AS id,
    record.lastmodifieddate AS last_modified_date

The returned row is straightforward to consume:

for (const row of rows) {
  log.debug({
    title: row.id,
    details: row.last_modified_date,
  });
}

Always make paging order deterministic

If this template is switched to runSuiteQLPaged(), its order must be unique and unambiguous.

This may contain ties:

ORDER BY record.lastmodifieddate

Use a unique final key:

ORDER BY
    record.lastmodifieddate,
    record.id

Read SuiteQL Result Limits and Pagination Explained before using the query for a large result set.

Be cautious with `WITH` clauses

An earlier version of this template placed all inputs in a common table expression. That can be pleasant to read, and some query environments may accept it, but Oracle’s current SuiteQL performance documentation lists WITH clauses as unsupported.

For a reusable public template, keeping parameters in JavaScript and using ordinary bind placeholders is the more portable choice across SuiteQL execution channels.

Debug the completed query safely

Log the query structure and useful parameter metadata, but avoid logging sensitive parameter values:

log.debug({
  title: 'SuiteQL execution',
  details: {
    customScriptId: 'custscript_example_query',
    parameterCount: params.length,
    conditionCount: conditions.length,
  },
});

The template is intentionally boring. That is its value: query-specific complexity stays in the selected fields, joins and conditions instead of being repeated in execution boilerplate.