Integrating Azure Application Insights with Knex and Postgres

If you’re using Azure, it’s likely you’ll want to use Application Insights, their monitoring toolkit. This is a pretty great tool and the Azure’s applicationinsights package does a good job automatically instrumenting most Node apps. Once place where it falls short is that it doesn’t know how to integrate with the Knex query builder.

I recently spent quite a bit of time figuring out how to integrate these two tools. This was one of those rabbit holes where all you get is one mysterious answer from 2017 with no code.

The fundamental problem is that, while Application Insights will automatically instrument pg as a dependency, it won’t correlate that tracking with the ongoing HTTP request, making it difficult to associate slow requests with slow queries.

I’ll get right to it, then do a little explanation afterwards.

Before you get started, make sure you’re using a 7.x version of pg. The recent 8.x line isn’t supported by diagnostic-channel-publishers, which applicationinsights uses under the hood to instrument pg with tracking.

We have some Express middleware that adds the apps current Knex connection to the request object.

It looks something like:

const knexBuilder = require('knex');
function createDBMiddleware(options) {
  const knex = knexBuilder(options);

  return function dbMiddleware(req, res, next) {
    req.knex = knex;
    next();
  }
}

To properly integrate Application Insights and Knex, we need to do the following:

const knexBuilder = require('knex');
const appInsights = require('applicationinsights');

function createDBMiddleware(options) {
  const knex = knexBuilder(options);
  const originalKnexQuery = knex.client.query;

  return function dbMiddleware(req, res, next) {
    // Clone the shared knex object for this request, so we can 
    // modify its parameters without impacting other requests.
    const requestKnex = knex.withUserParams();

    // Wrap the knex query function in the current request context.
    // See https://github.com/microsoft/ApplicationInsights-node.js/pull/586/files#diff-f72505085f92201f7739351a07b55525
    // Without this, the Postgres call is not correlated through the 
    // Knex query.
    requestKnex.client.query =    
      appInsights.wrapWithCorrelationContext(
        originalKnexQuery
      );
    
    req.knex = requestKnex;
    next();
  }
}

First, we clone the Knex connection object per-request. We do this because otherwise, we’d be setting the request context on the shared knex object which would lead to invalid correlation for overlapping requests.

This adds some memory overhead — I’d suggest profiling and wrapping it in a flag before deploying to expensive or high-volume systems. It’s the only way I could find to monkey-patch the knex.client.query method per-request, but I’d love to hear other solutions.

With the cloned knex object, we wrap the knex.client.query method in applicationinsight’s wrapWithCorrelationContext method. This returns a function with the correlation context of the application at the time of invocation. Because we’re calling this function in an Express middleware, it gets the correlation context of the ongoing request.

That’s it! With this monkey-patch, your Knex queries should all be correctly correlated in Application Insights.