时间戳

首页 > 解决方案 > 在sequelize中分配外键的默认值后自动同步出错

node.js - 在sequelize中分配外键的默认值后自动同步出错

问题描述

我正在使用 Sequelize 使用以下配置连接到 PostgreSQL 数据库

exports.sequelize = new Sequelize(process.env.DATABASE_URL, {
    dialect:  'postgres',
    protocol: 'postgres',
    pool: {
        "max": 1000,
        "min": 0,
        "idle": 650000,
        "acquire": 1000000
    }
  });

我在 Sequelize modal 中添加了如下外键关系

user.belongsTo(profileModel,{
  foreignKey : {
    defaultValue : 1
  }
});

即使我使用正确的方言，以下日志中似乎也存在某种 SQL 语法错误。我不确定问题是什么。我收到以下错误：

Error
    at exports.sequelize.sync.then (E:\office\recoverlution\config\sequelizeCon.js:122:19)
    at process._tickCallback (internal/process/next_tick.js:68:7)
{ SequelizeDatabaseError: syntax error at or near "REFERENCES"
    at Query.formatError (E:\office\recoverlution\node_modules\sequelize\lib\dialects\postgres\query.js:386:16)
    at Query.run (E:\office\recoverlution\node_modules\sequelize\lib\dialects\postgres\query.js:87:18)
    at process._tickCallback (internal/process/next_tick.js:68:7)
  name: 'SequelizeDatabaseError',
  parent:
   { error: syntax error at or near "REFERENCES"
       at Parser.parseErrorMessage (E:\office\recoverlution\node_modules\pg-protocol\dist\parser.js:278:15)
       at Parser.handlePacket (E:\office\recoverlution\node_modules\pg-protocol\dist\parser.js:126:29)
       at Parser.parse (E:\office\recoverlution\node_modules\pg-protocol\dist\parser.js:39:38)
       at Socket.stream.on (E:\office\recoverlution\node_modules\pg-protocol\dist\index.js:10:42)
       at Socket.emit (events.js:198:13)
       at addChunk (_stream_readable.js:288:12)
       at readableAddChunk (_stream_readable.js:269:11)
       at Socket.Readable.push (_stream_readable.js:224:10)
       at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
     length: 99,
     name: 'error',
     severity: 'ERROR',
     code: '42601',
     detail: undefined,
     hint: undefined,
     position: '60',
     internalPosition: undefined,
     internalQuery: undefined,
     where: undefined,
     schema: undefined,
     table: undefined,
     column: undefined,
     dataType: undefined,
     constraint: undefined,
     file: 'scan.l',
     line: '1128',
     routine: 'scanner_yyerror',
     sql:
      'ALTER TABLE "Users" ALTER COLUMN "ProfileId" SET DEFAULT 1 REFERENCES "Profiles" ("id") ON DELETE SET NULL ON UPDATE CASCADE;ALTER TABLE "Users" ALTER COLUMN "ProfileId" TYPE INTEGER;',
     parameters: undefined },
  original:
   { error: syntax error at or near "REFERENCES"
       at Parser.parseErrorMessage (E:\office\recoverlution\node_modules\pg-protocol\dist\parser.js:278:15)
       at Parser.handlePacket (E:\office\recoverlution\node_modules\pg-protocol\dist\parser.js:126:29)
       at Parser.parse (E:\office\recoverlution\node_modules\pg-protocol\dist\parser.js:39:38)
       at Socket.stream.on (E:\office\recoverlution\node_modules\pg-protocol\dist\index.js:10:42)
       at Socket.emit (events.js:198:13)
       at addChunk (_stream_readable.js:288:12)
       at readableAddChunk (_stream_readable.js:269:11)
       at Socket.Readable.push (_stream_readable.js:224:10)
       at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
     length: 99,
     name: 'error',
     severity: 'ERROR',
     code: '42601',
     detail: undefined,
     hint: undefined,
     position: '60',
     internalPosition: undefined,
     internalQuery: undefined,
     where: undefined,
     schema: undefined,
     table: undefined,
     column: undefined,
     dataType: undefined,
     constraint: undefined,
     file: 'scan.l',
     line: '1128',
     routine: 'scanner_yyerror',
     sql:
      'ALTER TABLE "Users" ALTER COLUMN "ProfileId" SET DEFAULT 1 REFERENCES "Profiles" ("id") ON DELETE SET NULL ON UPDATE CASCADE;ALTER TABLE "Users" ALTER COLUMN "ProfileId" TYPE INTEGER;',
     parameters: undefined },
  sql:
   'ALTER TABLE "Users" ALTER COLUMN "ProfileId" SET DEFAULT 1 REFERENCES "Profiles" ("id") ON DELETE SET NULL ON UPDATE CASCADE;ALTER TABLE 
"Users" ALTER COLUMN "ProfileId" TYPE INTEGER;',
  parameters: undefined }

这是我的用户模型

