What is Sequelize?

Sequelize is a javascript library used in NodeJS as ORM to access relational database schemas as objects.  The library is written entirely in Javascript to be used in NodeJS environment.

 

Features

  1. Sequelize is used in NodeJS and is available via npm.
  2. Sequelize is an ORM (Object Relational Mapper)
  3. It provides easy access to various databases like MySQL, MariaDB, SQLite, or PostgreSQL.
  4. It has good support for database synchronization, eager loading, associations, transactions and migrations.
  5. It is easy to test using frameworks like Mocha
  6. Its powerful migration mechanism helps transforming existing database schema into a new version and Its database synchronization mechanism helps in creating database structure by specifying the model structure.

Pre-requisites

  1. Node/Express already installed/configured

Install and Configure

Sequelize/Sequelize-cli is available via npm.

  1. To install sequelize/sequelize-cli

In root directory

$ npm install –save sequelize

$ npm install –save-dev sequelize-cli

To install the CLI globally

$ npm install -g angular-cli

 

  1. To configure Sequelize-cli
  2. The Sequelize CLI helps to run the sequelize commands on the terminal directly.
  3. As with any npm package, you can use the global flag (-g) to install the CLI globally. If you have installed the CLI without the global flag, use node_modules/.bin/sequelize (command), instead of sequelize (command).
  4. The Sequelize CLI allows you to set up and run migrations directly from your terminal. However, it uses a default file structure which may not work for all applications. To configure, create a file called .sequelizerc in the root directory and save the following with correct information:

 

var path = require(‘path’);

module.exports = {

‘config’: path.resolve(‘path/to/folder’, ‘config/config.json’),

‘migrations-path’: path.resolve(‘path/to/folder’, ‘migrations’),

‘models-path’: path.resolve(‘path/to/folder, ‘models’)

}

 

The CLI currently supports the following commands:

$ sequelize db:migrate        # Run pending migrations.

$ sequelize db:migrate:undo   # Revert the last migration run.

$ sequelize help              # Display this help text.

$ sequelize init              # Initializes the project.

$ sequelize migration:create  # Generates a new migration file.

$ sequelize version           # Prints the version number.

 

More information about the available commands can be obtained via the help command:

$ sequelize help:init

$ sequelize help:db:migrate

$ sequelize help:db:migrate:undo

 

Ref : https://github.com/sequelize/cli

 

  1. To setup Sequelize Connection (with DB)

Sequelize setup a connection between the rest api/application and your SQL database. In config.json/config.js file, define the general constraints needed to setup the connection with database:

 

{

“development”: {

“username”: “db_username”,

“password”: “db_password”,

“database”: “db_dev”,

“host”: “localhost”,

“dialect”: “mysql”

},

“test”: {

“username”: “db_username”,

“password”: “db_password”,

“database”: “db_test”,

“host”: “127.0.0.1”,

“dialect”: “mysql”

},

“production”: {

“username”: “db_username”,

“password”: “db_password”,

“database”: “db_prod”,

“host”: “127.0.0.1”,

“dialect”: “mysql”

}

}

 

Now create the connection in an index.js file (in models/ directory). This file can be auto generated with the sequelize CLI and collects all the models from the models directory and associates them if needed.

  1. To connect DB using tool

Use Sqliteman tool to connect

 

  1. To Initialize sequelize

$ sequelize init

This will create a config file and folders for migrations and models at the locations specified in the .sequelizerc file

This will also generate a few files for you, your project folder should now look like this:

 

├── config

│   └── config.json

├── migrations

├── models

│   └── index.js

└── package.json

 

Migration

Create a Model/Migration file

$ sequelize model:create –name ModelName –attributes “name:string, text:text, url:string”

 

  1. Feel free to name the migration whatever you like: sequelize-cli will automatically create a migration file with whatever name you choose, prepended with a timestamp. You can then open up that file and update it as you like.
  2. You can check out the inline docs for this command with sequelize help:model:create

 

