Published on August 19, 2020.

Over the past few months, I've written extensively about how I've been using the SuiteQL capabilities of SuiteTalk REST Web Services in my NetSuite integration projects. As a longtime SQL user, SuiteQL has opened up a world of possibilities for me, and drastically changed how I approach NetSuite integration projects.

For as good as SuiteTalk REST's SuiteQL support is, it does have a few limitations. Perhaps the biggest limitation - and one that many developers that I've spoken with have expressed the most frustration with - is that the query results are returned in pages that can contain a maximum of 1,000 results each. In addition, as of Release 2020.1, support for SuiteTalk REST Web Services is still in beta.

An Alternative

However, SuiteScript's support for SuiteQL doesn't have that limitation. By using the N/query module, it is possible to get beyond SuiteTalk's 1,000 result per page limit. In addition, SuiteScript's support for SuiteQL via the N/query module is a production feature.

Last week, I developed a very simple RESTlet that uses this approach. With it, you can remotely run SQL queries against a NetSuite instance.

As I've done in the past, I'm sharing the RESTlet. My hope is that it will help other NetSuite developers who are using or interested in SuiteQL. The SuiteScript is included below.

/**
* @NApiVersion 2.x
* @NScriptType Restlet
* @NModuleScope Public
*/

/* 

------------------------------------------------------------------------------------------
Script Information
------------------------------------------------------------------------------------------

Name:
SuiteQL

ID:
_suiteql

Description
A RESTlet for remotely running SQL queries against a NetSuite instance.


------------------------------------------------------------------------------------------
Developer(s)
------------------------------------------------------------------------------------------

Tim Dietrich
• timdietrich@me.com
• https://timdietrich.me


------------------------------------------------------------------------------------------
History
------------------------------------------------------------------------------------------

20200818 - Tim Dietrich
Initial version.


*/


define( [ 'N/query' ], main );

function main( query ) {
	
    return {
    
        post: function( request ) {
        
			try {		
				return query.runSuiteQL( request['sql'] ).asMappedResults();
			} catch (e) {
				return e;
			}        
			
        }
        
    }

}

The RESTlet uses the N/query module's "runSuiteQL" method, which makes it possible to run arbitrary SuiteQL queries. It then uses the ResultSet's "asMappedResults" method to convert the result set to an array. A JSON-encoded representation of that array is then returned via the RESTlet.

To use the RESTlet, send a JSON-encoded payload with a "sql" value in it. For example:

{
  "sql": "SELECT ID, LastName, FirstName FROM Employee"
}

It's important to note that this approach also has a limitation. The maximum number of results that the N/query supports in a ResultSet object is 5,000. That's significantly higher than the 1,000 result per page limit imposed by SuiteTalk REST. Regardless, it is something to keep in mind.

As always, I hope you find this information, and the SuiteScript, to be helpful. If you have any comments or questions, please feel free to email me or join the conversation over on the NetSuite Professionals Slack Community.