sequelize with postgres database not working after migration from mysql

Question

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

My User model:

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);
      }
    },
    .....

Migration file:

"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);
  }
};

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

Mike Sherrill 'Cat Recall' · Accepted Answer · 2015-03-04 00:43:15Z

12

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

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

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

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".

answered Mar 4, 2015 at 0:43

Mike Sherrill 'Cat Recall'

96.6k20 gold badges134 silver badges195 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Makromat Over a year ago

Thanks for answer! So my tables then will have first letter uppercase ? Is this good idea ?

mu is too short Over a year ago

@Makromat The usual advice with PostgreSQL is to use lower case identifiers with underscores between words. However, the naming convention that you'd use depends on what your ORM expects. If Sequalize doesn't have a convention then I'd recommend lower case and underscores so that you won't get stuck quoting your identifiers all the time but anything consistent should be good enough.

Mike Sherrill 'Cat Recall' Over a year ago

PostgreSQL doesn't care whether you use delimited identifiers. They're mildly inconvenient, because when you write SQL, you have to use double quotes all the time. But you're using an ORM, so you're unlikely to write much SQL. Most (all?) ORMs let you overide their conventions. For Sequelize, see Working with legacy tables.

Ben Over a year ago

Damn it this one is another bad part of sequelize (along with the migration and sync decoupling) ! So you migrate your table with "users", and you have no choice to call it "Users" in your code ? Having 3 points of verification ( model, migration file, automagic sync stuff) all this is bery misleading, and do not provide a robust pattern. Oh yeah; quote it if "...(no reason provided by any convention)..", that'll work

James Klein Over a year ago

@MikeSherrill'CatRecall' link is broken

|

Collectives™ on Stack Overflow

sequelize with postgres database not working after migration from mysql

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related