Guides
GuidesLog In
Guides

Utilizing DQL and the DataNinja API

How to use powerful DQL and API to pull large amounts of data

Use DataNinja Query Language (DQL), the "Explorer" tab on the API page, and the DataNinja API to query records, confirm available fields, build Grid Configuration filters, and support integrations.

DQL allows you to query information stored in DataNinja. The API allows technical users and integration developers to interact with DataNinja data through API endpoints.

📘

Recommended Uses

These tools are useful when you need to:

  • Pull large amounts of data from DataNinja
  • Support custom reporting
  • Review available fields for an object
  • Test filters before using them in reports or Grid Configurations
  • Support integrations with external systems
  • Better understand how DataNinja objects and fields are structured

DQL and API Overview

DQL and the "Explorer" tab are found on the API page in your DataNinja site. The "Explorer" tab lists the objects in the DataNinja database and their fields, and DQL queries use those objects and fields to return read-only data. The DataNinja API and Postman are outside of your DataNinja site, but work together to let you make changes to live data or pull read-only data.

ToolPurposeCommon Use
DataNinja Query Language (DQL)Allows users to query DataNinja data using a SQL-like syntaxReviewing records, testing filters, building reports, confirming field values
Explorer tabAllows users to review available DataNinja objects, fields, and relationshipsIdentifying field names for DQL, Grid Configurations, reports, or integrations
DataNinja APIAllows technical users to interact with DataNinja data through API endpointsCreating, updating, retrieving, or integrating records with external systems
API tokenLets an integration or tool connect to DataNinja as youConnecting Power BI, Excel, Google Sheets, scripts, or other integrations
Postman API DocumentationProvides API endpoint documentation and examplesReviewing API calls, finding parts, reviewing inventory levels, and supporting integrations

DataNinja Query Language (DQL)

DataNinja Query Language (DQL) is a SQL-like query language for data contained in a DataNinja application. Use DQL to review records, test filters, build reports, and confirm field values. Use object dot notation in both the SELECT and WHERE clauses to navigate relationships. DataNinja automatically creates the appropriate joins internally.

DQL supports:

  • Where
  • Group by
  • Having
  • Order by
  • Limit
  • Offset

Object and field names aren't case-sensitive, so part.partNumber and PART.PARTNUMBER return the same field.

DQL is commonly used by implementation teams, administrators, support teams, and technical users who need to query DataNinja data or confirm how information is structured in the system.

Accessing the API Page

You can run DQL from the API page in DataNinja. Click on the gear icon in the top right, go to "All Lists", then click on "API". The API page has five tabs: "DQL", "Explorer", "Examples", "Setup" and "Custom Fields". Write and run queries in the "Query Window" on the "DQL" tab.

API page on the DQL tab with the DQL syntax and examples

Using the Explorer Tab

To explore the objects you can query with DQL, open the "Explorer" tab at the top of the API page.

The "Explorer" tab allows you to review available DataNinja objects and fields. Use it to confirm the correct field name, or copy directly into a query.

Explorer tab with the list of objects and CF badges

If an object shows a green "CF" circle, custom fields can be created on it. See Custom Fields for more.

Address object on the Explorer tab with its Custom Fields tab open

Queries can be auto-generated by selecting the checkbox next to the fields you need on an object, then clicking "Copy". Only one object can be copied at a time, and copying from a new object overwrites the previous query.

Selected fields and the Copy button on the Batch object in the Explorer tab

If a field on an object has "Foreign Key" next to it, that field connects to a different object. Click on "Foreign Key" to navigate to the connected object.

Foreign Key link on a table field in the API Explorer

The "Explorer" tab can help you understand:

What to ReviewWhy It Matters
ObjectsHelps identify the DataNinja record type being queried
FieldsConfirms the exact field names available for filtering or reporting
Related objectsHelps identify fields that may be available through dot notation
Data structureHelps users understand how records are connected
Field valuesHelps users confirm values that may be used in filters, such as status, type, or category

Basic DQL Examples

These examples are for reference only. Confirm the correct object names, field names, and values in your own DataNinja site using the "Explorer" tab. In the table below, replace [fields] with the fields (columns) that you want to see in the results.

GoalExample DQL
Return valid recordsSELECT [fields] FROM part WHERE isvalid=true
Return records with a specific statusSELECT [fields] FROM manufacturedbatch WHERE lifecycleid=2
Return a limited number of recordsSELECT [fields] FROM part LIMIT 100
Sort records by nameSELECT [fields] FROM part WHERE isvalid=true ORDER BY name
❗️

Result Volume

If your DQL query generates a high volume of results, your DataNinja site performance may degrade; pages may load slower or you may see "Internal Server Error" messages show up. To avoid this, filter your search as much as possible, or use LIMIT to control the number of results that show on the page.

Using Dot Notation for Related Objects

DQL supports dot notation to reference fields on related objects. This allows you to filter or select information from related records without manually creating joins.

SELECT id, identifier, lifecycleId, partId, partNumber, part.defaultLocationId
FROM batch
LIMIT 100

In this example, part.defaultLocationId refers to the defaultLocationId field on the related part object.

Available related objects and fields depend on the object being queried. Use the "Explorer" tab to confirm what relationships are available.

