Published on August 6, 2020.

An update to the SuiteQL Query Tool is now available. Learn more >

Running SQL queries against NetSuite using SuiteQL has become an important technique that I use in my integration projects. I'm now using it in SuiteScripts (via the N/query module) and by making API calls to SuiteTalk REST Web Services.

Back in May, I developed a simple PHP app that I've been using to make it easier to develop and test SuiteQL calls. And while that's been very helpful, there are times when I'm in NetSuite and want to quickly run a SQL query.

So last week I developed a SuiteScript to do just that. It's a simple Suitelet that makes use of the N/ui/serverWidget module to present a basic form, through which you can enter a SQL SELECT statement, click a button, and see the results. It uses the N/query module to actually run the SQL query. Initially, I was only displaying the results as JSON-encoded text. I've since added support for displaying the results in a sublist as well.

I'm sharing the SuiteScript in the hope that it will help other NetSuite developers who are using SuiteQL, or that are interested in exploring it. You'll find the script displayed below, or you can click here to download it. If you're unfamiliar with installing scripts, you should ask your NetSuite administrator for assistance.

SuiteQL Tips and Tricks

Here are a couple of simple SuiteQL tips and tricks that you might find to be helpful.

When exploring a table, you might want to limit the number of rows that are returned by the query. You can do that pretty easily by filtering with the RowNum function. For example, this query will return the first 10 records in the Transaction table.

SELECT
	*
FROM 
	Transaction
WHERE
	RowNum <= 10

When developing and troubleshooting a more complex query, it can sometimes help to comment out parts of the query. In this example, I'm commenting out a block of columns using /* .. */, and specific lines of the query by prefixing them with two dashes ("--").

SELECT
	Transaction.ID,
	Transaction.TranDisplayName,
	Transaction.TranDate,
	BUILTIN.DF( Transaction.Status ) AS OrderStatus
	/*
	Customer.LastName,
	Customer.FirstName,
	Customer.Email
	*/
FROM 
	Transaction
	-- INNER JOIN Entity AS Customer ON
		-- ( Customer.ID = Transaction.Entity )
WHERE
	( Transaction .Type = 'SalesOrd' )
	AND ( RowNum <= 10 )

SuiteQL Resources

I've written several blog posts about things that I've discovered while working with SuiteQL, including querying an instance's File Cabinet, running queries with SuiteTalk REST, helpful "Built-In" functions, and more.

I highly recommend Eric Grubaugh's "Basic Querying in SuiteScript" ebook on SuiteScript 2.0's N/query module. You can find that here: https://stoic.software/cookbooks/basic-query/.

Also check out NetSuite's SuiteAnalytics Workbook PDF, which includes helpful information on SuiteQL, including syntax and example queries.

And finally, I highly recommend joining the NetSuite Professionals Slack Community. With more than 7,000 members, it's a great way to network, get and give help, and stay up-to-date on NetSuite.

Wrapping Up

I hope you find the SuiteQL Query Tool to be helpful, and that SuiteQL will become an increasingly important part of your NetSuite development work. As a longtime SQL developer, SuiteQL has been a game changer for me - especially now that we can leverage it in both SuiteScript and SuiteTalk.

One last thing: I'm booked solid through the rest of 2020, so I'm not taking on new clients or projects at the moment. However, if you'd like to drop me a line, please do. If you'd like to network, you'll find me on LinkedIn. And if you have questions about the script or about SuiteQL, please post to the NetSuite Professionals Slack Community. (I try to keep an eye on the "integrations" and "suitetalkapi" channels in particular.)

The Suitelet

The script can also be downloaded.

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

/* 

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

Name:
SQL Query Tool

ID:
_sql_query_tool

Description
A utility for running SQL queries against a NetSuite instance.


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

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


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

20200801 - Tim Dietrich
• Initial version.

20200805 - Tim Dietrich
• Added support for displaying the results in a sublist.


*/


var 	
	log,
	query,
	serverWidget;


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


