Sequelize CRUD 101

This tutorial covers the basics of CRUD operations using the Node ORM Sequelize. We will use Express.js to build out an API, but the focus here is Sequelize. I will assume you have PostgreSQL installed on your machine, and that you're using Node version 6. This tutorial uses Sequelize version 3.24.3.

Before we begin, grab the accompanying repo to follow along.

Overview of the file structure

In the server folder, you'll find three subfolders - config, models, and routes.

Config folder (two files):

  1. env.js has all of our environment variables for the database connection. This file only has information for a dev environment. In a real app, your production variables would go here, too. This information is bundled in a single objects, which is then exported for app-wide use.
  2. db.js is the more interesting of the two files. Here we initialize Sequelize, and pull in the models. Sequelize (capitalized), the sequelize instance (not capitalized), and the models are attached to a db object. This db object is exported for app-wide use.

Router folder (index file and router folder):

  1. The routes subfolder contains the Express.js routes. In this case you'll find two sets of routes (owners, and pets), one set per file.
  2. The index.js file pulls in the files from the routes folder, and injects the app object and db object into each route.

Models folder (one file per model):

  1. This folder contains an owner model, and a pet model. Each model defines a database table.
  2. The models are exported for use in the db.js file mentioned above.

As you may have guessed, the database we are making stores data for pets, pet owners, and the relationships between pets and owners.

Models

Owner model

'use strict'

module.exports = (sequelize, DataTypes) => {  
  const Owner = sequelize.define('owner', {
    id: {
      type: DataTypes.UUID,
      primaryKey: true,
      defaultValue: DataTypes.UUIDV4
    },
    name: {
      type: DataTypes.STRING,
      required: true
    },
    role: {
      type: DataTypes.ENUM,
      values: ['user', 'admin', 'disabled']

    },
    created_at: {
      type: DataTypes.DATE,
      allowNull: false
    },
    updated_at:  DataTypes.DATE,
    deleted_at: DataTypes.DATE
  }, {
    paranoid: true,
    underscored: true
  });
  return Owner;
};

Pet model

'use strict'

module.exports = (sequelize, DataTypes) => {  
  const Pet = sequelize.define('pet', {
    id: {
      type: DataTypes.UUID,
      primaryKey: true,
      defaultValue: DataTypes.UUIDV4
    },
    name: {
      type: DataTypes.STRING,
      required: true
    },
    owner_id: {
      type: DataTypes.UUID,
      allowNull: false
    },
    type: {
      type: DataTypes.ENUM,
      values: ['dog', 'cat', 'minx']
    },
    created_at: {
      type: DataTypes.DATE,
      allowNull: false
    },
    updated_at:  DataTypes.DATE,
    deleted_at: DataTypes.DATE
  }, {
    paranoid: true,
    underscored: true
  });
  return Pet;
};

Sequelize will take care of managing the id, created_at, updated_at, and deleted_at columns. By setting paranoid: true, a deleted record (table row) will not be returned in future queries. If the model is set to paranoid: false, then all records, including those with a deletion timestamp, will be returned if they match a query.

Model Associations

Before we can use our models, we need to declare how they are related. Our case is a simple one, involving two associations.

Association #1: belongsTo: This association is the one that, in a general manner, declares how the tables are connected. In our case, the pet model has a column for owner_id, meaning the pet belongs to an owner. That makes sense!

The way I remember this kind of association, a way that is helpful in less obvious examples, is through the phrase the table belongs to the column. That sounds weird, but look at our case. The pet table has an owner_id column, and using this helpful phrase we know that the table (pets) belongs to the column (owner).

db.pets.belongsTo(db.owners);  

Association #2: hasMany: The second set of associations we have to consider are those of:

  • one to many
  • many to one
  • many to many

In our world, each pet can only belong to one owner, and an owner can have more than one pet. That's a one to many relationship.

db.owners.hasMany(db.pets);  

These associations are found in the repo's db.js file.

For more on Sequelize associations, see my blog post dedicated to the topic.

Sequelize CRUD Basics

We've set up the the Owner and Pet models, our table associations, and are now ready for some CRUD operations. First things first - we can't update and delete non-existent data, so we need to create some.

Create

The creating a record takes the following form:

model.create({  
  // required columns go here in the form of object properties
})

Let's add a record to the owners table. This is important, because we need an owner_id to include in the pet table when we create pets.

db.owners.create({  
  name: 'Loren',
  role: 'admin'
})
.then(newUser => {
  console.log(`New user ${newUser.name}, with id ${newUser.id} has been created.`);
});

Three things to note:

  • The only valid entries for role are 'user', 'admin', and 'disabled' because that column is an enumerated data type (cf. Owner model lines 15-18).
  • The .then() is provided to show that, with the exception of deleted_at, all columns of a new owner are returned from the creation operation. This isn't always the case (watch out for the tricky bulkCreate method!).
  • The id is autogenerated and should not be included as a property during creation.

