26 September 2019

Create API using Express JS and Sequelize

Create API using Express JS and Sequelize

Introduction to ORM

  • Sequelize.js an ORM (Object/Relational Mapper) which provides easy access to MySQL, MariaDB, SQLite or PostgreSQL databases by mapping database entries to objects and vice versa. It has very powerful migrations mechanism that can transform existing database schema into a new version. It also provides database synchronization mechanisms that can create database structure by specifying the model structure.
  • Although Sequelize.js is a new library, it has been battle-tested. Currently, web sites like Shutterstock, Clevertech, Metamarkets, and Influence have announced that they are using Sequelize.js as ORM of their website.

Why do we need to learn Sequelize.js?

  • Since jsis build on the top of JavaScript. It has all the characteristics that JavaScript has. As web developers, we are familiar with JavaScript so it is very easy to understand the Node. Sand Sequelize.js.
  • Sequelize.js is a young ORM that has all features that any ORM has and open source developers are actively contributing it. Sequelize.js has good support for database synchronization, eager loading, associations, transactions, and migrations.
  • Another main advantage of Sequelize.js is it easy to test. We can use Mocha like frameworks to run tests. We can have two databases, one for tests and one to develop environments, as we will see in this article.

Getting Started with Sequelize

  • Sequelize is a promise-based ORM for Node.js. Sequelize is easy to learn and has dozens of cool features like synchronization, association, validation, etc. It also has support for PostgreSQL, MySQL, Maria DB, SQLite, and MSSQL.

Sequelize

Installation

Installation Packages :

  • Express
  • Mssql
  • Sequelize
  • Sequelize-cli

Sequelize is available via npm and Yarn

Using NPM

  • $ npm install –save Sequelize

# and one of the following:

  • $ npm install –save pg pg-hstore
  • $ npm install –save mysql2
  • $ npm install –save sqlite3
  • $ npm install –save tedious // MSSQL

Using Yarn

  • $ yarn add Sequelize

# and one of the following:

  • $ yarn add pg pg-hstore
  • $ yarn add mysql2
  • $ yarn add sqlite3
  • $ yarn add tedious // MSSQL

Sequelize Configuration for API

Make Sequelize js.

  • js file is made for the configuration setup of the database and global setup parameter of Sequelize.
  • Check Sequelize Authentication through authenticating () this method check if the database is connected or not.
  • And Inject in main app. as js file.

Make Models

  • Make one directory for models
  • Make one model in side models directory by define()
  • To Define mapping between a model and a table use the define method. Sequelize will then automatically add the attributes CreatedAt and UpdatedAt.

 Models Sync with database

  • Need to change in Sequalize.js with require models and  sync() method
  • User is the model name and sync is a method for update our database from model definition.

Sequelize initialization

  • Need to Confirm “Sequelize-cli” package installed
  • We go to our project root directory location and initialize sequelize by “sequelize init” command
  • “Sequelize init” command create migration directory and config directory with config.json (for database configuration setup for migraton).
  • also, create one index.js file in models folder.

Make Models by CLI.

  • sequelize model:generate –name User –attributes firstName:string,lastName:string,email:string
  • Note :
  • 1) Every table has also field id, Created At, Updated At auto-generate by sequelize.
  • 2) Also, auto-generate one migration file in the migration directory.

Models Sync with the database by CLI.

  • Sequelize db:migrate
  • This Command run migration file and sync/update database with changes.

Sequelize Configuration

Step 1 :

Need to install npm package  ‘sequelize’

Step 2 :  Create sequelize.js (For configuration of sequelize)

— Create Sequelize DB Connection like

Sequelize

Now we check connection is connected or not :

Sequelize

Step 3)  we create one directory for Models and create one model inside this directory (User.js)

Sequelize

Sequelize

Note : To define mappings between a model and a table, use the Define method(we have used in step3)

Step 4) now we have sync model to the database so we need a change in sequalize.js we have created in step2.

Sequelize

Note : {force: true} is for force fully database update , so if we can define true then delete existing table from database and create new.

Step 3 ) and 4 By Sequalize Auto model creation by command

  • First we need to install NPM package “sequalize-cli”
  • Then after one fire command “Sequalize init” for the initialize sequalize in our project. ( Auto Create two folder Migrations,config with config.json (dbconnection), and also create one index.js file in models folder.
  • Genearte Model using command line :

Sequelize

  • Above command Generate model only our code side.
  • update Changes in database we have to fire below command :

Sequelize db:migrate

Sequelize

Commands of Migration file :

SequelizeStep 5) Now we create api

First, we require a model for api.

Sequelize

Then we create api.

  • For Get api all Data : ModelName.findAll()
  • For Get api specific data: ModelName.findone(Options)

Sequelize

  • For post : Modelname.Create(req.body,options)
  • Fou Update : Modelname.Update(req.body,Options)
  • For Delete : Modelname.destroy(options)

Sequelize

Sequelize

Sequelize

Make API with Sequelize

  • Require model in our api file
  • Get :
    • findAll()
  • Get ById :
    • findone(Options)
var options = {

where: {

id: req.params.id

}

}
  • Post :
    • Create(req.body,options)
  • Update :
    • Update(req.body,Options)
  • Delete :
    • destroy(options)

Data Retrieval/Finders

Sequelize

Back to Index