v128.0 Upgrade migration script uses public schema hardcoding

[lezruk]

I have searched the existing issues to make sure this is not a duplicate report.

• Yes

The bug

For those who use standalone postgresql server with dedicated schema/user for immich, migrations script failling becuase of public schema refrence hardcoding. See provided log message and refrence to public. hardcosing cases in migrations folder. FYI this is recurring issue fixed in past few upgrades as well. Please remove "public." in scripts

The OS that Immich Server is running on

Debian

Version of Immich Server

v128.0

Version of Immich Mobile App

v128.0

Platform with the issue

• Server
• Web
• Mobile

Your docker-compose.yml content

N/A

Your .env content

N/A

Reproduction steps

1. Standalone postgresql DB with dedicated schema for immich (not public)
2. Upgrade from v127.0 to v128.0
   ...

Relevant log output

immich-microservices Migration "UsersAuditUuidv7PrimaryKey1740595460866" failed, error: index "IDX_users_audit_deleted_at_asc_user_id_asc" does not exist                                         immich-microservices QueryFailedError: index "IDX_users_audit_deleted_at_asc_user_id_asc" does not exist                                                                                          immich-microservices     at PostgresQueryRunner.query (/usr/src/app/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:219:19)                                                           immich-microservices     at process.processTicksAndRejections (node:internal/process/task_queues:105:5)                                                                                           immich-microservices     at async UsersAuditUuidv7PrimaryKey1740595460866.up (/usr/src/app/dist/migrations/1740595460866-UsersAuditUuidv7PrimaryKey.js:7:9)                                       immich-microservices     at async MigrationExecutor.executePendingMigrations (/usr/src/app/node_modules/typeorm/migration/MigrationExecutor.js:225:17)                                            immich-microservices     at async DataSource.runMigrations (/usr/src/app/node_modules/typeorm/data-source/DataSource.js:265:35)                                                                   immich-microservices     at async DatabaseRepository.runMigrations (/usr/src/app/dist/repositories/database.repository.js:203:9)                                                                  immich-microservices     at async /usr/src/app/dist/services/database.service.js:96:17                                                                                                            immich-microservices     at async /usr/src/app/dist/repositories/database.repository.js:211:23 {                                                                                                  immich-microservices   query: 'DROP INDEX "public"."IDX_users_audit_deleted_at_asc_user_id_asc"',                                                                                                 immich-microservices   parameters: undefined,                                                                                                                                                     immich-microservices   driverError: error: index "IDX_users_audit_deleted_at_asc_user_id_asc" does not exist                                                                                      immich-microservices       at /usr/src/app/node_modules/pg/lib/client.js:535:17                                                                                                                   immich-microservices       at process.processTicksAndRejections (node:internal/process/task_queues:105:5)                                                                                         immich-microservices       at async PostgresQueryRunner.query (/usr/src/app/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:184:25)                                                   immich-microservices       at async UsersAuditUuidv7PrimaryKey1740595460866.up (/usr/src/app/dist/migrations/1740595460866-UsersAuditUuidv7PrimaryKey.js:7:9)                                     immich-microservices       at async MigrationExecutor.executePendingMigrations (/usr/src/app/node_modules/typeorm/migration/MigrationExecutor.js:225:17)                                          immich-microservices       at async DataSource.runMigrations (/usr/src/app/node_modules/typeorm/data-source/DataSource.js:265:35)                                                                 immich-microservices       at async DatabaseRepository.runMigrations (/usr/src/app/dist/repositories/database.repository.js:203:9)                                                                immich-microservices       at async /usr/src/app/dist/services/database.service.js:96:17                                                                                                          immich-microservices       at async /usr/src/app/dist/repositories/database.repository.js:211:23 {

Additional information

I see few hardcodings to public schema in migrations, see output below of grep

node@immich-server-6cf87b95bf-mjm2g:/usr/src/app/dist/migrations$ grep public *
1700713871511-UsePgVectors.js: await queryRunner.query(SET search_path TO "$user", public, vectors);
1700713994428-AddCLIPEmbeddingIndex.js: await queryRunner.query(SET search_path TO "$user", public, vectors);
1700714033632-AddFaceEmbeddingIndex.js: await queryRunner.query(SET search_path TO "$user", public, vectors);
1707000751533-AddVectorsToSearchPath.js: await queryRunner.query(ALTER DATABASE "${databaseName}" SET search_path TO "$user", public, vectors);
1707000751533-AddVectorsToSearchPath.js: await queryRunner.query(ALTER DATABASE "${databaseName}" SET search_path TO "$user", public);
1718486162779-AddFaceSearchRelation.js: await queryRunner.query(SET search_path TO "$user", public, vectors);
1718486162779-AddFaceSearchRelation.js: await queryRunner.query(SET search_path TO "$user", public, vectors);
1732072134943-NaturalEarthCountriesIdentityColumn.js: CREATE FUNCTION ll_to_earth_public(latitude double precision, longitude double precision) RETURNS public.earth PARALLEL SAFE IMMUTABLE STRICT LANGUAGE SQL AS $$
1732072134943-NaturalEarthCountriesIdentityColumn.js: SELECT public.cube(public.cube(public.cube(public.earth()*cos(radians(latitude))*cos(radians(longitude))),public.earth()*cos(radians(latitude))*sin(radians(longitude))),public.earth()*sin(radians(latitude)))::public.earth
1732072134943-NaturalEarthCountriesIdentityColumn.js: await queryRunner.query(DROP FUNCTION ll_to_earth_public);
1740595460866-UsersAuditUuidv7PrimaryKey.js: await queryRunner.query(DROP INDEX "public"."IDX_users_audit_deleted_at_asc_user_id_asc");
1740595460866-UsersAuditUuidv7PrimaryKey.js: await queryRunner.query(DROP INDEX "public"."IDX_users_audit_deleted_at");

[mmomjian]

There is no way to change SET search_path TO "$user", public, vectors) as far as I know this is needed for vectors and earth distance.

These should be patched out:

1740595460866-UsersAuditUuidv7PrimaryKey.js: await queryRunner.query(DROP INDEX "public"."IDX_users_audit_deleted_at_asc_user_id_asc");
1740595460866-UsersAuditUuidv7PrimaryKey.js: await queryRunner.query(DROP INDEX "public"."IDX_users_audit_deleted_at");

[lezruk]

search path is fine, only direct reference to particular objects is the issue here exactly like "public".

[bentrnr21]

Thank you