Sequelize

• ORM
• основана на promises
• PostgreSQL, MySQL, SQLite, MSSQL

Sequelize

https://www.npmjs.com/package/sequelize
    

http://docs.sequelizejs.com/
    

Install

npm install --save sequelize

// Одно из:
npm install --save pg pg-hstore
npm install --save mysql2
npm install --save sqlite3
npm install --save tedious // MSSQL
    

Please install mysql package manually
    

npm install sequelize
npm install mysql
    

npm install sequelize mysql
    

Get started

CREATE TABLE Posts (
  id INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(500) NOT NULL,
  content TEXT NOT NULL,
  date DATE NOT NULL,
  rate INT NOT NULL DEFAULT 0
);
    

CREATE TABLE Comments (
  id INT PRIMARY KEY AUTO_INCREMENT,
  postId INT NOT NULL REFERENCES Posts(id),
  date DATE NOT NULL,
  text VARCHAR(5000) NOT NULL
);
    

config.json

{
  "db": {
    "host": "localhost",
    "name": "my_db",
    "user": "myuser",
    "pass": "123#qwe"
  }
}
    

index.js

const Sequelize = require('sequelize');
const config = require('./config.json');

const dbOptions = {
  host: config.db.host,
  dialect: 'mysql',
  define: { timestamps: false }
};

const sequelize = new Sequelize(config.db.name,
  config.db.user, config.db.pass, dbOptions);
    

index.js

sequelize
  .authenticate()
  .then(function() {
    console.log('Connected');
  })
  .catch(function (err) {
    console.log('Error:', err);
  });
    

index.js

const Posts = sequelize.define('Posts', {
  id: { type: Sequelize.INTEGER,
    primaryKey: true },

  title: { type: Sequelize.STRING(500),
    allowNull: false },

  content: { type: Sequelize.TEXT,
    allowNull: false },

  date: { type: Sequelize.DATEONLY,
    allowNull: false },

  rate: { type: Sequelize.INTEGER,
    allowNull: false }
});
    

index.js

const Comments = sequelize.define('Comments', {
  id: { type: Sequelize.INTEGER,
    primaryKey: true },

  postId: { type: Sequelize.INTEGER,
    allowNull: false },

  text: { type: Sequelize.STRING(5000),
    allowNull: false },

  date: { type: Sequelize.DATEONLY,
    allowNull: false }
});
    

index.js

Posts
  .findAll({ raw: true })
  .then((posts) => {
    console.log(posts);
  });
    

Executing (default): SELECT `id`, `title`,
  `content`, `date`, `rate` FROM `Posts`
  AS `Posts`;

[ { id: 1,
  title: 'График записи в библиотеку',
  content: 'График',
  date: 2016-09-01T00:00:00.000Z,
  rate: 0 },

  { id: 2,
  title: 'Встреча ректора БГТУ со студентами',
  content: 'Фото',
  date: 2016-09-02T00:00:00.000Z,
  rate: 0 } ]
    

index.js

Comments
  .findAll({
    where: { postId: 1 },
    raw: true
  })
  .then((comments) => {
    console.log(comments);
  });
    

Executing (default): SELECT `id`, `postId`, `text`,
  `date` FROM `Comments` AS `Comments`
  WHERE `Comments`.`post` = 1;

[ { id: 1, postId: 1, text: 'Первый',
    date: 2016-09-01T00:00:00.000Z },

  { id: 2, postId: 1, text: 'Второй',
    date: 2016-09-01T00:00:00.000Z },

  { id: 3, postId: 1, text: 'Третий',
    date: 2016-09-02T00:00:00.000Z } ]
    

index.js

Posts.hasMany(Comments, {
  as: 'comments',
  foreignKey: 'postId'
});

Comments.belongsTo(Posts, {
  as: 'post',
  foreignKey: 'postId'
});
    

index.js

Posts
  .findAll({
    include: {
      model: Comments,
      as: 'comments'
    }
  })
  .then(function(posts) {
    console.log(posts[0].get({ plain: true}));
  });
    

