Photos.app - All my albums have gone missing, using iCloud Photos

I have recovered my missing albums! It seems that syncing to my older iPad wiped all of my folders, but somehow my albums were still intact. Here is the solution that worked for me.

On a Mac:

1. First, confirm that Photos can still search for the missing albums. Launch Photos.app, and on the search box, start typing the name of a missing album (it doesn't have to be the full album name, just the beginning will do). The following search results will be grouped by type (Photos, Moments, Albums). If you're lucky, your "missing" album will appear under the Albums heading. Otherwise, this solution will not work.
2. Select the album from the search results and drag it anywhere under "My Albums" on the left sidebar. You can click the blue "See All" button on the right side of the search results and select+drag multiple albums to recover more albums all at once. You still have to manually recreate your folders, but your albums will be intact!

Fortunately for me, I prefixed most of my album names with dates (including the year). So I just had to search for "20" and all of my albums (which have names that start with 2020, 2019, 2018, etc.) showed up in just 1 search. All I had to do was select them all and drag them into the sidebar.

If you have more albums but can't remember their names, use the following steps to retrieve your album names:

1. Using Finder, navigate to ~/Pictures, look for Photos Library.photoslibrary, then Right Click -> Show Package Contents
2. Inside the database folder, look for a file named Photos.sqlite and make a copy of it somewhere (ex. your Desktop)
3. You need a tool to view the contents of the Photos.sqlite database file. I used "Native SQLite Manager" which is free on the Mac App Store. Launch it and use it to open the copy of the Photos.sqlite file that you made on your Desktop.
4. Once the database file is opened, the left sidebar will be populated with a list of table names. To save time, paste the following text into the query editor (it's the big dark-grey text box on the upper half of the right side of the app):

select ZTITLE from ZGENERICALBUM;

1. Then, press CMD+R to run the query.
2. The bottom half of the app should now show a table with the heading ZTITLE. The table will contain several elements with NULL values, but if you keep scrolling down you should see the names of your albums. (For convenience, you can click on the ZTITLE heading to sort the table in ascending or descending order. If you sort in ascending order, all the NULL values will be at the top, so you have to scroll down to the bottom to see your album names.)
3. Now that you have a list of album names, use the same steps above to search them in Photos.app and recover them. This will also allow you to recover your Smart Albums.

Hope this helps! I also hope Apple finds a way to fix/prevent this problem moving forward.

I forgot to mention: a few days ago I synced to an iPad 2nd gen running iOS 9.3.5 and my albums showed up there. But the device was so slow and it quickly used up the 16Gb capacity, so I disabled iCloud Photos sync in the Settings. My albums were still fine immediately afterwards so I didn't think it was related, but it may be similar to your case.

Furthermore, I just tested a few other apps. In some apps like Dropbox, Day One, Instagram and Facebook Messenger, the albums still show up as a long, flat list in their respective image upload screens. But on other apps like Facebook, VSCO, and even Apple Notes, the albums are gone too.

Since it's unlikely that the albums will reappear anytime soon, as in your experience, I may have to manually recreate my albums with the help of the information available from the third-party apps.

Cheers!