Sequelizejs: Unhandled rejection SequelizeDatabaseError: column user.playerstatsId does not exist

Question

I have an existing database and I want to retrieve from the Table "User", but the user Table has a one to one Relationship with the "PlayerStats" table. I want to retrieve the User object and also it's Playerstats as a Json object and not a foreign key Id.After writing my code and running it i get an error in the console saying:

    Sat, 05 May 2018 02:18:26 GMT sequelize deprecated String based operators are now deprecated. Please use Symbol based operators for better security, read more at http://docs.sequelizejs.com/manual/tutorial/querying.html#operators at node_modules\sequelize\lib\sequelize.js:242:13
Executing (default): SELECT "user"."id", "user"."address", "user"."bio", "user"."country", "user"."email", "user"."firstname", "user"."gender", "user"."lastname", "user"."locked", "user"."money", "user"."onlinestatus", "user"."password", "user"."phonenumber", "user"."picture", "user"."username", "user"."usertype", "user"."playerstatsId", "playerstats"."id" AS "playerstats.id", "playerstats"."time" AS "playerstats.time", "playerstats"."expenses" AS "playerstats.expenses", "playerstats"."friends" AS "playerstats.friends", "playerstats"."loss" AS "playerstats.loss", "playerstats"."matchplayed" AS "playerstats.matchplayed", "playerstats"."profit" AS "playerstats.profit", "playerstats"."readmessage" AS "playerstats.readmessage", "playerstats"."referrals" AS "playerstats.referrals", "playerstats"."referrer" AS "playerstats.referrer", "playerstats"."totalmessage" AS "playerstats.totalmessage", "playerstats"."unreadmessage" AS "playerstats.unreadmessage", "playerstats"."wallet" AS "playerstats.wallet", "playerstats"."wins" AS "playerstats.wins" FROM "users" AS "user" LEFT OUTER JOIN "playerstats" AS "playerstats" ON "user"."playerstatsId" = "playerstats"."id" LIMIT 1;
Unhandled rejection SequelizeDatabaseError: column user.playerstatsId does not exist

I from what i understand i feel the sequelize is trying to join the Users and Playerstats table together.

This is my code for User:

  var Sequelize = require('sequelize');

var db = require('../database/postgres');


var User =db.define('user', {

    id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        primaryKey: true
    },

    address: {
        type: Sequelize.STRING,
        field: 'address'
    },

    bio: {
        type: Sequelize.STRING,
        field: 'bio'
    },

    country: {
        type: Sequelize.STRING,
        field: 'country'
    },

    email: {
        type: Sequelize.STRING,
        field: 'email'
    },

    firstname: {
        type: Sequelize.STRING,
        field: 'firstname'
    },

    gender: {
        type: Sequelize.STRING,
        field: 'gender'
    },

    lastname: {
        type: Sequelize.STRING,
        field: 'lastname'
    },

    locked: {
        type: Sequelize.BOOLEAN,
        field: 'locked'
    },

    money: {
        type: Sequelize.DOUBLE,
        field: 'money'
    },

    onlinestatus: {
        type: Sequelize.STRING,
        field: 'onlinestatus'
    },

    password: {
        type: Sequelize.STRING,
        field: 'password'
    },

    phonenumber: {
        type: Sequelize.STRING,
        field: 'phonenumber'
    },

    picture: {
        type: Sequelize.STRING,
        field: 'picture'
    },

    username: {
        type: Sequelize.STRING,
        field: 'username'
    },

    usertype: {
        type: Sequelize.STRING,
        field: 'usertype'
    },



}, {
    tableName: 'users',

    timestamps: false
});


module.exports = User;

my code for PlayerStats:

    var Sequelize = require('sequelize');

var db = require('../database/postgres');


var PlayerStats =db.define('playerstats', {

    id: {
        type: Sequelize.INTEGER,
        field: 'id',
        allowNull: false,
        primaryKey: true
    },

    time: {
        type: Sequelize.DATE,
        field: 'time'
    },

    expenses: {
        type: Sequelize.DOUBLE,
        field: 'expenses'
    },

    friends: {
        type: Sequelize.INTEGER,
        field: 'friends'
    },

    loss: {
        type: Sequelize.INTEGER,
        field: 'loss'
    },

    matchplayed: {
        type: Sequelize.INTEGER,
        field: 'matchplayed'
    },

    profit: {
        type: Sequelize.DOUBLE,
        field: 'profit'
    },

    readmessage: {
        type: Sequelize.INTEGER,
        field: 'readmessage'
    },

    referrals: {
        type: Sequelize.INTEGER,
        field: 'referrals'
    },

    referrer: {
        type: Sequelize.INTEGER,
        field: 'referrer'
    },

    totalmessage: {
        type: Sequelize.INTEGER,
        field: 'totalmessage'
    },

    unreadmessage: {
        type: Sequelize.INTEGER,
        field: 'unreadmessage'
    },

    wallet: {
        type: Sequelize.DOUBLE,
        field: 'wallet'
    },

    wins: {
        type: Sequelize.INTEGER,
        field: 'wins'
    }

}, {
    tableName: 'playerstats',

    timestamps: false
});


module.exports = PlayerStats;

And the final main js class which run them:

    var io = require('socket.io')(process.env.PORT || 4000);

var User = require('./server/entities/user');

var PlayerStats = require('./server/entities/playerstats');





new Server();

const room = 'quick-chat';

function Server() {


    User.belongsTo(PlayerStats,{as: 'playerstats'});


    User.findOne({include: [{

        model: PlayerStats,

        as: 'playerstats'

    }]}).then(function (user) {

        console.log(user);

    });


    var global = this;



    io.on('connection',function(socket){

        console.log("A client has connected");

        socket.on('join', (data) => {

            //todo connect to the gambeat db and update the users info to online

            socket.join(room);

            console.log("client has joined");

        });

        socket.on('disconnect', () => {

            console.log("client has disconnect");

        });

    });



}

Can some please show me how to go about it.

Answer 1

I figured out the problem, based on this article,

By default the foreign key for a belongsTo relation will be generated from the target model name and the target primary key name. The default casing is camelCase however if the source model is configured with underscored: true the foreignKey will be snake_case.

This resulted in "playerstatsId" which I did not have in my user Table. You can correct this by specifying the right name for the foreign key, by using the Foreign key option. hence

User.belongsTo(PlayerStats,{foreignKey: 'playerstats',as: 'playerstat'});