We have received numerous requests over the past few years for direct access to the Device42 database to support ad hoc queries for reports and integrations. Until now we have maintained an API layer as the abstraction layer for programmatic access to the data. We have never allowed direct access to the DB layer because we were concerned that core Device42 changes to the database would inevitably cause customer code to stop working. However, despite our best efforts to create a granular and flexible API, customers have repeatedly expressed concerns about performance (effectively having to do joins between API results in their own code). To resolve these issue, we are pleased to announce that we are working on a new feature that we have termed the Device42 Object Query Language (DOQL).
How will DOQL work:
We will publish a schema of database views (limited at first, but we plan to evolve over time) that will be supported even when the underlying database changes. Via the Device42 API, you will be able to execute read-only queries in using Postgres compliant SQL (including joins, subqueries, etc…) against the published schema. The API calls will return CSV formatted results with optional user specified delimiters, quote characters, null strings, quote escape and header. Also, the DOQL views will include all custom fields (that will effectively be different for each customer) and dynamically change as your custom fields change.
Benefits of DOQL:
This will give you the best of all worlds:
- You can write queries directly against the DB (through the API)
- When the underlying DB changes, the DOQL view will not change (except for the addition of columns) so that any existing API or integration will continue to work
- There are no DB-related security issues because all access will be via an API call
- You will get good performance because the queries will be optimized by Postgres which has an excellent optimizer.
Documentation:
One of the challenges we ran into with DOQL was an ever-changing documentation requirement. Of course, Device42 would have a manual documentation effort when changes were made. However, more importantly, since different customers will have different custom fields, the database views will differ by customer. With that in mind, we are building DOQL with dynamic documentation that will be available within each D42 appliance.
Details & Fine Print:
- Access to the pre-defined database views will be read-only. If we make any database changes in the backend, we will either map an existing field to the new field, or if necessary return some sort of null value so that scripts remain backward compatible.
- Only superuser’s credentials will be accepted in DOQL API calls.
- The API endpoint for DOQL access will be: /services/data/v1.0/query/?query=SELECT…
Early Beta access program:
If you are interested in trying out this feature before it is production-ready and giving us feedback, please reach out to [email protected] and we will be happy to get you access.
