#database-migration

If you are using a Code First approach to generate database tables, you may need to order the tables to avoid violating foreign key constraints.

The gist below shows how to use QuickGraph to sort your table definitions that are decorated with ServiceStack.OrmLite's ForeignKey attribute. 

ServiceStack.OrmLite refers to this as a "text blob" and it is by default how it handles complex data inside a table POCO. So, this is a trivial way to proceed with new data.

For example, we may have a database of food products and now we will be ingesting "Nutrition Facts" per item. Inserting a record as follows:

db.Insert( new FoodItem { Name = "Oatmeal", NutritionFacts = new NutritionFacts { ServingSize = 1, ServingSizeUnits = "cup", Calories = 158, TotalFat = 3.2m } });

Would yield a record with NutritionFacts serialized as compact JSON.

Use migrations to create & modify the database. Use code-first generation to verify your migrations.

Known as Code-First (or CF), specifying database tables using Plain Old C# Objects (POCOs) is elegant. It provides basic Object Relational Mapping and the classes can be reused in other ways. It is hard to let go of this feature.

But, schemas were made to be broken. So we use Migrations to stay Agile despite database changes.

Unfortunately CF-generation and Migrations do not work well together. Creating tables for the first time seems fine with CF, or if migrations have already been done. But, what if you need to apply migrations after CF creation? The Migrations framework will not know what version the deployed schema is on.

CF ensures that your classes match your database design. Migrations provide no such guarantee. CF is declarative (preferred), while Migrations are procedural.

Therefore, I recommend using CF generation in tests in order to verify migrations.