Query logging/debug output

[pyoio]

Hi, I went through the documentation/examples and I can't find any obvious way to enable debug/logging output.

I'd just like any query being executed to be automatically output to console.log, is this currently possible?

[brianc]

Hey @drpyo there are two ways to do this.

One is to monkey patch the client#query method to log and then call the original method.

The second is to use a single place in your app to run queries such as a data access layer or query helper or something, and then you can log there.

There is no support for plugging a logger into node-postgres directly because everyone has a different concept of how things should be logged, etc.

[kerimdzhanov]

Hi!

What do you guys think about triggering event like pg.on('query') to give ability to bind any kind of logger/debugger?

For example, I have some lib/db.js in my project:

var pg = require('pg');
var bunyan = require('bunyan');

pg.on('query', function (sql) {
  bunyan.debug(sql);
  // or simply console.log(sql);
});

// ...

[antonmedv]

Is it possible to log queries with exact params?

[chriszrc]

I ended up enabling sql logging for the local postgres db directly, and this actually works quite well -

http://stackoverflow.com/a/722236/228369

[artyomtrityak]

You can use this monkeypatch snippet to log queries

const oldPoolQuery = pool.query;
pool.query = (...args) => {
  console.log('QUERY:', args);
  return oldPoolQuery.apply(pool, args);
};

This does not logs final query (that you should use postrgresql logging) but at least you will be able to see function calls. This might be helpful for simple use-cases.

[tugorez]

How about this ? This actually logs the final query.

const Query = require('pg').Query;
const submit = Query.prototype.submit;
Query.prototype.submit = function() {
  const text = this.text;
  const values = this.values;
  const query = values.reduce((q, v, i) => q.replace(`$${i + 1}`, v), text);
  console.log(query);
  submit.apply(this, arguments);
};

It can be enhanced with colors and styling stuff. Are you guys interested in this? So I can start working in a PR

[jwhiting]

@tugorez That's progress, but it still doesn't log valid SQL since it's just doing simple string conversion on the inputs, which is not what happens in actual fact. Case in point: pass a date in as an argument. What you see is the toString() representation of the date which is not valid SQL. The library is doing deeper preparation of the query arguments, and while I haven't looked thoroughly through the code, it appears to be sending the statement template and arguments using the extended query api provided by postgres - https://www.postgresql.org/docs/devel/static/protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY - so it may not be possible to actually get the valid final SQL, not sure.

[retorquere]

@tugorez when I try that code I get Cannot read property 'reduce' of undefined on the line that says const query = values.reduce((q, v, i) => q.replace($${i + 1}, v), text);

[retorquere]

Ah never mind, it does that when you have a query without parameters. While still not valid SQL, I settled on

  Query.prototype.submit = function () {
    const text = this.text;
    const values = this.values || [];
    const query = text.replace(/\$([0-9]+)/g, (m, v) => JSON.stringify(values[parseInt(v) - 1]))
    console.log(query);
    submit.apply(this, arguments);
  };

which also supports $x being present more than once, and $10 and higher.

[bf]

@retorquere your example still works. thank you

[mariusa]

How could this be adapted for pg-cursor ?

[hos]

With this, you can log only queries where you add comment --debug.

import pgUtils from 'pg/lib/utils'
const submit = Query.prototype.submit

export function prettyPrintQuery(text: string, values: any[]) {
  return text.replace(/\$([0-9]+)/g, (m, v) => {
    const value = pgUtils.prepareValue(values[parseInt(v, 10) - 1])
    if (typeof value === 'string') return `'${value}'`
    if (value instanceof Date) return `'${value.toISOString()}'::timestamptz`
    if (value instanceof Number) return value.toString()
    return JSON.stringify(value)
  })
}

Query.prototype.submit = function patchedSubmit() {
  const text = this.text
  const values = this.values || []
  const query = prettyPrintQuery(text, values)

  if (/--debug/i.test(query)) {
    logger.log(query)
  }

  // eslint-disable-next-line prefer-rest-params
  submit.apply(this, arguments)
}

[egorovli]

How could this be adapted for pg-cursor ?

@mariusa You could do exactly the same with Cursor:

let submit = {
  cursor: Cursor.prototype.submit
}

Cursor.prototype.submit = function patchedSubmit() {
  let text = this.text
  let values = this.values || []
  let query = prettyPrintQuery(text, values)

  if (/--debug/i.test(query)) {
    console.log(query)
  }

  submit.cursor.apply(this, arguments)
}