Utilizing DQL and the DataNinja API
How to use powerful DQL and API to pull large amounts of data
Español: Lee esta página en español.
Français : Consultez cette page en français.
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 UsesThese 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.
| Tool | Purpose | Common Use |
|---|---|---|
| DataNinja Query Language (DQL) | Allows users to query DataNinja data using a SQL-like syntax | Reviewing records, testing filters, building reports, confirming field values |
| Explorer tab | Allows users to review available DataNinja objects, fields, and relationships | Identifying field names for DQL, Grid Configurations, reports, or integrations |
| DataNinja API | Allows technical users to interact with DataNinja data through API endpoints | Creating, updating, retrieving, or integrating records with external systems |
| API token | Lets an integration or tool connect to DataNinja as you | Connecting Power BI, Excel, Google Sheets, scripts, or other integrations |
| Postman API Documentation | Provides API endpoint documentation and examples | Reviewing 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.

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.

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

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.

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.

The "Explorer" tab can help you understand:
| What to Review | Why It Matters |
|---|---|
| Objects | Helps identify the DataNinja record type being queried |
| Fields | Confirms the exact field names available for filtering or reporting |
| Related objects | Helps identify fields that may be available through dot notation |
| Data structure | Helps users understand how records are connected |
| Field values | Helps 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.
| Goal | Example DQL |
|---|---|
| Return valid records | SELECT [fields] FROM part WHERE isvalid=true |
| Return records with a specific status | SELECT [fields] FROM manufacturedbatch WHERE lifecycleid=2 |
| Return a limited number of records | SELECT [fields] FROM part LIMIT 100 |
| Sort records by name | SELECT [fields] FROM part WHERE isvalid=true ORDER BY name |
Result VolumeIf 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 100In 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 SalesOrderEach 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
LIMITapplies 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,LIMITandOFFSETapply 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.
- Click on the gear icon in the top right, then click on "User Profile".
- Open the "API tokens" tab. You can also get there from the "Setup" tab on the API page by clicking "Go to API Tokens".
- 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.
- Copy the token right away. It's only shown when you create it, so keep it somewhere safe, such as a password manager.

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 PasswordOlder 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 PasswordsAnyone 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
| Need | Recommended Tool |
|---|---|
| Review or query DataNinja records | DQL |
| Test filter logic | DQL |
| Identify available objects and fields | "Explorer" tab |
| Build or support Grid Configurations | "Explorer" tab and DQL |
| Create, update, or retrieve records through an integration | API, with an API token |
| Review API endpoint documentation | Postman API Documentation |
Troubleshooting
| Issue | Possible Cause | Suggested Action |
|---|---|---|
| Query does not return expected results | Field or object name may be incorrect | Confirm the field or object name in the "Explorer" tab |
| Query returns too many records | Filter may be too broad | Add additional conditions or use LIMIT |
| Query returns no records | Filter may be too restrictive or value may not match exactly | Remove one condition at a time and retest |
| Related field does not work | Dot notation may not match an available relationship, or the path is more than 3 relationships deep | Review related objects in the "Explorer" tab, and shorten the path |
| A child query returns an error | The child query's LIMIT is above 250, or the query has more than 5 child queries | Lower the LIMIT or split the query |
| User cannot access expected data | Role or permission access may be limited | Review the user's Permissions in DataNinja |
| "Create token" isn't available, or the "API tokens" tab is missing | The user has the Contractor role | Ask your administrator whether a different role fits |
| "You already have the maximum of 10 active API tokens." | You have 10 active tokens | Revoke 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 expired | Create a new token and update the integration |
Updated 11 days ago
