Sequelize‎ CLI: A comprehensive tutorial to get started

Sequelize CLI (Command Line Interface) is a user-friendly ORM (Object Relational Mapping) tool for Node.js, compatible with SQL databases. It offers numerous features such as support for multiple dialects, transaction management, data validation, and model associations.

Using Sequelize allows you to work more effectively on your Node.js projects while harnessing the capabilities of SQL databases. By following the instructions in this article, you will be on your way to mastering Sequelize CLI.

You’ll learn how to use Sequelize to interact with your database using JavaScript, including creating a Postgres database, generating models, running migrations, and seeding files from your terminal in MacOS or Linux.

Installation

To create a project that uses Postgres and Sequelize, you need to start by creating a new directory. Use the "mkdir" command followed by the desired folder name. In this example, we'll name the directory "sequelize-project".

To begin setting up a project that integrates Postgres with Sequelize and the useful 'Sequelize CLI' tools, you should create a new directory to house your project. You can do this by executing the 'mkdir' command with any appropriate name, which in our case will be "sequelize-project".

Here’s a code snippet demonstrating how to install Postgres, Sequelize, and the Sequelize CLI in a directory called "sequelize-project":

    

            Copy
    
// Create a new directory called "sequelize-project"
mkdir sequelize-project

// Navigate into the newly created directory
cd sequelize-project

// Initialize a new Node.js project with default settings
npm init -y

// Install the "sequelize" and "pg" packages
npm install sequelize pg

// Install the Sequelize CLI as a development dependency
npm install --save-dev sequelize-cli

Initialisation of the Project

Follow these steps to create a new Sequelize project and open the project directory in your code editor.

    

            Copy
    
// Initialize a new Sequelize project using the CLI
npx sequelize-cli init

// Open the project directory in your code editor
code .

By running npx sequelize-cli init, you'll create a basic project structure for your Sequelize application, including a config file, a models directory, and a migrations directory. This command will also create a .sequelizerc file, which allows you to configure certain aspects of Sequelize's behavior.

After initializing the Sequelize project using the CLI, the resulting project structure will include several subdirectories: config, migrations, models, and seeders. The config directory contains configuration files that allow your project to work with the Postgres database.

In order to set up your project for Postgres, you can access the config/config.json file and substitute the current code with the code provided below:

    

            Copy
    
{
"development": {
"database": "my_sequelize_project_development", // Database name for development environment
"host": "127.0.0.3", // Localhost IP address
"dialect": "postgres" // Using PostgreSQL dialect
},
"test": {
"database": "my_sequelize_project_test", // Database name for test environment
"host": "127.0.0.3", // Localhost IP address
"dialect": "postgres" // Using PostgreSQL dialect
},
"production": {
"database": "my_sequelize_project_production", // Database name for production environment
"host": "127.0.0.3", // Localhost IP address
"dialect": "postgres" // Using PostgreSQL dialect
}
}

Take note of this piece of code - it entails a categorization into three discrete environments, denoted by their respective names: development,  test, and production. The significance of each environment lies in its association with a specific Postgres database as well as setting out its individual host particulars along with dialects.

An important featured here is that all databases' naming conventions include the prefix "my_sequelize_project_". By customizing your project settings correspondingly you permit a troubleshoot-free protocol for interaction between it and your designated repository while using both Sequelize and its relevant CLI.

Creation of a Postgres database

The command "npx sequelize-cli db:create" is used to create a new database in a Sequelize-based Node.js application.

    

            Copy
    
npx sequelize-cli db:create

What is a Sequelize CLI model

The Sequelize CLI model refers to the file created by the Sequelize Command-Line Interface (CLI) that symbolizes a database table and outlines its structure within a Sequelize project. In Sequelize, models are JavaScript classes that represent database tables. They define the structure, relationships, and behavior of the respective tables.

Creation of a new Sequelize CLI model

To create a new model called "User" in a Sequelize-based Node.js application, we can use the command.

    

            Copy
    
npx sequelize-cli model:generate --name User --attributes 
firstName: string, lastName: string, email: string, password: string

This command generates a model file and a migration file based on the provided information.

The generated model file for "User" is located in the "sequelize-project/models/user.js" directory and contains the code to organize and interact with the User data in the database.

    

            Copy
    
'use strict';

module.exports = (sequelize, DataTypes) => {
  // Define a User model with the given attributes using Sequelize
  const User = sequelize.define('User', {
    firstName: DataTypes.STRING,  // Define a column for the first name of the user
    lastName: DataTypes.STRING,  // Define a column for the last name of the user
    email: DataTypes.STRING,  // Define a column for the email of the user
    password: DataTypes.STRING,  // Define a column for the password of the user
  }, {});

  // Define associations between this User model and other models
  User.associate = function(models) {
    // Associations can be defined here
  };

  // Return the User model
  return User;
};

Sequelize can be a powerful tool when utilized correctly. Here we have a code block that demonstrates how to create a User-model in a very clear way with four specified attributes: firstName, lastName, email and password; each attribute being given its own STRING data type.

To fully establish said Model through definition it leverages the use of sequelize.define(). Two inputs need to be provided when implementing this function - which ends up being the name chosen for our new model followed by an object containing those specific details pertaining to the models attributes & any additional details.

The associate() method is left empty for now, but this is where we can define any relationships between the User model and other models in the application.

Overall, this model file will help organize and interact with the User data in the database via the directory path as sequelize-project/migrations/20190914184520-create-user.js:

