Using Sequelize

What is Sequelize

Sequelize is a package ConnectEd uses to manage it's database. If a database is a series of tables that contain data about your application, then Sequelize allows you to create classes for your tables so that you can easily search and modify your tables without having to write SQL code.

Sequelize is very powerful but can be overwhelming to use. This page covers the basics of how to use sequelize.

Creating a Model

Every table in your database is represented by a model in Sequelize. The model file should specify the table name along with all table columns and their datatypes.

  sequelize.define('User', {  // User is the table name
    utorid: {                        // User has a column called utorid that is a String
      type: DataTypes.STRING,
      unique: true, 
    }, 
    name: DataTypes.STRING,   // User has a column called name that is a String
  });

Creating entries in a table

Table are represented by models. Once you have a model, you can easily use the model to create a new instance of the model. When you create a new instance, sequelize will automatically update your database to add this new entry to the mode. You can create an instance like so: const jane = await User.create({ utorid: 'jane1', name: "Jane" });

create takes in a hash that contains all the data that needs to go into the table. In this case, it takes in the utorid and name of the user. It will then add an entry to the user table with that information.

Simple Searches of a table

You can get all entries of a table with const users = await User.findAll();.

If you want to find a specific entry in a table, you can use where to narrow down your search. For example, to find a User with a specific utorid you can do:

User.findAll({
  where: {
    utorid: 'jane1'   // specify the column you want to search on (utorid) and the value it needs to match (jane1)
  }
});

You can do more complex queries as well. For example, say you want to find all users who have an id or either 1, 2, or 3. Then you can do:

const { Op } = require("sequelize");
User.findAll({
  where: {
    id: {
      [Op.or]: [1, 2, 3] // find all users who id is 1 OR 2 OR 3 
    }
  }
});

Deleting an entry

Once you have an instance of a model, it's easy to remove this instance from the database with destroy. For example, given that jane is an instance of the User model, then await jane.destroy(); will remove all information about jane from the Users table.

Associations ( 1 to Many)

In a database you want to create associations between tables. For example, if you want to keep track of which projects belong to which users, you want a way to connect projects to user. If a project only has one user, you can add a user_id column on the projects table to keep track of the id of the user who created the project. This is a one-to-many relationship - a project has one user but a user has many projects.

const Project = sequelize.define('Project', /* ... */);
const User = sequelize.define('User', /* ... */);

Project.belongsTo(User); // each project belongs to one user
User.hasMany(Project) // a user has many projects

When you explicitly define the association between the tables, Sequelize automatically generates helper methods. For projects it creates the getUser() method which lets you retrieve the projects's user. Likewise, for a user, Sequelize creates the getProjects() method which returns all the projects associated with the user.

In the background, what Sequelize does is read the projects table to determine the user_id associated with that project. With that user_id, it is able to return the associated user when you call getUser(). When you call jane.getProjects(), sequelize looks at the projects table and finds all the projects that have user_id set to jane's id and returns an array of these projects.

Associations ( Many to Many)

If a project has users working on it, and each user is working on multiple projects you have a many-to-many relationship. A project has many users and a user has many projects. You can not store this information within a column in either projects table or users table since there are a variable number of users and projects. Thus you have to create a new table called UsersProjects which keeps track of all user and project pairs.

const Project = sequelize.define('Projects', /* ... */);
const User = sequelize.define(User', /* ... */);
const User_Project = sequelize.define('User_Project', {}, {timestamps: false} )  // keep track of user, project pairs

User.belongsToMany(Project, {through: User_Project}); // each project has many users and this info is in UserProject
Project.belongsToMany(User, {through: User_Project});

When you explicitly define the association between the tables, Sequelize automatically generates helper methods. For projects it creates the getUsers() method which lets you retrieve the projects's users. Likewise, for a user, Sequelize creates the getProjects() method which returns all the projects associated with the user.

In the background, what Sequelize does is read the UserProject table to determine the user_id's associated with that project. With that user_id, it is able to return the associated users when you call getUsers(). When you call jane.getProjects(), Sequelize looks at the UserProjects table and finds all the projects that have user_id set to jane's id and returns an array of these projects.

Searching an Association