Aws-xray-sdk-node: How to trace Sequelize queries with AWS Xray in NodeJs/PSQL?

[Suvab-rently]

AWS X-ray documentation only shows how to capture pg client. How do we use it for sequelize queries?
https://github.com/shun-tak/sequelize-aws-x-ray
I have checked this out, doesn't work.

[willarmiros]

Currently the X-Ray SDK for node.js does not support queries made with sequelize. We'll add this feature request to our backlog. #272, requesting support for another ORM, is related.

The repository you linked is not officially supported by AWS so I cannot speak to its functionality.

[Suvab-rently]

Thanks for your reply. I hope this happens soon :).

[ianmetcalf]

This is how we capture the pg client:

const sequelize = new Sequelize(config.database_url);

if (xray) {
  Sequelize.useCLS(xray.getNamespace());

  try {
    xray.capturePostgres(sequelize.connectionManager.lib);
  } catch (err) {
    log.error(err);
  }
}

Using:

    "sequelize": "5.21.5",
    "aws-xray-sdk-postgres": "3.0.0-alpha.1",

[Suvab-rently]

@ianmetcalf Thanks. But this actually only captured the db calls and could not still capture the exact queries.
Still it was pretty helpful. thanks

[ianmetcalf]

@Suvab-rently I don't think this library supports capturing the actual SQL query, just the database call. We ended up writing our own capture function that sanitizes the query and includes it in the trace.

Here is what data is captured:
https://github.com/aws/aws-xray-sdk-node/blob/master/packages/postgres/lib/postgres_p.js#L124-L132

[JeremieDoctrine]

@ianmetcalf can you expose your capture function please? Thanks for your snippet above, very useful :)

[ianmetcalf]

@JeremieDoctrine sure.

I'll make the disclaimer that we are using a simple regex to sanitize the query. This works for us because our application isn't sending anything particularly sensitive to the datebase, but you should verify this for your application.

function capturePostgres(pg) {
  if (!pg.Client.prototype.__query) {
    Object.assign(pg.Client.prototype, {
      __query: pg.Client.prototype.query,

      query(...args) {
        const segment = xray.getSegment();
        let subsegment;

        if (segment) {
          subsegment = segment.addNewSubsegment(`postgres://${ this.host }`);
          subsegment.namespace = 'remote';

          subsegment.addSqlData({
            url: `postgres://${ this.host }:${ this.port }/${ this.database }`,
            user: this.user,
          });
        }

        const result = this.__query(...args);

        if (subsegment) {
          let query = {};

          if (this._queryable && !this._ending) {
            if (this.queryQueue.length) {
              query = this.queryQueue[this.queryQueue.length - 1];
            } else {
              query = this.activeQuery;
            }
          }

          if (query.text) {
            let sql = query.text;

            if (/INSERT|UPDATE/i.test(sql)) {
              sql = sql.replace(/'([^']|'')*?'(?!')/g, '?');
            }

            if (subsegment.sql) {
              subsegment.sql.sanitized_query = sql;
            }
          }

          if (typeof query.callback === 'function') {
            const cb = query.callback;

            query.callback = (err, data) => {
              subsegment.close(err);
              cb(err, data);
            };
          } else {
            query.on('end', () => {
              subsegment.close();
            });

            query.on('error', err => {
              subsegment.close(err);
            });
          }
        }

        return result;
      },
    });
  }
}

Using:

    "pg": "7.18.2",
    "aws-xray-sdk-postgres": "3.0.0-alpha.1",

[lostaloneesk]

This is how we capture the pg client:

const AWSXRay = require('aws-xray-sdk');
const sequelize = new Sequelize({
  host: 'localhost',
  port: 5432,
  username: 'xxx',
  password: 'xxx',
  database: 'xxx',
  dialectModule: AWSXRay.capturePostgres(require('pg')),
});

BTW, I found the work around in here.

[opusmagnum]

It works pretty well, thanks @lostaloneesk!
The actual version of Sequelize (e.g. 5.21.7) requires to specify the dialect: 'postgres' attribute, if dialectModule is set.
Otherwise:

"errorMessage":"Dialect needs to be explicitly supplied as of v4.0.0"

[ravitejavgyan]

Hi,

I am using sequelize with mysql db for nodejs. DB Queries are not traced by x-ray.
Is it possible or not. Can you please tell me and may i know the status?

This is the snippet i have used but queries are not generating.
dialectModule: AWSXRay.captureMySQL(require('mysql2'))

Thanks,
Raviteja

[willarmiros]

Hi @ravitejavgyan,

Can you make a separate issue with the exact error you're getting from this? It appears that workaround has worked for other customers. However since we don't officially support sequelize if your problem is an issue stemming from sequelize we won't be able to assist further.