node.js - 关系不是在 PostgreSQL 中创建的

Question

我在 node.js 中使用 Sequelize.js 作为 ORM，我需要建立一些关系。

我在 SQL RDMS 方面没有很好的经验，可能我在这里遗漏了一些要点。

我有 4 个不同的模型。所有这些都应该彼此有交叉关系。

//  consider there are catch blocks calling 'console.error(err)', consequently each then
Q.all([Building, Lesson, Section, Course, Schedule]
    .reduce(function(array, element) {
        return element.sync();
    }, [])
) .then(function () {
    return Section.hasMany(Lesson);
}).then(function () {
    return Lesson.belongsTo(Section);
}).then(function () {
    return Course.hasMany(Section);
}).then(function () {
    return Section.belongsTo(Course);
}).then(function () {
    return Schedule.hasMany(Course);
}).then(function () {
    return Course.belongsTo(Schedule);
}).then(function () {
    return Q.all([Building, Lesson, Section, Course, Schedule]
        .reduce(function(array, element) {
            return element.sync();
        }, [])
    )
});

我调用的任何 Promise 的 catch block 中都没有抛出错误，也没有未处理的拒绝。但是，当我尝试创建新对象时，我无法做到这一点，并抛出此错误:

{ [SequelizeDatabaseError: column "scheduleId" does not exist]
  name: 'SequelizeDatabaseError',
  message: 'column "scheduleId" does not exist',
  parent: 
   { [error: column "scheduleId" does not exist]
     name: 'error',
     length: 104,
     severity: 'ERROR',
     code: '42703',
     detail: undefined,
     hint: undefined,
     position: '117',
     internalPosition: undefined,
     internalQuery: undefined,
     where: undefined,
     schema: undefined,
     table: undefined,
     column: undefined,
     dataType: undefined,
     constraint: undefined,
     file: 'parse_relation.c',
     line: '2892',
     routine: 'errorMissingColumn',
     sql: 'SELECT "id", "code", "number", "isEnglish", "title", "prerequisites", "classRestriction", "createdAt", "updatedAt", "scheduleId" FROM "course" AS "course" WHERE "course"."code" = \'BEN\' AND "course"."number" = \'102\' AND "course"."isEnglish" = false AND "course"."title" = \'Microbiology\' LIMIT 1;' },
  original: 
   { [error: column "scheduleId" does not exist]
     name: 'error',
     length: 104,
     severity: 'ERROR',
     code: '42703',
     detail: undefined,
     hint: undefined,
     position: '117',
     internalPosition: undefined,
     internalQuery: undefined,
     where: undefined,
     schema: undefined,
     table: undefined,
     column: undefined,
     dataType: undefined,
     constraint: undefined,
     file: 'parse_relation.c',
     line: '2892',
     routine: 'errorMissingColumn',
     sql: 'SELECT "id", "code", "number", "isEnglish", "title", "prerequisites", "classRestriction", "createdAt", "updatedAt", "scheduleId" FROM "course" AS "course" WHERE "course"."code" = \'BEN\' AND "course"."number" = \'102\' AND "course"."isEnglish" = false AND "course"."title" = \'Microbiology\' LIMIT 1;' },
  sql: 'SELECT "id", "code", "number", "isEnglish", "title", "prerequisites", "classRestriction", "createdAt", "updatedAt", "scheduleId" FROM "course" AS "course" WHERE "course"."code" = \'BEN\' AND "course"."number" = \'102\' AND "course"."isEnglish" = false AND "course"."title" = \'Microbiology\' LIMIT 1;'
}

还有一件事，我还使用 pgAdmin 检查 SQL，在我的模型中没有诸如 ScheduleId 或任何与关系相关的列。我应该在模型声明中手动添加它们吗？

以下是模型定义:

pool = new Sequelize("postgres://Instigater@localhost:5432/Instigater");

Building = pool.define("building", {
    shortName: {
        type: Sequelize.STRING,
    },
    longName: {
        type: Sequelize.STRING,
    },
    campus: {
        type: Sequelize.STRING,    //    will be changed to enum
    },
    image: {
        type: Sequelize.INTEGER,
    },
}, {
    freezeTableName: true // Model tableName will be the same as the model name
});

Lesson = pool.define("lesson", {
    room: {
        type: Sequelize.STRING,
    },
    weekday: {
        type: Sequelize.STRING,    //    will be changed to enum
    },
    startTime: {
        type: Sequelize.INTEGER,
    },
    endTime: {
        type: Sequelize.INTEGER,
    },
}, {
    freezeTableName: true // Model tableName will be the same as the model name
});

Section = pool.define("section", {
    //    CRN is the identifier of a section
    crn: {
        type: Sequelize.INTEGER,
        primaryKey: true,
    },
    //    Instructor of the section
    instructor: {
        type: Sequelize.STRING,
    },
    capacity: {
        type: Sequelize.INTEGER,
    },
    enrolled: {
        type: Sequelize.INTEGER,
    },
    reservation: {
        type: Sequelize.STRING,
    },
    majorRestriction: {
        type: Sequelize.ARRAY(Sequelize.STRING),
    },
}, {
    freezeTableName: true // Model tableName will be the same as the model name
});

Course = pool.define("course", {
    code: {
        type: Sequelize.STRING,
    },
    number: {
        type: Sequelize.INTEGER,
    },
    isEnglish: {
        type: Sequelize.BOOLEAN,
    },
    title: {
        type: Sequelize.STRING,
    },
    prerequisites: {
        type: Sequelize.ARRAY(Sequelize.STRING),
    },
    classRestriction: {
        type: Sequelize.ARRAY(Sequelize.STRING),
    },
}, {

    instanceMethods: {
        textualRepresentation: function() { return this.code + " " + this.number + (this.isEnglish ? "E" : "") }
    },
    freezeTableName: true // Model tableName will be the same as the model name
});

Schedule = pool.define("schedule", {
    name: {
        type: Sequelize.STRING,
    },
    validUntil: {
        type: Sequelize.DATE()
    },
}, {
    freezeTableName:true
});

Section.hasMany(Lesson);
Lesson.belongsTo(Section);
Course.hasMany(Section);
Section.belongsTo(Course);
Schedule.hasMany(Course);
Course.belongsTo(Schedule);

pool.sync();

Answer 1

第二次调用 sync 方法时，它们不会更改表，因为表已经在第一次 sync 调用时在数据库中创建。如果使用选项 {force: true } 调用 sync，它将删除表并重新创建它。

此外，所有的关联定义都不是异步的，因此这段代码可以缩短为:

Section.hasMany(Lesson);
Lesson.belongsTo(Section);
Course.hasMany(Section);
Section.belongsTo(Course);
Schedule.hasMany(Course);
Course.belongsTo(Schedule);

sequelize.sync();