Declarative database schema migrations – yay or nay?

1 points by astronautas 249 days ago | 4 comments

Two issues I observed with typical migration as a separate new SQL file approach:

* Views don't have alter, so have to be fully redefined. Any change to a view obscures actually changed parts of a view, since you have to fully redefine it.

* Duplicate change tracking with git and migration history. Can't we just use git for history?

E.g. https://atlasgo.io/. Totally non affiliated with them. Just had a shower idea, but saw it already being implemented (yay).

verdverm 248 days ago |

Intermixing tools and processes like that will create a bunch of pain. Even Atlas seems to realize this (probably from customer feedback) as they offer the "versioned" mode that looks exactly like what we do today.

https://atlasgo.io/versioned/intro

Note how this section of the docs is much longer than the declarative version. One of the hard parts about adopting something like this is that it's easy for new projects, but hard to start using in an existing project.

I've built something similar myself: https://hofstadter.io/getting-started/data-layer/

You always need escape latches when there are things you cannot define in your declarative (DSL). This makes things more mentally and contextually difficult, so most people opt for the simple and clearly understandable.

astronautas 248 days ago | |

indeed, I was thinking about the same. How do you even escape the hatch with e.g. Atlas if your migrations folder is autogenerated? What if you modify /migrations but not your entities?

verdverm 248 days ago | | |

you do what I do!

1. Generate the migrations and commit them to git

2. Have your codegen use diff3 so users can modify the generated files, or fill in details to partially generated code

3. Give users a way to indicate they did this so you don't break things later with codegen

Most often the gap is about indexes or wierd database quirks than fields on an entity.

Also, make sure you are generating the migrations AND the entity implementations in code from the same source-of-truth. Similar to how people commit the migration history to git, I have hof commit the data model history to git (and the codegen'd output without changes, double files are worth the bloat). By doing this, you can also generate the code that moves data between schema or api versions. (i.e. data lenses a la ink&switch)