从mysql迁移后，postgres数据库无法正常工作 [英] sequelize with postgres database not working after migration from mysql

问题描述

我将MySQL数据库更改为sequelize的postgreSQL.但是在迁移后，我在表或模型中的大小写第一个字母有问题... 在我的MySQL版本正常运行之前，但是在迁移之后，我收到了错误消息: 500 SequelizeDatabaseError: relation "Users" does not exist

I change MySQL databese into postgreSQL in sequelize. But After migration I have issue with upper and lowercase first letter in Table or Model... Before my MySQL version was working properly but after migration I got error message: 500 SequelizeDatabaseError: relation "Users" does not exist

我的用户模型:

module.exports = function(sequelize, Sequelize) {
  var User = sequelize.define("User", {
    // profile
    userlevel: Sequelize.STRING,
    restaurant: Sequelize.STRING,
    access: Sequelize.STRING,
    optionsid: Sequelize.STRING,
    email: Sequelize.STRING,
    name: Sequelize.STRING,
    gender: Sequelize.STRING,
    location: Sequelize.STRING,
    website: Sequelize.STRING,
    picture: Sequelize.STRING,
    // Oauth
    password: {
      type: Sequelize.STRING,
      set: function(v) {
        var salt = bcrypt.genSaltSync(5);
        var password = bcrypt.hashSync(v, salt);
        return this.setDataValue('password', password);
      }
    },
    .....

迁移文件:

"use strict";
module.exports = {
  up: function(migration, DataTypes, done) {
    migration.createTable("users", {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: DataTypes.INTEGER
      },
      userlevel: {
        type: DataTypes.STRING,
        defaultValue: '5'
      },
      restaurant: {
        type: DataTypes.STRING,
        defaultValue: ''
      },
      access: {
        type: DataTypes.STRING,
        defaultValue: '1'
      },
      optionsid: {
        type: DataTypes.STRING,
        defaultValue: ''
      },
      email: {
        type: DataTypes.STRING,
        allowNull: false
      },
      name: {
        type: DataTypes.STRING,
        defaultValue: ''
      },
      gender: {
        type: DataTypes.STRING,
        defaultValue: ''
      },
      location: {
        type: DataTypes.STRING,
        defaultValue: ''
      },
      website: {
        type: DataTypes.STRING,
        defaultValue: ''
      },
      picture: {
        type: DataTypes.STRING,
        defaultValue: ''
      },
      password: {
        type: DataTypes.STRING
      },
      facebook: {
        type: DataTypes.STRING
      },
      twitter: {
        type: DataTypes.STRING
      },
      google: {
        type: DataTypes.STRING
      },
      tokens: {
        type: DataTypes.STRING
      },
      resetPasswordToken: {
        type: DataTypes.STRING
      },
      resetPasswordExpires: {
        type: DataTypes.DATE
      },
      createdAt: {
        allowNull: false,
        type: DataTypes.DATE
      },
      updatedAt: {
        allowNull: false,
        type: DataTypes.DATE
      }
    }).done(done);
  },
  down: function(migration, DataTypes, done) {
    migration.dropTable("users").done(done);
  }
};

如果我将postgreSQL中的表的首字母更改为大写，则一切工作正常...

If I change first letter of table in postgreSQL to uppercase everything is working properly...

推荐答案

PostgreSQL将普通标识符的名称折叠为小写.因此users，Users和USERS都解析为标识符users.

PostgreSQL folds the names of ordinary identifiers to lower case. So users, Users, and USERS all resolve to the identifier users.

分隔的标识符是不同的. (定界的标识符用双引号引起来.)标识符"users"，"Users"和"USERS"是三个不同的标识符.

Delimited identifiers are different. (Delimited identifiers are surrounded by double quotes.) The identifiers "users", "Users", and "USERS" are three different identifiers.

您的迁移创建了表"users". Sequelize正在寻找表"Users". (分隔的标识符-两个不同的表.)

Your migration created the table "users". Sequelize is looking for the table "Users". (Delimited identifiers--two different tables.)

您可能应该在迁移中将标识符更改为用户".还有其他方法，但这是阻力最小的途径.如果已经在生产中，则最好编写另一个将"users"重命名为"Users"的迁移.

You should probably change the identifier in your migration to "Users". There are other ways, but this is the path of least resistance. If this is already in production, you might be better off writing another migration that renames "users" to "Users".

这篇关于从mysql迁移后，postgres数据库无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！