What is Sequelize?

Sequelize is an Object-Relational Mapping (ORM). ORMs allow us to map objects (entities) to databases so we can focus on writing JS code instead of having to switch syntaxes from JS to SQL.

Connecting an application and a database using Sequelize

First, install Sequelize:

npm i sequelize

Next, we need to give Sequelize some data to be able to locate our db and give our application access to it.

// src/util/database.js

const Sequelize = require('sequelize')

const sequelize = new Sequelize(
  'library', // db name
  'root', // db username
  'password', // db password
  { // config object
    dialect: 'mysql', // type of db
    host: 'localhost' // db hosting location
  }
)

module.exports = sequelize

Initializing DB

In order to actually use our database, we need to send a message from our application using sequelize to our DB. For that we include the following at the bottom of app.js just above the module.exports = app statement.

sequelize.sync()
  .then(() => console.log('DB Connected'))
  .catch(console.error)

Models

• An model is an object that is mapped to the database

• In a relational database context, think of:

  • the class as the table
  • instance variables as the columns
  • objects (class instances) as the rows

Here is an example of how a model entity looks in Sequelize:

const Sequelize = require('sequelize')

const sequelize = require('../util/database')

const Book = sequelize.define('book', {
  id: {
    type: Sequelize.INTEGER,
    autoIncrement: true,
    allowNull: false,
    primaryKey: true
  },
  title: {
    type: Sequelize.STRING,
    allowNull: false
  },
  description: {
    type: Sequelize.STRING,
    allowNull: false
  }
});

module.exports = Book

Let's break that down.

sequelize.define()

This method is taking our sequelize context from the database connection file and defining a new model element. What this means for our database is that we are creating a new table for any books we create.

Model config object

const Book = sequelize.define('book', {
  id: {
    type: Sequelize.INTEGER,
    autoIncrement: true,
    allowNull: false,
    primaryKey: true
  },
  title: {
    type: Sequelize.STRING,
    allowNull: false
  },
  description: {
    type: Sequelize.STRING,
    allowNull: false
  }
});

We have a configuration object that we pass after the model entity name. This configuration object contains metadata about each field. This describes to our db how each field (or column) is to be treated. For example, our id has to be an integer or we will get an error. We're also saying the the ids will auto increment, which is to say our db will take care of making these unique for us. ids cannot be null and they are the Primary Key (main identifier) for these model entities.

Camels to Snakes

If Sequelize encounters a class or instance variable name containing more than one word, it converts these from camelCase to snake_case.

Consider this:

const VirtualPet = sequelize.define(`virtual_pet`, {
  id: {
    type: Sequelize.INTEGER,
    autoIncrement: true,
    allowNull: false,
    primaryKey: true
  },
  numberOfLegs: {
    type: Sequelize.INTEGER,
    allowNull: false
  }
}

In this case, Sequelize would expect/generate a table named virtual_pet with columns named id and number_of_legs.

CRUD

• Create: Create and persist (save) new objects.
• Read: Read objects from the database.
• Update: Persist changes made to objects' state (instance variables).
• Delete: Delete objects from the database.

CRUD in Sequelize

Sequelize offers methods to handle these operations. They exist on the Model entities themselves which makes organization a breeze:

Create

The following command will create a new book entity

Book.create({title: 'Refactoring', description: 'A great book on Refactoring by the great Martin Fowler'})

Read

The following will retrieve all existing book entities

Book.findAll()

The following will find a single book entity by it's id

Book.findByPk(123)

The following will find a book entity by it's title

Book.findOne({
  where: {
    title: 'Refactoring'
  }
})

Update

What if we have something existing that we want to update? There are two methods for that. First, let's look at a PUT method that will update the entirety of the existing object.

Book.findByPk(123)
  .then(book => {
    book.title = "New Title",
    book.description = "New description"
    return book.save()
  })
  .then(updatedBook => console.log("Updated", updatedBook))
  .catch(console.error)

We can also update single fields on an existing objects. For that we would use a PATCH method.

updateTitle(id, title) {
  Book.findByPk(id)
    .then(book => {
      book.title = title,
      return book.save()
    })
    .then(updatedBook => console.log("Updated", updatedBook))
    .catch(console.error)
}

updateDescription(id, description) {
  Book.findByPk(id)
    .then(book => {
      book.description = description,
      return book.save()
    })
    .then(updatedBook => console.log("Updated", updatedBook))
    .catch(console.error)
}

Delete

The following will get rid of an existing object.

Book.findByPk(123)
  .then(book => book.destroy())
  .then(book => {
    console.log('Deleted', book)
  })

Something to Relate to

Relational databases are so called because they manage relationships between entities for us. In order to relate two entities, we can use some special statements provided by Sequelize. These should be included just before the initialization statement in app.js. You'll need to import your model entities for this to work as well.

Author.hasMany(Book)
Book.belongsTo(Author)

sequelize.sync({ force: true })
  .then(() => console.log('DB Connected'))
  .catch(console.error)

Relationships

One to One

One to one relationships are when one entity can be related to another exclusively. If we think about a monogamous relationship, one person has one partner and that partner only has the other. This is a good illustration of a one to one relationship.

One to Many/Many to One

One to many can be thought of as a mother and her children. Each of those children only have one mother. But the mother can have multiple children. This is how many to one and one to many relationships work.

Many to Many

We can think of many to many relationships as siblings. One sibling can have many brothers and sisters and also be a brother or sister to many other siblings.

Conclusion

Sequelize is a really powerful abstraction that allow us to easily integrate relational databases into our Node applications

Docs

Make sure to familiarize yourself with the Sequelize Docs.