Sequelize

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: }); //SELECT id, name ... Model.findAll({ attributes: ] }); // 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