Skip to main content

How to edit data base structure in Sequelize (MYSQL)



Sequelize provides a powerful migration system that allows you to carefully manage database
 schema changes. Migrations ensure that you can apply specific changes to the
 database without risking data loss.

Steps to Set Up Migrations:

  • Install Sequelize CLI
      
    • npm install --save-dev sequelize-cli
  • Initialize Sequelize CLI:
    npx sequelize-cli init
    
 
    
These about two commands only will be run once while creating database or when we need to 
change structure of any table structure 

once ii is done  then for each changes we have to make migration file and have to run it. 

it may create config.json file in the  config/config/json 


    {
      "development": {
        "username": "root",
        "password": "Birla@123456",
        "database": "mhs",
        "host": "82.112.XXX.XX", // use you server's IP 
        "dialect": "mysql",
        "port": 3306
      },
      "test": {
        "username": "root",
        "password": "password",
        "database": "database_test",
        "host": "127.0.0.1",
        "dialect": "mysql"
      },
      "production": {
        "username": "root",
        "password": "password",
        "database": "database_production",
        "host": "127.0.0.1",
        "dialect": "mysql"
      }
    }

now if you have to add new column in table   

·  Create a migration for the new column (kil_col):

 

npx sequelize-cli migration:generate --name add-kil-col

 

This will generate a new migration file where you can specify the column additions.

·  Inside the generated migration file, write the migration logic:

 'use strict';


/** @type {import('sequelize-cli').Migration} */
module.exports = {
  async up (queryInterface, Sequelize) {
    // Add the 'kil_col' column to the 'tbl_members' table
    await queryInterface.addColumn('tbl_members', 'kil_col', {
      type: Sequelize.STRING(255),
      allowNull: false,
      defaultValue: 'kil_colkil_col', // Default value
    });
  },

  async down (queryInterface, Sequelize) {
    // Remove the 'kil_col' column from the 'tbl_members' table
    await queryInterface.removeColumn('tbl_members', 'kil_col');
  }
};

 

·  Run the migration to apply the changes:

npx sequelize-cli db:migrate



3. Rollback (if necessary):
If you need to undo the migration (for example, if you made a mistake or want to revert the changes), you can run:


npx sequelize-cli db:migrate:undo






//  NOW IF YOU WANT TO CHANGE AGAIN ( THIS TIME REMOVE SAME COLUMN ) 


Steps to Remove the Column
1. Create a New Migration to Remove the Column
Instead of deleting it manually from MySQL, create a new migration to remove the column. This approach is cleaner and maintains a proper history of database changes.

Run the following command to generate a new migration:



npx sequelize-cli migration:generate --name remove-kil-col
This will create a migration file in the migrations folder. Open it and modify it like this:


// INSIDE REMOVE MIGRATIN FILE
'use strict';

module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.removeColumn('tbl_members', 'kil_col');
  },

  async down(queryInterface, Sequelize) {
    // If you want to be able to undo this migration, define how to re-add the column
    await queryInterface.addColumn('tbl_members', 'kil_col', {
      type: Sequelize.STRING(255),
      allowNull: false,
      defaultValue: 'kil_colkil_col', // Restore the original default value
    });
  },
};
2. Run the Migration
Run the migration to remove the column from the database:



npx sequelize-cli db:migrate
3. Remove kil_col from the Sequelize Model
After the migration has successfully removed the column from the database, update your
Sequelize model (Member.js) to remove the kil_col field:



// Remove this block from the model
kil_col: {
  type: DataTypes.STRING(255),
  allowNull: false,
  defaultValue: 'kil_colkil_col',
},
Why This Approach?
Consistency: Ensures the model and database stay in sync.
Rollback Capability: If you ever need to restore the column, you can do so by rolling back the migration.
Clean History: Keeps a proper record of schema changes for yourself or other developers.


For  Mulitple changes ( adding multiple columns  

'use strict';

module.exports = {
  async up(queryInterface, Sequelize) {
    // Add the first column
    await queryInterface.addColumn('tbl_members', 'first_new_column', {
      type: Sequelize.STRING(100),
      allowNull: true,
      defaultValue: null,
    }, {
      after: 'email', // Place it after the 'email' column
    });

    // Add the second column
    await queryInterface.addColumn('tbl_members', 'second_new_column', {
      type: Sequelize.INTEGER,
      allowNull: false,
      defaultValue: 0,
    }, {
      after: 'first_new_column', // Place it after the 'first_new_column'
    });

    // Add more columns as needed
    await queryInterface.addColumn('tbl_members', 'third_new_column', {
      type: Sequelize.DATE,
      allowNull: true,
      defaultValue: null,
    }, {
      after: 'second_new_column', // Place it after the 'second_new_column'
    });
  },

  async down(queryInterface, Sequelize) {
    // Remove the columns in reverse order for rollback
    await queryInterface.removeColumn('tbl_members', 'third_new_column');
    await queryInterface.removeColumn('tbl_members', 'second_new_column');
    await queryInterface.removeColumn('tbl_members', 'first_new_column');
  },
};



Comments

Post a Comment

Popular posts from this blog

Part 15- What is Repeater (Networking Devices)- Computer Networking- CCNA

Repeater  hello friends i am Vasu Birla and today i am starting new segment of CCNA computer networking  its a Networking Devices. we will discuss every important Devices used in networks. Repeater is a first networking devices. What is Repeater  Repeater is a device that receives signals and re-transmits by amplifying or regenerating signals. Repeater has two port one is for receive signals from previous network and second port is for retransmit signals to the next extended network. Two Port Repeater Repeater is a device which is used to Regenerate or replicate signal (Pichhe se aa rahe signal ko regenerate karke ya amplify karke aage strong signals bhej sakte he) Analog and digital both type of signals can be retransmitted by repeater. Here there is difference between regeneration and amplifying.  Amplification means , received signals will be amplified as it is , whether there are impurities in signals  and Regeneration means repeate...
Post a Comment
Read more

GitHub Repo Collaboration Work on single project

 =============================================== To collaborate effectively with your friend on the same project, you should use Git branches to manage different lines of development. Here's a step-by-step procedure you can follow to streamline collaboration: 1. Create Separate Branches for Each Developer Create a New Branch for Your Friend: On your local repository, create a new branch for your friend. For example if your friend name is kilvish , if you want to create a branch named kilvish , you would run: make sure you would be on main branch already   command->  git checkout -b kilvish git push origin kilvish 2. Set Up Your Friend’s Environment( On your Friend's System )  at kilvish side  run ->  Clone the Repository (if not already done): If your friend hasn’t cloned the repository yet, they should do so: command ->  git clone https://github.com/Vasu-Birla/your-repo.git   // your your main clone line  Fetch All Bran...
Post a Comment
Read more

How to deploy NodeJS app on server with Apache2 and acess it with Server IP addresss - Node JS deploy project Live.

     hello friens , This is Vasu Birla , in previous post we have seen the deployement of NodeJS app on AWS instance server instance but AWS server is expensive than other servers . SO today we will use simple Ubuntu server for making Live out Project using Apache2 . At the end you will be abe to access your NodeJS app using server IP address from anywhere  .. 1.  Login to your server using SSH terminal . (in AWS part i already explained how to do this )     -> Open your putty in hostname put your server IP -      login with ssh username ->root and password     (if you dont have root username and pass ask your      provider or reset it from cpanel or hosting panel ) 2. after login - on terminal you can Put your project anywhere.     There are two ways to put your project folder on server location     (i) - > using Github - (very popular and easy to track your everyday code changes t...
Post a Comment
Read more