SELECT `Posts`.`id`, `Posts`.`title`,
  `Posts`.`content`, `Posts`.`date`, `Posts`.`rate`,
  `comments`.`id` AS `comments.id`,
  `comments`.`postId` AS `comments.postId`,
  `comments`.`text` AS `comments.text`,
  `comments`.`date` AS `comments.date`

  FROM `Posts` AS `Posts` LEFT OUTER JOIN
  `Comments` AS `comments`

  ON `Posts`.`id` = `comments`.`postId`
    

{ id: 1,
  title: 'График записи в библиотеку',
  content: 'График',
  date: 2016-09-01T00:00:00.000Z,
  rate: 0,
  comments:
   [ { id: 1,
     postId: 1,
     text: 'Первый',
     date: 2016-09-01T00:00:00.000Z },
   { id: 2,
     postId: 1,
     text: 'Второй',
     date: 2016-09-01T00:00:00.000Z },
   { id: 3,
     postId: 1,
     text: 'Третий',
     date: 2016-09-02T00:00:00.000Z } ] }
    

Models

Models - Definition

const Model = sequelize.define('table_name', {
  id: { type: Sequelize.INTEGER,
    primaryKey: true },
  field_1: Sequelize.STRING(500),
  field_2: Sequelize.INTEGER
})
    

Models - Data types

Sequelize.STRING          // VARCHAR(255)
Sequelize.STRING(1234)    // VARCHAR(1234)
Sequelize.STRING.BINARY   // VARCHAR BINARY
Sequelize.TEXT            // TEXT
Sequelize.TEXT('tiny')    // TINYTEXT
    

Models - Data types

Sequelize.INTEGER       // INTEGER
Sequelize.BIGINT        // BIGINT
Sequelize.BIGINT(11)    // BIGINT(11)
    

Models - Data types

Sequelize.FLOAT           // FLOAT
Sequelize.FLOAT(11)       // FLOAT(11)
Sequelize.FLOAT(11, 12)   // FLOAT(11,12)
    

Models - Data types

Sequelize.DOUBLE          // DOUBLE
Sequelize.DOUBLE(11)      // DOUBLE(11)
Sequelize.DOUBLE(11, 12)  // DOUBLE(11,12)
    

Models - Data types

Sequelize.DECIMAL         // DECIMAL
Sequelize.DECIMAL(10, 2)  // DECIMAL(10,2)
    

Models - Data types

Sequelize.DATE          // DATETIME
Sequelize.DATE(6)       // DATETIME(6)
Sequelize.DATEONLY      // DATE without time
    

Models - Data types

Sequelize.BOOLEAN         // TINYINT(1)
Sequelize.ENUM('value 1', 'value 2')
Sequelize.BLOB            // BLOB
Sequelize.BLOB('tiny')    // TINYBLOB
Sequelize.GEOMETRY        // Spatial column
    

Models - Getters & Setters

const Users = sequelize.define('Users', {
  id: { type: Sequelize.INTEGER,
    primaryKey: true },

  firstname: { type: Sequelize.STRING(200),
    set: function(value) {
      this.setDataValue('firstname',
        capitalize(value));
    }
  },

  lastname: { type: Sequelize.STRING(200),
    set: function(value) {
      this.setDataValue('lastname',
        capitalize(value));
    }
  }
});
    

Models - Getters & Setters

function capitalize(value) {
  value = value.toLowerCase();

  return value[0].toUpperCase()
    + value.substr(1);
}
    

Models - Getters & Setters

Users.findOne()
  .then((user) => {
    user.firstname = 'nikita';

    console.log(user.firstname);
  });

// => Nikita
    

Models - Getters & Setters

const Users = sequelize.define('Users', {
  id: { type: Sequelize.INTEGER,
    primaryKey: true },
  firstname: Sequelize.STRING(200),
  lastname: Sequelize.STRING(200)
}, {
  getterMethods: {
    name: function() {
      let firstname = this.firstname;
      let lastname = this.lastname;

      return `${firstname} ${lastname}`;
    }
  },
  setterMethods: { ... }
});
    

