Postgres needs Schema Name with Table Name when defining FK Constraints

[gynnantonix]

What are you doing?

Trying to create a table in a particular schema. The table contains an FK referencing another table in that schema.

Source Table

  var Source = sequelize.define(
    'Source',
    {
      id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true },
      name: { type: DataTypes.STRING, allowNull: false },
    },
    {
      timestamps: false,
      classMethods: {
        associate: function(models) {
          this.hasMany(models.Batch);
        },
      },
    }
  );

Batch Table

  var Batch = sequelize.define(
    'Batch',
    {
      id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true },
      startDate: { type: DataTypes.DATE, defaultValue: DataTypes.NOW, allowNull: false },
      endDate: { type: DataTypes.DATE },
      sourceLMS: {
        type: DataTypes.INTEGER,
        allowNull: false,
        references: { model: sequelize.models.Source },
        onUpdate: 'CASCADE',
        onDelete: 'RESTRICT',
      },
    },
    {
      timestamps: false,
      classMethods: {
        associate: function(models) {
          this.hasOne(models.Source, { foreignKey: 'sourceLMS' });
          this.hasMany(models.Assignment);
          this.hasMany(models.BLT);
          this.hasMany(models.Course);
          this.hasMany(models.Page);
          this.hasMany(models.Program);
          this.hasMany(models.Quiz);
          this.hasMany(models.URL);
          this.hasMany(models.URLCheck);
        },
      },
    }
  );

Loader

fs
  .readdirSync(__dirname)
  .filter((file) => {
    return file.indexOf('.') !== 0 && file !== basename && file.slice(-3) === '.js';
  })
  .sort()
  .forEach((file) => {
    var model = sequelize['import'](path.join(__dirname, file));
    if (config.schema && config.schema.length > 0) {
      model = model.schema(config.schema);
    }
    db[model.name] = model;
  });

Object.keys(db).forEach((modelName) => {
  if (db[modelName].associate) {
    db[modelName].associate(db);
  }
});

What do you expect to happen?

When the DDL for each table is generated, the names of other tables ought to be prefixed with the schema name. In most places where table names appear, this is true. For example, Sources is generated properly:

CREATE TABLE IF NOT EXISTS "content"."Sources" ("id"  SERIAL , "name" VARCHAR(255) NOT NULL, PRIMARY KEY ("id"));

What is actually happening?

When Batches is generated, the referential portion of the definition of sourceLMS, the column that is an FK on Sources, does not prefix the table name with the schema name. The result is that the DBMS throws an error. This is the DDL that is generated for Batches:

CREATE TABLE IF NOT EXISTS "content"."Batches" ("id"  SERIAL , "startDate" TIMESTAMP WITH TIME ZONE NOT NULL, "endDate" TIMESTAMP WITH TIME ZONE, "sourceLMS" INTEGER NOT NULL REFERENCES "Sources" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, PRIMARY KEY ("id"));

Output, either JSON or SQL
Shell output:

db.sequelize.sync();
Promise {
  _bitField: 0,
  _fulfillmentHandler0: undefined,
  _rejectionHandler0: undefined,
  _promise0: undefined,
  _receiver0: undefined }
> Executing (default): CREATE TABLE IF NOT EXISTS "content"."Sources" ("id"  SERIAL , "name" VARCHAR(255) NOT NULL, PRIMARY KEY ("id"));
Executing (default): 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, pg_namespace s WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'Sources' AND s.oid = t.relnamespace AND s.nspname = 'content' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): CREATE TABLE IF NOT EXISTS "content"."Batches" ("id"  SERIAL , "startDate" TIMESTAMP WITH TIME ZONE NOT NULL, "endDate" TIMESTAMP WITH TIME ZONE, "sourceLMS" INTEGER NOT NULL REFERENCES "Sources" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, PRIMARY KEY ("id"));
Unhandled rejection SequelizeDatabaseError: relation "Sources" does not exist
    at Query.formatError (node_modules/sequelize/lib/dialects/postgres/query.js:355:16)
    at query.catch.err (node_modules/sequelize/lib/dialects/postgres/query.js:86:18)
    at bound (domain.js:370:14)
    at runBound (domain.js:383:12)
    at tryCatcher (node_modules/bluebird/js/release/util.js:16:23)
    at Promise._settlePromiseFromHandler (node_modules/bluebird/js/release/promise.js:512:31)
    at Promise._settlePromise (node_modules/bluebird/js/release/promise.js:569:18)
    at Promise._settlePromise0 (node_modules/bluebird/js/release/promise.js:614:10)
    at Promise._settlePromises (node_modules/bluebird/js/release/promise.js:689:18)
    at Async._drainQueue (node_modules/bluebird/js/release/async.js:133:16)
    at Async._drainQueues (node_modules/bluebird/js/release/async.js:143:10)
    at Immediate.Async.drainQueues [as _onImmediate] (node_modules/bluebird/js/release/async.js:17:14)
    at runCallback (timers.js:756:18)
    at tryOnImmediate (timers.js:717:5)
    at processImmediate [as _immediateCallback] (timers.js:697:5)

Dialect: postgres
Dialect version: 9.5.2
Database version: 9.5.2
Sequelize version: 4.32.3
Tested with latest release: yes

[sushantdhiman]

Are you sure you are using v4, classMethods got removed from v4 so above code wont work with v4

[gynnantonix]

I realized that the other day; the boilerplate was created by sequelize model:generate, which is still emitting the classMethod nomenclature instead of composition. Having said that, does it even matter? Sequelize is still generating the FK constraint in the DDL that it produces, but it's not prefixing the target table name with the schema name, whereas it does do that elsewhere in the DDL.

[gynnantonix]

I think this is related to, if not the same issue as, #2464?