Run the migration

$ sequelize db:migrate

 

Add Column to Existing Model

  1. Need to use queryInterface object
  2. You need another migration file for adding the column. In the up function you need to add

 

queryInterface.addColumn (

‘nameOfAnExistingTable’,

‘nameofTheNewAttribute’,

Sequelize.STRING)

 

 

In the down function you need to

queryInterface.removeColumn (

‘nameOfAnExistingTable’,

‘nameOfTheAttribute’)

 

Now run the migration.

$ sequelize db:migrate

 

We can run pending migration

$ sequelize -m

 

Undo last migration

$ sequelize -u

 

Seeding the database

$ sequelize seed:create –name seed-file-name   // Running this command will result in a file in seeders directory with code

** it’s important to always have both up and down methods in your seed script.

You can seed your database with this data by running this sequelize-cli command:

$ sequelize db:seed:all

** After this command, and check your database. Your tables will have records now.

 

More details on

  1. model: http://sequelizejs.com/docs/latest/models
  2. Data insertion: http://sequelizejs.com/docs/latest/instances
  3. Migration: http://sequelizejs.com/docs/latest/migrations

 

Implementation (To Use)

  1. To check connection:

You can check the database connection string by running following code.

// Get dependencies

var app = require(‘express’)();

var Sequelize = require(‘sequelize’);

// Sequelize initialization

var sequelize = new Sequelize(“postgres://username:password@localhost:5432/dbname”);

// Check database connection

sequelize.authenticate().complete(function(err) {

if (err) {

console.log(‘Unable to connect to the database:’, err);

} else {

console.log(‘Connection has been established successfully.’);

}

});

// Initializing a port

app.listen(3000);

 

  1. Data insertion

There are in build functions to data insert and data retrieval. We can use create function to insert data into database.

 

// Get dependencies

var app = require(‘express’)();

var Sequelize = require(‘sequelize’);

 

// Sequelize initialization

var sequelize = new Sequelize(“postgres://username:password@localhost:5432/dbname”);

 

// Model definition

var User = sequelize.define(“User”, {

username: Sequelize.STRING,

password: Sequelize.STRING

});

 

// Sync the model with the database

sequelize.sync({ force: true }).success(function(err) {

// insert new user

User.create({

username: “linda”,

password: “password “;

}).success(function(user) {

// you can now access the newly created user via the variable user

console.log(user);

});

});

 

// Initializing a port

app.listen(3000);

 

  1. Data retrieval

User.create({

username: “linda”,

password: “password”

}).success(function() {

User.find({

where: { username: ‘linda’ }

}).success(function(linda) {

console.log(‘Hello ‘ + linda.username + ‘!’);

console.log(‘All attributes of linda:’, linda.values);

});

});

 

 

 

Querying

Model.findAll({

attributes: [‘id’, ‘name’]

});

//SELECT id, name …

 

Model.findAll({

attributes: [‘id’, [‘name’, ‘fullname’]]

});

// SELECT id, name AS fullname …

 

Model.findAll({

where: {

Id: 10,

status: ‘active’

}

});

// SELECT * FROM model WHERE Id = 10 AND status = ‘active’;

 

Limiting

Model.findAll({ offset: 5, limit: 5 })

Skip 5 records and fetch the 5 after that

 

Sort sequelize.js query (Ordering)

Model.findAll({ limit: 10, order: ‘”updatedAt” DESC’ })

 

Associations

Model.findAll({

include: [{

model: Task,

where: { state: Sequelize.col(model.state’) }

}]

})

// Find all records with a least one task where task.state === model.state

 

Raw queries

sequelize.query(“SELECT * FROM `users`”, { type: sequelize.QueryTypes.SELECT})

.then(users => {

// We don’t need spread here, since only the results will be returned for select queries

})

 

Ref for Query: http://docs.sequelizejs.com/manual/tutorial/querying.html