Models - Getters & Setters

Users.findOne()
  .then((user) => {
    console.log(user.name);
  });

// => Nikita Tsyhanenko
    

Models - Getters & Setters

/* a getter for 'title' property */
function() {
  return this.getDataValue('title');
}
    

/* a setter for 'title' property */
function(title) {
  this.setDataValue('title', title);
}
    

Models - Validations

const Users = sequelize.define('Users', {
  id: { type: Sequelize.INTEGER,
    primaryKey: true },

  email: {
    type: Sequelize.STRING(100),
    validate: {
      isEmail: {
        args: true,
        msg: 'Incorrect email'
      },
      notEmpty: true
    }
  }
});
    

Models - Validations

let user = Users.build({
  email: '123'
});

user.validate().then((result) => {
  console.log('OK');
}).catch(errors => {
  errors.forEach((err) => {
    console.log(err.message);
  });
});

// => Incorrect email
    

Models - Validations

let user = Users.build({
  email: ''
});

user.validate().then((result) => {
  console.log('OK');
}).catch(errors => {
  errors.forEach((err) => {
    console.log(err.message);
  });
});

// => Incorrect email
// => Validation notEmpty failed
    

Models - Validations

https://github.com/chriso/validator.js
    

Models - Validations

is: /^[a-z]+$/i
not: /^[a-z]+$/i
isEmail: true
isUrl: true
isIP: true
isAlpha: true
isAlphanumeric: true
isNumeric: true
isInt: true
isFloat: true
isLowercase: true
isUppercase: true
equals: 'specific value'
contains: 'foo'
    

Models - Validations

isIn: [['foo', 'bar']]
notIn: [['foo', 'bar']]
len: [2,10]
isDate: true
isAfter: "2011-11-05"
isBefore: "2011-11-05"
max: 23
min: 11
isCreditCard: true

// custom validator
isEven: function(value) {
  if (parseInt(value) % 2 != 0) {
    throw new Error('Not even')
  }
}
    

Models - Validations

isEmail: {
  msg: 'Incorrect email'
}
    

Models - Validations

