Data Migrations on Production Database

Is there any way to have data migrations on production database not to be with SQL?

We are using MigratorDotNet and when we build a new funcionality for the application that changes the scheme of the database and we need to do some data updates we have to do this complex and troublesome SQL statements so the data is consistent on production.

Was wondering if there was another way to do this, what are the bests practices开发者_JAVA百科 to do this? Any ideas on other possible solutions?

We cannot use something like NHibernate because then we have to keep fixing old migrations when the scheme changes, and that can be error prone.

The trick is to use your migration tool and fold said data manipulation statements into the migrations. We use an extended version of the same thing typically for a few projects and it can definitely handle that trick.

If you're already using a migration tool like Migrator.NET then I'd say you're most of the way there. Complex schema/data changes are just a fact of life in the RDBMS world.

Try mite. It let's you do anything that you can do with sql and use sql to do it but have the ability to ensure your database is on the desired version and not risk executing a script that has already run (or miss a script), leaving your database in a consistent state.

If your developers adopt this. Deployments are a simple mite update and then you know problems are product related or data related (but not schema related).

https://github.com/soitgoes/mite

Let me know what you think. I developed this and have been using this with my team for years with great success.