VuePress Final Report

# Sequelize

# What is Sequelize?

Sequelize is a promise-based Node.js ORM for Postgres, MySQL and MariaDB. It is supported by Node v10 applications, and the latest version at the moment is Sequelize v6, which is the reference version used to build this documentation. In order to understand Sequelize better, it is important to know its pros and cons:

Pros:

  • Widely known ORM for Nodejs
  • Easy setup
  • Support MySQL, MariaDB, PostgreSQL, MSSQL, SQLite, etc.
  • Open source
  • Free
  • Promise based
  • Conscious about the existence of the most commonly used npm modules

Cons:

  • The documentation is not clear for beginners, and there are a lot of changes between versions, which means it is more difficult to maintain and enhance the platform for adding new features
  • Relations can be confusing

# How to use Sequelize

Sequelize can be installed as an npm package using the following syntax:

npm install --save sequelize
1

Notice that the command above will install the latest stable version of Sequelize into the application. The --save is recommended but not mandatory

A database driver has to be installed in addition to the Sequelize package. Almost all database drivers are fully compatible with the module.

# Connecting to a database

Once the database is running, a Sequelize instance has to be created, adding to it the information of the database to create the connection:

const { Sequelize } = require('sequelize');

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

Check the Sequelize API to see other available syntaxes and properties

In order to test the connection, the .authenticate() function can be used:

try {
  await sequelize.authenticate();
  console.log('Connection has been established successfully.');
} catch (error) {
  console.error('Unable to connect to the database:', error);
}
1
2
3
4
5
6

Most of the methods provided by Sequelize are asynchronous and return Promises, so then or catch can be used

# Models

A model in Sequelize is an abstraction of a database table. It is represented in Sequelize with a class that extends Model.

The model has different properties that allows Sequelize to know the name of the table, columns and their datatypes of the database table. Each model has a name, which, by convention, is usually the name of the database table but in singular, but it is not a mandatory requirement.

