sqlcommenter

Introduction

This package is in the form of Sequelize.Client.prototype.query wrapper whose purpose is to augment a SQL statement right before execution, with information about the controller and user code to help correlate them with SQL statements emitted by Sequelize.js.

Besides plain sequelize.js wrapping, we also provide a wrapper for the following frameworks:

Requirements

Installation

We can add integration into our applications in the following ways:

Manually

Please read installing sqlcommenter-nodejs from source

Package manager

Add to your package.json the dependency

{     "@google-cloud/sqlcommenter-sequelize": "*" }

and then run npm install to get the latest version or

npm install @google-cloud/sqlcommenter-sequelize --save

Usage

Plain sequelize wrapper

const {wrapSequelize} = require('@google-cloud/sqlcommenter-sequelize'); const Sequelize = require('sequelize');  // Create the sequelize client. const sequelize = new Sequelize(options);  // Finally wrap the sequelize client. wrapSequelize(sequelize);

Express middleware

This wrapper/middleware can be used as is or better with express.js

const {wrapSequelizeAsMiddleware} = require('@google-cloud/sqlcommenter-sequelize'); const Sequelize = require('sequelize'); const sequelize = new Sequelize(options); const app = require('express')();  // Use the sequelize+express middleware. app.use(wrapSequelizeAsMiddleware(sequelize));

Fields

In the database server logs, the comment’s fields are:

comma separated key-value pairs e.g. route='%5E%2Fpolls%2F'
values are SQL escaped i.e. key='value'
URL-quoted except for the equals(=) sign e.g route='%5Epolls/%24'. so should be URL-unquoted

Field	Format	Description	Example
`client_timezone`	`<string>`	URL quoted name of the timezone used when converting a date from the database into a JavaScript date	`'+00:00'`
`db_driver`	`<sequelize>`	URL quoted name and version of the database driver	`db_driver='sequelize'`
`route`	`<the route used>`	URL quoted route used to match the express.js controller	`route='%5E%2Fpolls%2F`
`traceparent`	`<traceparent header>`	URL quoted W3C `traceparent` header	`traceparent='00-3e2914ebce6af09508dd1ff1128493a8-81d09ab4d8cde7cf-01'`
`tracestate`	`<tracestate header>`	URL quoted W3C `tracestate` header	`tracestate='rojo%253D00f067aa0ba902b7%2Ccongo%253Dt61rcWkgMzE'`

Options

When creating the middleware, one can optionally configure the injected comments by passing in the include and options objects:

wrapMainSequelizeAsMiddleware(Sequelize, include={...}, options={...});

`include` config

A map of values to be optionally included in the SQL comments.

Field	On by default
client_timezone	❌
db_driver	❌
route	✔
traceparent	❌
tracestate	❌

`options` config

A configuration object specifying where to collect trace data from. Accepted fields are: TraceProvider: Should be OpenTelemetry, indicating which library to collect trace context from.

Field	Possible values
TraceProvider	`OpenTelemetry`

Options examples

wrapMainSequelizeAsMiddleware(     Sequelize,     include={ traceparent: true, tracestate: true },     options={ TraceProvider: 'OpenTelemetry' } );

wrapMainSequelizeAsMiddleware(Sequelize, include={client_timezone: true});

wrapMainSequelizeAsMiddleware(Sequelize, include={route: true});

wrapMainSequelizeAsMiddleware(Sequelize, include={db_driver: true});

// Manually set all the variables. wrapMainSequelizeAsMiddleware(     Sequelize,     include={         client_timezone: true,         db_driver: true,         route: true,         traceparent: true,         tracestate: true,     },     options={ TraceProvider: 'OpenTelemetry' } );

End to end examples

Check out a full express + opentelemetry example here.

Source code

// In file app.js. const { NodeTracerProvider } = require("@opentelemetry/node"); const { BatchSpanProcessor } = require("@opentelemetry/tracing"); const {   TraceExporter, } = require("@google-cloud/opentelemetry-cloud-trace-exporter");  const tracerProvider = new NodeTracerProvider(); // Export to Google Cloud Trace tracerProvider.addSpanProcessor(   new BatchSpanProcessor(new TraceExporter({ logger }), {     bufferSize: 500,     bufferTimeout: 5 * 1000,   }) ); tracerProvider.register();  // OpenTelemetry initialization should happen before importing any libraries // that it instruments const { Sequelize } = require("sequelize"); const {   wrapSequelizeAsMiddleware, } = require("@google-cloud/sqlcommenter-sequelize");  const sequelize = new Sequelize("postgres://user:[email protected]:5432/dbname");  const express = require("express"); const app = express(); const port = process.env.APP_PORT || 3000;  // SQLCommenter express middleware injects the route into the traces app.use(   wrapSequelizeAsMiddleware(     sequelize,     {       client_timezone: false,       db_driver: false,       route: true,       traceparent: true,       tracestate: true,     },     { TraceProvider: "OpenTelemetry" }   ) );  app.get("/", (req, res) => res.send("Hello, sqlcommenter-nodejs!!")); app.get("^/polls/:param", function (req, res) {   sequelize     .query("SELECT * from polls_question")     .then(function (polls) {       const blob = JSON.stringify(polls);       res.send(blob);     })     .catch(function (err) {       console.log(err);       res.send(500);     }); }); app.listen(port, () => console.log(`Application listening on ${port}`));

