sequelize gives the wrong table name

Question

I have a database. Where i have a "user" table

I am trying to create my first REST api using sequelize

however when it executes my query i get the following in the console:

SELECT `id`, `username`, `password`, `name`, `organization_id`, `type_id`, `join_date` FROM `users` AS `user` WHERE `user`.`id` = '1';

as you can see it tries to use a table called users however this table does not exists.

Here is some of my code:

Please do tell me if you need more i am not really sure where it goes wrong? :S

    var User = sequelize.define('user', {
    id: DataTypes.INTEGER,
    username: DataTypes.STRING,
    password: DataTypes.STRING,
    name: DataTypes.STRING,
    organization_id: DataTypes.INTEGER,
    type_id: DataTypes.INTEGER,
    join_date: DataTypes.STRING

}, {
    instanceMethods: {
        retrieveAll: function(onSuccess, onError) {
            User.findAll({}, {raw: true})
                .ok(onSuccess).error(onError);
        },
        retrieveById: function(user_id, onSuccess, onError) {
            User.find({where: {id: user_id}}, {raw: true})
                .success(onSuccess).error(onError);
        },
        add: function(onSuccess, onError) {
            var username = this.username;
            var password = this.password;

            var shasum = crypto.createHash('sha1');
            shasum.update(password);
            password = shasum.digest('hex');

            User.build({ username: username, password: password })
                .save().ok(onSuccess).error(onError);
        },
        updateById: function(user_id, onSuccess, onError) {
            var id = user_id;
            var username = this.username;
            var password = this.password;

            var shasum = crypto.createHash('sha1');
            shasum.update(password);
            password = shasum.digest('hex');

            User.update({ username: username,password: password},{where: {id: id} })
                .success(onSuccess).error(onError);
        },
        removeById: function(user_id, onSuccess, onError) {
            User.destroy({where: {id: user_id}}).success(onSuccess).error(onError);
        }
    }
});

Just to be clear, does the "user" table actually already exist in your DB? And did you create it manually or did Sequelize create it? — HeadCode, Feb 16 '15 at 17:44
@HeadCode the user table exists in my database and was created manually and already contains loads of data — Marc Rasmussen, Feb 16 '15 at 17:51
I didn't realize this, but "user" looks to be a reserved keyword. I can't say this is causing you a problem, but check out this post: http://stackoverflow.com/questions/21114499/how-to-make-sequelize-use-singular-table-names — HeadCode, Feb 16 '15 at 17:55

Yuri Zarubin · Accepted Answer · 2019-03-05T15:44:08.410

To solve your problem, you need to set freezeTableName = true inside your options object.

e.g.

    var User = sequelize.define('user', {
    id: DataTypes.INTEGER,
    username: DataTypes.STRING,
    password: DataTypes.STRING,
    name: DataTypes.STRING,
    organization_id: DataTypes.INTEGER,
    type_id: DataTypes.INTEGER,
    join_date: DataTypes.STRING

}, {
    freezeTableName: true,
    instanceMethods: {
        retrieveAll: function(onSuccess, onError) {
            User.findAll({}, {raw: true})
                .ok(onSuccess).error(onError);
        },
        retrieveById: function(user_id, onSuccess, onError) {
            User.find({where: {id: user_id}}, {raw: true})
                .success(onSuccess).error(onError);
        },
        add: function(onSuccess, onError) {
            var username = this.username;
            var password = this.password;

            var shasum = crypto.createHash('sha1');
            shasum.update(password);
            password = shasum.digest('hex');

            User.build({ username: username, password: password })
                .save().ok(onSuccess).error(onError);
        },
        updateById: function(user_id, onSuccess, onError) {
            var id = user_id;
            var username = this.username;
            var password = this.password;

            var shasum = crypto.createHash('sha1');
            shasum.update(password);
            password = shasum.digest('hex');

            User.update({ username: username,password: password},{where: {id: id} })
                .success(onSuccess).error(onError);
        },
        removeById: function(user_id, onSuccess, onError) {
            User.destroy({where: {id: user_id}}).success(onSuccess).error(onError);
        }
    }
});

WOW ive been so close to solving this i just inserted that line in the wrong place thank you! — Marc Rasmussen, Feb 16 '15 at 17:52
Thanks for pointing us in the right direction, but the actual syntax above is not correct: `freezeTableName = true` should be `freezeTableName: true` — DavidP, Mar 02 '19 at 12:45

score -2 · Answer 2 · answered Feb 16 '15 at 17:03

-2

I have not used sequelize before, but reading from their documentation - may be you are missing -

User.sync({force: true}).then(function () { // Table created return User.create({ firstName: 'John', lastName: 'Hancock' }); });

i.e. the table creation part...

Thanks

answered Feb 16 '15 at 17:03

Piyas De

1,786
15
27

instead of using the table "user" this just creates a table called "users" – Marc Rasmussen Feb 16 '15 at 17:18

sequelize gives the wrong table name

2 Answers2