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

score 12 · Accepted Answer · answered Mar 04 '15 at 00:43

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 04 '15 at 00:43

Mike Sherrill 'Cat Recall'

91,602
17
122
185

Thanks for answer! So my tables then will have first letter uppercase ? Is this good idea ? – Makromat Mar 04 '15 at 01:08
1

@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. – mu is too short Mar 04 '15 at 01:15
1

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](http://docs.sequelizejs.com/en/latest/docs/legacy/). – Mike Sherrill 'Cat Recall' Mar 04 '15 at 01:23
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 – Ben Aug 13 '16 at 11:33
@MikeSherrill'CatRecall' link is broken – James Klein Jan 19 '18 at 23:31
@JamesKlein: Do we really want to work with frameworks that keep breaking links? It's 2018. We know how hypertext and web servers and URIs work now. [Updated link](http://docs.sequelizejs.com/manual/advanced/legacy.html#working-with-legacy-tables). Also, see [Cool URIs don't change](https://www.w3.org/Provider/Style/URI) – Mike Sherrill 'Cat Recall' Jan 20 '18 at 15:16

sequelize with postgres database not working after migration from mysql

1 Answers1

Linked