Skip to content

Using Sequelize

Jump to bottom
Veerpal Brar edited this page Mar 10, 2020 · 7 revisions

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.

Importing models

All models and tables are exports by the server/src/models/index.js file. If you want to access a model or table within another file (ex: ProfileController.js) you will have to import the model into the file like so: const Project = require('../models').Project where ../models is the path to the models/index.js file from the current file.

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.

For example, I can got all of a user projects like so:

user = await User.findOne({where: {id: user_id}});
projects = await user.getProjects();

Searching an Association

You can search using where on associations as well. For example, if you want to find all of a user projects that have an id of 1, 2, or 3 you can do: jane.getProjects({ where: { id : [1, 2, 3]} })

If you want to search through all Users and Projects and not just a particular user or project you can use include keyword. For example, let's find all users who have a project id of 1, 2, or 3. You can do a search on users, and use include with model Project, to search with projects associated with users.

User.findAll({                
  include: {                  
    model: Project,           // specify which model you want to join with the Users table
    where: {                  //this where applies to the projects table 
       id: : [1, 2, 3]  // find all projects who id is 1 OR 2 OR 3 
    }
  }
})

This would return an array of Users, where each user had an array of projects.

[{
  "name": "John Doe",        // John Doe is the user
  "id": 1,
  "projects": [{             // This is a list of John Doe's Projects
    "name": "Project 1",
    "id": 1,                 // Notice that the projects id is 1
  }]
}, 
{
  "name": "Jane Doe",
  "id": 2,
  "projects": [{
    "name": "Project 1",
    "id": 1,
  }]
}, 
{
  "name": "Bob Doe",
  "id": 3,
  "projects": [{
    "name": "Project 2",
    "id": 2,
  }]
}]

In the backend, Sequelize is just going a complicated query where it first finds all projects with an id equal to 1, 2, or 3 and then it finds the users associated with these projects using the UsersProject's table. This could be a complicated query to write in SQL but sequelize allows us to abstract away the complicated details.

Clone this wiki locally