[HN Gopher] Database branching: three-way merge for schema changes
       ___________________________________________________________________
        
       Database branching: three-way merge for schema changes
        
       Author : mschoening
       Score  : 52 points
       Date   : 2023-04-26 17:00 UTC (6 hours ago)
        
 (HTM) web link (planetscale.com)
 (TXT) w3m dump (planetscale.com)
        
       | jph wrote:
       | 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.
        
         | Pxtl wrote:
         | 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.
        
         | wmanley wrote:
         | See also: https://david.rothlis.net/declarative-schema-
         | migration-for-s...
        
         | aidos wrote:
         | 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.
        
       | zachmu wrote:
       | Dolt does 3-way merge for both schema and data
       | 
       | https://github.com/dolthub/dolt
        
       ___________________________________________________________________
       (page generated 2023-04-26 23:03 UTC)