Query values not captured when passed as second argument (prepared statements)

[estiller]

What version of OpenTelemetry are you using?

• @opentelemetry/instrumentation-pg: 0.59.0
• @opentelemetry/auto-instrumentations-node: 0.65.0
• @opentelemetry/sdk-node: 0.206.0

What version of Node are you using?

v22.21.0

What did you do?

I'm using Drizzle ORM with PostgreSQL with prepared statements, which internally calls pg.Client.query() with the following signature:

client.query(
  { name: 'statement_name', text: 'SELECT * FROM users WHERE id = $1' },
  [123] // values passed as second argument
)

I enabled enhancedDatabaseReporting: true in the pg instrumentation config:

'@opentelemetry/instrumentation-pg': {
  enabled: true,
  enhancedDatabaseReporting: true,
}

What did you expect to see?

I expected to see the db.postgresql.values attribute in my spans containing the query parameter values (e.g., ["123"]).

What did you see instead?

The db.postgresql.values attribute is not present in the spans. The span only contains:

• db.statement: The SQL query text
• db.postgresql.plan: The prepared statement name
• Other connection attributes

But no parameter values.

Root Cause

The bug is in instrumentation.ts:

const queryConfig = firstArgIsString
  ? {
      text: arg0 as string,
      values: Array.isArray(args[1]) ? args[1] : undefined,  // ✅ Checks args[1]
    }
  : firstArgIsQueryObjectWithText
  ? (arg0 as utils.ObjectWithText)  // ❌ Just casts arg0, ignores args[1]!
  : undefined;

When the first argument is a query config object (common with prepared statements), the code just casts arg0 without checking if values are passed as args[1]. This means queryConfig.values remains undefined even when values are provided.

Proposed Fix

const queryConfig = firstArgIsString
  ? {
      text: arg0 as string,
      values: Array.isArray(args[1]) ? args[1] : undefined,
    }
  : firstArgIsQueryObjectWithText
  ? {
      ...(arg0 as utils.ObjectWithText),
      // Merge values from args[1] if not already in the config object
      values: (arg0 as any).values ?? (Array.isArray(args[1]) ? args[1] : undefined),
    }
  : undefined;

This would check for values in args[1] when using a query config object, just like it already does for string queries.

Workaround

As a temporary workaround, I'm patching pg.Client.prototype.query and pg.Pool.prototype.query before OpenTelemetry instruments them, to merge args[1] into args[0].values when this pattern is detected.

Additional context

This affects any ORM or library that uses prepared statements with the signature query(config, values) where values are passed separately from the config object. This includes:

• Drizzle ORM (when using .prepare())
• Potentially other ORMs that use a similar pattern

The requestHook cannot work around this issue because by the time it's called, queryConfig.values is already undefined.

_{Tip: React with 👍 to help prioritize this issue. Please use comments to provide useful context, avoiding +1 or me too, to help us triage it. Learn more here.}

[aryamohanan]

Hi @maryliag ,

I’d like to work on this issue, but before proceeding, could you please review the reported bug? I believe it should be addressed, and if you agree, could you kindly assign the issue to me?

Thank you!

[maryliag]

@estiller to clarify, values are sanitize, so your values such as[123] will never be displayed, and you will see ? instead. So I'm wondering if is worth parsing the extra values, if we won't display them at all.
Do you have a use case where knowing that there are values is useful?

I'm trying to understand what value adding a lot of ?s would bring that would compensate the extra performance and cardinality cost.

[estiller]

Hi @maryliag, that's a great question. As I understand it, enhancedDatabaseReporting is meant to attach "additional information about query parameters and results." But when I turned it on in my use case (Drizzle ORM with prepared statements), it didn't add any extra tags at all.

Without it, I got db.postgresql.plan (prepared statement name) and db.statement (the parameterised statement with ?). However, in my case, I wanted to see the actual values used in the query (i.e. the id value itself). Reading the documentation, I thought that setting enhancedDatabaseReporting to true would provide it, but it made no difference at all. No extra tags were attached to the span.

I then used an AI agent to track down the bug (I thought I was doing something wrong), which led to the following workaround in my app's code before instrumentation:

const patchPgForDrizzle = () => {
  try {
    const pg = require('pg');
    const originalClientQuery = pg.Client.prototype.query;
    const originalPoolQuery = pg.Pool.prototype.query;

    const patchQuery = (original: typeof originalClientQuery) => {
      return function (this: unknown, ...args: unknown[]) {
        // If first arg is an object with a name (prepared statement) and
        // second arg is an array (values), merge them into the config object
        if (
          args.length >= 2 &&
          typeof args[0] === 'object' &&
          args[0] !== null &&
          'name' in args[0] &&
          Array.isArray(args[1]) &&
          !('values' in args[0])
        ) {
          args[0] = { ...args[0], values: args[1] };
          args.splice(1, 1); // Remove the second argument
        }
        return original.apply(this, args);
      };
    };

    pg.Client.prototype.query = patchQuery(originalClientQuery);
    pg.Pool.prototype.query = patchQuery(originalPoolQuery);
  } catch (error) {
    console.error('[Telemetry] Failed to patch pg library:', error);
  }
};

This is tested and works in my scenario, and adds the db.postgresql.values tag. It makes me believe that the above suggested fix could also work and produce the real values (not ?).

[maryliag]

Thank you for the detailed replied! 😄

In that case, I agree the flag should make the values to be added. To confirm to whomever will be working on this: the query text is still sanitized (even with this flag as true), you will however get a new attribute with the values.