Perhaps by now, you already know that you should be treating your database objects (tables, stored procs (if you’re still using those), triggers (tsk, tsk), keys, constraints, indexes, what have you, just like all your other code. They need to be versioned in a software version control system that can easily be tracked from one update to the next. Entity Framework Migrations help in this regard and if you have been doing .NET development for a while, you’re probably also familiar with DACPACs. But recently, I came across another project called RoundhousE that has been around for ever but one I clearly missed in my development journey, until now. It’s not flashy by any means but it does the job in an elegant way and has a few things going for it that I see as advantages over some of the others.
What is it?
RoundhousE is an open-source project developed out on GitHub, here. Simply put, it manages changes to your database. You point the tool at your database server. By looking at the migrations that you have specified, it will bring the database up to that migration level. You can start from scratch, that is, if no database is even there, you can have RoundhousE create it for you and then apply whatever changes on top of it. Alternatively, if you already have a well established database, you can use that as a baseline and then integrate RoundhousE to version your changes, moving forward. It was created by Rob Reynolds, aka @ferventcoder, also the creator of the awesome Chocolatey Package Manager for Windows. I’ve seen references to this tool as far back as 2009 on Google Code and 2011 on GitHub. It’s been around for a minute.
So What?
There are a few things that I really like about RoundhousE:
- It’s just SQL: There are no wrappers around the actual database Database Manipulation Language (DML) or Data Definition Language (DDL) or the actual Structured Query Language (SQL). EntityFramework for instance, generates a C# layer over the SQL layer to make its magic happen. In contrast, RoundhousE allows you to create plain old SQL files that you can test and tweak in the optimal database tooling of your choice and simply add those files to a specific folder structure (which you can also change with additional switches) and RoundHousE will start tracking them and applying the updates to your database accordingly. This is a big win for developers that have some familiarity with SQL. There is no additional Domain Specific Language (DSL) to cut through, to understand what the tool is doing. This will certainly speed up troubleshooting of issues. It is also friendly to others on the team such as Testers, Database Administrators and even analysts and product owners who often come with a SQL background.
- Support for all Major RDBMSs: As of this writing, you can use RoundhousE for managing SQL Server, Oracle, MySQL and PostgreSQL. It also supports Microsoft Access and SQLite.
- Cross Platform: dotnet is cross-plat and hence, RoundHousE is. You can use this dotnet global tool on Windows, Mac and Linux.
- Conventions are Straight Forward: There is very little friction in using this tool. You put SQL files in specific folders. It does its thing. Don’t like those folder structures? It allows you to change them. You just have to specify your changes as additional switches when invoking the tool.
- Automatic: Whatever tool you use for database versioning, it needs to be automatic. Manual steps introduce room for error. You use RoundhousE on the command line, as a dotnet global tool. That lends itself for automation. There is also a Docker image that you can use in your CI/CD pipelines.
- It’s Free: There are many flashy tools out there but with a pricing structure that makes it hard for even large enterprises to sign-off on. I won’t mention any names.
One Negative
One thing that I miss is the ability to see changes, historically in a straight-forward way. Say you have a table with 5 columns. In migration 12, you add 2 more columns. In Migration 17, you remove one. When looking at the latest migration, there is no way to easily see that evolution other than to scan through all the migrations or maybe perhaps address it with some clever naming conventions on your migrations. It’s not a deal breaker for me but may be it is for you.
Closing Remarks
I wondered why I didn’t stumble across this tool sooner. If you’re on the core team and reading this blog, an official website (in addition to the GitHub page) could be beneficial. I couldn’t find any step-by-step walkthroughs on YouTube or Pluralsight or elsewhere. Those may also help in evangelizing the product. And for the rest of us, please give this tool a spin, especially if you’re not versioning your database, at all. If you’re already using something else, it may still be worthwhile to try this out for the reasons I mentioned above.