| To be clear, I'm not saying it cannot be done — just that it's hard, especially if it's a one-size-fits-all solution that needs to support many databases and SQL dialects, and that a tool like this is going to continually be fighting the various disparities that exist between databases. I ran into an interesting challenge recently where it was necessary to replace the primary key. The only way to do this (with Postgres) on a live production database is drop the key and add it again in the same transaction, with a USING INDEX; so: CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS foo_new_pkey
ON foo(bar);
ALTER TABLE foo
DROP CONSTRAINT foo_pkey CASCADE,
ADD CONSTRAINT foo_pkey PRIMARY KEY
USING INDEX foo_new_pkey;
There are some challenges to making a tool be able to do this seamlessly:(1) The tool must understand that a modified primary key will need an existing index. (2) It must understand that it has to be done in a single transaction. (3) It must understand that this modifies the underlying catalogue: Postgres will rename the new "foo_new_pkey" index to "foo_pkey" and drop the old index. And that's just Postgres. I bet other databases are different. Most databases don't even allow transactional DDL (e.g. Oracle and Microsoft SQL Server). Does the amount of work required actually justify the end result? As I said earlier, developers need to understand migrations and their ordering in order to be able to plan their rollout. If a system cannot craft and "orchestrate" migrations perfectly — and I argue that this is infeasible without tons of work — then this means engineers have to run the tool and examine the output and understand it before rolling it out anyway. So now you have a smart, complex tool to learn that doesn't even do the whole job. And in the case of complex migrations it might not even do all of it, requiring the suggested SQL output to be tweaked before it can be run. You might as well just write migrations by hand, then. To be clear, I think it's good to be ambitious, I'm just generally skeptical for the above reasons. Last point: Having an SQL parser in Go would be great, so kudos if you manage to build this. Again, I think you will be fighting here to stay up to date with all the dialects, but it's a worthy goal. |