Tuesday, January 31, 2017

Database Development The Iterative Way

As I recently became more involved in developing a data layer (including database) I've been learning a new style of database migration. Previously, I've written plain SQL for database migrations for each deployment. Those were handed off to a DBA to run in each environment.


These days, I've entered the realm of Liquibase. Liquibase is my first experience with this sort of tool that allows for source controlled, database versioning. It's written in declarative style and translates the declarations into the underlying SQL. It's cross-platform and supports multiple declarative languages (JSON, XML).


Here's how it's changed my development process: In the old days, I used to write the SQL and run each file or put it all in one file and run as a batch. Then write some queries to validate, interrogate the schema etc. I would do mostly up-front design of the database components. Most of the queries and commands were generated by EF or in code. Other factors have changed that as well.


Nowadays, I'm writing a lot more stored procedures with none of the sql in code or generated by code. I'm writing tests first. Then I'm writing stored Procs, then creating/modifying tables declaratively via Liquibase. Sometimes I don't care about the table of the column until it's really needed. Sometimes the name of a column morphs during an iteration (a short cycle of failing test-code-passing test on the order of minutes). Nowadays, no problem! It's relatively simple to change a column name.


The big trick/advantage is that everyone else can pull in those source controlled changes and have their local databases updated by running a batch file or using a git hook. It only applies changes that are new on the target database, so if they haven't updated in awhile it'll apply all those changes until they are up to date.


It's all good! I never really had a chance to dig into EF code-first migrations to this degree. If its the same or similar in efficiency, I would recommend it for sure! Or any tool that enabled rapid changes to the backing store which has acceptable trade-offs (here's looking at you noSQL).

No comments:

Post a Comment