Now that we have an owner_id, we can enter my cat Max into the database.

db.pets.create({  
  name: 'Max',
  owner_id: 'c0eebc45-9c0b-4ef8-bb6d-6bb9bd380a13',
  type: 'cat'

})
.then(newPet => {
  console.log(`New ${newPet.type} ${newPet.name}, with id ${newPet.id} has been created.`);
});

Note that the only valid pet types are 'dog', 'cat', and 'minx'. This is for the same reason as noted in the Owners model; the type column is an enumerated data type.

Read

We finally have some data, so let's perform read operations on our tables. Searching by id is an efficient and precise way to find a record. For example, we can find the owner 'Loren' in the Owners table like so:

db.users.findOne({  
  name: 'Loren'
})
.then(user => {
  console.log(`Found user: ${user}`);
});

The problem is that findOne will return only the first matching record, even if it's the wrong Loren! Here's a better idea - search by id.

db.users.findOne({  
  id: 'c0eebc45-9c0b-4ef8-bb6d-6bb9bd380a13'
})
.then(user => {
  console.log(`Found user: ${user}`);
});

There's also the handy findAll method. Let's say we want to see all the owners with a role of 'admin'. Here's what that looks like using Sequelize:

db.users.findAll({  
  role: 'admin'
})
.then(admins => {
  console.log(`Found ${admins.length} matching records.`);
});

If you want to retrieve all the records from a table use: db.users.findAll().then(...)

Update

Updating a record is only slightly more involved than creating or finding one.

Before we can update a record, we have to find it. Then we use the updateAttributes method on the retrieved record.

Here's one way to update a record:

db.pets.findOne({  
  name: 'Max'
})
.then(pet => {
  pet.updateAttributes({
    name: 'Maxy-boi-boi'
  });
});

The above code finds the first pet named 'Max', and then changes the name to 'Maxy-boi-boi' (his favorite nickname).

The code below updated in the same way as that above, but does so in a single line.

const newData = {  
  name: 'Maxy-boi-boi'
};

db.pets.update(newData, {where: { name: 'Max' } })  
.then(updatedMax => {
  console.log(updatedMax)
})

Updating a record returns the record. This means that if you want to do something with the updated record (like send it back up to the client, or log it), you can add another .then() after the update, as the code above demonstrates.

Delete

Deleting a record follows the same procedure as finding one. Although we are sending a DELETE HTTP request, the Sequelize method is destroy.

db.pets.destory({  
  where: { name: 'Max' }
})
.then(deletedPet => {
  console.log(`Has the Max been deleted? 1 means yes, 0 means no: ${deletedPet}`);
});

As you've probably gathered from the logged out response, Sequelize return a 1 or a 0 indicating the success of the destroy method.

Routes and CRUD

Let's take a quick look at the routes for performing CRUD operations on the owners model. The scenario for pets is very similar, and you can check it out in the repo.

'use strict';

module.exports = (app, db) => {

  // GET all owners
  app.get('/owners', (req, res) => {
    db.owners.findAll()
      .then(owners => {
        res.json(owners);
      });
  });

  // GET one owner by id
  app.get('/owner/:id', (req, res) => {
    const id = req.params.id;
    db.owners.find({
      where: { id: id }
    })
      .then(owner => {
        res.json(owner);
      });
  });

  // POST single owner
  app.post('/owner', (req, res) => {
    const name = req.body.name;
    const role = req.body.role;
    db.owners.create({
      name: name,
      role: role
    })
      .then(newOwner => {
        res.json(newOwner);
      })
  });

  // PATCH single owner
  app.patch('/owner/:id', (req, res) => {
    const id = req.params.id;
    const updates = req.body.updates;
    db.owners.find({
      where: { id: id }
    })
      .then(owner => {
        return owner.updateAttributes(updates)
      })
      .then(updatedOwner => {
        res.json(updatedOwner);
      });
  });

  // DELETE single owner
  app.delete('/owner/:id', (req, res) => {
    const id = req.params.id;
    db.owners.destroy({
      where: { id: id }
    })
      .then(deletedOwner => {
        res.json(deletedOwner);
      });
  });
};

The only thing being done in the routes that we haven't covered above is grabbing ids from route parameters, and parsing the request body - both of which are routine procedures.

One thing above that is noteworthy, is the PATCH route/update method (lines 38 - 50). You can see we're sending an updates object to our API from the client. This is to provide the flexibility of updating only one, or updating many columns. For instance I wanted to update only an owner's name, I would send a JSON request like this:

{
  "updates": {
    "name": "Loren Stewart"
  }
}

If I wanted to update more than one column, I would send something like this:

{
  "updates": {
    "name": "Loren Stewart",
    "role": "user"
  }
}

That covers the basics of Sequelize CRUD operations. Check out my Sequelize CRUD 102 post to learn more advanced CRUD techniques.

If you'd like to be notified when I publish new content, sign up for my mailing list in the navbar.