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

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