I can't insert data into a table in Sequelize (foreign keys specifically)

2
votes

So I'm using Sequelize for my PostgreSQL database on my JavaScript app. I have 4 models (Users, Posts, Likes, and FollowingsFollowers). My Users model works fine, it doesn't have any foreign keys so I'm able to create and find data with no problems whatsoever. However, my Posts model has 2 foreign keys (user_id and parent_id). user_id references the primary key (id) from the Users table and parent_id references the primary key (id) from itself (the Posts table). When I try to insert data into the Posts table I'm able to insert data on the regular fields, but the foreign keys are simply ignored.

Here is my database connection:

const Sequelize = require('sequelize');
const API = require('../../client/public/config.js');
const db = new Sequelize(API.elephantSqlUrl);

db.authenticate()
  .then(() => {
    console.log('Connection has been established successfully.');
  })
  .catch((err) => {
    console.error('Unable to connect to the database:', err);
  });

module.exports = db;

Here is my Users model:

const Sequelize = require('sequelize');
const db = require('../db.js');

const Users = db.define('users', {
  username: {
    type: Sequelize.STRING,
    unique: true
  },
  bio: Sequelize.STRING,
  profile_picture: Sequelize.STRING
});

module.exports = Users;

Here is my Posts model:

const Sequelize = require('sequelize');
const db = require('../db.js');

const Posts = db.define('posts', {
  type: Sequelize.INTEGER,
  body: Sequelize.STRING,
  likesCount: {
    type: Sequelize.INTEGER,
    defaultValue: 0
  }
});

module.exports = Posts;

Here is where I define the foreign keys and sync my database:

const db = require('./db.js');
const Users = require('./models/users.js');
const Posts = require('./models/posts.js');
const Likes = require('./models/likes.js');
const FollowingsFollowers = require('./models/followingsFollowers.js');

Posts.belongsTo(Users, { foreignKey: 'user_id' });
Users.hasMany(Posts, { foreignKey: 'user_id' });
Posts.belongsTo(Posts, { foreignKey: 'parent_id' });
Posts.hasMany(Posts, { foreignKey: 'parent_id' });
Likes.belongsTo(Posts, { foreignKey: 'post_id' });
Posts.hasMany(Likes, { foreignKey: 'post_id' });
Likes.belongsTo(Users, { foreignKey: 'user_id' });
Users.hasMany(Likes, { foreignKey: 'user_id' });
FollowingsFollowers.belongsTo(Users, { foreignKey: 'following_id' });
Users.hasMany(FollowingsFollowers, { foreignKey: 'following_id' });
FollowingsFollowers.belongsTo(Users, { foreignKey: 'follower_id' });
Users.hasMany(FollowingsFollowers, { foreignKey: 'follower_id' });

db.sync({ force: true })
  .then(() => {
    console.log('db synced');
  })
  .catch(() => {
    console.log('error syncing db');
  });

and this is where I try to add a Post:

const addPost = (username, post) => {
  return new Promise((resolve, reject) => {
    Users.findOne({ where: { username: username } })
      .then((user) => {
        post.user_id = user.id;

        Posts.create()
          .then((created) => {
            resolve();
          });
      })
      .catch((err) => {
        reject(err);
      });
  });
};

When I call this function with a username and an object with "type" and "body" keys, a Post is created. However, the Post contains an "id", a "type", a "body", a "likesCount", a "createdAt", and an "updatedAt" fields. The "user_id" field never gets added in there.

3
javascriptpostgresqlsequelize.js
your Post model does not contain a user / userId attribute - messerbill
you mean as part of define? i know the field is there cause when i sync i get a message showing my table and both user_id and parent_id fields are in there - Alex
yes but i think some features do not work correctly without setting those attributes - i usually use sequelize-typescript and i always define foreign keys in the model - messerbill
would you mind showing me an example? ive been searching this problem for 3 days now with no luck. anything would help - Alex
github.com/RobinBuschmann/… - i use this one and it works nice in my projects. But of course this is written in typescript - messerbill

3 Answers

1
votes

I don't know if this is gonna solve your problem, but you don't pass any parameter to create() method. post object is passed to function, enriched with user_id and... ignored.

Shouldn't it be like this?:

(...)
Posts.create(post)
(...)
1
votes

I was able to solve it. My solution was to add foreign key relations on my model files, like so:

const Sequelize = require('sequelize');
const Users = require('./users.js');
const db = require('../db.js');

const Posts = db.define('posts', {
  type: Sequelize.INTEGER,
  body: Sequelize.STRING,
  likesCount: {
    type: Sequelize.INTEGER,
    defaultValue: 0
  }
});

Posts.belongsTo(Users, { foreignKey: 'user_id' });
Posts.belongsTo(Posts, { foreignKey: 'parent_id' });

module.exports = Posts;

and then for the file where i sync my db, thats all i would do, like so:

const db = require('./db.js');
const Users = require('./models/users.js');
const Posts = require('./models/posts.js');
const Likes = require('./models/likes.js');
const FollowingsFollowers = require('./models/followingsfollowers.js');

db.sync({ force: true })
  .then(() => {
    console.log('db synced');
  })
  .catch(() => {
    console.log('error syncing db');
  });
0
votes
1. Add user_id as foreign key in posts model and other models where realtion with users required.
2.Define your Posts models as:
const Posts = db.define('posts', {
  type: Sequelize.INTEGER,
  user_id: Sequelize.INTEGER,
  body: Sequelize.STRING,
  likesCount: {
    type: Sequelize.INTEGER,
    defaultValue: 0
  }
});
3. Remove Posts.belongsTo(Posts, { foreignKey: 'parent_id' }); not required