var Sequelize = require("sequelize");
var sequelize = require('../config/sequelizeCon').sequelize;
var sequelizePaginate = require('sequelize-paginate');
var logs = require('./logsModel');
var follows = require('./followModel');
let profileModel = require('./profileModel');
const generateGetstreamUserId = require('../controller/utilityController').generateGetstreamUserId;
const configVars = require('../config/configVars');

var user = sequelize.define("User", {
  name: Sequelize.STRING,
  email: {type : Sequelize.STRING,allowNull:false,unique: true},
  password : Sequelize.STRING(70),
  userType : {type: Sequelize.STRING,
    defaultValue : 'RECOVEREES'},
  activated : {type: Sequelize.STRING,
    allowNull:false,
    defaultValue:false},
  addictions : Sequelize.STRING,
  about : Sequelize.TEXT,
  img :  Sequelize.TEXT,
  gender : {type: Sequelize.STRING},
  total_posts : {type : Sequelize.BIGINT,allowNull:false,defaultValue : 0,validate : {min:0}},
  total_followers : {type : Sequelize.BIGINT,allowNull:false,defaultValue : 0,validate : {min:0}},
  total_following : {type : Sequelize.BIGINT,allowNull:false,defaultValue : 0,validate : {min:0}},
  getStreamUserId: {type : Sequelize.STRING,unique: true},
  stage : Sequelize.STRING,
  ProfileId: { type: Sequelize.BIGINT ,allowNull:false , defaultValue: 1}
},{
  hooks : {
    beforeCreate: function(user,options){
      user.getStreamUserId =  user.email?user.email.replace(/[^a-zA-Z-_]/g, "")+configVars.GETSTREAM_SUFFIX:null;
    }
  }
}
);

user.hasMany(logs, {as: 'logs',
  onUpdate:'CASCADE',
  onDelete:'CASCADE',
  foreignKey: { allowNull: false }
});

user.hasMany(follows, {as: 'users',
  onUpdate:'CASCADE',
  onDelete:'CASCADE',
  foreignKey: { allowNull: false }
});

user.hasMany(follows, {as: 'targets',
  onUpdate:'CASCADE',
  onDelete:'CASCADE',
  foreignKey: { name : 'target',
    allowNull: false }
});

user.belongsTo(profileModel,{
  as: 'profile',
  foreignKey: {
    name: 'ProfileId'
  }
});

sequelizePaginate.paginate(user);
module.exports = user;

这是我的个人资料模型

var Sequelize = require("sequelize");
var sequelize = require('../config/sequelizeCon').sequelize;
var sequelizePaginate = require('sequelize-paginate');

var profileModel = sequelize.define("Profiles", {
    name: {type:Sequelize.TEXT, allowNull:false, unique : true},
    subscription : {type: Sequelize.BOOLEAN,
      allowNull:false,
      defaultValue:false},
    content : {type: Sequelize.BOOLEAN,
      allowNull:false,
      defaultValue:false},
    profile : {type: Sequelize.BOOLEAN,
      allowNull:false,
      defaultValue:false},
    newsFeed : {type: Sequelize.BOOLEAN,
      allowNull:false,
      defaultValue:false},
    connect : {type: Sequelize.BOOLEAN,
      allowNull:false,
      defaultValue:false},
    message : {type: Sequelize.BOOLEAN,
      allowNull:false,
      defaultValue:false},
    follow : {type: Sequelize.BOOLEAN,
      allowNull:false,
      defaultValue:false},
    joinGroup : {type: Sequelize.BOOLEAN,
      allowNull:false,
      defaultValue:false},
    manageGroup : {type: Sequelize.BOOLEAN,
      allowNull:false,
      defaultValue:false},
    yoga : {type: Sequelize.BOOLEAN,
      allowNull:false,
      defaultValue:false},
    meditation : {type: Sequelize.BOOLEAN,
      allowNull:false,
      defaultValue:false},
    exercise : {type: Sequelize.BOOLEAN,
      allowNull:false,
      defaultValue:false},
    beFollowed : {type: Sequelize.BOOLEAN,
      allowNull:false,
      defaultValue:false},
    uploadContent : {type: Sequelize.BOOLEAN,
      allowNull:false,
      defaultValue:false}
  },{

})


sequelizePaginate.paginate(profileModel);
module.exports = profileModel;

标签： node.jspostgresqlsequelize.js

解决方案

模型：user.js

"use strict";
const { Model } = require("sequelize");
module.exports = (sequelize, DataTypes) => {
    class user extends Model {
        static associate(models) {
            // define association here
            this.hasOne(models.User, {
                foreignKey: "profileId",
                as: "profile",
            });
        }
    }
    user.init({
        name: { type: DataTypes.STRING, },
        img: { type: DataTypes.STRING, },
        profileId: { type: DataTypes.INTEGER, defaultValue: 0}
    }, {
        sequelize,
        modelName: "user",
    });
    return user;
};

模型：Profile.js

const { Model } = require("sequelize");
module.exports = (sequelize, DataTypes) => {
    class Profile extends Model {
        static associate(models) {
        }
    }
    Profile.init({
    }, {
        sequelize,
        modelName: "Profile"
    });
    return Profile;
};

推荐阅读