How to use the new Domino Query Language

By Tim Davis – Director of Development.

Before I talk about building a Domino-based API gateway on Node.js, I thought it would be a good idea to expand a little on how to use the new Domino Query Language (DQL) to run queries on Domino documents.

DQL is the new query language that comes with Domino 10. It is separate from the other query syntaxes in Domino, such as the Full Text Search syntax, or @formula selection criteria. You can use it in other places than Node.js, such as LotusScript and Java, and you can even test your queries from the command line.

What is cool about DQL is that it runs using the new Design Catalog system database, GQFdsgn.nsf. This does a lot of heavy lifting in the background with pre-built design data which makes running the queries more efficient. I am told this will not always be part of the DQL engine, but for now you do need it.

The DQL processing engine also does clever prioritizing of the components of the query to make the process as efficient as possible. You can see how this is done using the ‘explain’ tool (see below).

The syntax is pretty straight-forward. Here are some simple examples to give you an idea:

customer = 'Bob Smith'
age > 21
form = 'Person' and type = 'Client'

You have all the usual operators such as =, >, <, <=, >=, and, or, not. You can wrap elements in parentheses ( ) to control the precedence.

String values are enclosed in single quotes. You can duplicate quotes in your strings to escape them, e.g.:

name = 'James O''Brien'

You can also use lists with the ‘in’ operator, like this:

form = 'Person' and type in ( 'Client', 'Supplier', 'Agency' )

DQL comes with some useful built-in functions that you can use in your queries:

@Created
@ModifiedInThisFile
@DocumentUniqueID

You can search using dates, which are included using the built-in @dt function and are in RFC3339 format. This looks a bit clunky but is pretty clear once you get used to it:

@Created > @dt('2018-01-01T00:00:00+0500')

A cool feature is being able to search in view columns. You use the view name or alias and the programmatic column name, like this:

'PendingOrders'.orderNo = '000101'

These view and column searches are fast because the design analysis of the db has already been done and stored in the design catalog. However if the database isn’t in the catalog then this kind of search will fail.  Adding databases to the catalog is an optional step you would do if you want to perform this kind of query, but being in the catalog helps with the performance of regular searches as well and so is always recommended.

One thing to bear in mind is that currently you can only search on regular summary fields in the documents, which means you can’t search in rich text items yet. I am told this is coming later.

Also, if your query generates an error during the processing then you will get no results returned. You don’t get partial results (unlike some SQL behavior). This is likely not a problem provided you know to expect it.

With all of this you should have everything you need to run pretty much any query you want, with plenty of flexibility and control.

Talking of control, DQL comes with a command line DomQuery tool to test your queries. It has an -e (explain) option that will deconstruct how your query will be processed and details how it will perform. It splits out each element of the search and lists the order they are processed, how long each takes, and how the results get filtered down step by step. You can use this to test out your searches and tune them to make sure they run efficiently and perform well for the users.

However, just in case you do end up running a query that gets out of hand there are limits to protect the server. The limits are:

  • MaxDocsScanned – maximum allowable NSF documents scanned ( 200000 default)
  • MaxEntriesScanned – maximum allowable index entries scanned (200000 default)
  • MaxMsecs – maximum time consumed in milliseconds ( 120000 ) (2 minutes)

You can override these limits system-wide using notes.ini settings if you need to, but you really ought to instead review your query’s behaviour before you consider doing this.

The DQL engine is continuously being enhanced and the new Domino 10.0.1 version adds support for query arguments (also known as substitution variables). You use query arguments when building your queries to help avoid code injection. This works by defining specifically which elements of a query are user input rather than just constructing the query as one string. Without this, a user could maliciously input some DQL syntax into the query and access unexpected documents or data.

I hope all this helps give you an idea of how easy and fast it will be to run DQL queries from a Node.js app. In my next post I plan to bring this together with the domino-db module and build an API gateway.