Sequelize ORM

Sequelize is a promise-based ORM for Node.js supporting the PostgreSQL, MySQL MariaDB, SQLite and MSSQL relational databases.

A ORM (Object Relational Mapper) is a tool for mapping between programming language objects and relational dabase tables. It can simplify and streamline database operations. It also sits like an abstraction layer that should make it easy to change DB without altering code.

• A Database Abstraction Layer
• MySQL, SQLite, PostgreSQL, MS SQL Server
• Object Relational Mapper, ORM
• Transactions support
• API Reference

Resources

• Sequelize Course Repo
• Sequelize Docs
• Google Groups
• Node, Postgres, and Sequelize
• An Introduction to Sequelize.Js
• Sequelize, the JavaScript ORM, in practice
• Angular js app with PostgreSQL or MySQL using Sequelize js
• Node.js, Sequelize, PostgreSQL tutorial
• Mike Frey on Egghead
• How to use Associations in sequelize

const Car = sequelize.define('Car',{ make: Sequelize.String }); 

sequelize.sync()
  .then(() => Car.create({ make: 'Ford' })
  .then((myCar) => { console.log(myCar.get({ plain: true })); };

transactions

return sequelize.transaction((t) => { 
  Car.create({ make: 'Ford' }, { transaction: t }) 
    .then((myCar) => { 
       myCar.setOwner({}, { transaction: t }); 
    }); 
});

Derived Fields

fullname: {
  type: SEQUELIZE.STRING,
  get: function() {
    return `${this.getDataValue('firstName')} ${this.getDataValue('lastName')}`
  }
}

The CLI

To use the sequelize CLI you need to have it installed npm install --save sequelize-cli. I usually create a db folder for sequelize under the main project root and add the file .sequelizerc for defining paths. The documentation is outdated so use the command sequelize help.

module.exports = { 
  config: './config.json',
  'migrations-path': './migrations',
  'models-path': './models', 
  'seeders-path': './seeders' 
}

Then run the Sequelize CLI commands in the project\\\\db folder. To initialize a project run sequelize init. This will create the folders for models, seeders, migrations and the config.json file fo database configs. It will also create the models/index.js file that will import all models it finds in that folder and export a db object containing everything you need.

Remember to fix database configuration settings in config.json also.

{ 
  "development": { 
    "username": "root", 
    "password": null, 
    "database": "../todos_dev.sqlite", 
    "host": "127.0.0.1", 
    "dialect": "sqlite" 
  }, 
  "test": { 
    "username": "root", 
    "password": null, 
    "database": "../todos_test.sqlite", 
    "host": "127.0.0.1", 
    "dialect": "sqlite" 
  }, 
  "production": {
    "username": "Test", 
    "password": "Pass@word", 
    "database": "ToDos", 
    "host": "127.0.0.1", 
    "dialect": "mssql" 
  } 
}

UUID as primary key

id: Sequelize.UUID, primarykey: true, defaultValue: Sequelize.UUIDv4

Change Primary Key to use UUID

https://khalilstemmler.com/articles/auto-increment-or-uuid/

1. Create uuid field in all tables
2. Code for automatically setting uuid Model.beforeCreate(user => user.id = uuid());
3. Create foreign key fields for uuid
4. Run script to generate uuid for all records
5. run script to set uuid foreign keys
6. Create migration to replace id with uuid

id: { 
  type: Sequelize.UUID, 
  defaultValue: Sequelize.UUIDV4, 
  allowNull: false, 
  primaryKey: true 
} 

User.beforeCreate((user, _ ) => { return user.id = uuid(); }); 

module.exports = { 
  up: function(queryInterface, Sequelize) { 
    return queryInterface.createTable('Users', { 
      id: { allowNull: false, primaryKey: true, type: Sequelize.UUID }, 
      /* Other model properties */ 
      }); 
  }, 
  down: function(queryInterface, Sequelize) { 
    return queryInterface.dropTable('Users'); 
  } 
};

Sequelize MS-SQL Settings

dialectOptions.requestTimeOut: 0 -> to not have DB calls time out while debugging