Change paths of external library in database

[cr0n76]

I'm running immich for ages. Initially, I set up the external libraries with the standard path back then, e.g. /path/to/my/images/folder1:/usr/src/app/external/folder1:ro

Now, as I start using the folder view in immich, I see the target-path (/usr/src/app/external/) in the GUI. In my opinion, it's not that visually appealing.

So what I want to do is to change the target path to something more handsome, like suggested in the current official documentation (https://immich.app/docs/features/libraries) like /path/to/imagefolder/folder1:/mnt/media/folder1:ro.

So, to change it in the docker compose (via the .env file) is a no-brainer. To change the import path in the external libraries area of the immich administration is a no-brainer, as well.

But would I be able to change the path in the database as well so that I would not have to import the whole libraries again?

Do any of you have an idea?

[skatsubo]

Hey @cr0n76, recently I did a PoC of external-to-internal library migration - https://github.com/skatsubo/immich-library-external-to-internal. Its SQL part can be simplified/adjusted to your case.

(Btw, my handsome import paths look like /photo)

Usual disclaimer: direct modification of the database is not supported by the Immich team, have a DB backup.

Table of content

• Full-blown stored procedure with dry run and log
  • Steps
  • Stored procedure
  • Example run
• Minimalistic version: single UPDATE

Full-blown stored procedure with dry run and log

Steps

1. Create stored procedure, see its content below.

2. Dry run (default).

CALL update_external_paths('/tmp/extvol/media1', '/photo1');

3. View log/plan.

SELECT * FROM update_external_paths_log;

4. If log looks fine, do actual update.

CALL update_external_paths('/tmp/extvol/media1', '/photo1', false);

5. Check

select "originalPath" from assets;

Stored procedure

CREATE OR REPLACE PROCEDURE update_external_paths(
  old_prefix TEXT,
  new_prefix TEXT,
  dry_run BOOLEAN DEFAULT true
)
LANGUAGE plpgsql
AS $$
BEGIN
  -- Change log table contains proposed OR actual changes depending on dry run = true OR false
  CREATE TABLE IF NOT EXISTS update_external_paths_log (
    asset_id UUID PRIMARY KEY,
    old_originalPath TEXT NOT NULL,
    new_originalPath TEXT NOT NULL,
    old_sidecarPath TEXT,
    new_sidecarPath TEXT,
    dry_run BOOLEAN NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
  );
  -- Clear previous log entries
  TRUNCATE TABLE update_external_paths_log;

  -- Populate log for all matching assets
  INSERT INTO update_external_paths_log (asset_id, old_originalPath, new_originalPath, old_sidecarPath, new_sidecarPath, dry_run)
  SELECT
    a.id,
    a."originalPath",
    replace(a."originalPath", old_prefix, new_prefix),
    a."sidecarPath",
    replace(a."sidecarPath", old_prefix, new_prefix),
    dry_run
  FROM assets a
  WHERE a."isExternal" = true
    AND starts_with(a."originalPath", old_prefix);

  -- Perform the update (only if this is not a dry run)
  IF NOT dry_run THEN
    UPDATE assets
    SET "originalPath" = replace("originalPath", old_prefix, new_prefix),
      "sidecarPath" = replace("sidecarPath", old_prefix, new_prefix)
    WHERE "isExternal" = true
      AND starts_with("originalPath", old_prefix);
  END IF;
END;
$$;

Example run

immich=# CALL update_external_paths('/tmp/extvol/media', '/photo');
CALL

immich=# SELECT * FROM update_external_paths_log;
               asset_id               |          old_originalpath          |    new_originalpath     |          old_sidecarpath          |    new_sidecarpath     | dry_run |          created_at           
--------------------------------------+------------------------------------+-------------------------+-----------------------------------+------------------------+---------+-------------------------------
 379bd0ae-9f77-4720-8744-b7cfda56fba7 | /tmp/extvol/media/live/img.mov     | /photo/live/img.mov     | /tmp/extvol/media/live/img.xmp    | /photo/live/img.xmp    | t       | 2025-07-23 22:12:09.028716+00
 349bbc25-66cd-481b-81b7-ee4ed100d63b | /tmp/extvol/media/another/img.heic | /photo/another/img.heic | /tmp/extvol/media/another/img.xmp | /photo/another/img.xmp | t       | 2025-07-23 22:12:09.028716+00
 47d3e49c-c071-4805-9726-3fc138a099b4 | /tmp/extvol/media/_result3.jpg     | /photo/_result3.jpg     |                                   |                        | t       | 2025-07-23 22:12:09.028716+00
(3 rows)

immich=# select "originalPath" from assets;
            originalPath            
------------------------------------
 /tmp/extvol/media/live/img.mov
 /tmp/extvol/media/another/img.heic
 /tmp/extvol/media/_result3.jpg
(3 rows)

immich=# CALL update_external_paths('/tmp/extvol/media', '/photo', false);
NOTICE:  relation "update_external_paths_log" already exists, skipping
CALL

immich=# select "originalPath" from assets;
      originalPath       
-------------------------
 /photo/live/img.mov
 /photo/another/img.heic
 /photo/_result3.jpg
(3 rows)

Minimalistic version: single UPDATE

All the stuff above can be boiled down to a single UPDATE if we don't care about dry run or log/history.

UPDATE assets
SET "originalPath" = replace("originalPath", '/tmp/extvol/media', '/photo'),
  "sidecarPath" = replace("sidecarPath", '/tmp/extvol/media', '/photo')
WHERE "isExternal" = true
    AND starts_with("originalPath", '/tmp/extvol/media');

Adjust 5 parameters above to match your paths.

Probably wise to run it in a transaction block: start with begin;, do the update, verify select "originalPath" from assets;, then apply commit; if everything looks fine.

[cr0n76]

Nice, thank you for this elaborate reply!

[skatsubo]

Just stumbled across that PR in 1.136

immich/server/src/schema/migrations/1752759108283-ConvertToAbsolutePaths.ts

Lines 21 to 28 in 977c9b9

	const items: Array<{ table: string; column: string }> = [
	{ table: 'asset', column: 'originalPath' },
	{ table: 'asset', column: 'encodedVideoPath' },
	{ table: 'asset', column: 'sidecarPath' },
	{ table: 'asset_file', column: 'path' },
	{ table: 'person', column: 'thumbnailPath' },
	{ table: 'user', column: 'profileImagePath' },
	];

It's about internal library paths. But you may want to check those tables/columns if they need replacement too in your case. Because my script covers only originalPath and sidecarPath.

Good luck!

[noliveleger]

Thank you @skatsubo , it was useful for me too.
For the records, in version 2.5.6 :

• I had to use asset instead of assets
• sidecarPath is not a column in my asset table. I had to remove it.

I also replaced the path in library table.