sequelize_notes.md

Sequelize Tips

Schema Design

• Plan out your schema without thinking about Sequelize. This can be done using pen (or pencil) and paper.
  • Start with your entities.
  • Determine how the entities relate to each other.

Sequelize

• Using Sequelize and pg create your Sequelize object
• Each of your entities will be a Sequelize model
• Define your relationships.
  • Relationships can be limited to belongsTo and hasMany.
  • Many to Many relationships can be modeled with an intermediate model and belongsTo and hasMany relationships

An example

• The intent is to manage users and groups. A user can belong to at most one group. A user doesn't have to belong to any groups.
• after sketching out the properties for the User, and Group entity you should determine that a user belongs to at most one group and a group has many users.

//assume conn is created with new Sequelize
const User = conn.define('user', {
  name: {
    type: STRING
  }
});

const Group = conn.define('group', {
  name: {
    type: STRING
  }
});

User.belongsTo(Group);//this adds a groupId foreign key to the users table

• at this point you can seed some data. The easiest way to do this would be to create the groups first.
• you can then create users with their appropriate groupId
• you can now query users with their group.

User.findAll({ include: [Group]})

• However you can not query groups and include users, unless you set up the following:

Group.hasMany(User);//this will assume a groupId is on the users table

• At this point you can run the following:

Group.findAll({ include: [User]});

• Notice there were no alias's used, which is fine

Another Example

The intent is to allow users to belong to many groups, and groups can have many users.

• the easiest way to accomplish this is with another model. In this case we can refer to it as Membership

Membership = conn.define('membership', {});

• in this case we will need to add the appropriate foreign keys to the memberships table.

Membership.belongsTo(Group);//adds groupId to memberships table
Membership.belongsTo(User);//adds userid to memberships table

• at this point we can query the memberships and include the User and/or Group

Membership.findAll({ include: [ User, Group]});

• note that we would not be able to do either of the following:

User.findAll({ include: [Membership]});
Group.findAll({ include: [Membership]});

• if we wanted to be able to include Membership in User and Group queries, we would need to add the following:

User.hasMany(Membership);
Group.hasMany(Membership);

• again notice we don't have any alias's, which is fine

An example with an alias

The intent is to manage products and categories. A product belongs to at most one category and a category can have many products. for whatever reason we want to refer to a product's category as cat, and we want to name our foreign key catId in the products table.

//assume we have an object named db which we have instantiated with new Sequelize

const Product = db.define('product', {
  name: {
    type: STRING
  }
});

const Category = db.define('product', {
  name: {
    type: STRING
  }
});

Product.belongsTo(Category, { as: 'cat' });//this will add a catId foreign key to the products table

• at this point we can run the following query

Product.findAll({ include: [
  { model: Category, as: 'cat' }
]});

• note we need to use as because that's how e defined our belongs To

• we would not be able to run the following query:

Category.findAll({ include: [ Product ]});

• we would need to first add the following:

Category.hasMany(Product, { foreignKey: 'catId'});

• in this case we need to make sure that we don't add an additional foreign key. (catId was created with the belongsTo relationship)