Database Queries

danger

Keep in mind that mucking around in the database might set the moon on fire. Avoid modifying the database directly when possible, and always have current backups.

tip

Run docker exec -it immich_postgres psql --dbname=immich --username=<DB_USERNAME> to connect to the database via the container directly.

(Replace <DB_USERNAME> with the value from your .env file).

Assets

note

The "originalFileName" column is the name of the file at time of upload, including the extension.

Find by original filename

SELECT * FROM "assets" WHERE "originalFileName" = 'PXL_20230903_232542848.jpg';
SELECT * FROM "assets" WHERE "originalFileName" LIKE 'PXL_%'; -- all files starting with PXL_
SELECT * FROM "assets" WHERE "originalFileName" LIKE '%_2023_%'; -- all files with _2023_ in the middle

Find by path

SELECT * FROM "assets" WHERE "originalPath" = 'upload/library/admin/2023/2023-09-03/PXL_2023.jpg';
SELECT * FROM "assets" WHERE "originalPath" LIKE 'upload/library/admin/2023/%';

note

You can calculate the checksum for a particular file by using the command sha1sum <filename>.

Find by checksum (SHA-1)

SELECT encode("checksum", 'hex') FROM "assets";
SELECT * FROM "assets" WHERE "checksum" = decode('69de19c87658c4c15d9cacb9967b8e033bf74dd1', 'hex');
SELECT * FROM "assets" WHERE "checksum" = '\x69de19c87658c4c15d9cacb9967b8e033bf74dd1'; -- alternate notation

Find duplicate assets with identical checksum (SHA-1) (excluding trashed files)

SELECT T1."checksum", array_agg(T2."id") ids FROM "assets" T1
  INNER JOIN "assets" T2 ON T1."checksum" = T2."checksum" AND T1."id" != T2."id" AND T2."deletedAt" IS NULL
  WHERE T1."deletedAt" IS NULL GROUP BY T1."checksum";

Live photos

SELECT * FROM "assets" WHERE "livePhotoVideoId" IS NOT NULL;

By description

SELECT "assets".*, "exif"."description" FROM "exif"
  JOIN "assets" ON "assets"."id" = "exif"."assetId"
  WHERE TRIM("exif"."description") <> ''; -- all files with a description
SELECT "assets".*, "exif"."description" FROM "exif"
  JOIN "assets" ON "assets"."id" = "exif"."assetId"
  WHERE "exif"."description" ILIKE '%string to match%'; -- search by string

Without metadata

SELECT "assets".* FROM "exif"
  LEFT JOIN "assets" ON "assets"."id" = "exif"."assetId"
  WHERE "exif"."assetId" IS NULL;

size < 100,000 bytes, smallest to largest

SELECT * FROM "assets"
  JOIN "exif" ON "assets"."id" = "exif"."assetId"
  WHERE "exif"."fileSizeInByte" < 100000
  ORDER BY "exif"."fileSizeInByte" ASC;

Without thumbnails

SELECT * FROM "assets" WHERE "assets"."previewPath" IS NULL OR "assets"."thumbnailPath" IS NULL;

By type

SELECT * FROM "assets" WHERE "assets"."type" = 'VIDEO';
SELECT * FROM "assets" WHERE "assets"."type" = 'IMAGE';

Count by type

SELECT "assets"."type", COUNT(*) FROM "assets" GROUP BY "assets"."type";

Count by type (per user)

SELECT "users"."email", "assets"."type", COUNT(*) FROM "assets"
  JOIN "users" ON "assets"."ownerId" = "users"."id"
  GROUP BY "assets"."type", "users"."email" ORDER BY "users"."email";

Failed file movements

SELECT * FROM "move_history";

Users

List all users

SELECT * FROM "users";

Get owner info from asset ID

SELECT "users".* FROM "users" JOIN "assets" ON "users"."id" = "assets"."ownerId" WHERE "assets"."id" = 'fa310b01-2f26-4b7a-9042-d578226e021f';

System Config

Custom settings

SELECT "key", "value" FROM "system_metadata" WHERE "key" = 'system-config';

(Only used when not using the config file)

Persons

Delete person and unset it for the faces it was associated with

DELETE FROM "person" WHERE "name" = 'PersonNameHere';

Postgres internal

Change DB_PASSWORD

ALTER USER <DB_USERNAME> WITH ENCRYPTED PASSWORD 'newpasswordhere';