How sequelize works?

Question

I'm trying to understand how sequelize works on a simple example : User can have many posts and post can have only one user.

First question, I don't know if I have to use the migrations or the models with sync for creating my database. I mean, I have to put bearly the same code in both. This is the migration for the users table:

module.exports = {
    up: (queryInterface, Sequelize) => {
        return queryInterface.createTable('Users', {
            id: {
                allowNull: false,
                autoIncrement: true,
                primaryKey: true,
                type: Sequelize.INTEGER
            },
            username: {
                allowNull: false,
                type: Sequelize.STRING,
                unique: true
            },
            password: {
                allowNull: false,
                type: Sequelize.STRING
            },
            email: {
                allowNull: false,
                type: Sequelize.STRING,
                unique: true
            },
            createdAt: {
                allowNull: false,
                type: Sequelize.DATE
            },
            updatedAt: {
                allowNull: false,
                type: Sequelize.DATE
            }
        });
    },
    down: (queryInterface, Sequelize) => {
        return queryInterface.dropTable('Users');
    }
};

And this is the Post model :

'use strict';
module.exports = (sequelize, DataTypes) => {
    const User = sequelize.define('User', {
        username: DataTypes.STRING,
        password: DataTypes.STRING,
        email: DataTypes.STRING
    }, {
        classMethods: {
            associate: (models) => {
                User.hasMany(models.Post);
            }
        }
    });
    return User;
};

Do I also have to specify that the username, email can't be null and must be unique here in the model?

And how do I have to add the foreign key ? In one tutorial, they said me that the database add automaticly the foreign key but I don't think it works if I use the migrations, I have to set it manualy no?

Answer 1

I am really just restrucuring your code.

// Create One database config file

var Sequelize=require('sequelize');

var connection=new Sequelize('project','user','password',{
    dialect:'mysql',
    logging:false
});

connection.authenticate()
  .then(() => {    
    console.log("Connected to database");
  })
  .catch(err => {
    //console.error("Can't connect to database :(\n", err);
  });


module.exports={
    database:connection,
}

//Your User Schema File

var database = require('your_file_path/DatabaseConnection').database;

var Sequelize = require('sequelize');

var Users = database.define('users', {

    username: {
        allowNull: false,
        type: Sequelize.STRING,
        unique: true
    },
    password: {
        allowNull: false,
        type: Sequelize.STRING
    },
    email: {
        allowNull: false,
        type: Sequelize.STRING,
        unique: true
    }
}, {
    underscored: true
},hooks: {
    beforeCreate: (user, option) => {
        users.password = encrypto.encryptEntity(user.password);
        //for automatic encryption of password
    },

}

);

  Users.sync();

    //id, updated_at , and modified_at will be maintained by default

    module.exports = {
        Users
    }

// your post file path

var Posts = database.define('posts', {

    post_content: {
        allowNull: false,
        type: Sequelize.STRING,
        unique: true
    }
}, {
    underscored: true
});

//importing User
var Users = require('file_path')

Users.hasMany(Posts, {
    foreignKey: 'user_id',
    sourceKey: 'user_id',
    onDelete: 'cascade',
    as:'Posts',
});

Posts.belongsTo(Users, {
    foreignKey: 'user_id',
    sourceKey: 'user_id',
    onDelete: 'cascade',
    as:'Users',
}); 
// two way binding.
Posts.sync();

BY maintaining Relation you can easily update data using setter and getter methods

Posts.setUsers(user_object);

// above code will automatically put the user_id found in user_object

//for select query you can use:

