[jph]

Good article. By describing the workarounds, the article highlights the need for a SQL declarative table statement, something akin to `DECLARE TABLE`.

The DECLARE TABLE statement could list the expected fields, then the SQL engine could create as needed, or alter as needed.

[aidos]

I guess one issue with that is that it’s not going to play nice with data migrations.

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.

[Pxtl]

Microsoft has a product called SSDT for this. You provide the SQL schema, it compiles a file called a DACPAC, and provides tools for deploying a DACPAC against a database. Where it can figure out the migration (eg. changing column types, adding columns) it does so automatically. Where it can't (dangerous changes, dropping columns) it blocks.

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.

[WorldMaker]

Data migration (including static/initial data) can in theory be done using BACPAC (and DACPAC and BACPAC share a file structure so you could start with an SSDT DACPAC and expand it, in theory), but the tools that work with BACPAC in general are even flakier and BACPAC was primarily designed for backups rather than data compare, which is why SSDT mostly opted out of managing BACPACs at all and stuck primarily to the DACPAC subset when outputting artifacts (and part of why DACPAC and BACPAC even have different names). (SSDT can read BACPACs pretty well as I recall, and using BACPACs in SSDT for things is still sometimes useful.)

(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.)

[wmanley]

See also: https://david.rothlis.net/declarative-schema-migration-for-s...