#database-migration

[82unpluggd]

Sort POCOs for Table Creation

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. 

Bear in mind that database schemas change over time. So, consider using code-first generation to verify database migrations.

[82unpluggd]

Object Serialization as Step Towards Normalization

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.

If, in the future, we need to query data we can use a database migration to transform it to a more normalized format. In some cases the POCO may not change much. We can take advantage of ServiceStack.OrmLite's [References] and [ForeignKey] relational attributes to read entities from the more normalized database.

[82unpluggd]

Using Code-First to Verify Migrations

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.

In a follow-up post I will share some technology & code examples.