Postgres needs Schema Name with Table Name when defining FK Constraints #9029
Description
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