After MacOS Sonoma update, Photos app is unable to open my Photos Library because it's stored in iCloud Drive.

Just to be clear, prior to Sonoma, I never once thought about or knew where my photoslibrary file was. I didn't even know there was one. Then when I tried to launch Photos in Sonoma, I got the same error message as the OP. I hope these notes are useful:

1. I found my photos library file here: iCloud Drive/Pictures/Photos Library.photoslibrary
2. I dragged it to the Macintosh HD/Users/<my name>/Pictures folder.
3. I opened Photos app--it opened and updated my library without me having to hunt for the file or take any other action.
4. For those of you worried about space on your local drive, it should not make any (negative) difference. It takes up the same 100 GB (in my case) on your local drive whether you have it in the iCloud Drive or not--because there's a local copy of the file anyway, so moving it is a net zero operation. There's a benefit, though: You'll get that 100GB (or whatever) back from your iCloud storage, because this one (giant) file will no longer need to take up room in the cloud.
5. For those of you worried about syncing your photos with your phone/tablet/whatever, that should also not be a problem. If you do the first 3 steps above, your photo library connection to the iCloud Photos service should still be intact and everything will still sync.
6. The reason for 4 and 5 is that this Photos Library.photoslibrary is just your local copy of all your photos. When you sync photos from the cloud, this is where they are stored so you can have quick access to them. There's still a separate copy of all your photos in the cloud (if you use iCloud Photos service).

I don't know whether Sonoma installation moved my Photos Library.photoslibrary file from my local Pictures drive to iCloud Drive/Pictures or whether it had always been in iCloud and Photos just hadn't complained about it before. The primary answer on this page, while technically accurate, is a bit insulting as it implies that customers who encounter this have done something wrong by moving the file to the iCloud Drive--that is not the case. And also, it doesn't provide instructions or the information needed to fix the situation that installing Sonoma caused (not the customer who installed it). I hope this post addresses this gap for folks.