Sling Academy
Home/Node.js/Sequelize.js: How to add a column to an existing table

Sequelize.js: How to add a column to an existing table

Last updated: December 29, 2023

In modern web development, database migrations are a vital part of the application’s lifecycle. Sequelize.js, a robust Node.js ORM (Object-Relational Mapping) for Postgres, MySQL, MariaDB, SQLite, and Microsoft SQL Server, provides a rich set of tools for managing data models and database schemas over time. This article will guide you through the steps to add a column to an existing table using Sequelize.

Understanding Migrations

Before adding a column to a table, it’s important to understand the concept of database migrations. A database migration is a way of altering the database schema over time in a consistent and easy-to-track manner. Each migration file represents a change to the database, such as creating or modifying tables, indexes, or columns.

Setting Up the Environment

To start adding a column using Sequelize, ensure you have Sequelize CLI installed:

$ npm install --save sequelize-cli

You also need to have an existing Sequelize project with a configured database connection and model definitions.

Adding a Simple Column

First, create a new migration file:

$ sequelize migration:generate --name add-column-to-user

This will create a new migration file in your migrations directory. The name of the file will be a timestamp followed by ‘-add-column-to-user.js’. Now open the migration file and define the changes:

'use strict';

module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.addColumn(
      'Users', // name of the Source model
      'age', // name of the key we're adding
      {
        type: Sequelize.INTEGER,
        allowNull: true
      }
    );
  },
  down: async (queryInterface, Sequelize) => {
    await queryInterface.removeColumn('Users', 'age');
  }
};

Execute the migration:

$ sequelize db:migrate

Adding a Column with More Options

Sometimes, you might need to add a column with more specific requirements such as adding default values, making it unique, or not allowing null values. Here’s an example:

'use strict';

module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.addColumn(
      'Users',
      'email',
      {
        type: Sequelize.STRING,
        allowNull: false,
        unique: true,
        defaultValue: '[email protected]'
      }
    );
  },
  down: async (queryInterface, Sequelize) => {
    await queryInterface.removeColumn('Users', 'email');
  }
};

Remember to test the migration thoroughly in a development environment before running it in production.

Handling Complex Changes

When a column addition involves more complex changes, such as adding foreign keys or creating indexes, your migration script will require additional commands. Here is an example of adding a foreign key column:

//... Inside up function
await queryInterface.addColumn('Posts', 'userId', {
  type: Sequelize.INTEGER,
  references: {
    model: 'Users',
    key: 'id',
  },
  onUpdate: 'CASCADE',
  onDelete: 'SET NULL',
});

//... Inside down function
await queryInterface.removeColumn('Posts', 'userId');

Testing Your Migration

Always test your migration before applying it to your production database. This ensures that the migration behaves as expected and does not cause data loss or downtime.

Summary

Adding a column to an existing Sequelize.js model requires understanding migrations, setting up your environment, and considering the effects on your database schema. By following the steps outlined in this guide and carefully designing your database changes, you can confidently modify your schemas as the needs of your application evolve. As with many aspects of database management, exercise caution and ensure that you have a reliable backup and recovery plan in place for your production databases.

Next Article: Sequelize.js: How to Delete a Column from an Existing Table

Previous Article: Sequelize.js: Find all records that match an array of values

Series: Sequelize.js Tutorials

Node.js

You May Also Like

  • NestJS: How to create cursor-based pagination (2 examples)
  • Cursor-Based Pagination in SequelizeJS: Practical Examples
  • MongooseJS: Cursor-Based Pagination Examples
  • Node.js: How to get location from IP address (3 approaches)
  • SequelizeJS: How to reset auto-increment ID after deleting records
  • SequelizeJS: Grouping Results by Multiple Columns
  • NestJS: Using Faker.js to populate database (for testing)
  • NodeJS: Search and download images by keyword from Unsplash API
  • NestJS: Generate N random users using Faker.js
  • Sequelize Upsert: How to insert or update a record in one query
  • NodeJS: Declaring types when using dotenv with TypeScript
  • Using ExpressJS and Multer with TypeScript
  • NodeJS: Link to static assets (JS, CSS) in Pug templates
  • NodeJS: How to use mixins in Pug templates
  • NodeJS: Displaying images and links in Pug templates
  • ExpressJS + Pug: How to use loops to render array data
  • ExpressJS: Using MORGAN to Log HTTP Requests
  • NodeJS: Using express-fileupload to simply upload files
  • ExpressJS: How to render JSON in Pug templates