GeezerVison

The World Wide Web, restored to 800 pixels of glory.

GeezerVison is showing google.github.io — sqlcommenter

Sequelize.js

Introduction
Requirements
Installation
- Manually
- Package manager
Usage
- Plain sequelize wrapper
- Express middleware
Fields
- Options
End to end examples

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`

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

With OpenTelemetry
With Route
With DB Driver and CLIENT TIMEZONE
With All Options Set

// 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:

With OpenTelemetry
With Route
With DB Driver and CLIENT TIMEZONE
With All Set

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/

Knex.js Express.js

You are visitor 01402503 — GeezerVison does not store the pages you look up.
Best viewed with Netscape Navigator 4.7 or Internet Explorer 6 at 800×600. No cookies required.