Can photo sync be as effortless as music sync, between macOS and iOS? To copy photos from iOS (and make space for more photos) one connects the device (iPhone or iPad) with a cable to the Mac and opens Photos, then imports.

But with music things "just work" without the user having to do anything. I have "smartlists" set up. When the music (even just play count) changes on my Mac, they sync wirelessly and with no intervention on my part, not even triggering the sync.

Does such a convenience exist for photos? Is there a way to say "iOS: everytime you're on the same WiFi as my Mac, copy the photos over to the Mac, and delete the photos"?

Basically I'm looking for functionality similar to Dropbox/iCloud, but without any cloud or cloud-like features. It seems like a pretty obvious feature to add, especially in a homogeneous ecosystem, and so the question is: are we there yet?
Yes - enable iCloud Photo Library. I’ve been happy with this for my main personal library consisting of 90 GB library size and 75,000 photos.

I do maintain some side libraries that don’t sync to iCloud - but those are specific professional and trip specific libraries where I just save my best 100 or so photos from those libraries to my “main” library.

I have a MacPro that keeps original size of all photos. The rest of the iPad, iPhone, Apple TV and Mac that sync are allowed to “optimize” photo sizes when the main storage for that device runs low.

I’m happy knowing I can use Time Machine (or SuperDuper / Arq / CrashPlan / whatever) to make offline and off-site backups of my Main libraries and not trust that iCloud Photo Library will never mess things up. So far it hasn’t, but I have a plan to walk away from iCloud any time and restore from backup if needed.

—– 

Even better - Apple offers caching service so you could cache all your iCloud data locally on pretty much and Mac you can leave running with an inexpensive external drive if needed. I like to give 500 GB of space to cache downloads and iCloud data and that works well for 5 people, each with several devices and similar sized photo and music libraries. The caching server makes backup and restore very fast as well as updates and upgrades to new OS and new devices when they get configured the first time.

On 10.12 and lower - this caching service is part of Server.app and works great.
On 10.13 and higher - there are signs you won’t need server.app to cache apps and iCloud data locally. I have no complaints yet testing this in beta.