Skip to main content

Database Queries


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.


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).



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_20230903_232542848.jpg';
SELECT * FROM "assets" WHERE "originalPath" LIKE 'upload/library/admin/2023/%';

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');
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"."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";


List all users
SELECT * FROM "users";

System Config

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

(Only used when not using the config file)


Delete person and unset it for the faces it was associated with
DELETE FROM person WHERE name = 'PersonNameHere';