Skip to main content

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 immich <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';
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_20230903_232542848.jpg';
SELECT * FROM "assets" WHERE "originalPath" LIKE 'upload/library/admin/2023/%';
Find by checksum
SELECT encode("checksum", 'hex') FROM "assets";
SELECT * FROM "assets" WHERE "checksum" = decode('69de19c87658c4c15d9cacb9967b8e033bf74dd1', 'hex');
Live photos
SELECT * FROM "assets" where "livePhotoVideoId" IS NOT NULL;
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"."resizePath" IS NULL OR "assets"."webpPath" 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
SELECT * FROM "users";

System Config​

Custom settings
SELECT "key", "value" FROM "system_config";

(Only used when not using the config file)