I've run a Photos and then subsequently Lightroom library off an external drive without any issues. I opted for an SSD just so it would be comfortably fast but I'm sure a non-SMR type HDD would be perfectly fine. SMR drives may also be fine (and very cheap) but they tend to have terrible write speeds which will make setting it up in the first place a nightmare and any future large-scale imports will be painful.
This is actually pretty common scenario; Apple makes a media/creator-centric product (or at least it's marketed that way), charges an absolute FORTUNE for small increments of extra storage, and so people end up buying a ton of external storage. So fortunately they've got a pretty straightforward way of doing it -
here.
EDIT:
There's other advantages to using an external drive as well - you can use it to easily copy your data elsewhere, they're much easier to recover data from if they ever fail, and your data will be fine if your Mac dies.