'How to indent logged queries in sequelize?

Indenting long queries is fundamental to help debug or understand what Sequelize is doing. Is there any built-in way to do it automatically for every logged query?

E.g. given:

#!/usr/bin/env node
const assert = require('assert')
const path = require('path')
const { DataTypes, Sequelize } = require('sequelize')
let sequelize
if (process.argv[2] === 'p') {
  sequelize = new Sequelize('tmp', undefined, undefined, {
    dialect: 'postgres',
    host: '/var/run/postgresql',
  })
} else {
  sequelize = new Sequelize({
    dialect: 'sqlite',
    storage: 'tmp.sqlite'
  })
}
;(async () => {
const IntegerNames = sequelize.define('IntegerNames', {
  value: { type: DataTypes.INTEGER },
  name: { type: DataTypes.STRING },
});
await IntegerNames.sync({ force: true })
async function reset() {
  await sequelize.truncate({ cascade: true })
  await IntegerNames.create({ value: 2, name: 'two' })
  await IntegerNames.create({ value: 3, name: 'three' })
  await IntegerNames.create({ value: 5, name: 'five' })
}
await reset()
let rows
rows = await IntegerNames.findAll()
assert.strictEqual(rows[0].id, 1)
assert.strictEqual(rows[0].name, 'two')
assert.strictEqual(rows[0].value, 2)
assert.strictEqual(rows[1].id, 2)
assert.strictEqual(rows[1].name, 'three')
assert.strictEqual(rows[1].value, 3)
assert.strictEqual(rows[2].id, 3)
assert.strictEqual(rows[2].name, 'five')
assert.strictEqual(rows[2].value, 5)
assert.strictEqual(rows.length, 3)
})().finally(() => { return sequelize.close() })

I get one of the logged lines:

Executing (default): CREATE TABLE IF NOT EXISTS `IntegerNames` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `value` INTEGER, `name` VARCHAR(255), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL);

but how could I get something like:

CREATE TABLE IF NOT EXISTS `IntegerNames` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `value` INTEGER,
  `name` VARCHAR(255),
  `createdAt` DATETIME NOT NULL,
  `updatedAt` DATETIME NOT NULL
);

?

Tested with

package.json:

{
  "name": "tmp",
  "private": true,
  "version": "1.0.0",
  "dependencies": {
    "pg": "8.5.1",
    "pg-hstore": "2.3.3",
    "sequelize": "6.14.0",
    "sqlite3": "5.0.2"
  }
}
sequelize.js


Solution 1:[1]

I couldn't find a good built-in way, so I'm using the sql-formatter third party library to parse and indent the query strings as a workaround: https://github.com/zeroturnaround/sql-formatter

Unfortunately that library doesn't support SQLite yet: https://github.com/zeroturnaround/sql-formatter/issues/133 but whatever it is that it uses by default at least still makes the query more readable (but not runnable):

main.js

#!/usr/bin/env node
// https://cirosantilli.com/sequelize-example
const assert = require('assert')
const path = require('path')
const { DataTypes, Sequelize } = require('sequelize')
const sql_formatter = require('sql-formatter')
let dialect
let language
function logging(query_string, query_object) {
  console.log(sql_formatter.format(query_string.replace(/^.*: /, ''), { language }))
  if (query_object.bind !== undefined) {
    // https://stackoverflow.com/questions/55715724/how-to-log-queries-with-bounded-paramenters-in-sequelize
    // https://stackoverflow.com/questions/59712807/sequelize-how-to-log-raw-query
    console.log(query_object.bind.map((v, i) => [i + 1, v]));
  }
  console.log();
}
if (process.argv[2] === 'p') {
  dialect = 'postgres'
  sequelize = new Sequelize('tmp', undefined, undefined, {
    dialect,
    host: '/var/run/postgresql',
    logging,
  })
} else {
  dialect = 'sqlite'
  sequelize = new Sequelize({
    dialect,
    storage: 'tmp.sqlite',
    logging,
  })
}
if (dialect === 'sqlite') {
  // Not implemented.
  // https://github.com/zeroturnaround/sql-formatter/issues/133
} else if (dialect === 'postgres') {
  language = 'postgresql'
}
;(async () => {
const IntegerNames = sequelize.define('IntegerNames', {
  value: { type: DataTypes.INTEGER },
  name: { type: DataTypes.STRING },
});
await IntegerNames.sync({ force: true })
async function reset() {
  await sequelize.truncate({ cascade: true })
  await IntegerNames.create({ value: 2, name: 'two' })
  await IntegerNames.create({ value: 3, name: 'three' })
  await IntegerNames.create({ value: 5, name: 'five' })
}
await reset()
let rows
rows = await IntegerNames.findAll()
assert.strictEqual(rows[0].id, 1)
assert.strictEqual(rows[0].name, 'two')
assert.strictEqual(rows[0].value, 2)
assert.strictEqual(rows[1].id, 2)
assert.strictEqual(rows[1].name, 'three')
assert.strictEqual(rows[1].value, 3)
assert.strictEqual(rows[2].id, 3)
assert.strictEqual(rows[2].name, 'five')
assert.strictEqual(rows[2].value, 5)
assert.strictEqual(rows.length, 3)
})().finally(() => { return sequelize.close() })

package.json

{
  "name": "tmp",
  "private": true,
  "version": "1.0.0",
  "dependencies": {
    "pg": "8.5.1",
    "pg-hstore": "2.3.3",
    "sequelize": "6.14.0",
    "sql-formatter": "4.0.2",
    "sqlite3": "5.0.2"
  }
}

SQLite output for the above:

DROP TABLE IF EXISTS ` IntegerNames `;

