# v4.4.5 to v4.5.1 migration guide

The Strapi v4.4.5 to v4.5.1 migration guide upgrades v4.4.5 to v4.5.1. We introduced unique indexes on relationship tables and the application will not start if there are duplicated relationships. The migration guide consists of:

Upgrading the application dependencies
Installing database migration script (optional)
Reinitializing the application

# Upgrading the application dependencies to 4.5.1

PREREQUISITES

Stop the server before starting the upgrade.

Upgrade all of the Strapi packages in package.json to 4.5.1:

// path: package.json

{
  // ...
  "dependencies": {
    "@strapi/strapi": "4.5.1",
    "@strapi/plugin-users-permissions": "4.5.1",
    "@strapi/plugin-i18n": "4.5.1"
    // ...
  }
}

1
2
3
4
5
6
7
8
9
10
11

Save the edited package.json file.
Run either yarn or npm install to install the new version.

💡 TIP

If the operation doesn't work, try removing your yarn.lock or package-lock.json. If that doesn't help, remove the node_modules folder as well and try again.

# Installing database migration script (optional)

This step is only required if you have relationship duplicates in your application. If you have them, the application will not start and you will see an error message in the terminal similar to:

error: alter table <SOME_TABLE> add constraint <SOME_TABLE_INDEX>
unique (<COLUMN_NAME>, <COLUMN_NAME>) - could not create unique index <SOME_TABLE>

1
2

To remove the duplicated relationships easily, the following migration script file was created. It will automatically remove duplicates of any relationship in the database and will be executed only once at the next launch of Strapi.

To prepare the migration:

Make a backup of the database in case something unexpected happens.
In the ./database/migrations folder, create a file named 2022.11.16T00.00.00.remove_duplicated_relationships.js.
Copy and paste the following code into the previously created file:

'use strict';

/**
 * Get the link tables names that need to be updated
 */
const getLinkTables = ({ strapi }) => {
  const contentTypes = strapi.db.metadata;
  const tablesToUpdate = {};

  contentTypes.forEach(contentType => {
    // Get attributes
    const attributes = contentType.attributes;

    // For each relation type, add the joinTable name to tablesToUpdate
    Object.values(attributes).forEach(attribute => {
      if (attribute.type === 'relation' && attribute.joinTable) {
        tablesToUpdate[attribute.joinTable.name] = attribute.joinTable;
      }
    });
  });

  return Object.values(tablesToUpdate);
};

async function up(trx) {
  const linkTablesToUpdate = getLinkTables({ strapi });

  // Remove duplicates from link tables
  for (const table of linkTablesToUpdate) {
    const tableExists = await trx.schema.hasTable(table.name);
    if (!tableExists) continue;

    strapi.log.info(`Deleting duplicates of table ${table.name}...`);

    try {
      // Query to delete duplicates from a link table
      let query = `
        CREATE TEMPORARY TABLE tmp as SELECT DISTINCT t2.id as id
        FROM ?? as t1 JOIN ?? as t2
        ON t1.id < t2.id
      `;
      const pivotWhereParams = [];

      // For each pivot column, add a on condition to the query
      table.pivotColumns.forEach(column => {
        query += ` AND t1.?? = t2.??`;
        pivotWhereParams.push(column, column);
      });

      // Create temporary table with the ids of the repeated rows
      await trx.raw(query, [table.name, table.name, ...pivotWhereParams]);

      // Delete repeated rows from the original table
      await trx.raw(`DELETE FROM ?? WHERE id in (SELECT * FROM tmp)`, [table.name]);
    } finally {
      // Drop temporary table
      await trx.raw(`DROP TABLE IF EXISTS tmp `);
    }
  }
}

async function down() {}

module.exports = { up, down };

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64

# Reinitializing the application

Rebuild the administration panel and start the application: