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

Searching an Association