The DECLARE TABLE statement could list the expected fields, then the SQL engine could create as needed, or alter as needed.
Personally, I’m happy with declaring my tables in sqlalchemy and having alembic do the grunt work. Allows me to work in the declarative way you describe while also making it easy to handle the data migration component.
The challenge is that more complicated refactoring like renaming columns has to be done through a weird xml DSL.
It also treats static/initial data as out-of-scope.
And it's a product of the bad old days of clumsy closed-source designers and the like.
I use it extensively... and in general, wish I didn't. Maybe it's "grass is always greener" but I wish I'd just stuck to a stack of migration scripts. The fact is that 99% of the time you're deploying to an existing SQL server, so expressing your schema as a series of sequential changes is the only workflow that makes sense, sadly.
(Plus, yeah, BACPAC doesn't solve all the other problems with DACPAC that most of the tools are still clumsy mostly closed-source things that work with Microsoft's SQL Server alone.)
