prettify SQL queries during debug (#3635)

* prettify SQL queries during debug

* Use sql-formatter

Co-authored-by: Chocobozzz <me@florianbigard.com>

4 files changed, 21 insertions, 5 deletions

diff --git a/package.json b/package.json
index 26ed6453f..ff03e2be9 100644
--- a/package.json
+++ b/package.json
@@ -147,6 +147,7 @@
     "sequelize-typescript": "^2.0.0-beta.1",
     "sitemap": "^6.1.0",
     "socket.io": "^3.0.2",
+    "sql-formatter": "^4.0.0-beta.0",
     "srt-to-vtt": "^1.1.2",
     "tsconfig-paths": "^3.9.0",
     "tslib": "^2.0.0",
diff --git a/server/helpers/logger.ts b/server/helpers/logger.ts
index a4bd41427..f1808849e 100644
--- a/server/helpers/logger.ts
+++ b/server/helpers/logger.ts
@@ -1,10 +1,11 @@
 // Thanks http://tostring.it/2014/06/23/advanced-logging-with-nodejs/
 import { mkdirpSync } from 'fs-extra'
+import { omit } from 'lodash'
 import * as path from 'path'
+import { format as sqlFormat } from 'sql-formatter'
 import * as winston from 'winston'
 import { FileTransportOptions } from 'winston/lib/winston/transports'
 import { CONFIG } from '../initializers/config'
-import { omit } from 'lodash'
 import { LOG_FILENAME } from '../initializers/constants'
 
 const label = CONFIG.WEBSERVER.HOSTNAME + ':' + CONFIG.WEBSERVER.PORT
@@ -39,13 +40,20 @@ function getLoggerReplacer () {
 }
 
 const consoleLoggerFormat = winston.format.printf(info => {
-  const obj = omit(info, 'label', 'timestamp', 'level', 'message')
+  const obj = omit(info, 'label', 'timestamp', 'level', 'message', 'sql')
 
   let additionalInfos = JSON.stringify(obj, getLoggerReplacer(), 2)
 
   if (additionalInfos === undefined || additionalInfos === '{}') additionalInfos = ''
   else additionalInfos = ' ' + additionalInfos
 
+  if (info.sql) {
+    additionalInfos += '\n' + sqlFormat(info.sql, {
+      language: 'sql',
+      ident: '  '
+    })
+  }
+
   return `[${info.label}] ${info.timestamp} ${info.level}: ${info.message}${additionalInfos}`
 })
 
diff --git a/server/initializers/database.ts b/server/initializers/database.ts
index 128ed5b75..61768234f 100644
--- a/server/initializers/database.ts
+++ b/server/initializers/database.ts
@@ -69,12 +69,12 @@ const sequelizeTypescript = new SequelizeTypescript({
   logging: (message: string, benchmark: number) => {
     if (process.env.NODE_DB_LOG === 'false') return
 
-    let newMessage = message
+    let newMessage = 'Executed SQL request'
     if (isTestInstance() === true && benchmark !== undefined) {
-      newMessage += ' | ' + benchmark + 'ms'
+      newMessage += ' in ' + benchmark + 'ms'
     }
 
-    logger.debug(newMessage)
+    logger.debug(newMessage, { sql: message })
   }
 })
 
diff --git a/yarn.lock b/yarn.lock
index 61a2ea05e..11f5dc893 100644
--- a/yarn.lock
+++ b/yarn.lock
@@ -7259,6 +7259,13 @@ sprintf-js@~1.0.2:
   resolved "https://registry.yarnpkg.com/sprintf-js/-/sprintf-js-1.0.3.tgz#04e6926f662895354f3dd015203633b857297e2c"
   integrity sha1-BOaSb2YolTVPPdAVIDYzuFcpfiw=
 
+sql-formatter@^4.0.0-beta.0:
+  version "4.0.0-beta.0"
+  resolved "https://registry.yarnpkg.com/sql-formatter/-/sql-formatter-4.0.0-beta.0.tgz#4981ecf86268184a61f19745d820a70c947a7d3f"
+  integrity sha512-ltWCnnig6UNvnl6MFAoegO13xiET2trcX8Idbq1LBpkCCOn9x/V2Gqs6Md+fGxnpkP8dMtUM1HWHdUYGuEDfSg==
+  dependencies:
+    argparse "^2.0.1"
+
 srt-to-vtt@^1.1.2:
   version "1.1.3"
   resolved "https://registry.yarnpkg.com/srt-to-vtt/-/srt-to-vtt-1.1.3.tgz#a9bc16cde5412e000e59ffda469f3e9befed5dde"