log to mysql

sqlLogger.js

// the logger to sql class....based on winston-mysql
/**
 * This is a MySQL transport module for winston.
 * https://github.com/winstonjs/winston
 * I made some customizations
 */

const Transport = require('winston-transport');
const MySql = require('mysql2');

/**
 * @constructor
 * @param {Object} options      Options for the MySQL & log plugin
 * @param {String} options.host Database host
 * @param {String} options.user Database username
 * @param {String} options.password Database password
 * @param {String} options.database Database name
 * @param {String} options.table  Database table for the logs
 * @param {Object} **Optional** options.fields Log object, set custom fields for the log table
 */
module.exports = class MySQLTransport extends Transport {
  constructor(options = {}) {
    super(options);

    this.name = 'MySQL';

    //Please visit https://github.com/felixge/node-mysql#connection-options to get default options for mysql module
    this.options = options || {};

    // check parameters
    if (!options.host) {
      throw new Error('The database host is required');
    }
    if (!options.user) {
      throw new Error('The database username is required');
    }
    if (!options.password) {
      throw new Error('The database password is required');
    }
    if (!options.database) {
      throw new Error('The database name is required');
    }
    if (!options.table) {
      throw new Error('The database table is required');
    }

    //check custom table fields - protect
    if (!options.fields) {
      this.options.fields = {};
      //use default names
      this.fields = {
        level: 'level',
        meta: 'meta',
        message: 'message',
        timestamp: 'timestamp'
      }

    } else {
      //use custom table field names
      const { level, meta, message, timestamp } = this.options.fields;
      this.fields = {
        // for custome fields
        ...this.options.fields,
        level,
        meta,
        message,
        timestamp
      }
    }

    const connOpts = {
      host: options.host,
      user: options.user,
      password: options.password,
      database: options.database
    }

    this.pool = MySql.createPool(connOpts);

  }

  /**
   * function log (info, callback)
   * {level, msg, [meta]} = info
   * @level {string} Level at which to log the message.
   * @msg {string} Message to log
   * @meta {Object} **Optional** Additional metadata to attach
   * @callback {function} Continuation to respond to when complete.
   * Core logging method exposed to Winston. Metadata is optional.
   */

  log(info, callback) {
    // get log content
    const { level, message, ...winstonMeta } = info;

    process.nextTick(() => {
      // protect
      if (!callback) {
        callback = () => { };
      }

      this.pool.getConnection((err, connection) => {
        if (err) {
          // connect error
          return callback(err, null);
        }

        //set log object
        const log = {};
        delete winstonMeta.env
        log[this.fields.meta] = JSON.stringify(winstonMeta);
        log[this.fields.level] = level;
        log[this.fields.message] = message;
        log[this.fields.timestamp] = new Date();

        for (const key in info) {
          if (!['meta', 'level', 'message', 'timestamp'].includes(key) && this.fields.hasOwnProperty(key)) {
            log[key] = info[key]
          }
        }

        //Save the log
        connection.query(
          `INSERT INTO ${this.options.table} SET ?`,
          log,
          (err, results, fields) => {
            if (err) {
              setImmediate(() => {
                this.emit('error', err);
              });
              return callback(err, null);
            }
            //finished
            connection.release();
            setImmediate(() => {
              this.emit('logged', info);
            });
            callback(null, true);
          }
        );
      });
    });
  }
};

//the using service

var ip = require("ip");
const { format, createLogger, transports } = require('winston');
const { combine, errors, prettyPrint } = format;
// see above class
const winstonMysql = require('../utils/winston-sql.util');


const fs = require('fs');

const logsDir = './logs';
if (!fs.existsSync(logsDir)) {
  fs.mkdirSync(logsDir);
}

const myFormatter = format((info) => {
  info.env = ip.address()
  return info;
})();

const logFormat = format.printf(function (info) {
  let date = new Date().toISOString();
  return `${date}-${info.level}:${info.message} \n meta:${JSON.stringify(info.meta, null, 4)}\n`;
});

const SqlLogger = createLogger({
  level: 'debug',
  format: combine(errors({ stack: true }), myFormatter, prettyPrint()),
  transports: [
    new transports.Console({
      format: format.combine(format.colorize(), logFormat)
    }),
    new winstonMysql({
      host: process.env.DB_HOST,
      user: process.env.DB_USER,
      password: process.env.DB_PASSWORD,
      database: 'logging_nodejs',
      table: 'sys_logs_default',
      charset: 'utf8_general_ci',
      fields: { env: 'env', level: 'level', meta: 'meta', message: 'message', timestamp: 'timestamp' }
    }),
    new transports.File({ filename: 'logs/error.log', level: 'error' }),
    new transports.File({ filename: 'logs/combined.log' }),
  ],
});

function limitString(str) {
  return str?.length > 200 ? str.substring(0, 200) + '...' : str;
}

function limitJson(obj) {
  try {
    const str = JSON.stringify(obj)
    if (str.length < 200) return obj

    return { shortDescription: limitString(str) }
  } catch (error) {
    console.log("🚀 ~ file: SqlLogger.service.js ~ line 54 ~ limitJson ~ error", error)
    return { error: 'meta json too big' }
  }
}

// module.exports = { SqlLogger }
module.exports = {
  debug: (message, meta = '') => {
    SqlLogger.debug(limitString(message), { meta: limitJson(meta) });
  },

  info: (message, meta = '') => {
    SqlLogger.info(limitString(message), { meta: limitJson(meta) });
  },

  warn: (message, meta = '') => {
    SqlLogger.warn(limitString(message), { meta: limitJson(meta) });
  },

  error: (message, meta = '') => {
    const errorMsg = new Error(limitString(message));
    SqlLogger.error(errorMsg, { meta: limitJson(meta) });
  },
};