Users.findOne({
    where:{
        id:user_id
    },
    include: [{
        model: Posts,
        attributes: ['post_content'],
        as: "Posts"
    }//this will bring every posts user has created
})

I think above coding standard will make your code looks cleaner and will be more helpful for larger projects.

Hope this helps.

Answer 2

For your version "sequelize": "^4.13.2":

classMethods and instanceMethods are removed.

Previous:

const Model = sequelize.define('Model', {
    ...
}, {
    classMethods: {
        associate: function (model) {...}
    },
    instanceMethods: {
        someMethod: function () { ...}
    }
});

New:

const Model = sequelize.define('Model', {
    ...
});

// Class Method
Model.associate = function (models) {
    ...associate the models
};

// Instance Method
Model.prototype.someMethod = function () {..}

See official docs Upgrade to V4

---

So for relations u should walkthrough this steps:

1. Import models
2. Call class "associate" method if exists
3. Export

Example:

// models/index.js
import fs from 'fs';
import path from 'path';
import Sequelize from 'sequelize';
import config from './config';

const sequelize = new Sequelize(config.db.url, config.db.options);

const DB = {};

// Import models
fs
  .readdirSync(__dirname)
  .filter(file => (file.indexOf('.') !== 0) && (file !== path.basename(__filename)) && (file.slice(-3) === '.js'))
  .forEach((file) => {
    const model = sequelize.import(path.join(__dirname, file));
    DB[model.name] = model;
  });

// Here u should call class method for associations
Object.keys(DB).forEach((modelName) => {
  if ('associate' in DB[modelName]) {
    DB[modelName].associate(DB);
  }
});

DB.sequelize = sequelize;
DB.Sequelize = Sequelize;

export default DB;

All relations u can put in your models.

User:

// models/user.js
export default (sequelize, DataTypes) => {
  const User = sequelize.define(
    'users',
    // Fields
    {
      id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true,
      },
      // etc ...
    },
    // Options
    {
      timestamps: false, // <-- turn off timestamps
      underscored: true, // <-- this option for naming with underscore. example: createdAt -> created_at
      validate: {},
      indexes: [],
    },
  );

  User.associate = (models) => {
    User.hasMany(models.post, { 
      // ... 
    });
    User.hasMany(models.comment, { 
      // ... 
    });
    // OR
    models.user.hasMany(models.post, {
      // ...
    });
  };

  return User;
};

Post:

// models/post.js
export default (sequelize, DataTypes) => {
  const Post = sequelize.define(
    'posts',
    // Fields
    {
      // ...
    },
    // Options
    {
      // ...
    },
  );

  Post.associate = (models) => {
    Post.belongsTo(models.user, { 
      // ... 
    });
    // OR
    models.post.belongsTo(models.user, {
      // ...
    });
  };

  return Post;
};

---

Do I also have to specify that the username, email can't be null and must be unique here in the model?

Yes u should define all things in your models, such as keys, relations, whatever. Because your app use models for actions with database.

---

And how do I have to add the foreign key ? In one tutorial, they said me that the database add automaticly the foreign key but I don't think it works if I use the migrations, I have to set it manualy no?

Actually u cant define composite keys in migrations that creates the table and fields.

Best practise for migrations should be like this:

1. 000000_create_users_table
2. 000001_add_foreign_keys_to_users_table
3. 000002_add_new_field_to_users_table
4. etc...

So u should add all things manually in migrations.

For adding indexes in migrations you should use queryInterface.addIndex

module.exports = {
  up: queryInterface => queryInterface.addIndex(
    'users',
    {
      unique: true,
      fields: ['username', 'email'],
// if u want to rename u can use:
//    name: 'whatever'
// by convention default name will be: table_field1_fieldN
    },
  ),

  down: queryInterface => queryInterface.removeIndex(
    'users',
    'users_username_email', // <-- this name by convention, but u can rename it
  ),
};

---

For "keys" you should use queryInterface.addConstraint

Primary Key

queryInterface.addConstraint('Users', ['username'], {
   type: 'primary key',
   name: 'custom_primary_constraint_name'
});

Foreign Key

queryInterface.addConstraint('Posts', ['username'], {
  type: 'FOREIGN KEY',
  name: 'custom_fkey_constraint_name',
  references: { //Required field
    table: 'target_table_name',
    field: 'target_column_name'
  },
  onDelete: 'cascade',
  onUpdate: 'cascade'
});

---

Check all API References

Answer 3

You are right you have to manually set the foreign key relations.

Here is official documentation link : http://docs.sequelizejs.com/manual/tutorial/associations.html

You can try following code:

var user_object = require('your_file_path');
var post_object = require('your_file_path');

user_object.hasMany(post_object, {
    foreignKey: 'user_id',
    sourceKey: 'user_id',
    onDelete: 'cascade',
    as:'Posts',
});

post_object.belongsTo(user_object, {
    foreignKey: 'user_id',
    sourceKey: 'user_id',
    onDelete: 'cascade',
    as:'Posts',
});