function main( logModule, queryModule, serverWidgetModule ) {

	log = logModule;
	query= queryModule;
	serverWidget = serverWidgetModule;
	
    return {
    
    	onRequest: function( context ) {

			// Create a form.
			var form = serverWidget.createForm(
				{
					title: 'SuiteQL Query Tool',
					hideNavBar: false
				}
			);		
			
			// Add a submit button.
			form.addSubmitButton( { label: 'Run Query' } );	
			
			// Add the query field.
			var queryField = form.addField(
				{
					id: 'custpage_field_query',
					type: serverWidget.FieldType.LONGTEXT,
					label: 'Query'
				}								
			);
			
			// Make the query field required.
			queryField.isMandatory = true;								
												
			// If the form has been submitted...
			if ( context.request.method == 'POST' ) {	
			
				// Set the query field's default value.
				queryField.defaultValue = context.request.parameters.custpage_field_query;
			
				// Add the Results field.
				var resultsField = form.addField(
					{
						id: 'custpage_field_results',
						type: serverWidget.FieldType.LONGTEXT,
						label: 'Results'
					}								
				);			
				
				try {
			
					// Run the query.
					var queryResults = query.runSuiteQL(
						{
							query: context.request.parameters.custpage_field_query
						}
					); 					
					
					// Get the mapped results.
					var beginTime = new Date().getTime();
					var records = queryResults.asMappedResults();
					var endTime = new Date().getTime();
					// var elapsedTime = Math.round( endTime - beginTime );
					var elapsedTime = endTime - beginTime ;
					
					// Adjust the label so that it includes the number of results.
					resultsField.label = queryResults.results.length + ' Results (JSON)';					
					
					// If records were returned...
					if ( records.length > 0 ) {
			
						// Create a sublist for the results.
						var resultsSublist = form.addSublist(
							{ 
								id : 'results_sublist', 
								label : 'Results (' + records.length + ' records retrieved in ' + elapsedTime + 'ms)', 
								type : serverWidget.SublistType.LIST 
							}
						);
	
						// Get the column names.
						var columnNames = Object.keys( records[0] );
	
						// Loop over the column names...
						for ( i = 0; i < columnNames.length; i++ ) {
	
							// Add the column to the sublist as a field.
							resultsSublist.addField(
								{ 
									id: 'custpage_results_sublist_col_' + i,
									type: serverWidget.FieldType.TEXT,
									label: columnNames[i]
								}
							);
	
						}
	
						// Add the records to the sublist...
						for ( r = 0; r < records.length; r++ ) {
	
							// Get the record.
							var record = records[r];
	
							// Loop over the columns...
							for ( c = 0; c < columnNames.length; c++ ) {
		
								// Get the column name.
								var column = columnNames[c];
			
								// Get the column value.
								var value = record[column];
								if ( value != null ) {
									value = value.toString();
								}
		
								// Add the column value.		
								resultsSublist.setSublistValue(
									{
										id : 'custpage_results_sublist_col_' + c,
										line : r,
										value : value
									}
								);        
					
							}
	
						}

					}
					
					// Set the results field to a text version of the mapped results.
					// This will fail if the text is > 100000 characters.
					// However, the results sublist will still render properly.
					resultsField.defaultValue = JSON.stringify( records, null, 2 );						

				} catch( e ) {	
				
					// Update the results field to reflect the error.
					resultsField.label = 'Error';			
					resultsField.defaultValue = e.message;			
						
				}
								
			}
			
			// Add an inline HTML field so that JavaScript can be injected.
			var jsField = form.addField(
				{
					id: 'custpage_field_js',
					type: serverWidget.FieldType.INLINEHTML,
					label: 'Javascript'
				}								
			);
			
			// Add Javascript...
			jsField.defaultValue = '<script>\r\n';
			
			// Adjust the size of the textareas.
			jsField.defaultValue += 'document.getElementById("custpage_field_query").rows=20;\r\n';
			if ( context.request.method == 'POST' ) {	
				jsField.defaultValue += 'document.getElementById("custpage_field_results").rows=20;\r\n';
			}		
				
			// Use jQuery to modify the tab key's behavior when in the query textarea.
			// This allows the user to use the tab key when editing a query.
			// Source: https://stackoverflow.com/questions/6140632/how-to-handle-tab-in-textarea		
			jsField.defaultValue += 'window.jQuery = window.$ = jQuery;\r\n';			
			jsField.defaultValue += '$(\'textarea\').keydown(function(e) {\r\n';
			jsField.defaultValue += 'if(e.keyCode === 9) {\r\n';
			jsField.defaultValue += 'var start = this.selectionStart;\r\n';
			jsField.defaultValue += 'var end = this.selectionEnd;\r\n';
			jsField.defaultValue += 'var $this = $(this);\r\n';
			jsField.defaultValue += 'var value = $this.val();\r\n';
			jsField.defaultValue += '$this.val(value.substring(0, start)';
			jsField.defaultValue += '+ "	"';
			jsField.defaultValue += '+ value.substring(end));\r\n';
			jsField.defaultValue += 'this.selectionStart = this.selectionEnd = start + 1;\r\n';
			jsField.defaultValue += 'e.preventDefault();\r\n';
			jsField.defaultValue += '}\r\n';
			jsField.defaultValue += '});\r\n';	
			
			jsField.defaultValue += '</script>';
		
			// Display the form.
			context.response.writePage( form );			
	
        }
        
    }

}