Ask HN: Do you use database version control?
Do you use a database version control system ?
If you do;
Which product ? Why do you prefer it ? What are Pros/Cons ? How do you use it ? Do you run your ddl manually or let the tool do it ?
Thanks in advance.
5 comments
[ 60.0 ms ] story [ 37.1 ms ] threadThe reason we use Flyway is that we are developing on the JVM so it has support for Java, Scala and Groovy (which we are currently using) and eventually also Kotlin.
I suggest to use a version control tool when you are in greenfield projects and your database schema and data might change a lot.
Additionally it offers an easy way to control and keep track on how the database evolves. Imagine having to run SQL statements directly on the production environment without a clear way of keeping track of the changes.
On top you will have consistent changes that you can roll-out across your dev, qa and prod environments in case you have that set-up.
As for how we run it, the migration scripts are part of the project and when we release a new version we apply the migration scripts automatically when the artefact bootstraps.
From .NET land, the guys who wrote DbUp are a good example of a reasonably neat way of doing this. Check out their library.
I wrote a little tool for those of you who use ServiceStack, the approach is to spin up an application host that loads a list of db initialization and migration scripts to apply in sequence, scans your db for current version, and runs any scripts that have not yet been applied to your db.
This way, your db creation and migration is repeatable (I fire up all my test cases against a REAL db that i KNOW matches my production environment in structure), you can do various tricks to inject seed / test data along the way, you can get vastly greater comfort that your development and release will go smoother.
Its still unfortunately very light on readme documentation I just realised, but there is a test case which will demonstrate how a database can be created, and migrations, applied.
https://github.com/daleholborow/iayos.core.db.deploy/tree/ma...
Priorities in its design and why I prefer to use it: It lives where I work, in SSMS. It's as close to zero-friction as possible, so I'm not getting slowed down or sitting around waiting on it when I could be working. It commits SQL code to a subfolder right alongside the application code in Git/SVN.
Cons: Skips some of the more complex features of other apps in the space, like building migration scripts for deployment or rollback of changes. Also, the add-in is maybe a bit too zero-friction -- it's nice that it doesn't get in my way, but that makes it easy to neglect committing code altogether. Have a couple rough ideas about how to solve that one (suggestions welcome!)
[0] https://www.versionsql.com/