如何在sequelize中缩进/漂亮地打印记录的查询

Question

缩进长查询对于帮助调试或理解Sequelize正在做的事情至关重要。有没有内置的方法可以为每个记录的查询自动执行此操作？

例如：

#!/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() })

我得到一条记录的线路：

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);

但是我怎么能得到这样的东西：

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

测试

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"
}
}

Answer 1

我找不到一个好的内置方法，所以我使用sql格式化程序第三方库来解析和缩进查询字符串作为一种解决方法：https://github.com/zeroturnaround/sql-formatter

不幸的是，该库还不支持SQLite：https://github.com/zeroturnaround/sql-formatter/issues/133但无论它默认使用什么，至少仍然使查询更可读(但不可运行(：

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() })

软件包.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输出：

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输出：

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";

我应该向Sequelize发送一个补丁，至少提供原始查询字符串，这样我就不必用正则表达式删除Executing (default):。

此外，我不使用logQueryParameters: true，因为这意味着需要更多的正则表达式解析，所以我只打印自己的绑定值，相关：

• 如何在Sequelize中使用有界参数记录查询
• sequelize：如何记录原始查询