Sequelize will connect the model with the database table by pluralizing the name of the model automatically using an internal library, but this feature can be customized ((See documentation)[https://sequelize.org/master/])

In order to define a model manually, it can be done in two main ways:

  1. Calling sequelize.define(name, attributes, options)
  2. Extending Model and calling init(attributes, options)

Examples of both methods are available in the official documentation site

# Model synchronization

This is an important aspect to know about models. Once a model is defined, it is expected to match with an existent database table, but the table could not exist in the database, or have differences with the model in the number, name or datatype of the columns. The .sync(options) method can be used to automatically synchronize the actual model with the database table performing a SQL query. This method will change only the database table, not the model.

The method sequelize.sync() can be used to synchronize all models at the same time

# Timestamps

Sequelize will automatically create two more fields for each model: createdAt and updatedAt, both with the DATE datatype. This fields do not need to be created in the database, since Sequelize will update and manage them.

This behavior can be modified using the timestamp: false option when defining the Sequelize instance

# Useful options

There are a lot of different options to customize the models, but here is a short list of the most useful ones:

  • Default values for columns
  • Create Model Instances to use models as Data Access Objects
  • Getters & Setters for models

There are of course more elements to customize and manage the Sequelize package, and some of the previous are going to be explained in following examples throughout this section

# Sequelize CLI

The Sequelize Command Line Interface allows to create and manage different core elements of Sequelize.

For beginners, it is recommended the use of Sequelize CLI since the package will automatically create the basic configuration and correct folder structure for the project.

In order to install Sequelize CLI, Sequelize package has to be already installed in the project:

npm install --save-dev sequelize-cli
1

# Sequelize CLI commands

# Init

The init command will create the necessary folders and files for adding Sequelize to a Node.js project

npx sequelize-cli init
1

The configuration file created behind the config folder assumes MySQL by default. In case you are using another driver, you need to change the dialect option

# Create

If there is no database created yet, the following command can be used to create a database starting from the configuration generated with the previous command

npx sequelize-cli db:create
1

# Generate

npx sequelize-cli model:generate ModelName --attributes attrName1:string [, ...]
1

Executing the code above, a new model with the name ModelName and the attribute attrName1 will be created. A new migration will be created also, which name starting with a sequence of numbers representing a timestamp.

# Migrations

Migrations are used to keep track of the changes to the database. With the generated migrations the state of the database can be changed between versions. The Sequelize CLI is mandatory to use the migrations. They have two functions: up and down, to indicate how to perform and undo the migration.

At least one migration is created for each model, but it is possible (and usual) to have more than one migration for each model

The following command will run a migration, which means that Sequelize will create a table with the columns as specified in the migration file.

npx sequelize-cli db:migrate
1

# Seeders

Seeders are used to insert data into a database tables by default. It is used in development environments to test functionalities.

npx sequelize-cli seed:generate --name demo-myModel
1

This command will create a seed behind the seeders folder, using a sequence of numbers in the name as the migrations files. This file could be modified to insert the demo data on it

This command will run all whe seeders:

npx sequelize-cli db:seed:all
1

# Useful Sequelize-CLI commands

There are different commands that can be useful for version management, testing and configuration. Some of the following commands facilitates the deploy and charge of the different Sequelize modules:

  • Run pending migrations npx sequelize-cli db:migrate
  • Reverts a migration npx sequelize-cli db:migrate:undo
  • Run specified seeder npx sequelize-cli db:seed

# Alternatives

As it is shown above, Sequelize is one of the most powerful Node ORMs, especially complementing it with Sequelize-CLI, but there are other tools that can be also interesting to know about. This section aims to point what are the key differences between them and if it is worth to use other tools depending on the application requirements.

# TypeORM

TypeORM is an ORM that can run in Node and other frameworks, which support Active Record and Data Mapper patterns, unlike all other JavaScript ORMs currently in existence. This means you can write high quality, scalable and maintainable applications in the most productive way. Some of the key features of TypeORM are:

Pros:

  • Support for TypeScript
  • Supports MySQL, PostgreSQL, MariaDB, SQLite, etc.
  • Easy setup
  • Works in Nodejs and browser, among others
  • Promise based

Cons:

  • The creator abandoned the project, which means it is supported only by the community
  • Complex for what it offers
  • Does not support native JavaScript
  • Can not use query on any relation

# Prisma

Prisma is a new kind of ORM that mitigates some problems of traditional ORMs, such as bloated model instances, mixing business with storage logic, lack of type-safety or unpredictable queries caused by lazy loading.

It uses its own schema (called Prisma schema) to define models in a declarative way. Then allows to generate SQL migrations from the schema and executes them against the database. Prisma developers have planned also the inclusion of non-relational databases in the future, so it can be a good tool if you are looking for a scalable solution that can be used in different projects.

Pros:

  • Developer friendly
  • Scalable
  • Works especially well in collaborative environments
  • Type-safety
  • Open source

Cons:

  • Lack of control over all database queries
  • It needs to be coded manually (Does not have a CLI tool to build the basic configuration)
  • Does not allow raw queries (SQL)

# NoSQL Alternative: Mongoose

This comparative is a bit unfair, since the previous alternatives could be used without changing the way the data is accessed, but if the project is in its initial phases, it is important to spend time thinking about if it is actually necessary to use a SQL-based database or, on the other hand, the project requirements can fit with the inclusion of a non-relational database.

The most common database of this kind is MongoDB, and pure MongoDB is usually replaced by the Mongoose middleware, which is defined as an ORM designed for non-relational databases which wants to become a bridge between the traditional SQL users and the benefits of the use of NoSQL in determined projects. It offers a schema-based solution to model application data, including built-in type casting and validation, query building, business logic hooks and allows to write MongoDB code in a more traditional way.

Pros:

  • Easy setup
  • NoSQL beginner friendly structure
  • Light and powerful
  • Secure
  • Clear separation between the logic and the rest of the application

Cons:

  • Less consistent than pure MongoDB
  • Has the same problems or inconsistences as all non-relational models
  • Not all data operations will fit in a model structure

← THUMFI →