Relationships not created in PostgreSQL

Question

I'm using Sequelize.js as ORM in node.js, I need to build some relationships.

I have no great experience in SQL RDMSs, possibly I'm missing some point here.

I have 4 different models. All of them should have cross-relationship with each other.

//  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();
        }, [])
    )
});

There is no error thrown in catch blocks of any Promise I invoked, also no unhandled rejections. However when I try to create new objects, I'm not able to do that, throwing this error:

{ [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;'
}

One more thing, I'm also inspecting the SQL with pgAdmin, there is no such as ScheduleId or any relationship-related column in my models. Should I add them manually in model declarations?

Here are the model definitions:

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

Evan Siroky · Accepted Answer · 2015-10-29 18:51:28Z

3

The second time the sync methods are called, they will not alter the tables because the tables have already been created in the database from the first sync calls. If sync is called with the options { force: true } it will drop the table and recreate it.

Also, all the association definitions are not asynchronous, therefore this code could be shortened to this:

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

sequelize.sync();

answered Oct 29, 2015 at 18:51

Evan Siroky

9,4366 gold badges58 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Buğra Ekuklu Over a year ago

Great for hearing this. I did this, now there is no CourseId column in Section table. Am I doing something wrong?

Evan Siroky Over a year ago

Are the tables from your previous work still in the db?

Evan Siroky Over a year ago

Can you post your model definitions? I made some basic model defs to test this and am seeing CourseId in the Sections table.

Buğra Ekuklu Over a year ago

Edited post, you can check now.

Buğra Ekuklu Over a year ago

By the way I do have belongsTo relationships now, but not others. Should there be another column in models with hasMany? Is it a normal behaviour?

|

Collectives™ on Stack Overflow

Relationships not created in PostgreSQL

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related