Programatically finding the original filename of a photo in the macOS Photos Library

I was doing some poking around in the Photos Library recently, and found something that might be useful to other people.

If you enable “Download Originals to this Mac”, and you look inside the Photos Library package, you’ll find all your full-resolution original photos:

A Finder window showing the path 'Photos Library.photoslibrary > originals > 6 > 682DB554-618E-42D6-92A1-3695CFF10D3B.orf'.

Problem is, these files are named with UUIDs, rather than the original filenames set by the camera (e.g. IMG_1770.jpg or PA090075.ORF). For what I was doing, I wanted that original filename.

The Photos Library does record this information, which you can get inside the app by selecting “Window > Info (⌘I)” – but what if you’re starting from the folder full of files? How do I find the camera filename from a UUID’d file?

The information lives in the Photos database, which is a SQLite database:

$ sqlite3 "file:///Users/alexwlchan/Pictures/Photos Library.photoslibrary/database/Photos.sqlite?mode=ro"

Then a SQL query like this will retrieve the original filename:

SELECT ZORIGINALFILENAME FROM ZCLOUDMASTER
INNER JOIN ZGENERICASSET ON ZCLOUDMASTER.Z_PK = ZGENERICASSET.ZMASTER
WHERE ZGENERICASSET.ZFILENAME = "682DB554-618E-42D6-92A1-3695CFF10D3B.orf";

I found this by getting a list of all SQL tables in the database, then looking at the column names for interesting-sounding fields. Other people have done more sophisticated analysis of the Photos database, but for what I was doing this was plenty.