Database Migrations

A synopsis of the different database migration methods that exist - both good and bad!

Transcript

1. Bringing the database schema up to date with a new code base
2. Backup the database in a dev environment – restore it in production The problem: Production data is completely overwritten by Dev data
3. Use a database schema synchronisation tool The problem: Can still be destructive
4. Script every change IF NOT EXISTS (…) Becomes a problem if a script undoes the changes of a previous script
5. Write scripts that check a version table Add a row to the version table if the script is successful The best approach, but scripts are difficult to write Transactions are tricky Rolling back is difficult
6. Library that provides an API for database changes Transactions and the version table is handled behind the scenes Provides forward and backward migration Libraries available in .NET • FluentMigrator • Migrator.NET
8. Always back up the database before a migration Rather restore the database than run the “Down” migration
9. …but don’t be “that guy”.