Steps to Add a New Column with Sequelize Migrations

In an update for my blogging platform, I needed to add a thumbnail image column to make the articles more visually appealing. This update accompanied a shift from displaying all articles on a single page to showing each one on its own page, improving navigation as the number of articles grows.

The thumbnail column stores a string representing a link to an image. This approach keeps the database lightweight and separates concerns by handling image storage elsewhere.

Adding the New Column to the Model

To begin, I updated the BlogPosts model to include the thumbnail column. Setting allowNull: true ensured that adding a thumbnail was optional and avoided issues with older posts that didn’t have a thumbnail yet:

thumbnail: {
    type: DataTypes.STRING,
    allowNull: true, // Optional for older posts
    },

However, this change to the model alone wasn’t enough. The Postgres database didn’t recognize the new column, and I encountered the error:

error: the column "thumbnail" doesn't exist

This happens because Sequelize models define how the application interacts with the database, but they don’t automatically modify the database schema. To resolve this, I needed to use a Sequelize migration.

Preparing Sequelize for Migrations

Before creating the migration, I ensured the Sequelize CLI was initialized in the project. Before running the following command, which will create a config/ folder and related setup, be sure to install it first by entering npm install sequelize-cli

npx sequelize-cli init

Inside the config/ folder, I opted to use a config.js file instead of config.json. This allowed me to use environment variables with dotenv for configuration:

require('dotenv').config();

module.exports = {
   development: {
       username: process.env.DB_USER,
       password: process.env.DB_PASSWORD,
       database: process.env.DB_NAME,
       host: process.env.DB_HOST,
       dialect: "postgres",
   },
   production: {
       username: process.env.DB_USER,
       password: process.env.DB_PASSWORD,
       database: process.env.DB_NAME,
       host: process.env.DB_HOST,
       dialect: "postgres",
   },
};

I then removed the config.json file to avoid conflicts.

Generating the MIgration File

Next, I created the migration file for adding the thumbnail column using the following command:

npx sequelize-cli migration:generate --name add-thumbnail-to-blogposts

This created a new file in the migrations/ folder, named something like:

20241206232526-add-thumbnail-to-blogposts.js

I modified this file to add the new column:

module.exports = {
  up: async (queryInterface, Sequelize) => {
      await queryInterface.addColumn('blogposts', 'thumbnail', {
          type: Sequelize.STRING,
          allowNull: true,
      });
  },

  down: async (queryInterface) => {
      await queryInterface.removeColumn('blogposts', 'thumbnail');
  },
};

Applying the Migration

To update the development database, I ran the following command:

npx sequelize-cli db:migrate

This successfully added the thumbnail column to the blogposts table, enabling me to store image links for each article.

Applying the Migration in Production

For the deployed version of the database, I followed these steps:

Backup the Production Database: Before making changes, I downloaded a backup to safeguard against misconfigurations.
Copy the Migration Files: I ensured the updated config.js file and add-thumbnail-to-blogposts.js migration file were present in the server repository.
Set Environment Variables: I verified that the production environment variables required by dotenv were configured correctly. These were set in the Setup Node.js App section of cPanel.
Run the Migration: I navigated to the backend app directory in the production terminal and ran:

npx sequelize-cli db:migrate --env production

This successfully added the thumbnail column to the production Postgres database.

Conclusion

Adding the thumbnail column made it possible to display images for each blog article, enhancing the platform's visual appeal and usability. These steps provide a structured and repeatable approach to adding new columns to the database using Sequelize migrations, which is critical for maintaining synchronization between the models and database.

The above is the detailed content of Steps to Add a New Column with Sequelize Migrations. For more information, please follow other related articles on the PHP Chinese website!