Use Sequelize ORM with Node JS Project

What is Sequelize

Sequelize is a promise-based Node.js ORM for Postgres, MySQL, MariaDB, SQLite and Microsoft SQL Server. It features solid transaction support, relations, eager and lazy loading, read replication and more.

Installation

Sequelize is available via npm (or yarn).

npm install --save sequelize

You’ll also have to manually install the driver for your database of choice:

# One of the following:
$ npm install --save pg pg-hstore # Postgres
$ npm install --save mysql2
$ npm install --save mariadb
$ npm install --save sqlite3
$ npm install --save tedious # Microsoft SQL Server

Setting up Connection

To connect to the database, you must create a Sequelize instance. This can be done by either passing the connection parameters separately to the Sequelize constructor or by passing a single connection URI:

const Sequelize = require('sequelize');

const sequelize = new Sequelize('database', 'username', 'password', {
  host: 'localhost',
  dialect: /* one of 'mysql' | 'mariadb' | 'postgres' | 'mssql' */
});

If you’re connecting to the database from a single process, you should create only one Sequelize instance. Sequelize will set up a connection pool on initialization.

This connection pool can be configured through the constructor’s options parameter (using

options.pool

), as is shown in the following example:

const sequelize = new Sequelize(/* ... */, {
  // ...
  pool: {
    max: 5,
    min: 0,
    acquire: 30000,
    idle: 10000
  }

Modeling Tables

const User = sequelize.define('user', {
  // attributes
  firstName: {
    type: Sequelize.STRING,
    allowNull: false
  },
  lastName: {
    type: Sequelize.STRING
    // allowNull defaults to true
  }
}, {
  // options
});

Internally,

sequelize.define

calls

Model.init

The above code tells Sequelize to expect a table named users in the database with the fields

firstName

and

lastName

. The table name is automatically pluralized by default (a library called inflection is used under the hood to do this). This behavior can be stopped for a specific model by using the

freezeTableName

: true option, or for all models by using the define option from the Sequelize constructor.

Sequelize also defines by default the fields id (primary key),

createdAt

and

updatedAt

to every model.

If you want Sequelize to automatically create the table (or modify it as needed) according to your model definition, you can use the

sync

method, as follows:

// Note: using `force: true` will drop the table if it already exists
User.sync({ force: true }).then(() => {
  // Now the `users` table in the database corresponds to the model definition
  return User.create({
    firstName: 'John',
    lastName: 'Hancock'
  });
});

Querying in Sequelize

A few simple queries are shown below:

// Find all users
User.findAll().then(users => {
  console.log("All users:", JSON.stringify(users, null, 4));
});
// Create a new user
User.create({ firstName: "Jane", lastName: "Doe" }).then(jane => {
  console.log("Jane's auto-generated ID:", jane.id);
});
// Delete everyone named "Jane"
User.destroy({
  where: {
    firstName: "Jane"
  }
}).then(() => {
  console.log("Done");
});
// Change everyone without a last name to "Doe"
User.update({ lastName: "Doe" }, {
  where: {
    lastName: null
  }
}).then(() => {
  console.log("Done");
});

Datatypes in Sequelize

Below are some of the datatypes supported by sequelize.

Sequelize.STRING                      // VARCHAR(255)
Sequelize.STRING(1234)                // VARCHAR(1234)
Sequelize.STRING.BINARY               // VARCHAR BINARY
Sequelize.TEXT                        // TEXT
Sequelize.TEXT('tiny')                // TINYTEXT
Sequelize.CITEXT                      // CITEXT      PostgreSQL and SQLite only.
Sequelize.INTEGER                     // INTEGER
Sequelize.BIGINT                      // BIGINT
Sequelize.BIGINT(11)                  // BIGINT(11)
Sequelize.FLOAT                       // FLOAT
Sequelize.FLOAT(11)                   // FLOAT(11)
Sequelize.FLOAT(11, 10)               // FLOAT(11,10)

Raw Queries in Sequelize

As there are often use cases in which it is just easier to execute raw / already prepared SQL queries, you can use the function sequelize.query.

By default the function will return two arguments — a results array, and an object containing metadata (affected rows etc.).

Note that since this is a raw query, the metadata (property names etc.) is dialect specific. Some dialects return the metadata “within” the results object (as properties on an array).

However, two arguments will always be returned, but for MSSQL and MySQL it will be two references to the same object.

sequelize.query("UPDATE users SET y = 42 WHERE x = 12").then(([results, metadata]) => {
  // Results will be an empty array and metadata will contain the number of affected rows.
})

In cases where you don’t need to access the metadata you can pass in a query type to tell sequelize how to format the results.

For example, for a simple select query you could do:

sequelize.query("UPDATE users SET y = 42 WHERE x = 12").then(([results, metadata]) => {
  // Results will be an empty array and metadata will contain the number of affected rows.
})

Please refer https://sequelize.org for more details.