Sequelize can't synchronize models which references other tables

[TitaneBoy]

What are you doing?

Trying to synchronize my defined Models with my sequelize instance

Code sample

import { Model, Options, Sequelize } from 'sequelize';
import groupPrivilege from '../../../models/group_privilege';
import groupUser from '../../../models/group_user';
import measurement from '../../../models/measurement';
import measurementV2 from '../../../models/measurement_v2';
import privilege from '../../../models/privilege';
import readings_resume from '../../../models/readings_resume';
import session from '../../../models/sessions';
import user from '../../../models/user';

protected importDatabaseModels() {
    const groupPrivilegeModel = groupPrivilege(this._sequelizer);
    const groupUserModel = groupUser(this._sequelizer);
    const privilegeModel = privilege(this._sequelizer);
    const userModel = user(this._sequelizer);
    const measurementModel = measurement(this._sequelizer);
    const readingsResumeModel = readings_resume(this._sequelizer);

    session(this._sequelizer);
    measurementV2(this._sequelizer);

    userModel.belongsTo(groupUserModel, {foreignKey: 'group_id', targetKey: 'id'});
    userModel.belongsTo(groupPrivilegeModel, {foreignKey: 'group_id', targetKey: 'group_id'});
    groupPrivilegeModel.belongsTo(userModel, {foreignKey: 'group_id', targetKey: 'group_id'});
    groupPrivilegeModel.belongsTo(privilegeModel, {foreignKey: 'privilege_id', targetKey: 'id'});
    groupPrivilegeModel.belongsTo(groupUserModel, {foreignKey: 'group_id', targetKey: 'id'});
    privilegeModel.hasMany(groupPrivilegeModel, {foreignKey: 'privilege_id'});
    groupUserModel.hasMany(groupPrivilegeModel, {foreignKey: 'group_id'});

    readingsResumeModel.belongsTo(measurementModel, {foreignKey: 'measurement_id', targetKey: 'id'});
    measurementModel.hasMany(readingsResumeModel, {foreignKey: 'measurement_id'});

    this._sequelizer.sync({alter: true})
      .then(() => {
        debugConsole('Database tables correctly synchronized');
      })
      .catch((error) => {
        debugConsole(`Error when synchronizing database tables: ${error}`);
      });
  }

User Model

import { BuildOptions, DataTypes, InitOptions, Model, ModelAttributes, Sequelize } from 'sequelize';

export interface IUserModel extends Model {
  readonly id: number;
  group_id: number;
  username: string;
  password: Date;
  readonly createdAt: Date;
  readonly updateAt: Date;
}

export type UserModel = typeof Model & (new (values ?: object, options ?: BuildOptions) => IUserModel);

export default function user(sequelizeInstance: Sequelize) {
  const options: InitOptions = {
    freezeTableName: true,
    name: {singular: 'user', plural: 'users'},
    sequelize: sequelizeInstance,
    tableName: 'user',
    timestamps: true,
    underscored: true,
  };

  const attributes: ModelAttributes = {
    group_id: {
      allowNull: true,
      defaultValue: '1',
      references: {
        key: 'id',
        model: 'group_user',
      },
      type: DataTypes.INTEGER,
    },
    id: {
      autoIncrement: true,
      primaryKey: true,
      type: DataTypes.INTEGER,
    },
    password: {
      allowNull: false,
      type: DataTypes.TEXT,
    },
    username: {
      allowNull: false,
      type: DataTypes.TEXT,
      unique: true,
    },
  };

  return sequelizeInstance.define('user', attributes, options) as UserModel;
}

To Reproduce
Steps to reproduce the behavior:

1. Define models X, Y, ...
2. Run the following
3. See error

What do you expect to happen?

I expect sequelize to synchronize my defined models with my databasde

What is actually happening?

An error occurs while sequelize is trying to execute its generated sql instructions...

Here is the query Sequelize is trying to execute:

ALTER TABLE "user" ALTER COLUMN "group_id" SET DEFAULT '1' REFERENCES "group_user" ("id") ON DELETE SET NULL ON UPDATE CASCADE;ALTER TABLE "user" ALTER COLUMN "group_id" TYPE INTEGER;

I tried to execute manually with pgAdmin the generated sql query and this is what I got:

In english, the translated error will be something like: "Syntax error near << REFERENCES >>

Environment

Dialect:

• mysql
• postgres
• sqlite
• mssql
• any
  Dialect library version:
  "pg": "7.11.0",
  "pg-hstore": "2.3.3",
  Database version: "PostgreSQL 9.4.4, compiled by Visual C++ build 1800, 64-bit"
  Sequelize version: 5.8.7
  Node Version: v10.10.0
  OS: Windows 7 Professional SP1, 64 bits
  If TypeScript related: TypeScript version: 3.5.1

[papb]

Does this only happen with postgres? Can you please check another dialect?

[TitaneBoy]

Hello... I unfortunetly don't have the necessary resources to test it with other dialects... It can take me a while to have these resources... but for sure, it doesn't work with postgres dialect

[papb]

You can instantiate an in-memory SQLite instance as follows:

1. npm install sqlite3
2. new Sequelize('sqlite::memory:')

Can you please try it?

[kartonrad]

yes, this is not an issue in sqlite 3

[kartonrad]

wait, i'm not sure sure that model.sync({alter:true}) in sqlite actually executes ALTER TABLE statements

regardless, sqlite allows the REFERENCES clause in ALTER TABLE ALTER COLUMN, postgres sql does not, instead requiring something like ALTER TABLE ADD CONSTRAINT

i'm facing the same issue currently

[kartonrad]

https://www.sqlite.org/lang_altertable.html
https://www.postgresql.org/docs/current/sql-altertable.html

yea, sqllite doesn't support ALTER COLUMN, only DROP and ADD column

[github-actions]

This issue has been automatically marked as stale because it has been open for 14 days without activity. It will be closed if no further activity occurs within the next 14 days. If this is still an issue, just leave a comment or remove the "stale" label. 🙂