What is the advantage of using SuiteQL in SuiteScript (N/query module) compared by using N/search module?

It's an order of magnitude easier to get at data

SuiteQL allows you to write much more complex queries than N/search, and IMO is is much easier to work with SQL than search. Search has other limitations like only allowing one join (meaning, you can’t join from table a > b > c if you need data in C

If SuiteQL is better than N/search, when do we need to use N/search?

The most important instance where it might be needed is when you want to have a pre-defined saved search (which can be viewed/edited outside of the script) that can also be used within a script. That can be a double-edged sword though, because while it allows changing the behavior of a script without programming, it also allows someone to easily break the script or change its behavior in undesirable ways. It is a somewhat common practice, though.

The other positive is that SuiteQL is way faster as well. It can pull queries in a fraction of the time. Saved searches came first and everybody is used to them. SuiteQL is a "new" search function for NetSuite and is an improvement, but like others have said, there isn't really a UI for it.

I just connect to the data source using DataGrip. Nice and easy to write & test the SQL that you need for your scripts. There are also a few nice suitelets out there that offer a SQL interface

Tim's tool is really nice for it. https://timdietrich.me/netsuite-suitescripts/suiteql-query-tool/

SuiteQL doesn't have a 'data source' to connect to, does it @CD?

It's the same as the SuiteAnalytics Connect netsuite2 data source. Unless I've gone completely mad

the SuiteQL schema is [unfortunately] different than the SA Connect one, or so I believe.

no apology, looks like we're talking about two different schemas both called "SuiteQL"

haha 🙂

Suiteql allows you to write SQL-92 syntax or oracle syntax. The odbc connection only allows us to write SQL-92 syntax. Most notably the

WITH

statement is missing.

ODBC also requires a monthly license (we were quoted $500/mo I believe)

Yeah we paid something like that.

Also — correct me if I’m wrong but ODBC doesn’t inherit user permissions does it? Meaning in SuiteQL you can enforce data security more easily because the user can only query tables he has access to. I belive ODBC would require you to write your own permissions/security layer?