Cannot migrate database schema error: Column Contains null value

You may encounter a common issue when attempting to migrate your LoopBack 4 application's database schema. The error message you might encounter is: "Cannot migrate database schema error: column 'profile_pic' of relation 'agent' contains null values."

The error message essentially means that there is a required constraint set on the 'profile_pic' column in the 'agent' table, which means that it cannot contain null values. However, during the migration process, the system encountered existing rows where this column is empty (null), violating the constraint.

Or think like this. You have an existing table with data, and you've recently added a new column that is set to "not null." However, since there were already five rows of data in the table before adding this new column, the "not null" constraint is being violated because those five rows do not have any values in this new field.

Step 1: Temporarily Comment Out the Required Property

To address this issue, start by temporarily commenting out the required property in your model. In this example, we're dealing with a 'profile_pic' property in the 'Agent' model.

@property({
  type: 'string',
// Comment out this code temporarily
//  required: true,
})
profile_pic: string;

Step 2: Run the Migration

Now that you've commented out the required property, it's time to run the migration. Open your terminal and execute the following command:

npm run migrate

This command will make the necessary changes to your database schema.

Step 3: Add Migration Scripts

To populate existing rows with data for the new 'profile_pic' column, you'll need to create migration scripts. Add the following code to your 'application.ts' file, which is the entry point of your LoopBack 4 application:

import { SchemaMigrationOptions } from '@loopback/repository';
import { AgentRepository } from './repositories';

// ...

export class ToDoApplication extends BootMixin(
  ServiceMixin(RepositoryMixin(RestApplication)),
) {
  // ...

  // Migration scripts
  async migrateSchema(options?: SchemaMigrationOptions) {
    await super.migrateSchema(options);

    const agentRepository = await this.getRepository(AgentRepository);
    const agents = await agentRepository.find();

    for (const agent of agents) {
      const newValue = '10_2023-09-01T09:35:17.991Z_agent_default.jpg';

      agent.profile_pic = newValue;
      await agentRepository.update(agent);
    }
  }

  // ...
}

Step 4: Save and Run Migrations Again

Save the 'application.ts' file after adding the migration scripts. Now, you can rerun the migrations by executing:

npm run migrate

This time, your existing rows will be populated with data for the 'profile_pic' column.

Step 5: Uncomment the Required Property

Finally, don't forget to uncomment the required property you initially commented out in your model:

@property({
  type: 'string',
  required: true,
})
profile_pic: string;

Apply the change on the database level:-

npm run migrate

Conclusion:

Handling database schema migrations in LoopBack 4 is a crucial part of maintaining your application as it evolves. By following these steps, even beginners can successfully address common issues and populate existing rows with data for new columns. Remember to always backup your data and follow best practices when making schema changes to production databases.