During development it is not uncommon for database tables to change design overtime, both in terms of add/remove/change of columns as the data model evolves. Unfortunately, doing a CreateTable if not existing, or DropCreate is not always an option … I coded a semi-smart AlterTable<T> to add new columns, but a long-term migration/versioning path is needed.
Any recommendations on how best to maintain versioned SQL update files (something like https://flywaydb.org) or ideally something that can leverage OrmLite’s awesome use of Db ext methods to create, etc.
In StackOverflow we used an in-house db framework where we ran external .sql scripts in a sqlmigrations folder and maintained the state of which scripts were called in a migrations RDBMS table. We’d then run a generic script which runs all non-executed sql scripts that don’t already exist in the migrations table. We’d run this script locally to update our local development RDBMS schema whilst the CI Server would run it automatically before deploying to UAT/Production so the scripts are always run (and RDBMS state kept in-sync with App) before deployment.
But for myself I’m currently maintaining all db migrations in a Unit test (ordered sequentially and alphabetically with naming convention prefix) which I run manually from VS.NET/R# with a flag property to indicate which RDBMS connection the migration scripts should be run against. I prefer writing migrations with C# and OrmLite since a lot of cases I’m usually doing more than just modifying the schema, e.g. adding/modifying/transforming existing data which I prefer to write in C# than SQL.
Eventually I’d like to formalize a proposed simple solution like the above that makes use of a migrations state table so you could rerun a generic command to execute all migrations and it would only run any new migrations (i.e. tetsts) in alphabetical order. I wouldn’t consider a 3rd party framework myself because I don’t feel any friction with my current solution that would justify introducing and learning a 3rd party tool. The noticeable thing any of the db migration solutions are missing is the ability to rollback a deploy where IMO the additional effort for maintaining compensating logic for each migration is not worth it for the few times you’d rollback deploys in production. It’s easier to just “push ahead” with a new deploy with a new migration script fixing any bugs/issues from the previous one.
We have used RoundhousE for maintaining our legacy database for the last 3 years and it works well. If you use functions, triggers, views and stored procedures extensively, this is a good solution. It differentiates between schema changes and changes to db objects, so you don’t need a new script file everytime you want to change a stored procedure.
For simpler db schemas RoundhousE would be overkill, I’d instead recommend DbUp which supports code-based scripts as well as SQL ones.
Both of these tools create a journal table to keep track of scripts run, just like the migrations table mythz described.
Thanks guys - I had come across DbUp too that I think is probably the basis of what we’ll go with, since it allows for a nice blend of raw SQL but also C#, so it should play nicely with OrmLite.
I agree with mythz that it is less about rolling back, and more about ease of moving forward. I have little intent to want to rollback to version X … way too tricky, but knowing exactly what to run to update to catch up - that’s far more beneficial.