Database Migration Tools

Narbase always looks for ways to improve the workflow and quality of their projects. Having been away from the core members of the team for a while, I found they made many changes making programming easier, cleaner, and more efficient. These include utilizing coroutines to get rid of the tangle of callbacks that plagued the codebase before, using MVVM and data binding to ease the use of ui elements in Android, and using exposed for database access from the server code. The last one was chosen in place of Jooq. But Jooq, despite its shortcomings, could easily be integrated with a migration tool named Flyway. Exposed on the other hand doesn’t seem to suggest any widely accepted migration tool, so it seems we should make one.
Database (Schema) migration tools are useful to manage the changes in the schema accross time in a way similar to how version control sytems like git manages the changes in the code. There are different interesting migrations systems imbedded in popular frameworks like Laravel and Django. There is also one in the Android Room persistence library. Looking at these tools I felt that the simplest decent form of our migration tool would be implementing migrations as objects implementing up and down abstract functions that uses any database abstraction (exposed in our case) to upgrade or downgrade the schema. The tool can hash a selected query (for mysql it can utilize the schema information table) to check whether the schema has been changed externally.

Generally this seems fine but I am not sure how to combine this with exposed where a lot of the schema changes occur in their own classes. Furthermore, there is already a tool (Harmonica) that does a lot of what we want but, in my opinion, doesn’t handle exposed in a satisfying way. Finally, since exposed schema changes happen in code, it might be possible to manage them to some extent using traditional vcs with tags for example. So, for example  developing a Django style migration tool to exposed might not be worth it.

I will give it further thought, discuss it with colleagues, and keep you posted.

Leave a Reply

Your email address will not be published. Required fields are marked *