Within Umbraco there are some really handy tools for creating tables from DTO object definitions, saving you having to explicitly define your DB table structures.

public class MyModel {
    public int Prop1 { get; set; }
    public int Prop2 { get; set; }
    public int Prop3 { get; set; }
}

_

...
Create.Table<MyModel>().Do();
...

These all work fine, until we start to think about migrations.

Lets say we are creating a new product, so our initial Migration will be to create our DB tables. For ease, we'll use the handy extensions that let you pass it a DTO object and create the table for you based on the object definition. Great, perfect, we have a DB table.

Now lets say we want to pre-populate that DB table with some data, so we create a new migration and do a bunch of insert statements to send the data through. Great, perfect, we now have data in our DB table.

db.Execute($"INSERT myModel (prop1, prop2, prop3) VALUES (1, 2, 3);");

Now, lets say a couple months down the road, we decide one of the properties on our object is no longer needed and so update our DTO and create a migration step to remove the column. Great, perfect, for anyone now upgrading the column will be removed and they will be in the desired state.

public class MyModel {
    public int Prop1 { get; set; }
    public int Prop2 { get; set; }
}

_

...
Delete.Column("Prop3").FromTable("myModel").Do();
...

However, we now have a problem because we've modified our DTO, this has effectively changed our original create db tables migration such that anyone installing a fresh will already be in the state where the removed property is not there. Now, migration 2 will fail because a column it expects to be there is no longer there.

Ultimately, by using this approach it can bring in inconsistencies between migrations, such that we can no longer predict the previous state of the system.

One approach would be to say not use the object type based DB creation methods and instead explicitly define your Schema like https://github.com/umbraco/Umbraco-CMS/blob/dev-v8/src/Umbraco.Core/Migrations/Upgrade/V80_0/AddContentNuTable.cs, however this is a bit more complex.

Create.Table("myModel")
  .WithColumn("Prop1").AsInt32().NotNullable()
  .WithColumn("Prop2").AsInt32().NotNullable()
  .WithColumn("Prop3").AsInt32().NotNullable()
  .Do();

The question then is can this be made to work in some way? Or should we be discouraging / removing the ability to create tables from an object type definition because it's just not scalable?

For reference, this discussion originated on twitter here https://twitter.com/mattbrailsford/status/1098669770809987077