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:

1. Backup the Production Database: Before making changes, I downloaded a backup to safeguard against misconfigurations.
2. 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.
3. 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.
4. 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.