// In file app.js. const Sequelize = require('sequelize'); const {wrapSequelizeAsMiddleware} = require('@google-cloud/sqlcommenter-sequelize'); const express = require('express');  // Using a connection URI const sequelize = new Sequelize('postgres://user:[email protected]:5432/dbname');  const app = express(); const port = process.env.APP_PORT || 3000;  // Use the sequelize+express middleware with route app.use(wrapSequelizeAsMiddleware(sequelize, {route: true}));  app.get('/', (req, res) => res.send('Hello, sqlcommenter-nodejs!!')); app.get('^/polls/:param', function(req, res) {     sequelize.query('SELECT * from polls_question').then(function(polls) {         const blob = JSON.stringify(polls);         res.send(blob);     }).catch(function(err) {         console.log(err);         res.send(500);     }); }); app.listen(port, () => console.log(`Application listening on ${port}`));

// In file app.js const Sequelize = require('sequelize'); const {wrapSequelizeAsMiddleware} = require('@google-cloud/sqlcommenter-sequelize'); const express = require('express');  // Using a connection URI const sequelize = new Sequelize('postgres://user:[email protected]:5432/dbname');  const app = express(); const port = process.env.APP_PORT || 3000;  // Use the sequelize+express middleware with db driver and timezone app.use(wrapSequelizeAsMiddleware(sequelize, {     db_driver: true,      client_timezone: true }));  app.get('/', (req, res) => res.send('Hello, sqlcommenter-nodejs!!')); app.get('^/polls/:param', function(req, res) {     sequelize.query('SELECT * from polls_question').then(function(polls) {         const blob = JSON.stringify(polls);         res.send(blob);     }).catch(function(err) {         console.log(err);         res.send(500);     }); }); app.listen(port, () => console.log(`Application listening on ${port}`));

// In file app.js. const { NodeTracerProvider } = require("@opentelemetry/node"); const { BatchSpanProcessor } = require("@opentelemetry/tracing"); const {   TraceExporter, } = require("@google-cloud/opentelemetry-cloud-trace-exporter");  const tracerProvider = new NodeTracerProvider(); // Export to Google Cloud Trace tracerProvider.addSpanProcessor(   new BatchSpanProcessor(new TraceExporter({ logger }), {     bufferSize: 500,     bufferTimeout: 5 * 1000,   }) ); tracerProvider.register();  // OpenTelemetry initialization should happen before importing any libraries // that it instruments const { Sequelize } = require("sequelize"); const {   wrapSequelizeAsMiddleware, } = require("@google-cloud/sqlcommenter-sequelize");  const sequelize = new Sequelize("postgres://user:[email protected]:5432/dbname");  const express = require("express"); const app = express(); const port = process.env.APP_PORT || 3000;  // SQLCommenter express middleware injects the route into the traces app.use(   wrapSequelizeAsMiddleware(     sequelize,     {       client_timezone: true,       db_driver: true,       route: true,       traceparent: true,       tracestate: true,     },     { TraceProvider: "OpenTelemetry" }   ) );  app.get("/", (req, res) => res.send("Hello, sqlcommenter-nodejs!!")); app.get("^/polls/:param", function (req, res) {   sequelize     .query("SELECT * from polls_question")     .then(function (polls) {       const blob = JSON.stringify(polls);       res.send(blob);     })     .catch(function (err) {       console.log(err);       res.send(500);     }); }); app.listen(port, () => console.log(`Application listening on ${port}`));

which after running by

$ node app.js  Application listening on 3000

Results

On making a request to that server at http://localhost:3000/polls/1000, the PostgreSQL logs show:

2019-06-03 14:32:10.842 PDT [32004] LOG:  statement: SELECT * from polls_question  /*traceparent='00-11000000000000ff-020000ee-01',tracestate='brazzaville=t61rcWkgMzE,rondo=00f067aa0ba902b7'*/

2019-06-03 14:32:10.842 PDT [32004] LOG:  statement: SELECT * from polls_question  /*route='%5E%2Fpolls%2F%1000'*/

2019-06-03 14:32:10.842 PDT [32004] LOG:  statement: SELECT * from polls_question  /*client_timezone:'%2B00%3A00',db_driver='sequelize%3A0.0.1'*/

2019-06-03 14:32:10.842 PDT [32004] LOG:  statement: SELECT * from polls_question  /*client_timezone:'%2B00%3A00',db_driver='sequelize%3A0.0.1',route='%5E%2Fpolls%2F%1000',traceparent='00-11000000000000ff-020000ee-01',tracestate='brazzaville=t61rcWkgMzE,rondo=00f067aa0ba902b7'*/

References

Resource	URL
@google-cloud/sqlcommenter-sequelize on npm	https://www.npmjs.com/package/@google-cloud/sqlcommenter-sequelize
express.js	https://expressjs.com/

Sequelize.js