CREATE TABLE IF NOT EXISTS ` IntegerNames ` (
  ` id ` INTEGER PRIMARY KEY AUTOINCREMENT,
  ` value ` INTEGER,
  ` name ` VARCHAR(255),
  ` createdAt ` DATETIME NOT NULL,
  ` updatedAt ` DATETIME NOT NULL
);

PRAGMA INDEX_LIST(` IntegerNames `)

DELETE FROM
  ` IntegerNames `

INSERT INTO
  ` IntegerNames ` (` id `, ` value `, ` name `, ` createdAt `, ` updatedAt `)
VALUES
  (NULL, $ 1, $ 2, $ 3, $ 4);
[
  [ 1, 2 ],
  [ 2, 'two' ],
  [ 3, '2022-02-02 10:32:12.569 +00:00' ],
  [ 4, '2022-02-02 10:32:12.569 +00:00' ]
]

INSERT INTO
  ` IntegerNames ` (` id `, ` value `, ` name `, ` createdAt `, ` updatedAt `)
VALUES
  (NULL, $ 1, $ 2, $ 3, $ 4);
[
  [ 1, 3 ],
  [ 2, 'three' ],
  [ 3, '2022-02-02 10:32:12.581 +00:00' ],
  [ 4, '2022-02-02 10:32:12.581 +00:00' ]
]

INSERT INTO
  ` IntegerNames ` (` id `, ` value `, ` name `, ` createdAt `, ` updatedAt `)
VALUES
  (NULL, $ 1, $ 2, $ 3, $ 4);
[
  [ 1, 5 ],
  [ 2, 'five' ],
  [ 3, '2022-02-02 10:32:12.588 +00:00' ],
  [ 4, '2022-02-02 10:32:12.588 +00:00' ]
]

SELECT
  ` id `,
  ` value `,
  ` name `,
  ` createdAt `,
  ` updatedAt `
FROM
  ` IntegerNames ` AS ` IntegerNames `;

PostgreSQL output:

DROP TABLE IF EXISTS "IntegerNames" CASCADE;

CREATE TABLE IF NOT EXISTS "IntegerNames" (
  "id" SERIAL,
  "value" INTEGER,
  "name" VARCHAR(255),
  "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL,
  "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL,
  PRIMARY KEY ("id")
);

SELECT
  i.relname AS name,
  ix.indisprimary AS primary,
  ix.indisunique AS unique,
  ix.indkey AS indkey,
  array_agg(a.attnum) as column_indexes,
  array_agg(a.attname) AS column_names,
  pg_get_indexdef(ix.indexrelid) AS definition
FROM
  pg_class t,
  pg_class i,
  pg_index ix,
  pg_attribute a
WHERE
  t.oid = ix.indrelid
  AND i.oid = ix.indexrelid
  AND a.attrelid = t.oid
  AND t.relkind = 'r'
  and t.relname = 'IntegerNames'
GROUP BY
  i.relname,
  ix.indexrelid,
  ix.indisprimary,
  ix.indisunique,
  ix.indkey
ORDER BY
  i.relname;

TRUNCATE "IntegerNames" CASCADE

INSERT INTO
  "IntegerNames" ("id", "value", "name", "createdAt", "updatedAt")
VALUES
  (DEFAULT, $1, $2, $3, $4) RETURNING "id",
  "value",
  "name",
  "createdAt",
  "updatedAt";
[
  [ 1, 2 ],
  [ 2, 'two' ],
  [ 3, '2022-02-02 10:33:15.859 +00:00' ],
  [ 4, '2022-02-02 10:33:15.859 +00:00' ]
]

INSERT INTO
  "IntegerNames" ("id", "value", "name", "createdAt", "updatedAt")
VALUES
  (DEFAULT, $1, $2, $3, $4) RETURNING "id",
  "value",
  "name",
  "createdAt",
  "updatedAt";
[
  [ 1, 3 ],
  [ 2, 'three' ],
  [ 3, '2022-02-02 10:33:15.867 +00:00' ],
  [ 4, '2022-02-02 10:33:15.867 +00:00' ]
]

INSERT INTO
  "IntegerNames" ("id", "value", "name", "createdAt", "updatedAt")
VALUES
  (DEFAULT, $1, $2, $3, $4) RETURNING "id",
  "value",
  "name",
  "createdAt",
  "updatedAt";
[
  [ 1, 5 ],
  [ 2, 'five' ],
  [ 3, '2022-02-02 10:33:15.869 +00:00' ],
  [ 4, '2022-02-02 10:33:15.869 +00:00' ]
]

SELECT
  "id",
  "value",
  "name",
  "createdAt",
  "updatedAt"
FROM
  "IntegerNames" AS "IntegerNames";

I should send a patch to Sequelize to at least provide the raw query string so I don't have to remove the Executing (default): with a regex.

Also I don't use logQueryParameters: true because it would mean more regex parsing, so I just print the bound value myself, related:

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions

Sequelize default connection pool size

How to solve EagerLoadingError [SequelizeEagerLoadingError]: Table_B is not associated to Table_A?

How to set primary key type to UUID via Sequelize CLI

sequelize destroy record with join

Sequelize how to add folder for version inside Migrations folder

sequelize does not creating a table shows this result : "Executing (default): SELECT 1+1 AS result"

sequelize does not creating a table shows this result : "Executing (default): SELECT 1+1 AS result"

node.js + sequelize bulk insert

nodejs adminBro mysql There are no adapters supporting one of the resource you provided

Conversion failed when converting date and/or time from character string mssql sequelize

Sequelize.js onDelete: 'cascade' is not deleting records sequelize

Change sequelize timezone

Sequelize: can you use hooks to add a comment to a query?

Is there a difference between findOne and findByPk in Sequelize?

How to run Sequelize migrations inside Docker