SQL Passthrough API overview

The SQL Passthrough API enables PMS integrators to switch existing products to Halo Connect with minimal changes and to reduce development costs for new products. It accepts any standard SQL query -- if you could successfully run a query on the PMS database directly, then you can run it via the SQL Passthrough API.

This page assumes you have already read the Halo Cloud overview.

Supported PMS

The SQL Passthrough API currently supports:

• Bp Premier
• Zedmed (cloud and on-prem)
• Dental4Windows and Dental4Web

Query types

The SQL Passthrough has three types of queries:

Query type	Description	Restrictions
Immediate queries	Immediate queries are small, time-sensitive queries that for real-time use cases and are given priority in the queue. The result is returned to the integrator immediately in the POST response.	- Max query execution time: 60 seconds. - Max result size: 8MB.
Async queries	Async queries are queued and executed when bandwidth is available, and the query results are made available via an API endpoint for approximately 24 hours.	- Max query execution time: 5 minutes (does not include time taken for queuing or uploading results). - No result size limit.
Registered queries	Registered queries allow integrators to register a query with a practice for up to 24 hours, to be queued at a specified frequency. Results can be fetched periodically in the same way async query results are.	- Max duration: 86400 seconds (24 hours). - Min frequency: 60 seconds. - Max query execution time: 60 seconds. - Max result size: 8MB.

Asynchronous query workflow

Async queries are designed for anything that isn't time sensitive, but particularly for large data extractions. Async queries are queued when they are received by Link, but their execution and result upload can be interrupted by immediate queries, to ensure they do not interrupt real time applications. The query result is also not returned directly, but is made available via an endpoint.

The API endpoints relevant to async queries include:

• Create an async query
• Check query status
• Stream query result page
• Retrieve query result page

Async query workflow (click to expand)

  sequenceDiagram
    participant I as Integrator
    participant HC as Halo Cloud
    participant HL as Halo Link
    participant DB as PMS database

    I->>+HC: POST async query
    HC->>+HL: Route query to practice
    HL-->>-HC: Return query ID
    HC-->>-I: Return query ID

    HL->>+DB: Executes query
    DB-->>-HL: Return result
    HL->>HC: Uploads result to cache

    loop Check query status
      I->>+HC: GET query status
      HC-->>-I: Return status
    end
    loop Get result pages
      I->>+HC: GET query result page
      HC-->>-I: Return query result page
    end

Recommendations

• For large results, we recommend retrieving results via the streaming endpoint as it is faster and more resilient.
• In order to receive results as soon as they are ready, we also recommend using webhooks.
• Async queries can take a while to execute. If not using webhooks, please consider optimising how frequently you check query status and results. It may be possible to optimise the frequency based on statistics such as the average of previous query result times.

Pagination

Async query results are paged. Pages are intended to be maximum 1MB, however we do not break up rows of data. So if a row of data is >1MB, the row will be returned as one page, no matter how big it is. This is most likely to happen with large documents.

For such use cases we recommend streaming the query result instead of fetching it as serialised JSON.

If the query result is paged, you'll need to stitch the rows together by merging the JSON arrays once all pages have been fetched.

See SQL Passthrough examples for more information on how to check the number of pages for a result and how to fetch each page.

Registered query workflow

To reduce the overhead of polling practice databases, queries can be registered at a practice for up to 24 hours, to be run at a specified frequency. Results can be fetched via an endpoint in a similar way to async query results.

• To register a query, specify:
  • the query to be run,
  • a name to reference it by,
  • the frequency to run it at, and
  • the duration to run it for.
• The API endpoint for registering queries will return a query ID which can be used to fetch the results.
• When the query is run, if the result has changed since the last time (or it is being run for the first time), the result will be uploaded to cloud storage. It can then be fetched using the query ID via the Get registered query result endpoint.
• The result of each run overwrites any previous results. Halo Connect will not maintain a history of query results. We will only store the result from the most recent execution that produced a different result.
• Registered queries can be cancelled using the query ID.
• Endpoints are also available to list out active registered queries for a practice, or to get the details of a particular registered query. The results of these endpoints include the query name, to make it easier to know which query is which.
• Registered queries are queued at the specified frequency. This may result in a slight delay before the query is executed, if there are many other immediate or registered queries already queued.

See the API reference for more information.

Registered query workflow (click to expand)

  sequenceDiagram
  participant I as Integrator
  participant HC as Halo Cloud
  participant HL as Halo Link
  participant PMS as PMS database

  I->>+HC: POST query, {frequency}, {duration}
  HC->>HL: Registers query with Link
  loop Every {frequency} for {duration}
    HL->>+PMS: Executes query
    PMS-->>-HL: Returns query result
    HL->>HC: Uploads result
  end
  loop
    I->>+HC: Polls for latest result
    HC->>-I: Returns result
  end

Query priority in queue

One of Halo Connect's key goals is to manage and reduce load on the practice server. To do this, Halo Link prioritises processing queries according to their type:

• Immediate queries have highest priority, and will be queued ahead of async and registered queries (but not other immediate queries).
• Async queries are queued and executed in the order they are received by Halo Link.
• Async query results are chunked for upload, and each chunk is queued individually.
• Registered queries are queued at the specified frequency.

Query lifetime

All queries have a maxTimeInQueue configuration value that dictates the length of time a query will remain queued. After this time has elapsed, the query is marked as expired. While this occurrence is rare, it can occur during periods of high demand and an integrator must be able to handle the re-queuing of expired queries.

Recommendations

Handling personally identifiable information (PII)

Where possible, Halo Connect recommends pre-processing data on the server to reduce the amount of PII transmitted over the internet.

Example: Pre-processing PII

When checking if a patient qualifies for something by collating several pieces of data, you could:

1. Send us a SQL query which returns multiple pieces of data, receive that data back, then process it on your server.
2. Send us a SQL query which retrieves multiple pieces of data and processes it on the server, such that only the required data is returned to you via our API.

The first option requires less complicated SQL, but increases the amount of PII transmitted over the internet for this query. The second option reduces PII and reduces the query result size which, due to Halo Connect charging based on usage, could also reduce your cost per query.

Use async queries where possible

Many integrators we talk to have time-sensitive applications which require results within seconds. Integrators should use immediate queries for such use cases. However, integrators should also be aware that because immediate queries have a short timeout (only 60 seconds), if a practice server is receiving too many immediate queries too quickly, the queue can fill and immediate queries may start to timeout due to there being too many other immediate queries ahead of them.

Halo Connect does actively monitor query behaviour in order to identify such issues, and will keep improving our query handling and queueing in order to minimise this risk. However, we do also appreciate integrators assisting us in optimising query frequency and size, in order to improve the experience for everyone.

For that reason, Halo Connect recommends integrators use async queries where possible.

Using async queries allows Halo Link to better control query queueing and resource usage on the practice server. It allows time-sensitive immediate queries to be prioritised appropriately, while reducing the risk of any query timing out.

Optimise usage by requesting batches of data

To reduce your usage and to lighten the load on the practice server, we recommend sending one query to retrieve a batch of data instead of sending multiple small queries at once, and processing the batched data on your server.

For example, if you wish to poll multiple database tables for changes every minute, we recommend sending one query which returns the data for all tables over sending one query per table. You can then post-process the data to split it up. This will save you money on usage, and allow Halo Link to better handle queries from multiple integrations. It will also ensure you receive all the results at once. If you send multiple queries, there may be a slight delay between receiving each result, due to the way Halo Link queues queries and may interleave queries from multiple integrators.

---

Prev: Halo Cloud overview Next: SQL Passthrough query examples