Published on January 5, 2021.

One of the most frequently asked questions that I get from other NetSuite developers regarding SuiteQL is something along the lines of, "Is it possible to get NetSuite's schema?" What they're really looking for is a visual representation of NetSuite's Oracle database. Unfortunately, there's nothing that I'm aware of that presents the schema visually.

At this point, the most helpful tool that we have access to is the Records Catalog, which is available via Setup > Records Catalog.

However, it is possible to use the API that powers the Records Catalog, and extract some very helpful information from it. If you study the network traffic that is generated when working with the Records Catalog, you'll see the API requests that are sent (via XMLHttpRequest). By doing so, you can reverse engineer the Records Catalog a bit, and then use its API to retrieve the data and use it in other ways.

Here's how the technique works.

First, log into your NetSuite account using an Administrator role. Then open up your browser's Javascript console. It doesn't matter what page you're on.

Next, use this Javascript snippet to create an array of the instance's record types.

console.clear();
var rcEndpoint = '/app/recordscatalog/rcendpoint.nl';
var recordTypes;
var action = 'getRecordTypes';
var data = encodeURI( JSON.stringify( { structureType: 'FLAT' } ) );
var url = rcEndpoint + '?action=' + action + '&data=' + data;
var xhr = new XMLHttpRequest();
xhr.open( 'get', url, false );
xhr.send();
recordTypes = JSON.parse( xhr.response );	
console.log( JSON.stringify( recordTypes, null, 5 ) );	
console.log( 'recordTypes loaded.' );

The recordTypes array will look something like the JSON shown in this file.

With the RecordTypes array, you can run the following Javascript snippet to create an array that represents the full schema, which will include details for every record type.

var schema = [];
recordTypes.data.forEach( function( recordType ) {

	console.log( 'Loading details for record type ' + recordType.id + '...' );

	// Get details for the record type.
	action = 'getRecordTypeDetail';
	data = encodeURI( JSON.stringify( { scriptId: recordType.id, detailType: 'SS_ANAL' } ) );
	var url = rcEndpoint + '?action=' + action + '&data=' + data;
	var xhr = new XMLHttpRequest();
	xhr.open( 'get', url, false );
	xhr.send();
	recordDetail = JSON.parse( xhr.response );	
	schema.push( recordDetail.data );

});	
console.log( JSON.stringify( schema, null, 5 ) );
console.log( 'Schema created.' );

It will likely take quite a bit of time for the process to complete. However, when it has finished, the schema array will look something like the JSON shown in this file.

As I mentioned above, I've been using the resulting JSON-encoded schema in local FileMaker databases. I'm also hoping to make use of it in a macOS / Windows native version of the SuiteQL Query Tool that I'm working on, with the goal being to provide some sort of SQL code completion for table and column names. But, of course, you can use the JSON in whatever way makes sense for you.

If you have any questions about this technique, please feel free to contact me.