node.js - Sequelize 同步复制现有的外键关联

Question

我遇到了一个问题，即 Sequelize 的 sync 功能在每次执行 sync 时都会复制现有的外键关联。

无法解决问题，也无法在这里或其他地方找到任何解决方案，因此我在这里分享这个问题。

---

所以，这演示了第一次执行所有代码块时的样子。它两次创建相同的外键。

---

这演示了第二次执行代码时的样子。现在还有第三个。

---

八次...

---

...等等。

---

依赖项，package.json:

{
  "name": "lab-sequelize",
  "version": "0.0.1",
  "main": "./src/index.js",
  "dependencies": {
    "pg": "^8.5.0",
    "pg-hstore": "^2.3.3",
    "sequelize": "^6.3.5"
  },
  "scripts": {
    "start": "node ./src"
  }
}

整个代码块，index.js:下面的代码建立数据库 (postgres) 连接，初始化模型并使用 Sequelize 将它们同步到数据库。

const { Sequelize, DataTypes } = require('sequelize');

const sequelize = new Sequelize({
    host: "127.0.0.1",
    port: 5432,
    database: "mydb",
    username: "myusername",
    password: "mypassword",
    dialect: "postgres",
    logging: console.log,
});

const schemaName = 'aschema';

async function main() {

    ////////////////////////////////////////////////////////////
    // Establish the connection
    await sequelize.authenticate();
    console.log('Connection has been established successfully.');

    ////////////////////////////////////////////////////////////
    // Create db schema
    await sequelize.createSchema(schemaName);

    ////////////////////////////////////////////////////////////
    // Define models

    const Company = sequelize.define('company', {
        id: {
            type: DataTypes.INTEGER,
            allowNull: false,
            primaryKey: true,
            autoIncrement: true
        },
        name: {
            type: DataTypes.STRING,
            allowNull: false
        },
    }, {
        schema: schemaName,
        // freezeTableName: true,
        tableName: 'company'
    });

    const User = sequelize.define('user', {
        id: {
            type: DataTypes.INTEGER,
            allowNull: false,
            primaryKey: true,
            autoIncrement: true
        },
        name: {
            type: DataTypes.STRING
        },
        companyId: {
            type: DataTypes.INTEGER
        },
        // aNewPotentialField: {
        //     type: DataTypes.INTEGER
        // }
    }, {
        schema: schemaName,
        // freezeTableName: true,
        tableName: 'user'
    });

    ////////////////////////////////////////////////////////////
    // Set relations
    User.belongsTo(Company, { as: 'company', foreignKey: 'companyId' });
    // Company.hasOne(User, { as: 'users', foreignKey: 'companyId' });

    // User.belongsTo(Company);
    ////////////////////////////////////////////////////////////
    // Sync

    // * These syncs below multiply the existing relations.
    // * Will add the field 'aNewPotentialField' in the future since alter set to true.
    await Company.sync({ alter: true });
    await User.sync({ alter: true });

    // * These syncs below don't multiply the existing relations.
    // * However, also wont add the field 'aNewPotentialField' in the future since alter set to false.
    // await Company.sync();
    // await User.sync();

    ////////////////////////////////////////////////////////////
    // Close the established connection.
    await sequelize.close();
    ////////////////////////////////////////////////////////////
}


main()
    .then(() => {
        console.log('App started successfully.');
    })
    .catch((e) => {
        throw e;
    });

Answer 1

仔细调查后发现，当为模型提供 schema 选项时会出现此问题。

根据执行日志，Sequelize 中的queryGenerator.getForeignKeyReferencesQuery (dialects/postgres) 生成了错误的查询。

... WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name = '"aschema"."user"' AND tc.table_catalog = 'testdb'

它必须在的地方:

... WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name = 'user' AND tc.table_catalog = 'testdb' AND tc.table_schema = 'aschema'

而且 query-interface 中还有另一件事导致了这个问题。

PR for the fix can be found here now