const Users = sequelize.define('Users', {
  id: { type: Sequelize.INTEGER,
    primaryKey: true },
  email: Sequelize.STRING(200),
  login: Sequelize.STRING(200)
}, {
  validate: {
    emailOrLogin: function () {
      if (!this.email && !this.login) {
        throw new Error('Either email or
          login should be set');
      }
    }
  }
});
    

Models - Configuration

const Users = sequelize.define('Users', { ... }, {
  // updatedAt, createdAt
  timestamps: false,

  // deletedAt
  paranoid: true,

  // created_at, updated_at, deleted_at
  underscored: true
});
    

Models - Configuration

const Users = sequelize.define('Users', { ... }, {
  createdAt: false,
  updatedAt: 'updateTimestamp'
  deletedAt: 'destroyTime',
  paranoid: true
});
    

Models - Import

// index.js
const User = sequelize.import(__dirname
      + "/model/user");

// model/user.js
module.exports = function(sequelize, Sequelize) {
  return sequelize.define("Users", {
    id: { 
      type: Sequelize.INTEGER,
      primaryKey: true 
    },
    email: Sequelize.STRING(200),
    login: Sequelize.STRING(200)
  });
}
    

Models - Synchronization

Posts.sync()
Comments.sync()

Posts.sync({force: true})

Comments.drop()
Posts.drop()

Posts.[sync|drop]().then(function() {
  // done
}).catch(function(error) {
  // error
})
    

Models - Synchronization

// Create tables that not in DB
sequelize.sync()

// Force sync all models
sequelize.sync({ force: true })

// Drop all tables
sequelize.drop()

sequelize.[sync|drop]().then(function() {
  // done
}).catch(function(error) {
  // error
})
    

Models - Synchronization

// This will run .sync() only if
// database name ends with '_test'
sequelize.sync({ force: true, match: /_test$/ });
    

Models - Custom methods

const User = sequelize.define('user', { ... });

// Adding a class level method
User.classLevelMethod = function() {
  return 'foo';
};

// Adding an instance level method
User.prototype.instanceLevelMethod = function() {
  return 'bar';
};

User.build({ ... }).instanceLevelMethod();
    

Models - Indexes

sequelize.define('user', { ... }, {
  indexes: [
  // index name will be [table]_[fields]
  {
    unique: true,
    fields: ['email']
  },
  {
    name: 'public_by_author',
    fields: ['author', 'status'],
    where: {
    status: 'public'
    }
  }
  ]
})
    

Models - Finders

Users.findById(123)
  .then(function(user) { ... });

Users.findOne({where: {email:'user@example.com'}})
  .then(function(user) {
    // first or null
  });

Users.findOne({
  where: {email:'user@example.com'},
  attributes: ['id', ['firstname', 'name']]
}).then(function(user) {
    // user.id
    // user.name
  });
    

Models - Finders

User.findOrCreate({
    where: { username: 'alankey' },
    defaults: { job: 'Technical Lead' }
  })
  .spread(function(user, created) {
    // user - object
    // created - bool
  });

// User.findCreateFind
    

Models - Finders

Users.findAndCountAll({
   where: {
    title: {
      [Op.like]: '%@gmail.com'
    }
   },
   offset: 10,
   limit: 5
  })
  .then(function(result) {
    console.log(result.count);
    console.log(result.rows);
  });
    

Models - Finders

// count - кол-во пользователей с профайлами
User.findAndCountAll({
  include: [
   { model: Profile, required: true}
  ],
  limit: 3
});
    

Models - Finders

Users.findAll().then(function(users) { ... })

Users.all().then(function(users) { ... })

Users.findAll({ where: { name: 'Nikita' } })
  .then(function(users) { ... })

Users.findAll({ where: ["id > ?", 25] })
  .then(function(users) { ... })

Users.findAll({ where: { id: [1,2,3] } })
  .then(function(users) { ... })
    

Models - Filtering

// Sequelize.Op
where: {
  id: {
    [Op.and]: {a: 5}       // AND (a = 5)
    [Op.or]: [{a: 5}, {a: 6}]  // (a = 5 OR a = 6)
    [Op.gt]: 6,        // id > 6
    [Op.gte]: 6,         // id >= 6
    [Op.lt]: 10,         // id < 10
    [Op.lte]: 10,        // id <= 10
    [Op.ne]: 20,         // id != 20
    [Op.between]: [6, 10],   // BETWEEN 6 AND 10
    [Op.notBetween]: [11, 15], // NOT BETWEEN 11 AND 15
    [Op.in]: [1, 2],       // IN [1, 2]
    [Op.notIn]: [1, 2],    // NOT IN [1, 2]
    [Op.like]: '%hat',     // LIKE '%hat'
    [Op.notLike]: '%hat'     // NOT LIKE '%hat'
  }
}
    

Models - Filtering

// WHERE name = 'a project' AND
// (id IN (1, 2, 3) OR (id > 10 AND id < 100));
Model.findAll({
  where: {
  name: 'a project',
  [Op.or]: [
    {id: [1, 2, 3]},
    {
      [Op.and]: [
        {id: {gt: 10}},
        {id: {lt: 100}}
      ]
    }
  ]
  }
});
    

Models - Filtering

Project.findOne({
  where: {
  name: 'a project',
  [Op.or]: [
    { id: [1,2,3] },
    { id: { $gt: 10 } }
  ]
  }
})
    

Models - Filtering

Project.findOne({
  where: {
  name: 'a project',
  id: {
    [Op.or]: [
      [1,2,3],
      { [Op.gt]: 10 }
    ]
  }
  }
})
    

Models - Filtering

SELECT *
FROM `Projects`
WHERE (
  `Projects`.`name` = 'a project'
   AND (`Projects`.`id` IN (1,2,3)
      OR `Projects`.`id` > 10)
)
LIMIT 1;
    

Models - Paggination

// limit the results of the query
Users.findAll({ limit: 10 })

// step over the first 10 elements
Users.findAll({ offset: 10 })

// step over the first 10 elements, and take 2
Users.findAll({ offset: 10, limit: 2 })
    

Models - Ordering & Grouping

Project.findAll({order: ['email', 'DESC']})

Project.findAll({group: 'name'})
    

Models - Raw

Project.findAll({ where: { ... }, raw: true })
    

Models - Count

Project.count().then(function(c) {
  console.log("There are " + c + " projects!")
})

Project.count({ where: ["id > ?", 25] })
  .then(function(c) {
  console.log("There are " + c
    + " projects with an id greater than 25.")
  })
    

Models - Min & Max

/*
  The first one is 10 years old,
  The second one is 5 years old,
  The third one is 40 years old.
*/
Users.max('age')
  .then(function(max) {
    // this will return 40
  })

Users.max('age', { where: { age: { [Op.lt]: 20 } } })
  .then(function(max) {
    // will be 10
  })
    

Models - Min & Max

/*
  The first one is 10 years old,
  The second one is 5 years old,
  The third one is 40 years old.
*/
Users.min('age')
  .then(function(min) {
    // this will return 5
  })

Users.min('age', { where: { age: { [Op.gt]: 5 } } })
  .then(function(min) {
    // will be 10
  })
    

Models - Sum

/*
  The first one is 10 years old,
  The second one is 5 years old,
  The third one is 40 years old.
*/
Users.sum('age')
  .then(function(sum) {
    // this will return 55
  })

Users.sum('age', { where: { age: { [Op.gt]: 5 } } })
  .then(function(sum) {
    // will be 50
  })
    

Models - Include

Comments.findAll({
  include: [{
    model: Users,
    as: 'users',
    where: { email: 'user@example.com'}
  }]
}).then(function () { ... });

Users.findAll({ include: [{ all: true }]})
  .then(function () { ... });
    

Models - Include

Users.findAll({
  include: [{ model: Comments, as: 'comments' }],
  order: [ [{ model: Comments, as: 'comments' },
  'date', 'DESC'] ]
}).then(function () { ... });
    

Models - Include

Posts.findAll({
  include: [{ 
    model: Comments, as: 'comments',
    include: [{ model: Users, as: 'user' }]
  }]
}).then(function () { ... });
    

Models - Include

User.findAll({ 
  include: [{ 
    all: true, 
    nested: true 
  }]
});
    

Scopes

Scopes - Definition

const Posts = sequelize.define('Posts', {...}, {
  defaultScope: { where: { deleted: false } },

  scopes: {
    draft: { where: { draft: true } },
    published: { where: { draft: false } },
    publishedOn: function (date) {
      return {
        where: {
          draft: false,
          date: date
        }
      };
    }
  }
});
    

Scopes - Default

Posts.findAll();
    

SELECT * FROM Posts WHERE deleted = false
    

Scopes - Default

Posts.unscope().findAll();
Posts.scope().findAll();
    

SELECT * FROM Posts
    

Scopes - One

Posts.scope('draft').findAll();
    

SELECT * FROM Posts WHERE draft = true
    

Scopes - Multi

Posts.scope('published', 'defaultScope')
  .findAll();

Posts.scope(['published', 'defaultScope'])
  .findAll();
    

SELECT * FROM Posts WHERE draft = false
  AND deleted = false
    

Scopes - Reuse

let DraftPosts = Posts.scope('draft');

DraftPosts.findAll();

// later
DraftPosts.findAll();
    

Scopes - Methods

scopes: {
  publishedOn: function (date) {
    return {
      where: {
        draft: false,
        date: date
      }
    };
  },
  random: function () {
    return {
      where: {
        id: Math.random()
      }
    };
  }
}
    

Scopes - Methods

Posts.scope(

 'random', 
 { method: ['publishedOn', '14.09.2016'] }

).findAll();
    

SELECT * FROM Posts
  WHERE id = 42
    AND draft = false
    AND date = '14.09.2016'
    

Scopes - Merge

scope1: {
  where: {
    firstname: 'bob',
    age: { [Op.gt]: 20 }
  }
  limit: 2
},
scope2: {
  where: {
    age: { [Op.gt]: 30 }
  },
  limit: 10
}
    

Scopes - Merge

Users.scope('scope1', 'scope2').findAll()
    

SELECT * FROM Posts
  WHERE firstName = 'bob'
    AND age > 30 LIMIT 10
    

Scopes - Merge

Users.scope('scope1', 'scope2')
  .findAll({
    where: {
      firstname: 'tommy'
    }
  })
    

SELECT * FROM Posts
  WHERE firstName = 'tommy'
    AND age > 30 LIMIT 10
    

Scopes - Relations

Posts.hasMany(Comments, {
  foreignKey: 'commentable_id',
  scope: {
    commentable: 'post'
  }
});
    

Scopes - Relations

post.getComments()
    

WHERE commentable = 'post'
    

Scopes - Relations

user.getPosts()
    

WHERE deleted = false
    

user.getPosts({ scope: null })
user.getPosts({ scope: ['scope1', 'scope2'] })
    

Scopes - Relations

Users.hasMany(Posts);

Users.hasMany(Posts.scope('deleted'),
  { as: 'deletedPosts' });
    

// WHERE deleted = false
user.getPosts();

// WHERE deleted = true
user.getDeletedPosts();
    

Instances

Instances - Build

let post = Posts.build({
  title: 'my awesome post',
  date: new Date('2016-09-14')
});

post.isNewRecord // => true

post.save()
  .then(...)
  .catch(...);
    

Instances - Default value

const Posts = sequelize.define('Posts', {
  title: Sequelize.STRING,
  draft: {
    type: Sequelize.BOOLEAN,
    defaultValue: true
  }
});

let post = post.build({title: 'important post'});

post.title  // ==> 'important post'
post.draft // ==> true
    

Instances - Create

Post.create({ title: 'post', draft: false })
  .then(...)
  .catch(...);

Post.create(
    { title: 'post', draft: false },
    { fields: [ 'title' ] }
  )
  .then(...)
  .catch(...);

// => title: 'post', draft: true
    

Instances - Update

// 1
post.title = 'a very different title now'
post.save().then(..)

// 2
post.update({
  title: 'a very different title now'
}).then(...)
    

Instances - Update

// 1
post.title = 'a very different title now'
post.draft = false
post.save({ fields: [ 'title' ] }).then(..)

// 2
post.update({
  title: 'a very different title now',
  draft: false
}, { fields: [ 'title' ] }).then(...)
    

Instances - Delete

post.destroy().then(...)

post.destroy({ force: true }).then(...)
    

Instances - Bulk | Batch

Posts.bulkCreate([
    { title: 'post #1', draft: false },
    { title: 'post #2', draft: true },
    { title: 'post #3', draft: false },
  ], { validate: true })
  .then(function() {
    return User.findAll();
  })
  .then(...);
    

Instances - Bulk | Batch

Posts.update(
    { draft: true },
    { where: { title: { [Op.like]: '%Internet%' }}}
  )
  .spread(function(affectedCount, affectedRows) {
    ...
  });
    

Instances - Bulk | Batch

Posts.destroy(
    { where: { title: { [Op.like]: '%Internet%' }}}
  )
  .then(function(affectedCount) {
    ...
  });
    

Instances - Get & Set

post.get();
post.get({ plain: true });

post.set('title', 'post #9000');
post.changed('title'); // => true
post.save();
    

Instances - Reload

post.reload().then(...)
    

Instances - In(de)crement

Users.findById(1).then(function(user) {
  return user.increment({
    'my-integer-field':  2,
    'my-very-other-field': 3
  })
}).then(...)

Users.findById(1).then(function(user) {
  return user.decrement({
    'my-integer-field':  2,
    'my-very-other-field': 3
  })
}).then(...)

    

Instances - JSON

const Users = sequelize.define('Users', {
  id: { type: Sequelize.INTEGER,
    primaryKey: true },
  firstname: Sequelize.STRING(200),
  lastname: Sequelize.STRING(200),
  email: Sequelize.STRING(100)
}, {
  getterMethods: {
    fullname: function () {
      return
      `${this.firstname} ${this.lastname}`;
    }
  }
});
    

Instances - JSON

let user = Users.create({
  firstname: 'Nikita',
  lastname: 'Tsyhanenko',
  email: 'user@example.com'
});

user.toJson();

/* =>
{ fullname: 'Nikita Tsyhanenko',
  id: 1,
  firstname: 'Nikita',
  lastname: 'Tsyhanenko',
  email: 'user@example.com' }
*/
    

Relations / Associations

Relations - 1-to-1

const Users = sequelize.define('Users', {
  id: { type: Sequelize.INTEGER,
    primaryKey: true },
  email: Sequelize.STRING(100)
});

const Profiles = sequelize.define('Profiles', {
  id: { type: Sequelize.INTEGER,
    primaryKey: true },
  firstname: Sequelize.STRING(200),
  lastname: Sequelize.STRING(200),
});
    

Relations - 1-to-1

Users.hasOne(Profiles);
// => profile.UserId
// => profile.getUser()
// => profile.setUser()

Users.hasOne(Profiles);
Profiles.belongsTo(Users);
// => user.getProfile()
// => user.setProfile()
    

Relations - 1-to-1

Users.hasOne(Profiles, { foreignKey: 'userId' });
Profiles.belongsTo(Users);
// => profile.userId

Users.hasOne(Profiles, { as: 'pro' });
Profiles.belongsTo(Users);
// => user.getPro()
    

Relations - 1-to-Many

Users.hasMany(Posts);
Posts.belongsTo(Users);

/*
- setPosts
- getPosts
- addPost

- setUser
- getUser
*/
    

Relations - Many-to-Many

Users.belongsToMany(Roles,
  { through: UserRoles });

Roles.belongsToMany(Users,
  { through: UserRoles });
    

Relations - No constraints

Users.hasOne(Profiles, { foreignKey: 'profileId',
  contstraints: false });

Profiles.belongsTo(Users,
  { foreignKey: 'userId' });
    

Hooks

Hooks - Order

(1)
  beforeBulkCreate(instances, options, fn)
  beforeBulkDestroy(options, fn)
  beforeBulkUpdate(options, fn)
(2)
  beforeValidate(instance, options, fn)
(-)
  validate
(3)
  afterValidate(instance, options, fn)
  - or -
  validationFailed(instance, options, error, fn)
(4)
  beforeCreate(instance, options, fn)
  beforeDestroy(instance, options, fn)
  beforeUpdate(instance, options, fn)
    

Hooks - Order

  beforeSave(instance, options, fn)
  beforeUpsert(values, options, fn)
(-)
  create
  destroy
  update
(5)
  afterCreate(instance, options, fn)
  afterDestroy(instance, options, fn)
  afterUpdate(instance, options, fn)
  afterSave(instance, options, fn)
  afterUpsert(created, options, fn)
(6)
  afterBulkCreate(instances, options, fn)
  afterBulkDestroy(options, fn)
  afterBulkUpdate(options, fn)
    

Hooks - In Model

const Users = sequelize.define('Users', {...}, {
  hooks: {
    beforeValidate: function(user, options) {
      user.mood = 'happy'
    },
    afterValidate: function(user, options) {
      user.username = 'Toni'
    }
  }
});
    

Hooks - hook() Method

Users.hook('beforeValidate', (user, options) => {
  user.mood = 'happy';
});

Users.hook('afterValidate', (user, options) => {
  user.username = 'Toni';
});
    

Hooks - Direct Method

Users.beforeValidate((user, options) => {
  user.mood = 'happy';
});

Users.afterValidate((user, options) => {
  user.username = 'Toni';
});
    

Hooks - Global

const sequelize = new Sequelize(..., {
  define: {
    hooks: {
      beforeCreate: function () {
        // Do stuff
      }
    }
  }
});
    

Hooks - Bulk + Individual

Model.destroy({ where: {accessLevel: 0},
  individualHooks: true})
    

Transactions

Transactions - Managed

sequelize.transaction(function (t) {
  // chain all your queries
  return User.create({name:'Abraham Lincoln'},
    {transaction: t})
  .then(function (user) {
    return user.setShooter({name:'John Boothe'},
      {transaction: t});
  });
}).then(function (result) {
  // transaction has been committed
  // result - from promisses chain
}).catch(function (err) {
  // transaction has been rolled back
  // err - from promisses chain
});
    

Transactions - Unmanaged

return sequelize
  .transaction()
  .then(function (t) {
  return User.create({name:'Homer'},
      {transaction: t})
    .then(function (user) {
      return user.addSibling({name:'Bart'},
        {transaction: t});
    })
    .then(function () {
      return t.commit();
    }).catch(function (err) {
      return t.rollback();
    });
  });
    

Transactions - Isolation level

sequelize.transaction({
  isolationLevel:
    Sequelize.Transaction
      .ISOLATION_LEVELS.SERIALIZABLE
  }, function (t) {...});

// default = REPEATABLE READ
Sequelize.Transaction
  .ISOLATION_LEVELS.READ_UNCOMMITTED
Sequelize.Transaction
  .ISOLATION_LEVELS.READ_COMMITTED
Sequelize.Transaction
  .ISOLATION_LEVELS.REPEATABLE_READ
Sequelize.Transaction
  .ISOLATION_LEVELS.SERIALIZABLE
    

Legacy tables

Legacy tables

sequelize.define('user', {
  ...
}, {
  tableName: 'users'
});

sequelize.define('modelName', {
  userId: {
    type: Sequelize.INTEGER,
    field: 'user_id'
  }
});
    

Legacy tables

sequelize.define('collection', {
  uid: {
    type: Sequelize.INTEGER,
    primaryKey: true,
    autoIncrement: true
  }
});

User.hasMany(Role, {through: 'user_has_roles',
  foreignKey: 'user_role_user_id'});
Role.hasMany(User, {through: 'user_has_roles',
  foreignKey: 'roles_identifier'});
    

Raw queries

Raw queries - Metadata

sequelize.query("UPDATE users
    SET y = 42 WHERE x = 12")
  .spread(function(results, metadata) { ... })

sequelize.query("SELECT * FROM `users`",
    { type: sequelize.QueryTypes.SELECT})
  .then(function(users) { ... })
    

Raw queries - Replacements

sequelize.query('SELECT * FROM projects
  WHERE status = ?', { replacements: ['active'],
    type: sequelize.QueryTypes.SELECT }
).then(function(projects) { ... })

sequelize.query('SELECT * FROM projects
  WHERE status = :status', { replacements:
    { status: 'active' },
    type: sequelize.QueryTypes.SELECT }
).then(function(projects) { ... })
    

Migrations

Migrations - CLI

npm install -g sequelize-cli
    

Migrations

sequelize init
    

// connections info
./config/config.json
    

sequelize migration:create
    

./migrations/[date]_[name].js
    

Migrations - Up

return Promise.all([
  queryInterface.addColumn('Users', 'firstname',
    { type: Sequelize.STRING }),
  queryInterface.addColumn('Users', 'lastname',
    { type: Sequelize.STRING }),
  queryInterface.dropTable('Profiles')
]);
    

Migrations - Down

return Promise.all([
  queryInterface.removeColumn('Users','firstname'),
  queryInterface.removeColumn('Users','lastname'),
  queryInterface.createTable('Profiles', { ... })
]);
    

Migrations - Down

sequelize db:migrate
    

sequelize db:migrate:undo