v3 to v4 postgres migration script fails: relation "up_users_role_links" does not exist

[vitormanfredini]

Bug report

Required System information

• Node.js version: v14.20.0
• NPM version: 6.14.17
• Strapi version: v4.3.8
• Database: PostgreSQL 14.5
• Operating system: Ubuntu 20.04

Describe the bug

After following the steps to migrate database from v3 to v4, the scripts fails with the following error:

error: delete from "up_users_role_links" - relation "up_users_role_links" does not exist

Steps to reproduce the behavior

1. Follow steps in https://github.com/strapi/migration-scripts/tree/main/v3-sql-v4-sql

Expected behavior

Database should be fully migrated to v4.

yarn start entire output

yarn run v1.22.19
$ node index.js
Migrating Core Store TBA
Migrating 17/29 items from core_store to strapi_core_store_settings
core_store batch strapi/strapi#1
Migrating Admin
Migrating 3 items from strapi_role to admin_roles
DBV4 ITEMS
strapi_role batch strapi/strapi#1
Migrating 5 items from strapi_administrator to admin_users
DBV4 ITEMS
strapi_administrator batch strapi/strapi#1
Migrating 12 items from strapi_users_roles to admin_users_roles_links
DBV4 ITEMS
strapi_users_roles batch strapi/strapi#1
Migrating 85 items from strapi_permission to admin_permissions
strapi_permission batch strapi/strapi#1
strapi_permission batch strapi/strapi#2
Migrating Users
Migrating 2 items from users-permissions_role to up_roles
DBV4 ITEMS
users-permissions_role batch strapi/strapi#1
Migrating 14/210 items from users-permissions_permission to up_permissions
users-permissions_permission batch strapi/strapi#1
Migrating 1069 items from users-permissions_user to up_users
(node:238514) UnhandledPromiseRejectionWarning: error: delete from "up_users_role_links" - relation "up_users_role_links" does not exist
    at Parser.parseErrorMessage (/mnt/480gb/casion-projects/migration-scripts/v3-sql-v4-sql/node_modules/pg-protocol/dist/parser.js:287:98)
    at Parser.handlePacket (/mnt/480gb/casion-projects/migration-scripts/v3-sql-v4-sql/node_modules/pg-protocol/dist/parser.js:126:29)
    at Parser.parse (/mnt/480gb/casion-projects/migration-scripts/v3-sql-v4-sql/node_modules/pg-protocol/dist/parser.js:39:38)
    at Socket.<anonymous> (/mnt/480gb/casion-projects/migration-scripts/v3-sql-v4-sql/node_modules/pg-protocol/dist/index.js:11:42)
    at Socket.emit (events.js:400:28)
    at addChunk (internal/streams/readable.js:293:12)
    at readableAddChunk (internal/streams/readable.js:267:9)
    at Socket.Readable.push (internal/streams/readable.js:206:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:188:23)
(Use `node --trace-warnings ...` to show where the warning was created)
(node:238514) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). To terminate the node process on unhandled promise rejection, use the CLI flag `--unhandled-rejections=strict` (see https://nodejs.org/api/cli.html#cli_unhandled_rejections_mode). (rejection id: 2)
(node:238514) [DEP0018] DeprecationWarning: Unhandled promise rejections are deprecated. In the future, promise rejections that are not handled will terminate the Node.js process with a non-zero exit code.

Additional context

The error seems to originate in https://github.com/strapi/migration-scripts/blob/main/v3-sql-v4-sql/migrate/migrateUsers.js on line 64, where it tries to delete everything from the table up_users_role_links but this table doesn't exist.

[vitormanfredini]

I should also point out that I do have the plugin-users-permissions installed.

"dependencies": {
    "pg": "^8.7.1",
    "@strapi/strapi": "4.3.8",
    "@strapi/plugin-documentation": "4.3.8",
    "@strapi/plugin-i18n": "4.3.8",
    "@strapi/plugin-users-permissions": "4.3.8",
    "@strapi/provider-upload-aws-s3": "4.3.8"
  },

[vitormanfredini]

Ok, I figured it out. First thing that I did was to change table names in v3-sql-v4-sql/migrate/migrateUsers.js to match the right ones:

async function migrateUsersData() {
-  const source = "users-permissions_user";
-  const destination = "up_users";
-  const destinationLinks = "up_users_role_links";
+  const source = 'users-permissions_user';
+  const destination = 'users-permissions_user';
+  const destinationLinks = 'users_permissions_user_role_links';

I don't know if the current names are valid in other circunstances, tho.

I also added double quotes to this SQL query in v3-sql-v4-sql/migrate/helpers/migrate.js (otherwise it throws an error if the tablename has "-"):

-      await dbV4.raw(
-        `SELECT SETVAL ('${seq}', (SELECT MAX(id) + 1 FROM ${destination}))`
-      );
+      await dbV4.raw(`SELECT SETVAL ('${seq}', (SELECT MAX(id) + 1 FROM "${destination}"))`);

Unrelated to this particular issue but also important: In v3-sql-v4-sql/migrate/helpers/migrateValues.js, I applied the same pluralize.singular function as strapi/codemods does when migrating code. Now the migration doesn't lose all upload relations in files_related_morphs.

+  if (result.substring(0, 5) == 'api::') {
+    const arrParts = result.substring(5).split('.');
+    if (arrParts.length == 2) {
+      result = `api::${_.kebabCase(pluralize.singular(arrParts[0]))}.${_.kebabCase(
+        pluralize.singular(arrParts[1])
+      )}`;
+    }
+  }

That might be useful to others. Should I submit a pull request?

[cristiangrojas]

I've been having this issue for a while and was the only bottle neck on my migration process.

Thanks for sharing this solution.

[vitormanfredini]

You're welcome.

Until my pull request isn't processed you can use this fork with the mentioned changes: https://github.com/vitormanfredini/strapi-v3tov4-modified-migration-scripts

[derrickmehaffy]

migrating this to the migration scripts repo, not here.

[derrickmehaffy]

I believe I found the root cause of this issue here. The problem comes from codemods in https://github.com/strapi/codemods/issues/47

Can you confirm that your extensions of users-permissions has the proper collectionName value of up_users

[vitormanfredini]

No, it's using the old ones. Good catch.

When I get the time, I'll try to redo the database migration with the right ones.

Thank you

[derrickmehaffy]

For now I'll go ahead and close this but if anyone hits the issue please let us know.