kiran

Senior Full Stack

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.


Tags

More by kiran

Topics of interest