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 sequelizeYou’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 ServerSetting 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.poolconst 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.defineModel.initThe above code tells Sequelize to expect a table named users in the database with the fields
firstNamelastNamefreezeTableNameSequelize also defines by default the fields id (primary key),
createdAtupdatedAtIf you want Sequelize to automatically create the table (or modify it as needed) according to your model definition, you can use the
sync// 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.