A dotted path can go up to 3 relationships deep. For example, part.partNumber on a batch is 1 relationship deep. If a path goes deeper than that, or one of its steps isn't a relationship on that object, the query stops with an error that names the path, so you can shorten it or correct the step.

Return Related Records With Child Queries

To return a record together with a list of its related records, such as a sales order and its lines, put a child query in parentheses inside the SELECT. The child query's FROM is the name of a collection on the main object, and it can have its own WHERE, ORDER BY and LIMIT.

SELECT Id,
    (
        SELECT Id, Quantity, Part.Name AS PartName
        FROM Lines
        WHERE IsDeleted = false
        ORDER BY LineNumber DESC
        LIMIT 100
    ) AS Lines
FROM SalesOrder

Each sales order comes back once, with its lines in a list named after the alias (Lines). A record with no related rows returns an empty list, and each list comes with a total count of the matching child rows, even when the LIMIT cuts the list short.

  • A query can have up to 5 child queries.
  • A child query's LIMIT applies to each parent record. It's 100 rows if you leave it out, and can be at most 250.
  • The main query's WHERE, ORDER BY, LIMIT and OFFSET apply to the main records only.

Connect an Integration With an API Token

Integrations and tools such as Power BI, Excel, Google Sheets or your own scripts use an API token to connect to DataNinja. You create and manage your own tokens on your user profile.

  1. Click on the gear icon in the top right, then click on "User Profile".
  2. Open the "API tokens" tab. You can also get there from the "Setup" tab on the API page by clicking "Go to API Tokens".
  3. Click "Create token" and give the token a name that matches the app it connects, such as "Power BI sync", so it's easy to find later.
  4. Copy the token right away. It's only shown when you create it, so keep it somewhere safe, such as a password manager.
API tokens tab on the user profile with the Create token button

Your integration sends the token in the ApiToken header of each request to the DataNinja API. To read data with DQL, send a GET request to your DataNinja site's /api/dql/query address with your query in the dql parameter:

GET https://<your site>.dataninja.com/api/dql/query?dql=SELECT Id, Name FROM Part LIMIT 10
ApiToken: <your token>

Things to know about tokens:

  • A token acts as you, with your roles and permissions, and everything it does is recorded under your name.
  • Each token expires one year after it's created. The "API tokens" tab shows each token's expiry date.
  • You can have up to 10 active tokens, and each one needs a unique name.
  • You can revoke a token on the "API tokens" tab at any time. You're asked to confirm first, then it stops working right away, and your other tokens keep working.
  • Users with the Contractor role can't create tokens. Disabling or deleting a user, or moving them to the Contractor role, revokes all of their tokens.
🚧

Integrations That Signed In With a Username and Password

Older integrations got their token by sending a username and password to DataNinja. That no longer returns a token. If an integration stops connecting, create a token on the "API tokens" tab and add it to the integration.

🔒

Treat Tokens Like Passwords

Anyone with your token can read and change your DataNinja data with your permissions. Don't share it, and revoke it as soon as an integration no longer needs it.


Using the DataNinja API

DataNinja uses Postman to document and interact with the API. The DataNinja API documentation includes examples and instructions for API-related activities, such as:

  • Reviewing available endpoints
  • Posting data to DataNinja
  • Retrieving data from DataNinja
  • Supporting integrations with external systems

See the DataNinja API documentation.

Best Practices

  • Use the "Explorer" tab to confirm object names and field names before writing queries
  • Start with a simple query and add filters gradually
  • Use filters and limits when querying large datasets
  • Confirm exact field values before filtering by status, type, category, or location
  • Use selected fields instead of SELECT * when only specific information is needed
  • Test query results before using the same logic in a report, integration, or Grid Configuration
  • Avoid overly broad queries, especially when working with large datasets

DQL vs API

NeedRecommended Tool
Review or query DataNinja recordsDQL
Test filter logicDQL
Identify available objects and fields"Explorer" tab
Build or support Grid Configurations"Explorer" tab and DQL
Create, update, or retrieve records through an integrationAPI, with an API token
Review API endpoint documentationPostman API Documentation

Troubleshooting

IssuePossible CauseSuggested Action
Query does not return expected resultsField or object name may be incorrectConfirm the field or object name in the "Explorer" tab
Query returns too many recordsFilter may be too broadAdd additional conditions or use LIMIT
Query returns no recordsFilter may be too restrictive or value may not match exactlyRemove one condition at a time and retest
Related field does not workDot notation may not match an available relationship, or the path is more than 3 relationships deepReview related objects in the "Explorer" tab, and shorten the path
A child query returns an errorThe child query's LIMIT is above 250, or the query has more than 5 child queriesLower the LIMIT or split the query
User cannot access expected dataRole or permission access may be limitedReview the user's Permissions in DataNinja
"Create token" isn't available, or the "API tokens" tab is missingThe user has the Contractor roleAsk your administrator whether a different role fits
"You already have the maximum of 10 active API tokens."You have 10 active tokensRevoke a token you no longer use on the "API tokens" tab
An integration gets "401 Unauthorized"The ApiToken header is missing, or the token was revoked or has expiredCreate a new token and update the integration