The Sequelize CLI generates migration files with timestamps based on the current time and date. As a result, the timestamp in the file name, such as "20190914184520", will be different each time a new migration is created.

    

            Copy
    
'use strict';

module.exports = {
  // Define the migration for creating the Users table
  up: (queryInterface, Sequelize) => {
    // Create the Users table with columns for id, firstName, lastName, email, password, createdAt, and updatedAt
    return queryInterface.createTable('Users', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      firstName: {
        type: Sequelize.STRING
      },
      lastName: {
        type: Sequelize.STRING
      },
      email: {
        type: Sequelize.STRING
      },
      password: {
        type: Sequelize.STRING
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });
  },

  // Define the migration for dropping the Users table
  down: (queryInterface, Sequelize) => {
    // Drop the Users table
    return queryInterface.dropTable('Users');
  }
};

• The above code is written in strict mode to enforce cleaner and safer JavaScript code. It exports an object as a module.
• The object contains two properties: up and down.
• The up property represents the migration for creating the Users table in the database.
• It takes two parameters: queryInterface and Sequelize.
• Inside the up function, queryInterface.createTable is called to create the Users table.
• To hold data about registered individuals, the Users table features multiple columns. These columns are variously tagged as id, firstName, lastName, email, password and two datetime entries - createdAt and updatedAt.
• Each column is defined with its data type using Sequelize.
• The function returns a promise that represents the creation of the table.
• The down property represents the migration for dropping the Users table from the database.
• It takes two parameters: queryInterface and Sequelize.
• Inside the down function, queryInterface.dropTable is called to drop the Users table.
• The function returns a promise that represents the dropping of the table.
• The module.exports statement makes the object with the up and down properties available for other modules to import and use.

Sequelize CLI migration of database

Sequelize CLI migration involves using the Sequelize Command-Line Interface (CLI) to handle and apply database migrations within a Sequelize project. To execute a Sequelize CLI migration and create a Users table with the necessary columns in your Postgres database, follow these steps and run the following command:

    

            Copy
    
npx sequelize-cli db:migrate

Next, we'll create a seed file using the following command:

    

            Copy
    
npx sequelize-cli seed:generate --name user

In the generated seed file, you can replace the temporary data with actual data you want to use to describe the code.

Below is an example of how we can insert a single JavaScript object with a key for each attribute described in our User model, along with createdAt and updatedAt timestamps:

    

            Copy
    
module.exports = {
  // Define the migration for inserting sample data into the Users table
  up: async (queryInterface, Sequelize) => {
    // Insert a sample user into the Users table
    await queryInterface.bulkInsert('Users', [
      {
        firstName: 'Anom',
        lastName: 'Warbhuvan',
        email: 'anomwarbhuvan91.com',
        password: 'xyz321',
        createdAt: new Date(),
        updatedAt: new Date()
      }
    ]);
  },

  // Define the migration for deleting all data from the Users table
  down: async (queryInterface, Sequelize) => {
    // Delete all rows from the Users table
    await queryInterface.bulkDelete('Users', null, {});
  }
};

In the above code snippet, up is the function that inserts the data into the Users table, while down is the function that removes the data. We use queryInterface.bulkInsert to insert the data and queryInterface.bulkDelete to remove it. The createdAt and updatedAt fields are set to the current date and time using the new Date() function.

After creating the seed file, we can execute it using the following command:

    

            Copy
    
npx sequelize-cli db:seed:all

This will insert the data we specified in the seed file into the Users table in our Postgres database.

To check if the data has been successfully inserted, we can drop into psql and query the database by running the following commands:

    

            Copy
    
npx sequelize-cli seed:generate --name user

The first command opens the psql shell and connects to our development database. The second command selects all rows from the Users table.

If the data has been inserted correctly, we ‎‎should see a sin‎gle row show‎ing the information we included in our seed file, such as:

Sequelize CLI Typescript

Sequelize CLI with TypeScript combines the robust capabilities of an Object-Relational Mapping (ORM) library with the advantages of TypeScript's static typing. This powerful combination enables developers to build scalable and maintainable applications efficiently. The Sequelize CLI integrates seamlessly with TypeScript, allowing you to define models, establish associations, and perform database operations with the support of TypeScript's strong typing system.

Using Sequelize CLI and TypeScript together, you benefit from enhanced code readability, autocompletion, and type safety, which minimizes errors and boosts productivity. This synergy simplifies the development process, making it easier to create sophisticated, database-driven applications while ensuring high code quality and maintainability.

Conclusion

Sequelize CLI is a powerful tool for managing databases in Node.js applications. It allows you to easily create models, migrations, and seed files, and execute them with simple commands, saving significant time and effort compared to writing database-related code from scratch.

By following the steps outlined in this topic, you can get started with Sequelize CLI to set up a basic database with a Users table, insert data into the table, and query the data to ensure it has been successfully inserted. As you become more familiar with Sequelize CLI, you can build more complex database structures and perform advanced operations with ease.

Set up Zipy and start tracking Sequelize CLI errors:

1. Visit https://app.zipy.ai/sign-up to get the project key.
2. Install Zipy via script tag or npm. window.zipy.init() must be called client-side, not server-side.

Script Tag:

    

            Copy
    
// Add to your HTML:<script src="https://cdn.zipy.ai/sdk/v1.0/zipy.min.umd.js" crossorigin="anonymous"></script><script> window.zipy && window.zipy.init('Project SDK Key');</script>

NPM:

    

            Copy
    
npm i --save zipyai

    

            Copy
    
//Code:import zipy from 'zipyai'; zipy.init('Project SDK Key');

‍