iCloud Photos: Albums missing from folders, found via search

Ok, I've spent the afternoon figuring this out, as it's important to me. I'll explain how I've fixed it - but beware, I'm a software developer so this is arguably easier for me to understand than if you have no knowledge of how these things work.

The solution is:

Photos library is backed by an sqlite database, which you definitely should NOT modify if you don't know what you're doing - and if you do know what you're doing, make a backup of it first. You can get to it by right clicking your .photoslibrary file and 'show package contents'. It's in the database folder and it's called Photos.sqlite.

I edited it with a free app called DB Browser for SQLite. First close your Photos app.

The problem I had remember was missing top level folders. All albums and folders are listed in a database tabled called ZGENERICALBUM. Albums and folders are differentiated by the ZKIND column.

You can find trashed folder with this SQL:

SELECT Z_PK, ZTITLE, ZTRASHEDDATE

  FROM ZGENERICALBUM

  WHERE ZTRASHEDSTATE = 1

    AND ZKIND = 4000

  ORDER BY ZTRASHEDDATE;

If you execute this, you will see all the folders that have been trashed. You can untrash them by executing this SQL:

UPDATE ZGENERICALBUM

  SET ZTRASHEDSTATE = 0,

      ZTRASHEDDATE = NULL

  WHERE ZTRASHEDSTATE = 1

    AND ZKIND = 4000;

I then closed the DB Browser app and opened Photos again. These changes alone made the missing folders appear again in my Photos library, and the albums were inside the folders as they should be. 👍

However they didn't sync to iCloud straight away. I had to rename each folder in the Photos app, just a little change to the name, which triggered them to rsync to iCloud.

This fixed it completely.

This certainly could be scripted or added to an app such as 'power photos' to make it easier for the layperson to fix their library.

Good luck.

By the way. To help me understand the sqlite database structure, I first dumped the whole database and contents to a text file, using sqlite3 on the command line (install this with brew). I then use Claude Code to inspect this text file, giving it examples of the folders that were missing and those that were ok, and getting it to compare and tell me the difference and the structure of the data. I then asked Claude to generate the SQL statements for me.

Thanks for your response, but it's almost like you didn't really read any of it properly.

Firstly I did say in my response, to make a backup copy first - which I did - and I tested on first. So yes, I do value my photo data, which is why this was necessary to do this fix, as I had orphaned albums that I couldn't access.

The original problem was not because I deleted a folder - I'd be aware if I did that. You get confirmation warnings when you delete items - and there were 10 deleted folders - and the albums were NOT deleted, they were somehow orphaned from their parent folder and not accessible.

And yes... a backup of the library is probably the best remedy - but I also stated my time machine backup kept failing to recover.

Despite your best advice - by modifying my Photos.sqlite database, I fixed the problem.